Wednesday, March 28, 2012
IA64 bit Performance vs X64 - Update statistics
s
4-way SQL EE x64, 9 GB RAM.
At the end of the day (just prior to midnight) I backup the prod database,
then run update statistics fullscan on 13 tables, 30% scan on 9 other tables
.
Elapsed time is 4.5 hrs.
I can restore the backup just taken on prod over to Dev, run the same update
statistics process from Prod on Dev and the elapsed time on Dev is 3.25 hour
s.
Any ideas that adequately explain the difference in elapsed time?
--
jl"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:FE1A33F1-2EA1-4FF6-8E5B-6D469E398BB9@.microsoft.com...
> Prod server is a 4-way IA64 running SQL EE(64-bit), 32 GB RAM. Dev server
> is
> 4-way SQL EE x64, 9 GB RAM.
> At the end of the day (just prior to midnight) I backup the prod
> database,
> then run update statistics fullscan on 13 tables, 30% scan on 9 other
> tables.
> Elapsed time is 4.5 hrs.
> I can restore the backup just taken on prod over to Dev, run the same
> update
> statistics process from Prod on Dev and the elapsed time on Dev is 3.25
> hours.
> Any ideas that adequately explain the difference in elapsed time?
Perhaps your dev server is just faster than your production server.
David|||I forgot to mention that the SQL 2000 version for both is SP4.
--
jl
"John L" wrote:
> Prod server is a 4-way IA64 running SQL EE(64-bit), 32 GB RAM. Dev server
is
> 4-way SQL EE x64, 9 GB RAM.
> At the end of the day (just prior to midnight) I backup the prod database
,
> then run update statistics fullscan on 13 tables, 30% scan on 9 other tabl
es.
> Elapsed time is 4.5 hrs.
> I can restore the backup just taken on prod over to Dev, run the same upda
te
> statistics process from Prod on Dev and the elapsed time on Dev is 3.25 ho
urs.
> Any ideas that adequately explain the difference in elapsed time?
> --
> jl|||Trying to have that make sense to mgt would be a challenge even though I
inherited these servers. The dev box does have faster CPUs than the prod
server. Regrettably mgt listened to someone's notion that the IA64 servers
were "high performers" Maybe that pitch was referring Oracle on Linux :-)
--
jl
"David Browne" wrote:
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:FE1A33F1-2EA1-4FF6-8E5B-6D469E398BB9@.microsoft.com...
> Perhaps your dev server is just faster than your production server.
> David
>
>|||Hi John,
x64 is definitely better than the IA64 processor. But, you cannot really
compare your production numbers to the development numbers because
Production usually has a lot more going on VS your Dev environment. Plus,
you should check your waitstats counters, filestats, and various other
perfmon counters to get a better picture on the difference in performance.
-Shiva
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:555B3999-CFF7-43BD-80E3-D85B863623E0@.microsoft.com...[vbcol=seagreen]
>I forgot to mention that the SQL 2000 version for both is SP4.
> --
> jl
>
> "John L" wrote:
>|||Shiva:
I might be guilty of some gross oversimplification here. I agree that
production servers theoretically have more going on with them but this an 8-
5
organization meaning that there is no user activity during the time frames i
n
which these maintenance activities occur (on either server). Ruling out
application related traffic generated by end-users, I am left with elapsed
job times. The IA 64 single core box (prod) runs at 4.5 hours ( replace the
single core CPUs with dual core IA 64s and the run time goes up to 6.25
hours), the EMT64T single core box (dev) runs in 3.25 hours.
The EMT64T's are 3600 Mhz CPUS, IA 64 single cores are 1500 Mhz, and the IA
64 dual cores are 1200 Mhz.
My hope with this post was that perhaps there was someone else out there who
had seen something the same or similar in an IA 64 environment. I am gettin
g
the feeling that there were not many who chose to go with SQL 2000 64-bit on
the IA 64 which may be why SQL 2005 is the only MS product certified for IA
64. I fully expect them to drop support for this architecture especially if
the marketplace doesn't ask for it.
There might be a very simple explanation for the observed phenonmenon and
that is simply CPU speed as David Browne has already hinted at.
jl
"Shiva" wrote:
> Hi John,
> x64 is definitely better than the IA64 processor. But, you cannot really
> compare your production numbers to the development numbers because
> Production usually has a lot more going on VS your Dev environment. Plus,
> you should check your waitstats counters, filestats, and various other
> perfmon counters to get a better picture on the difference in performance.
> -Shiva
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:555B3999-CFF7-43BD-80E3-D85B863623E0@.microsoft.com...
>
>|||Hi,
I don't think that updating statistics is the right load to test Itanium2
processor. Based on my experiences when you update statistics, only one
processor is used so it doesn't matter how many cores you have. There is
usually a lot of disk activity involved (depending on amount of RAM) so disk
subsistem has important role here.
I've been testing SQL2000 sp4 IA64 on Itanium2 and found out that when
running simple querries (basic selects...where...) Itanium2 is almost never
faster, usualy is 10-20% slower than Xeon, BUT when running more complex
querries like large complex joins, sorts on large amounts of data, Itanium
is always faster. More complex querry it is and more data is executing on,
faster it is comparing to Xeon. I think that Itanium is best suited for
running large reports, OLAP, complex calculations and so on, when X64 on
Xeons is faster for simple busines applications.
I've also noticed that processor parallelism is much better implemented on
IA64 than running the same load on SQL2000 sp4 x64 or x86. Using 2 Itaniums
relly made execution times for a lot of procedures almost twice as fast than
using one proccesor. When running the same load on Xeons, parallelism made
it actually slower.
I'm begining to test it on SQL2005. So far, a lot of procedures are faster
then on SQL2000.....
Those are my experiences so far...
Tom
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:12FCED74-D78D-4912-A81F-13732A833316@.microsoft.com...[vbcol=seagreen]
> Shiva:
> I might be guilty of some gross oversimplification here. I agree that
> production servers theoretically have more going on with them but this an
> 8-5
> organization meaning that there is no user activity during the time frames
> in
> which these maintenance activities occur (on either server). Ruling out
> application related traffic generated by end-users, I am left with elapsed
> job times. The IA 64 single core box (prod) runs at 4.5 hours ( replace
> the
> single core CPUs with dual core IA 64s and the run time goes up to 6.25
> hours), the EMT64T single core box (dev) runs in 3.25 hours.
> The EMT64T's are 3600 Mhz CPUS, IA 64 single cores are 1500 Mhz, and the
> IA
> 64 dual cores are 1200 Mhz.
> My hope with this post was that perhaps there was someone else out there
> who
> had seen something the same or similar in an IA 64 environment. I am
> getting
> the feeling that there were not many who chose to go with SQL 2000 64-bit
> on
> the IA 64 which may be why SQL 2005 is the only MS product certified for
> IA
> 64. I fully expect them to drop support for this architecture especially
> if
> the marketplace doesn't ask for it.
> There might be a very simple explanation for the observed phenonmenon and
> that is simply CPU speed as David Browne has already hinted at.
> --
> jl
>
> "Shiva" wrote:
>|||Tom:
A funny thing about our IA64 experience is that our h/w mgr jumped out and
bought dual core CPUs for the server. After installation the UPDATE STATS
took 1.25 hrs longer than with the single core IA64 CPUs.
Thanks for sharing your experience, this is exactly the sort of feedback
that I have been seeking.
jl
"Tom" wrote:
> Hi,
> I don't think that updating statistics is the right load to test Itanium2
> processor. Based on my experiences when you update statistics, only one
> processor is used so it doesn't matter how many cores you have. There is
> usually a lot of disk activity involved (depending on amount of RAM) so di
sk
> subsistem has important role here.
> I've been testing SQL2000 sp4 IA64 on Itanium2 and found out that when
> running simple querries (basic selects...where...) Itanium2 is almost nev
er
> faster, usualy is 10-20% slower than Xeon, BUT when running more complex
> querries like large complex joins, sorts on large amounts of data, Itanium
> is always faster. More complex querry it is and more data is executing on,
> faster it is comparing to Xeon. I think that Itanium is best suited for
> running large reports, OLAP, complex calculations and so on, when X64 on
> Xeons is faster for simple busines applications.
> I've also noticed that processor parallelism is much better implemented on
> IA64 than running the same load on SQL2000 sp4 x64 or x86. Using 2 Itanium
s
> relly made execution times for a lot of procedures almost twice as fast th
an
> using one proccesor. When running the same load on Xeons, parallelism made
> it actually slower.
> I'm begining to test it on SQL2005. So far, a lot of procedures are faster
> then on SQL2000.....
> Those are my experiences so far...
> Tom
>
>
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:12FCED74-D78D-4912-A81F-13732A833316@.microsoft.com...
>
>
IA64 bit Performance vs X64 - Update statistics
4-way SQL EE x64, 9 GB RAM.
At the end of the day (just prior to midnight) I backup the prod database,
then run update statistics fullscan on 13 tables, 30% scan on 9 other tables.
Elapsed time is 4.5 hrs.
I can restore the backup just taken on prod over to Dev, run the same update
statistics process from Prod on Dev and the elapsed time on Dev is 3.25 hours.
Any ideas that adequately explain the difference in elapsed time?
--
jl"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:FE1A33F1-2EA1-4FF6-8E5B-6D469E398BB9@.microsoft.com...
> Prod server is a 4-way IA64 running SQL EE(64-bit), 32 GB RAM. Dev server
> is
> 4-way SQL EE x64, 9 GB RAM.
> At the end of the day (just prior to midnight) I backup the prod
> database,
> then run update statistics fullscan on 13 tables, 30% scan on 9 other
> tables.
> Elapsed time is 4.5 hrs.
> I can restore the backup just taken on prod over to Dev, run the same
> update
> statistics process from Prod on Dev and the elapsed time on Dev is 3.25
> hours.
> Any ideas that adequately explain the difference in elapsed time?
Perhaps your dev server is just faster than your production server.
David|||I forgot to mention that the SQL 2000 version for both is SP4.
--
jl
"John L" wrote:
> Prod server is a 4-way IA64 running SQL EE(64-bit), 32 GB RAM. Dev server is
> 4-way SQL EE x64, 9 GB RAM.
> At the end of the day (just prior to midnight) I backup the prod database,
> then run update statistics fullscan on 13 tables, 30% scan on 9 other tables.
> Elapsed time is 4.5 hrs.
> I can restore the backup just taken on prod over to Dev, run the same update
> statistics process from Prod on Dev and the elapsed time on Dev is 3.25 hours.
> Any ideas that adequately explain the difference in elapsed time?
> --
> jl|||Trying to have that make sense to mgt would be a challenge even though I
inherited these servers. The dev box does have faster CPUs than the prod
server. Regrettably mgt listened to someone's notion that the IA64 servers
were "high performers" Maybe that pitch was referring Oracle on Linux :-)
--
jl
"David Browne" wrote:
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:FE1A33F1-2EA1-4FF6-8E5B-6D469E398BB9@.microsoft.com...
> > Prod server is a 4-way IA64 running SQL EE(64-bit), 32 GB RAM. Dev server
> > is
> > 4-way SQL EE x64, 9 GB RAM.
> >
> > At the end of the day (just prior to midnight) I backup the prod
> > database,
> > then run update statistics fullscan on 13 tables, 30% scan on 9 other
> > tables.
> > Elapsed time is 4.5 hrs.
> >
> > I can restore the backup just taken on prod over to Dev, run the same
> > update
> > statistics process from Prod on Dev and the elapsed time on Dev is 3.25
> > hours.
> >
> > Any ideas that adequately explain the difference in elapsed time?
> Perhaps your dev server is just faster than your production server.
> David
>
>|||Hi John,
x64 is definitely better than the IA64 processor. But, you cannot really
compare your production numbers to the development numbers because
Production usually has a lot more going on VS your Dev environment. Plus,
you should check your waitstats counters, filestats, and various other
perfmon counters to get a better picture on the difference in performance.
-Shiva
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:555B3999-CFF7-43BD-80E3-D85B863623E0@.microsoft.com...
>I forgot to mention that the SQL 2000 version for both is SP4.
> --
> jl
>
> "John L" wrote:
>> Prod server is a 4-way IA64 running SQL EE(64-bit), 32 GB RAM. Dev
>> server is
>> 4-way SQL EE x64, 9 GB RAM.
>> At the end of the day (just prior to midnight) I backup the prod
>> database,
>> then run update statistics fullscan on 13 tables, 30% scan on 9 other
>> tables.
>> Elapsed time is 4.5 hrs.
>> I can restore the backup just taken on prod over to Dev, run the same
>> update
>> statistics process from Prod on Dev and the elapsed time on Dev is 3.25
>> hours.
>> Any ideas that adequately explain the difference in elapsed time?
>> --
>> jl|||Shiva:
I might be guilty of some gross oversimplification here. I agree that
production servers theoretically have more going on with them but this an 8-5
organization meaning that there is no user activity during the time frames in
which these maintenance activities occur (on either server). Ruling out
application related traffic generated by end-users, I am left with elapsed
job times. The IA 64 single core box (prod) runs at 4.5 hours ( replace the
single core CPUs with dual core IA 64s and the run time goes up to 6.25
hours), the EMT64T single core box (dev) runs in 3.25 hours.
The EMT64T's are 3600 Mhz CPUS, IA 64 single cores are 1500 Mhz, and the IA
64 dual cores are 1200 Mhz.
My hope with this post was that perhaps there was someone else out there who
had seen something the same or similar in an IA 64 environment. I am getting
the feeling that there were not many who chose to go with SQL 2000 64-bit on
the IA 64 which may be why SQL 2005 is the only MS product certified for IA
64. I fully expect them to drop support for this architecture especially if
the marketplace doesn't ask for it.
There might be a very simple explanation for the observed phenonmenon and
that is simply CPU speed as David Browne has already hinted at.
--
jl
"Shiva" wrote:
> Hi John,
> x64 is definitely better than the IA64 processor. But, you cannot really
> compare your production numbers to the development numbers because
> Production usually has a lot more going on VS your Dev environment. Plus,
> you should check your waitstats counters, filestats, and various other
> perfmon counters to get a better picture on the difference in performance.
> -Shiva
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:555B3999-CFF7-43BD-80E3-D85B863623E0@.microsoft.com...
> >I forgot to mention that the SQL 2000 version for both is SP4.
> > --
> > jl
> >
> >
> > "John L" wrote:
> >
> >> Prod server is a 4-way IA64 running SQL EE(64-bit), 32 GB RAM. Dev
> >> server is
> >> 4-way SQL EE x64, 9 GB RAM.
> >>
> >> At the end of the day (just prior to midnight) I backup the prod
> >> database,
> >> then run update statistics fullscan on 13 tables, 30% scan on 9 other
> >> tables.
> >> Elapsed time is 4.5 hrs.
> >>
> >> I can restore the backup just taken on prod over to Dev, run the same
> >> update
> >> statistics process from Prod on Dev and the elapsed time on Dev is 3.25
> >> hours.
> >>
> >> Any ideas that adequately explain the difference in elapsed time?
> >> --
> >> jl
>
>|||Hi,
I don't think that updating statistics is the right load to test Itanium2
processor. Based on my experiences when you update statistics, only one
processor is used so it doesn't matter how many cores you have. There is
usually a lot of disk activity involved (depending on amount of RAM) so disk
subsistem has important role here.
I've been testing SQL2000 sp4 IA64 on Itanium2 and found out that when
running simple querries (basic selects...where...) Itanium2 is almost never
faster, usualy is 10-20% slower than Xeon, BUT when running more complex
querries like large complex joins, sorts on large amounts of data, Itanium
is always faster. More complex querry it is and more data is executing on,
faster it is comparing to Xeon. I think that Itanium is best suited for
running large reports, OLAP, complex calculations and so on, when X64 on
Xeons is faster for simple busines applications.
I've also noticed that processor parallelism is much better implemented on
IA64 than running the same load on SQL2000 sp4 x64 or x86. Using 2 Itaniums
relly made execution times for a lot of procedures almost twice as fast than
using one proccesor. When running the same load on Xeons, parallelism made
it actually slower.
I'm begining to test it on SQL2005. So far, a lot of procedures are faster
then on SQL2000.....
Those are my experiences so far...
Tom
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:12FCED74-D78D-4912-A81F-13732A833316@.microsoft.com...
> Shiva:
> I might be guilty of some gross oversimplification here. I agree that
> production servers theoretically have more going on with them but this an
> 8-5
> organization meaning that there is no user activity during the time frames
> in
> which these maintenance activities occur (on either server). Ruling out
> application related traffic generated by end-users, I am left with elapsed
> job times. The IA 64 single core box (prod) runs at 4.5 hours ( replace
> the
> single core CPUs with dual core IA 64s and the run time goes up to 6.25
> hours), the EMT64T single core box (dev) runs in 3.25 hours.
> The EMT64T's are 3600 Mhz CPUS, IA 64 single cores are 1500 Mhz, and the
> IA
> 64 dual cores are 1200 Mhz.
> My hope with this post was that perhaps there was someone else out there
> who
> had seen something the same or similar in an IA 64 environment. I am
> getting
> the feeling that there were not many who chose to go with SQL 2000 64-bit
> on
> the IA 64 which may be why SQL 2005 is the only MS product certified for
> IA
> 64. I fully expect them to drop support for this architecture especially
> if
> the marketplace doesn't ask for it.
> There might be a very simple explanation for the observed phenonmenon and
> that is simply CPU speed as David Browne has already hinted at.
> --
> jl
>
> "Shiva" wrote:
>> Hi John,
>> x64 is definitely better than the IA64 processor. But, you cannot really
>> compare your production numbers to the development numbers because
>> Production usually has a lot more going on VS your Dev environment. Plus,
>> you should check your waitstats counters, filestats, and various other
>> perfmon counters to get a better picture on the difference in
>> performance.
>> -Shiva
>> "John L" <JohnL@.discussions.microsoft.com> wrote in message
>> news:555B3999-CFF7-43BD-80E3-D85B863623E0@.microsoft.com...
>> >I forgot to mention that the SQL 2000 version for both is SP4.
>> > --
>> > jl
>> >
>> >
>> > "John L" wrote:
>> >
>> >> Prod server is a 4-way IA64 running SQL EE(64-bit), 32 GB RAM. Dev
>> >> server is
>> >> 4-way SQL EE x64, 9 GB RAM.
>> >>
>> >> At the end of the day (just prior to midnight) I backup the prod
>> >> database,
>> >> then run update statistics fullscan on 13 tables, 30% scan on 9 other
>> >> tables.
>> >> Elapsed time is 4.5 hrs.
>> >>
>> >> I can restore the backup just taken on prod over to Dev, run the same
>> >> update
>> >> statistics process from Prod on Dev and the elapsed time on Dev is
>> >> 3.25
>> >> hours.
>> >>
>> >> Any ideas that adequately explain the difference in elapsed time?
>> >> --
>> >> jl
>>|||Tom:
A funny thing about our IA64 experience is that our h/w mgr jumped out and
bought dual core CPUs for the server. After installation the UPDATE STATS
took 1.25 hrs longer than with the single core IA64 CPUs.
Thanks for sharing your experience, this is exactly the sort of feedback
that I have been seeking.
--
jl
"Tom" wrote:
> Hi,
> I don't think that updating statistics is the right load to test Itanium2
> processor. Based on my experiences when you update statistics, only one
> processor is used so it doesn't matter how many cores you have. There is
> usually a lot of disk activity involved (depending on amount of RAM) so disk
> subsistem has important role here.
> I've been testing SQL2000 sp4 IA64 on Itanium2 and found out that when
> running simple querries (basic selects...where...) Itanium2 is almost never
> faster, usualy is 10-20% slower than Xeon, BUT when running more complex
> querries like large complex joins, sorts on large amounts of data, Itanium
> is always faster. More complex querry it is and more data is executing on,
> faster it is comparing to Xeon. I think that Itanium is best suited for
> running large reports, OLAP, complex calculations and so on, when X64 on
> Xeons is faster for simple busines applications.
> I've also noticed that processor parallelism is much better implemented on
> IA64 than running the same load on SQL2000 sp4 x64 or x86. Using 2 Itaniums
> relly made execution times for a lot of procedures almost twice as fast than
> using one proccesor. When running the same load on Xeons, parallelism made
> it actually slower.
> I'm begining to test it on SQL2005. So far, a lot of procedures are faster
> then on SQL2000.....
> Those are my experiences so far...
> Tom
>
>
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:12FCED74-D78D-4912-A81F-13732A833316@.microsoft.com...
> > Shiva:
> >
> > I might be guilty of some gross oversimplification here. I agree that
> > production servers theoretically have more going on with them but this an
> > 8-5
> > organization meaning that there is no user activity during the time frames
> > in
> > which these maintenance activities occur (on either server). Ruling out
> > application related traffic generated by end-users, I am left with elapsed
> > job times. The IA 64 single core box (prod) runs at 4.5 hours ( replace
> > the
> > single core CPUs with dual core IA 64s and the run time goes up to 6.25
> > hours), the EMT64T single core box (dev) runs in 3.25 hours.
> >
> > The EMT64T's are 3600 Mhz CPUS, IA 64 single cores are 1500 Mhz, and the
> > IA
> > 64 dual cores are 1200 Mhz.
> >
> > My hope with this post was that perhaps there was someone else out there
> > who
> > had seen something the same or similar in an IA 64 environment. I am
> > getting
> > the feeling that there were not many who chose to go with SQL 2000 64-bit
> > on
> > the IA 64 which may be why SQL 2005 is the only MS product certified for
> > IA
> > 64. I fully expect them to drop support for this architecture especially
> > if
> > the marketplace doesn't ask for it.
> >
> > There might be a very simple explanation for the observed phenonmenon and
> > that is simply CPU speed as David Browne has already hinted at.
> >
> > --
> > jl
> >
> >
> > "Shiva" wrote:
> >
> >> Hi John,
> >>
> >> x64 is definitely better than the IA64 processor. But, you cannot really
> >> compare your production numbers to the development numbers because
> >> Production usually has a lot more going on VS your Dev environment. Plus,
> >> you should check your waitstats counters, filestats, and various other
> >> perfmon counters to get a better picture on the difference in
> >> performance.
> >>
> >> -Shiva
> >>
> >> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> >> news:555B3999-CFF7-43BD-80E3-D85B863623E0@.microsoft.com...
> >> >I forgot to mention that the SQL 2000 version for both is SP4.
> >> > --
> >> > jl
> >> >
> >> >
> >> > "John L" wrote:
> >> >
> >> >> Prod server is a 4-way IA64 running SQL EE(64-bit), 32 GB RAM. Dev
> >> >> server is
> >> >> 4-way SQL EE x64, 9 GB RAM.
> >> >>
> >> >> At the end of the day (just prior to midnight) I backup the prod
> >> >> database,
> >> >> then run update statistics fullscan on 13 tables, 30% scan on 9 other
> >> >> tables.
> >> >> Elapsed time is 4.5 hrs.
> >> >>
> >> >> I can restore the backup just taken on prod over to Dev, run the same
> >> >> update
> >> >> statistics process from Prod on Dev and the elapsed time on Dev is
> >> >> 3.25
> >> >> hours.
> >> >>
> >> >> Any ideas that adequately explain the difference in elapsed time?
> >> >> --
> >> >> jl
> >>
> >>
> >>
>
>sql
Monday, March 26, 2012
I wonder which fixes I should apply to the SQL Server.
As the subject mentioned, I don't know what to apply to the SQL Server 2000.
In Windows, there is 'Windows Update' which informs the administrators what
to apply.
But in SQL Server, there is no one like 'Windows Update'
Today I found that MS had released the new cumulative patch for SQL Server.
I read carefully documentation and found that this patch does not include
fixes for MDAC or OLAP.
It listed a list of fixes, and I read the documentation about the patches.
The document said that it would be included
in the future service packs.
I'm puzzled that if I should apply patches which had been released before
Service Pack 3.
What should I do?Kim,
>> I'm puzzled that if I should apply patches which had been released
before
> Service Pack 3.
No need.If you are going to apply SP3(or SP3a), that should suffice.For
security patches after that, you may need to read the documentation or use
suggestions by a tool called Baseline Security Analyzer.Here are more
details:
Microsoft Baseline Security Analyzer (MBSA) Version 1.1.1 Is Available
http://support.microsoft.com/?id=320454
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Kim, KeukTae" <zyuuzika@.korea.com> wrote in message
news:exBGVfgZDHA.1940@.TK2MSFTNGP10.phx.gbl...
> Hi, all.
> As the subject mentioned, I don't know what to apply to the SQL Server
2000.
> In Windows, there is 'Windows Update' which informs the administrators
what
> to apply.
> But in SQL Server, there is no one like 'Windows Update'
> Today I found that MS had released the new cumulative patch for SQL
Server.
> I read carefully documentation and found that this patch does not include
> fixes for MDAC or OLAP.
> It listed a list of fixes, and I read the documentation about the patches.
> The document said that it would be included
> in the future service packs.
> I'm puzzled that if I should apply patches which had been released before
> Service Pack 3.
> What should I do?
>
>
Friday, March 23, 2012
I want to transfer ONLY new records AND update any modified records from Oracle into SQL S
I want to transfer ONLY new records AND update any modified records
from Oracle into SQL Server using DTS. How should I go about it?
a) how do I use global variable to get max date.
Where and what DTS task should I use to complete the job? Data Driven
Query? Transform data task? How ? can u give me samples. Perhaps you
can email me the Demo Package as well.
b) so far, what I did was,
- I have datemodified field in my Oracle table so that I can compare
with datelastrun of my DTS package to get new records
- records in Oracle having datemodified >Max(datelastrun), and transfer
to SQL Server table.
Now, I am stuck as to where should I proceed - how can I transfer these
records?
Hope u can give me some lights. Thank you in advance.Hi,
Should could start trying the DTS Import wizard. You can start it form
the SQL Server Enterprise Manager. Select the Databases node in the
console window. Choose All tasks | Import data from the context menu. In
one of the steps you can choose to write a query to select the data. In
another step, you can choose to save the wizards action into a new
package. Later, you van view and modify the package. It is found in Data
Transformation Services node in the console window.
Allthough there may be a far better way to get a max date value, you
might consider storing the value in a settings table in the Oracle
schema, so you can include in in you queries.
Good luck,
Roel Schreurs
fhadlaq@.yahoo.com schreef:
> I need a little help here..
> I want to transfer ONLY new records AND update any modified records
> from Oracle into SQL Server using DTS. How should I go about it?
> a) how do I use global variable to get max date.
> Where and what DTS task should I use to complete the job? Data Driven
> Query? Transform data task? How ? can u give me samples. Perhaps you
> can email me the Demo Package as well.
>
> b) so far, what I did was,
> - I have datemodified field in my Oracle table so that I can compare
> with datelastrun of my DTS package to get new records
> - records in Oracle having datemodified >Max(datelastrun), and transfer
> to SQL Server table.
> Now, I am stuck as to where should I proceed - how can I transfer these
> records?
> Hope u can give me some lights. Thank you in advance.|||hi Roel ,
thank you for ur reply , i have created a DTS Package and and filled it
with queries and stuff :) , it is working fine now , thanks again :)
Roel Schreurs wrote:
> Hi,
> Should could start trying the DTS Import wizard. You can start it
form
> the SQL Server Enterprise Manager. Select the Databases node in the
> console window. Choose All tasks | Import data from the context menu.
In
> one of the steps you can choose to write a query to select the data.
In
> another step, you can choose to save the wizards action into a new
> package. Later, you van view and modify the package. It is found in
Data
> Transformation Services node in the console window.
> Allthough there may be a far better way to get a max date value, you
> might consider storing the value in a settings table in the Oracle
> schema, so you can include in in you queries.
> Good luck,
> Roel Schreurs
> fhadlaq@.yahoo.com schreef:
> > I need a little help here..
> > I want to transfer ONLY new records AND update any modified records
> > from Oracle into SQL Server using DTS. How should I go about it?
> > a) how do I use global variable to get max date.
> > Where and what DTS task should I use to complete the job? Data
Driven
> > Query? Transform data task? How ? can u give me samples. Perhaps
you
> > can email me the Demo Package as well.
> > b) so far, what I did was,
> > - I have datemodified field in my Oracle table so that I can
compare
> > with datelastrun of my DTS package to get new records
> > - records in Oracle having datemodified >Max(datelastrun), and
transfer
> > to SQL Server table.
> > Now, I am stuck as to where should I proceed - how can I transfer
these
> > records?
> > Hope u can give me some lights. Thank you in advance.sql
Monday, March 19, 2012
i want help to build a application that will load data in msolap
i am new to msolap , i want to make an application that will update data or
insert data in msolap.
Any link , help ,book,sample program will be helpful .
thanks in advance .
kjangirHere are 2 books
Microsoft(r) SQL Server(tm) 2000 Analysis Services Step by Step
(http://www.amazon.com/exec/obidos/A...link%5Fcode=xm2)
Professional SQL Server 2000 Data Warehousing with Analysis Services
(http://www.amazon.com/exec/obidos/A...link%5Fcode=xm2)
Also take a look at the MS OLAP newsgroup
(http://groups.google.com/group/micr...sqlserver.olap)
and the MS Datawarehouse newsgroup
(http://groups.google.com/group/micr...r.datawarehouse)
http://sqlservercode.blogspot.com/|||Which version of Analysis Services: 2000 or 2005?
When you say "update data or insert data", are you referring to:
.. loading source data into SQL Server fact tables
.. processing the dimensions/cubes from the fact tables
.. changing the underlying values stored in the cubes (write-back) ?
Best Practices for Using DTS for Business Intelligence Solutions
http://msdn.microsoft.com/library/d...ntbpwithdts.asp
Loading Data into the Data Warehouse Database
http://msdn.microsoft.com/library/d...r />
_88it.asp
Building and Processing Cubes
http://msdn.microsoft.com/library/e...>
ack_9xsx.asp
"kuldeep kumar" <kuldeepkumar@.discussions.microsoft.com> wrote in message
news:A19E2425-397D-419C-88C2-273F50C1801B@.microsoft.com...
> Hi ,
> i am new to msolap , i want to make an application that will update data
> or
> insert data in msolap.
> Any link , help ,book,sample program will be helpful .
> thanks in advance .
> kjangir
I WANNA UPDATE IMAGE
SET FOTO=(SELECT FOTO FROM #temp_FOTO WHERE NOID=TDAV.NOID)
Where FOTO is Image field. This syntax is error. How to Update Image field?If the comparison is based on a column with unique values ( the column noid
in your case ), you can use t-SQL update statement like:
UPDATE tdav
SET foto = t.foto
FROM #temp t
WHERE t.noid = tdav.noid ;
Anith
I update SqlExpress database but i don't see the records
Hi everybody! I 'm facing a strange problem and i hope someone could help me understand what i'm doing wrong.
The situation has as follows:
1. I make a new windows project in VB .Net 2005 and add a listbox to form1
2. With "Add new Item" from the solution explorer I add a new Sql Database from the templates to the project (e.g. MyDatabase1.mdf)
3. I add a table to the SQL Database e.g. Books - with 2 only fields (BookID and BookDescription) and I add some records to the table
4. I add a new DataSource to the project and I bind the LIstBox to the table Books.
5. I add two buttons to the project. Button1 adds a new book, Button2 gives me record by record the books in the table (e.g. 1-Vb for begginers, 2-.Net for begginers etc.)
6. I Run the project and I add a new record to the table through button1. With button2 I confirm the record is added to the table (3-Vb for me)
Till here, everything works fine...
7. I close the project. I open the table Books to see for the new record (VB for me). There is no record in the Database!
Any idea?
P.S. I've done all possible ways to add the new record (with tableadapter and with a SqlCommand). In both ways the record in runtime is added to the table and it can be confirmed.
But when I close the project and try to find it , there is no record at all!
Hi,
make sure you call the update method on your SQLDataAdapter. Otherwise the data won′t be updated though the Dataset you are working with a in its normal "disconnected" mode.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Thanks for the suggestion but of cource and i do call the update method. As I told you I can confirm that the records have been inserted.
The point is that I cannot see the records when I close the project
|||Why do you think you got a confirmation ?Monday, March 12, 2012
I ran a update quey by mistake
With out any BEGIN Transaction statement , i ran a update query.
I had to update just one record using the "Where field='abc'"
But i happened to miss that. Is there any chance of recovery? can i use the transaction logs to bring back the records before the update?
A quick answer is appreciated.
Sahel
Hi Sahel.
No way to get it back directly using native Sql tools/operations without performing some restore type operations.
Best way may be, if you have a standard backup structure in place, take a log backup immediately, then restore a copy of your database side-by-side with the existing db (using a different db name), and restore logs up to the point in time right before you ran the update statement (see the STOPAT option of the RESTORE LOG statement), then update the existing table with the values that exist in the table(s) from the restored database.
There are some 3rd party applications that you could consider researching and using, although these aren't a supported solution generally speaking, so be sure to clarify/understand that before using them (Lumigent Log Explorer is one for example).
HTH
Friday, March 9, 2012
I need to know the data changes in some table on a row level
Hello Guys
I have a table, contains some fileds
When update statment runs, I need to know which columns has been changed and to know the old and the new data for each row
simply i need to do the following in a trigger:
For each row ROW in tbl_Table
for each col COL in ROW
Save COL.oldValue and COL.newValue
I don't know how to do it by cursors and I don't want to use Cursors
If any one can help or provide a good advice, please help
Thanks and have a great day
Hi
It is very simply.
From Books Online:
"DML trigger statements use two special tables: the deleted table and the inserted tables. SQL Server 2005 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions."
More , look for "inserted tables" in Books Online
Gigi,
www.sqlserver.ro
|||The client has SQL Server 2000 installed and don't want to upgrade to SQL Server 2005|||
On the triggers use the following statements
Insert Into SomeLogTable Select Deleted.*,'Old Value' RowStatus From Deleted
Insert Into SomeLogTable Select Inserted.*,'New Value' RowStatus From Inserted
|||In addition to Mani, you should use the full qualified names rather than *. Be aware that triggers are executed for each DML statement not per row, the trigger is even fired if no row is affected:UPDATE SomeTable SET SomeCol = 1 WHERE 1 =2 --Will fire the trigger
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
The concepts (the deleted table and the inserted table) was in SQL 2000 too.
Gigi Ciubuc
www.sqlserver.ro
I need to know the data changes in some table on a row level
Hello Guys
I have a table, contains some fileds
When update statment runs, I need to know which columns has been changed and to know the old and the new data for each row
simply i need to do the following in a trigger:
For each row ROW in tbl_Table
for each col COL in ROW
Save COL.oldValue and COL.newValue
I don't know how to do it by cursors and I don't want to use Cursors
If any one can help or provide a good advice, please help
Thanks and have a great day
Hi
It is very simply.
From Books Online:
"DML trigger statements use two special tables: the deleted table and the inserted tables. SQL Server 2005 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions."
More , look for "inserted tables" in Books Online
Gigi,
www.sqlserver.ro
|||The client has SQL Server 2000 installed and don't want to upgrade to SQL Server 2005|||On the triggers use the following statements
Insert Into SomeLogTable Select Deleted.*,'Old Value' RowStatus From Deleted
Insert Into SomeLogTable Select Inserted.*,'New Value' RowStatus From Inserted
|||In addition to Mani, you should use the full qualified names rather than *. Be aware that triggers are executed for each DML statement not per row, the trigger is even fired if no row is affected:UPDATE SomeTable SET SomeCol = 1 WHERE 1 =2 --Will fire the trigger
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
The concepts (the deleted table and the inserted table) was in SQL 2000 too.
Gigi Ciubuc
www.sqlserver.ro
Wednesday, March 7, 2012
I need some help with update query
Ab:
You have "table1.field1, table2.field2, table3.field3" in your select list; you then say later, " ... I need to update the same tables and fields ..."
Understand, if you really are wanting to update multiple tables this cannot be done in a single update statement. Update statements can only operate on one table at a time. If you need to update three tables, you are going to need three separate updates -- each update statment corresponding to the particular table that is targeted for update. The update statements will tend to look something like
|||I actually figured this out, but I guess there is no way to update multiple tables in 1 update query? Why can selects use multiple tables but updates cant?update table1
set field1 = newValue1,
field2 = newValue2,
...
fieldN = newValueN
from table1
someKindOfJoin table2
on (joinConditionsFor_2)
someKindOfJoin tableN
on (joinConditionsFor_N)
where (whereConditions)
Friday, February 24, 2012
I need help with rolling back transaction
I have the foll statements that are calling a dts package. I deliberately
misspelt the table name on the last update statement but my transaction
doesnot rollback why the DTS is suppose to rollback
SET NOCOUNT ON
BEGIN TRANSACTION
Select @.doc_no = str_inv_no from dbo.arctlfil_sql
WITH (TABLOCKX )
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUT
IF @.hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN
END
-- DTSSQLServerStorageFlags :
-- DTSSQLStgFlag_Default = 0
-- DTSSQLStgFlag_UseTrustedConnection = 256
EXEC @.hr = sp_OAMethod @.oPKG,
'LoadFromSQLServer("", , "dts")',
NULL
IF @.hr <> 0
BEGIN
PRINT '*** Load Package failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN
END
.......
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
PRINT '*** Destroy Package failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN
END
UPDATE dbo.fil_sql1 <--here i mispelt the name (dbo.fil_sql)
SET str_inv_no = @.doc_no + 1
WHERE str_inv_no = @.doc_no
IF (@.@.error <> 0)
BEGIN
ROLLBACK TRANSACTION
END
COMMIT TRANSACTION
The DTS basically imports data from a textfile. The data is imported from
the text file even when I get the error
Server: Msg 208, Level 16, State 1, Line 64
Invalid object name 'dbo.fil_sql1' .
I want the entire procedure to be rolled back including the DTS. The
transaction is enabled on the DTS property.
Any ideas?Hi Chris
The DTS package is not being executed in the same context (it will have it's
own connection) and therefore will not be part of the transaction. Include
your update a a step in the package.
John
"Chris" wrote:
> Hi,
> I have the foll statements that are calling a dts package. I deliberately
> misspelt the table name on the last update statement but my transaction
> doesnot rollback why the DTS is suppose to rollback
>
>
> SET NOCOUNT ON
> BEGIN TRANSACTION
> Select @.doc_no = str_inv_no from dbo.arctlfil_sql
> WITH (TABLOCKX )
> EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUT
> IF @.hr <> 0
> BEGIN
> PRINT '*** Create Package object failed'
> EXEC sp_displayoaerrorinfo @.oPKG, @.hr
> RETURN
> END
> -- DTSSQLServerStorageFlags :
> -- DTSSQLStgFlag_Default = 0
> -- DTSSQLStgFlag_UseTrustedConnection = 256
> EXEC @.hr = sp_OAMethod @.oPKG,
> 'LoadFromSQLServer("", , "dts")',
> NULL
> IF @.hr <> 0
> BEGIN
> PRINT '*** Load Package failed'
> EXEC sp_displayoaerrorinfo @.oPKG, @.hr
> RETURN
> END
> .......
> EXEC @.hr = sp_OADestroy @.oPKG
> IF @.hr <> 0
> BEGIN
> PRINT '*** Destroy Package failed'
> EXEC sp_displayoaerrorinfo @.oPKG, @.hr
> RETURN
> END
>
> UPDATE dbo.fil_sql1 <--here i mispelt the name (dbo.fil_sql)
> SET str_inv_no = @.doc_no + 1
> WHERE str_inv_no = @.doc_no
> IF (@.@.error <> 0)
> BEGIN
> ROLLBACK TRANSACTION
> END
> COMMIT TRANSACTION
> The DTS basically imports data from a textfile. The data is imported from
> the text file even when I get the error
> Server: Msg 208, Level 16, State 1, Line 64
> Invalid object name 'dbo.fil_sql1' .
> I want the entire procedure to be rolled back including the DTS. The
> transaction is enabled on the DTS property.
> Any ideas?
>
Sunday, February 19, 2012
I need Help Urgent
Hi all!
I have an Application thats runs on my network Peoples have to Update their Client Apps to the newst version.
Most of Users are not well aware of computers.
I have a sinario to update the Client Application details bellow:
Provide a link e.g. Http://www.myserver.com/updates.aspx?action=getupdates
I want the client click the Link and my asp.net Page do the following
1- Compare the version of the Application on local machine With App.exe on server
2- Tell User if he / she needs Updates
3- Proceed according to the users Desion
Download without prompting the User
Install Update
Exit the web browser or redirct
Please Help me if any one have some directions or Working Examples.
Happy Coding
//////// Rashed
Look at the ClickOnce technology, it brings all the stuff out-of-the-box.
Jens K. Suessmeyer
http://www.sqlserver2005.de
I need Help
I need some help on SQL Server.
I need to create a trigger on the update of a particular field in a table.
This trigger has to perform some action after an hour since this field is updated.
Java has a sleep function, does SQL also have something like that.
Please (if ull can)Reply on my personal id.
parul.gulati@.india.birlasoft.com
Thanks and Regards,
ParulSQL Server has:
WAITFOR { DELAY 'time' | TIME 'time' }
The WAITFOR statement suspends the execution of a connection until either:
i) specified time interval has passed.
ii) specified time of day is reached.
Example:
WAITFOR DELAY '00:00:02'
However I would not use this within a trigger. Since a trigger is fired on a event (INSERT,UPDATE,DELETE), that event will also be suspend for an hour (your example), untill the trigger has completed.
If you need to perform some sort of action after an hour, look at:
1) use xp_cmdshell to call an external program which will sleep for the hour, then connect to the DB and perform the action.
2) Look at sp_OA procedures (sp_OACreate, sp_OAMethod, ...) which could be used to run a COM object program.
3) Have the trigger load a seperate table or set a flag indicating an action to be taken, then have a seperate stored procedure perform your work for you, the procedure could be scheduled using SQL Server's scheduler to run every 15 minutes or so.