IA64 bit Performance vs X64 - Update statistics

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"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