help emergency: ram

Showing posts with label ram. Show all posts

Wednesday, March 28, 2012

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

I/O performance tuning

Hi
This is a long post, but all advice appreciated.
I configured a new HP DL380 G5 SQL 2000 Standard SP3 server, 4 GB RAM,
2 x quad core processors recently. It has a p800 RAID controller with
2 x Storageworks MSA 50 disk arrays.
It's used as a reporting server during the day and as a transaction
processing server at night. Only a handful of users use it to run
reports but they are heavy duty reports!
Anyhow, the server originally had 2 x RAID 1 disks for the OS and 6 x
146 GB disks in RAID 5 for everything else. Not ideal, but we were
pushed for time and needed the disk space.
Recently I reconfigured the disks by buying the new p800 controller
and the 2 storage arrays plus a bunch of disks.
The config below uses all but 8 disk slots.
This is how I had it. The Req. column is the space required:
OS - 2 x 72 GB 15k rpm, raid 1, 58% free space
Tempdb - 2 x 72 GB 15k rpm, raid 1, 30% free space
- After moving data around and restarting sql, tempdb shrunk to a
fraction of its size and hasn't grown much so it's nearer 16 GB now
than 50 GB.
So far, so good. Bit of a no brainer. Now for the user dbs and logs:
All log files including tempdb log
- 2 x 72 GB 15k rpm, raid 1, 46% free space
- Not ideal having the logs on one array but a budgetary
consideration.
Financial History mdf
- 4 x 146 GB 10k, raid 10, 58% free
- this db gets written to each night. This seems a good config to me,
though I've done no performance monitoring.
- the fact that its log file is on a raid 1 array with all other log
files may be an issue.
Reporting mdf
- 2 x 146 GB 10k, raid 1, 49% free
- as the name implies, this is a reporting database. It's created
from some of the other databases and de-normalised.
- the performance of this db is dire. Performance monitor today
showed the RAID 1 array is being hammered while CPU does nothing
much. This is essentially what happened before the upgrade. I
figured that the load needs spreading over more disks. I'm
considering buying an additional disk and creating a RAID 5 array.
Adhoc Reporting mdf
- 4 x 146 GB 10k, raid 10, 57% free
- Another reporting database used by a different group of people.
This one, like tempdb, shrunk hugely when I moved it to a different
array. It used to be 126 GB but is probably half that now.
- The main person who uses this hasn't commented that performance has
improved, but we've not generated any performance stats to see what's
going on.
UserDb1 mdf
- 2 x 146 GB 10k, raid 1, 61% free
- this is a copy of a userDb that resides on another server.
- it's backed up and restored to this reporting server each night in
order to build the reporting data. The data is also available for use
in reports during the day.
- it's not written to, but I went for raid 1 because it's around 50 GB
and restored from the network nightly. The restore times have got a
lot faster since it was moved off the single big raid 5 array.
UserDb2, UserDb3, etc mdfs
- 2 x 72 GB 15k, raid 1, 67% free
- this is a copy of various smaller userDbs that reside on other
servers.
- again, these dbs are backed up and restored to this reporting server
each night.
- a couple of very small ones (1 GB or less) are written to but the
larger ones (between 4 and 9 GB) aren't.
So, excluding the OS disks, I have 6 x 72 GB 15k rpm disks and 12 x
146 GB 10k disks. As of tomorrow I'll have 13 of the larger disks and
7 free slots.
Priority number 1 is to improve the performance of the Reporting db.
Today I moved the reporting mdf to the same array as the recently
shrunken Adhoc Reporting mdf because this array has a RAID 10 config.
I see this as a short term measure.
I have, as I've said, 8 slots free. I can justify the purchase of 8
more disks if I can more or less guarantee significant performance
benefits.
Would I be better off giving 3 of the db logs and tempdb their very
own raid 1 arrays? The log files are small and this strikes me as
rather wasteful.
How about going back to the future so to speak and opting for a raid 5
array with say 6 disks for the two reporting databases?
I could go for a RAID 10 array for the two reporting databases with
say 8 disks in total. Might this be a better option than having 2 x
RAID 10 arrays of 4 disks each because the data will be striped over
more disks?
Perhaps opting for RAID 5 for the dbs that aren't written to (3 dbs of
about 70 GB in total) makes sense? If a db is only read, does the log
file need to be on a separate array? As I said earlier, the reason I
didn't go down this track is that these dbs are restored from backup
nightly so hence I went for raid 10. The thing is, the restore is
over the network so presumably that will be the bottleneck?You are right - LONG post. Perhaps you would be . . . no, let me rephrase
that. You will DEFINITELY be better served by getting an expert to give you
a few hours to perhaps a few days of his/her time to help you determine the
optimal configuration for this system.
I will make one general comment. When it comes to I/O the number of
spindles is HUGELY important. For example, IIRC you have just ONE active
spindle (2 drives RAID1) serving up your tempdb. This will almost certainly
be a bottleneck, especially during reporting periods when hashes and sorts
and work tables come into play for the reporting queries. I just got called
into a client where they had partitioned a new SAN all to heck and back and
it was a total DOG. They tried to set things up without expert DBA advice
and they did a bunch of other things wrong too, like not using appropriate
RAID set stripe sizes, setting controller cache ratios optimally, sector
aligning the partitions, setting format size, etc, etc. Please listen to my
advice above - or you will probably be wasting a lot of money for suboptimal
performance.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net|||I would put all of the data files for the reporting databases on raid 10
with the most spindles possible. Before you go out and fill the other slots,
I would make sure the indexing is tuned for the reports. Add a step to do
this with your nightly restore. Also what is memory\Page Life expectancy
looking like? Having the pages in memory is going to be fast than whatever
disk configuration you come up with. Lastly, it will be best to test to find
the optimal setting based on your IO patterns and hardware but I would start
off with a 64K stripe size in the ACU, 128 diskpart offset and 64k nt
allocation unit.
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<pwelsh@.uk2.net> wrote in message
news:27b4b09d-1bf0-4870-a869-fe860fd0b998@.v4g2000hsf.googlegroups.com...
> Hi
> This is a long post, but all advice appreciated.
> I configured a new HP DL380 G5 SQL 2000 Standard SP3 server, 4 GB RAM,
> 2 x quad core processors recently. It has a p800 RAID controller with
> 2 x Storageworks MSA 50 disk arrays.
> It's used as a reporting server during the day and as a transaction
> processing server at night. Only a handful of users use it to run
> reports but they are heavy duty reports!
> Anyhow, the server originally had 2 x RAID 1 disks for the OS and 6 x
> 146 GB disks in RAID 5 for everything else. Not ideal, but we were
> pushed for time and needed the disk space.
> Recently I reconfigured the disks by buying the new p800 controller
> and the 2 storage arrays plus a bunch of disks.
> The config below uses all but 8 disk slots.
> This is how I had it. The Req. column is the space required:
> OS - 2 x 72 GB 15k rpm, raid 1, 58% free space
> Tempdb - 2 x 72 GB 15k rpm, raid 1, 30% free space
> - After moving data around and restarting sql, tempdb shrunk to a
> fraction of its size and hasn't grown much so it's nearer 16 GB now
> than 50 GB.
> So far, so good. Bit of a no brainer. Now for the user dbs and logs:
> All log files including tempdb log
> - 2 x 72 GB 15k rpm, raid 1, 46% free space
> - Not ideal having the logs on one array but a budgetary
> consideration.
> Financial History mdf
> - 4 x 146 GB 10k, raid 10, 58% free
> - this db gets written to each night. This seems a good config to me,
> though I've done no performance monitoring.
> - the fact that its log file is on a raid 1 array with all other log
> files may be an issue.
> Reporting mdf
> - 2 x 146 GB 10k, raid 1, 49% free
> - as the name implies, this is a reporting database. It's created
> from some of the other databases and de-normalised.
> - the performance of this db is dire. Performance monitor today
> showed the RAID 1 array is being hammered while CPU does nothing
> much. This is essentially what happened before the upgrade. I
> figured that the load needs spreading over more disks. I'm
> considering buying an additional disk and creating a RAID 5 array.
> Adhoc Reporting mdf
> - 4 x 146 GB 10k, raid 10, 57% free
> - Another reporting database used by a different group of people.
> This one, like tempdb, shrunk hugely when I moved it to a different
> array. It used to be 126 GB but is probably half that now.
> - The main person who uses this hasn't commented that performance has
> improved, but we've not generated any performance stats to see what's
> going on.
> UserDb1 mdf
> - 2 x 146 GB 10k, raid 1, 61% free
> - this is a copy of a userDb that resides on another server.
> - it's backed up and restored to this reporting server each night in
> order to build the reporting data. The data is also available for use
> in reports during the day.
> - it's not written to, but I went for raid 1 because it's around 50 GB
> and restored from the network nightly. The restore times have got a
> lot faster since it was moved off the single big raid 5 array.
> UserDb2, UserDb3, etc mdfs
> - 2 x 72 GB 15k, raid 1, 67% free
> - this is a copy of various smaller userDbs that reside on other
> servers.
> - again, these dbs are backed up and restored to this reporting server
> each night.
> - a couple of very small ones (1 GB or less) are written to but the
> larger ones (between 4 and 9 GB) aren't.
> So, excluding the OS disks, I have 6 x 72 GB 15k rpm disks and 12 x
> 146 GB 10k disks. As of tomorrow I'll have 13 of the larger disks and
> 7 free slots.
> Priority number 1 is to improve the performance of the Reporting db.
> Today I moved the reporting mdf to the same array as the recently
> shrunken Adhoc Reporting mdf because this array has a RAID 10 config.
> I see this as a short term measure.
> I have, as I've said, 8 slots free. I can justify the purchase of 8
> more disks if I can more or less guarantee significant performance
> benefits.
> Would I be better off giving 3 of the db logs and tempdb their very
> own raid 1 arrays? The log files are small and this strikes me as
> rather wasteful.
> How about going back to the future so to speak and opting for a raid 5
> array with say 6 disks for the two reporting databases?
> I could go for a RAID 10 array for the two reporting databases with
> say 8 disks in total. Might this be a better option than having 2 x
> RAID 10 arrays of 4 disks each because the data will be striped over
> more disks?
> Perhaps opting for RAID 5 for the dbs that aren't written to (3 dbs of
> about 70 GB in total) makes sense? If a db is only read, does the log
> file need to be on a separate array? As I said earlier, the reason I
> didn't go down this track is that these dbs are restored from backup
> nightly so hence I went for raid 10. The thing is, the restore is
> over the network so presumably that will be the bottleneck?|||Quad-core and SQL 2000'
It is likely your problem is too many competing databases for the 2 GB of
available memory. Monitor the page life expectancy and cache hit ratio.
First thought would be to spend any future money on 64 bit Windows and SQL
2005 then max out your RAM. The less you have to go to the disk for data,
the less spindles you will need...
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dw_perf_top10.mspx
<pwelsh@.uk2.net> wrote in message
news:27b4b09d-1bf0-4870-a869-fe860fd0b998@.v4g2000hsf.googlegroups.com...
> Hi
> This is a long post, but all advice appreciated.
> I configured a new HP DL380 G5 SQL 2000 Standard SP3 server, 4 GB RAM,
> 2 x quad core processors recently. It has a p800 RAID controller with
> 2 x Storageworks MSA 50 disk arrays.
> It's used as a reporting server during the day and as a transaction
> processing server at night. Only a handful of users use it to run
> reports but they are heavy duty reports!
> Anyhow, the server originally had 2 x RAID 1 disks for the OS and 6 x
> 146 GB disks in RAID 5 for everything else. Not ideal, but we were
> pushed for time and needed the disk space.
> Recently I reconfigured the disks by buying the new p800 controller
> and the 2 storage arrays plus a bunch of disks.
> The config below uses all but 8 disk slots.
> This is how I had it. The Req. column is the space required:
> OS - 2 x 72 GB 15k rpm, raid 1, 58% free space
> Tempdb - 2 x 72 GB 15k rpm, raid 1, 30% free space
> - After moving data around and restarting sql, tempdb shrunk to a
> fraction of its size and hasn't grown much so it's nearer 16 GB now
> than 50 GB.
> So far, so good. Bit of a no brainer. Now for the user dbs and logs:
> All log files including tempdb log
> - 2 x 72 GB 15k rpm, raid 1, 46% free space
> - Not ideal having the logs on one array but a budgetary
> consideration.
> Financial History mdf
> - 4 x 146 GB 10k, raid 10, 58% free
> - this db gets written to each night. This seems a good config to me,
> though I've done no performance monitoring.
> - the fact that its log file is on a raid 1 array with all other log
> files may be an issue.
> Reporting mdf
> - 2 x 146 GB 10k, raid 1, 49% free
> - as the name implies, this is a reporting database. It's created
> from some of the other databases and de-normalised.
> - the performance of this db is dire. Performance monitor today
> showed the RAID 1 array is being hammered while CPU does nothing
> much. This is essentially what happened before the upgrade. I
> figured that the load needs spreading over more disks. I'm
> considering buying an additional disk and creating a RAID 5 array.
> Adhoc Reporting mdf
> - 4 x 146 GB 10k, raid 10, 57% free
> - Another reporting database used by a different group of people.
> This one, like tempdb, shrunk hugely when I moved it to a different
> array. It used to be 126 GB but is probably half that now.
> - The main person who uses this hasn't commented that performance has
> improved, but we've not generated any performance stats to see what's
> going on.
> UserDb1 mdf
> - 2 x 146 GB 10k, raid 1, 61% free
> - this is a copy of a userDb that resides on another server.
> - it's backed up and restored to this reporting server each night in
> order to build the reporting data. The data is also available for use
> in reports during the day.
> - it's not written to, but I went for raid 1 because it's around 50 GB
> and restored from the network nightly. The restore times have got a
> lot faster since it was moved off the single big raid 5 array.
> UserDb2, UserDb3, etc mdfs
> - 2 x 72 GB 15k, raid 1, 67% free
> - this is a copy of various smaller userDbs that reside on other
> servers.
> - again, these dbs are backed up and restored to this reporting server
> each night.
> - a couple of very small ones (1 GB or less) are written to but the
> larger ones (between 4 and 9 GB) aren't.
> So, excluding the OS disks, I have 6 x 72 GB 15k rpm disks and 12 x
> 146 GB 10k disks. As of tomorrow I'll have 13 of the larger disks and
> 7 free slots.
> Priority number 1 is to improve the performance of the Reporting db.
> Today I moved the reporting mdf to the same array as the recently
> shrunken Adhoc Reporting mdf because this array has a RAID 10 config.
> I see this as a short term measure.
> I have, as I've said, 8 slots free. I can justify the purchase of 8
> more disks if I can more or less guarantee significant performance
> benefits.
> Would I be better off giving 3 of the db logs and tempdb their very
> own raid 1 arrays? The log files are small and this strikes me as
> rather wasteful.
> How about going back to the future so to speak and opting for a raid 5
> array with say 6 disks for the two reporting databases?
> I could go for a RAID 10 array for the two reporting databases with
> say 8 disks in total. Might this be a better option than having 2 x
> RAID 10 arrays of 4 disks each because the data will be striped over
> more disks?
> Perhaps opting for RAID 5 for the dbs that aren't written to (3 dbs of
> about 70 GB in total) makes sense? If a db is only read, does the log
> file need to be on a separate array? As I said earlier, the reason I
> didn't go down this track is that these dbs are restored from backup
> nightly so hence I went for raid 10. The thing is, the restore is
> over the network so presumably that will be the bottleneck?

I/O performance tuning

Hi
This is a long post, but all advice appreciated.
I configured a new HP DL380 G5 SQL 2000 Standard SP3 server, 4 GB RAM,
2 x quad core processors recently. It has a p800 RAID controller with
2 x Storageworks MSA 50 disk arrays.
It's used as a reporting server during the day and as a transaction
processing server at night. Only a handful of users use it to run
reports but they are heavy duty reports!
Anyhow, the server originally had 2 x RAID 1 disks for the OS and 6 x
146 GB disks in RAID 5 for everything else. Not ideal, but we were
pushed for time and needed the disk space.
Recently I reconfigured the disks by buying the new p800 controller
and the 2 storage arrays plus a bunch of disks.
The config below uses all but 8 disk slots.
This is how I had it. The Req. column is the space required:
OS - 2 x 72 GB 15k rpm, raid 1, 58% free space
Tempdb - 2 x 72 GB 15k rpm, raid 1, 30% free space
- After moving data around and restarting sql, tempdb shrunk to a
fraction of its size and hasn't grown much so it's nearer 16 GB now
than 50 GB.
So far, so good. Bit of a no brainer. Now for the user dbs and logs:
All log files including tempdb log
- 2 x 72 GB 15k rpm, raid 1, 46% free space
- Not ideal having the logs on one array but a budgetary
consideration.
Financial History mdf
- 4 x 146 GB 10k, raid 10, 58% free
- this db gets written to each night. This seems a good config to me,
though I've done no performance monitoring.
- the fact that its log file is on a raid 1 array with all other log
files may be an issue.
Reporting mdf
- 2 x 146 GB 10k, raid 1, 49% free
- as the name implies, this is a reporting database. It's created
from some of the other databases and de-normalised.
- the performance of this db is dire. Performance monitor today
showed the RAID 1 array is being hammered while CPU does nothing
much. This is essentially what happened before the upgrade. I
figured that the load needs spreading over more disks. I'm
considering buying an additional disk and creating a RAID 5 array.
Adhoc Reporting mdf
- 4 x 146 GB 10k, raid 10, 57% free
- Another reporting database used by a different group of people.
This one, like tempdb, shrunk hugely when I moved it to a different
array. It used to be 126 GB but is probably half that now.
- The main person who uses this hasn't commented that performance has
improved, but we've not generated any performance stats to see what's
going on.
UserDb1 mdf
- 2 x 146 GB 10k, raid 1, 61% free
- this is a copy of a userDb that resides on another server.
- it's backed up and restored to this reporting server each night in
order to build the reporting data. The data is also available for use
in reports during the day.
- it's not written to, but I went for raid 1 because it's around 50 GB
and restored from the network nightly. The restore times have got a
lot faster since it was moved off the single big raid 5 array.
UserDb2, UserDb3, etc mdfs
- 2 x 72 GB 15k, raid 1, 67% free
- this is a copy of various smaller userDbs that reside on other
servers.
- again, these dbs are backed up and restored to this reporting server
each night.
- a couple of very small ones (1 GB or less) are written to but the
larger ones (between 4 and 9 GB) aren't.
So, excluding the OS disks, I have 6 x 72 GB 15k rpm disks and 12 x
146 GB 10k disks. As of tomorrow I'll have 13 of the larger disks and
7 free slots.
Priority number 1 is to improve the performance of the Reporting db.
Today I moved the reporting mdf to the same array as the recently
shrunken Adhoc Reporting mdf because this array has a RAID 10 config.
I see this as a short term measure.
I have, as I've said, 8 slots free. I can justify the purchase of 8
more disks if I can more or less guarantee significant performance
benefits.
Would I be better off giving 3 of the db logs and tempdb their very
own raid 1 arrays? The log files are small and this strikes me as
rather wasteful.
How about going back to the future so to speak and opting for a raid 5
array with say 6 disks for the two reporting databases?
I could go for a RAID 10 array for the two reporting databases with
say 8 disks in total. Might this be a better option than having 2 x
RAID 10 arrays of 4 disks each because the data will be striped over
more disks?
Perhaps opting for RAID 5 for the dbs that aren't written to (3 dbs of
about 70 GB in total) makes sense? If a db is only read, does the log
file need to be on a separate array? As I said earlier, the reason I
didn't go down this track is that these dbs are restored from backup
nightly so hence I went for raid 10. The thing is, the restore is
over the network so presumably that will be the bottleneck?
You are right - LONG post. Perhaps you would be . . . no, let me rephrase
that. You will DEFINITELY be better served by getting an expert to give you
a few hours to perhaps a few days of his/her time to help you determine the
optimal configuration for this system.
I will make one general comment. When it comes to I/O the number of
spindles is HUGELY important. For example, IIRC you have just ONE active
spindle (2 drives RAID1) serving up your tempdb. This will almost certainly
be a bottleneck, especially during reporting periods when hashes and sorts
and work tables come into play for the reporting queries. I just got called
into a client where they had partitioned a new SAN all to heck and back and
it was a total DOG. They tried to set things up without expert DBA advice
and they did a bunch of other things wrong too, like not using appropriate
RAID set stripe sizes, setting controller cache ratios optimally, sector
aligning the partitions, setting format size, etc, etc. Please listen to my
advice above - or you will probably be wasting a lot of money for suboptimal
performance.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
|||I would put all of the data files for the reporting databases on raid 10
with the most spindles possible. Before you go out and fill the other slots,
I would make sure the indexing is tuned for the reports. Add a step to do
this with your nightly restore. Also what is memory\Page Life expectancy
looking like? Having the pages in memory is going to be fast than whatever
disk configuration you come up with. Lastly, it will be best to test to find
the optimal setting based on your IO patterns and hardware but I would start
off with a 64K stripe size in the ACU, 128 diskpart offset and 64k nt
allocation unit.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<pwelsh@.uk2.net> wrote in message
news:27b4b09d-1bf0-4870-a869-fe860fd0b998@.v4g2000hsf.googlegroups.com...
> Hi
> This is a long post, but all advice appreciated.
> I configured a new HP DL380 G5 SQL 2000 Standard SP3 server, 4 GB RAM,
> 2 x quad core processors recently. It has a p800 RAID controller with
> 2 x Storageworks MSA 50 disk arrays.
> It's used as a reporting server during the day and as a transaction
> processing server at night. Only a handful of users use it to run
> reports but they are heavy duty reports!
> Anyhow, the server originally had 2 x RAID 1 disks for the OS and 6 x
> 146 GB disks in RAID 5 for everything else. Not ideal, but we were
> pushed for time and needed the disk space.
> Recently I reconfigured the disks by buying the new p800 controller
> and the 2 storage arrays plus a bunch of disks.
> The config below uses all but 8 disk slots.
> This is how I had it. The Req. column is the space required:
> OS - 2 x 72 GB 15k rpm, raid 1, 58% free space
> Tempdb - 2 x 72 GB 15k rpm, raid 1, 30% free space
> - After moving data around and restarting sql, tempdb shrunk to a
> fraction of its size and hasn't grown much so it's nearer 16 GB now
> than 50 GB.
> So far, so good. Bit of a no brainer. Now for the user dbs and logs:
> All log files including tempdb log
> - 2 x 72 GB 15k rpm, raid 1, 46% free space
> - Not ideal having the logs on one array but a budgetary
> consideration.
> Financial History mdf
> - 4 x 146 GB 10k, raid 10, 58% free
> - this db gets written to each night. This seems a good config to me,
> though I've done no performance monitoring.
> - the fact that its log file is on a raid 1 array with all other log
> files may be an issue.
> Reporting mdf
> - 2 x 146 GB 10k, raid 1, 49% free
> - as the name implies, this is a reporting database. It's created
> from some of the other databases and de-normalised.
> - the performance of this db is dire. Performance monitor today
> showed the RAID 1 array is being hammered while CPU does nothing
> much. This is essentially what happened before the upgrade. I
> figured that the load needs spreading over more disks. I'm
> considering buying an additional disk and creating a RAID 5 array.
> Adhoc Reporting mdf
> - 4 x 146 GB 10k, raid 10, 57% free
> - Another reporting database used by a different group of people.
> This one, like tempdb, shrunk hugely when I moved it to a different
> array. It used to be 126 GB but is probably half that now.
> - The main person who uses this hasn't commented that performance has
> improved, but we've not generated any performance stats to see what's
> going on.
> UserDb1 mdf
> - 2 x 146 GB 10k, raid 1, 61% free
> - this is a copy of a userDb that resides on another server.
> - it's backed up and restored to this reporting server each night in
> order to build the reporting data. The data is also available for use
> in reports during the day.
> - it's not written to, but I went for raid 1 because it's around 50 GB
> and restored from the network nightly. The restore times have got a
> lot faster since it was moved off the single big raid 5 array.
> UserDb2, UserDb3, etc mdfs
> - 2 x 72 GB 15k, raid 1, 67% free
> - this is a copy of various smaller userDbs that reside on other
> servers.
> - again, these dbs are backed up and restored to this reporting server
> each night.
> - a couple of very small ones (1 GB or less) are written to but the
> larger ones (between 4 and 9 GB) aren't.
> So, excluding the OS disks, I have 6 x 72 GB 15k rpm disks and 12 x
> 146 GB 10k disks. As of tomorrow I'll have 13 of the larger disks and
> 7 free slots.
> Priority number 1 is to improve the performance of the Reporting db.
> Today I moved the reporting mdf to the same array as the recently
> shrunken Adhoc Reporting mdf because this array has a RAID 10 config.
> I see this as a short term measure.
> I have, as I've said, 8 slots free. I can justify the purchase of 8
> more disks if I can more or less guarantee significant performance
> benefits.
> Would I be better off giving 3 of the db logs and tempdb their very
> own raid 1 arrays? The log files are small and this strikes me as
> rather wasteful.
> How about going back to the future so to speak and opting for a raid 5
> array with say 6 disks for the two reporting databases?
> I could go for a RAID 10 array for the two reporting databases with
> say 8 disks in total. Might this be a better option than having 2 x
> RAID 10 arrays of 4 disks each because the data will be striped over
> more disks?
> Perhaps opting for RAID 5 for the dbs that aren't written to (3 dbs of
> about 70 GB in total) makes sense? If a db is only read, does the log
> file need to be on a separate array? As I said earlier, the reason I
> didn't go down this track is that these dbs are restored from backup
> nightly so hence I went for raid 10. The thing is, the restore is
> over the network so presumably that will be the bottleneck?
|||Quad-core and SQL 2000?
It is likely your problem is too many competing databases for the 2 GB of
available memory. Monitor the page life expectancy and cache hit ratio.
First thought would be to spend any future money on 64 bit Windows and SQL
2005 then max out your RAM. The less you have to go to the disk for data,
the less spindles you will need...
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dw_perf_top10.mspx
<pwelsh@.uk2.net> wrote in message
news:27b4b09d-1bf0-4870-a869-fe860fd0b998@.v4g2000hsf.googlegroups.com...
> Hi
> This is a long post, but all advice appreciated.
> I configured a new HP DL380 G5 SQL 2000 Standard SP3 server, 4 GB RAM,
> 2 x quad core processors recently. It has a p800 RAID controller with
> 2 x Storageworks MSA 50 disk arrays.
> It's used as a reporting server during the day and as a transaction
> processing server at night. Only a handful of users use it to run
> reports but they are heavy duty reports!
> Anyhow, the server originally had 2 x RAID 1 disks for the OS and 6 x
> 146 GB disks in RAID 5 for everything else. Not ideal, but we were
> pushed for time and needed the disk space.
> Recently I reconfigured the disks by buying the new p800 controller
> and the 2 storage arrays plus a bunch of disks.
> The config below uses all but 8 disk slots.
> This is how I had it. The Req. column is the space required:
> OS - 2 x 72 GB 15k rpm, raid 1, 58% free space
> Tempdb - 2 x 72 GB 15k rpm, raid 1, 30% free space
> - After moving data around and restarting sql, tempdb shrunk to a
> fraction of its size and hasn't grown much so it's nearer 16 GB now
> than 50 GB.
> So far, so good. Bit of a no brainer. Now for the user dbs and logs:
> All log files including tempdb log
> - 2 x 72 GB 15k rpm, raid 1, 46% free space
> - Not ideal having the logs on one array but a budgetary
> consideration.
> Financial History mdf
> - 4 x 146 GB 10k, raid 10, 58% free
> - this db gets written to each night. This seems a good config to me,
> though I've done no performance monitoring.
> - the fact that its log file is on a raid 1 array with all other log
> files may be an issue.
> Reporting mdf
> - 2 x 146 GB 10k, raid 1, 49% free
> - as the name implies, this is a reporting database. It's created
> from some of the other databases and de-normalised.
> - the performance of this db is dire. Performance monitor today
> showed the RAID 1 array is being hammered while CPU does nothing
> much. This is essentially what happened before the upgrade. I
> figured that the load needs spreading over more disks. I'm
> considering buying an additional disk and creating a RAID 5 array.
> Adhoc Reporting mdf
> - 4 x 146 GB 10k, raid 10, 57% free
> - Another reporting database used by a different group of people.
> This one, like tempdb, shrunk hugely when I moved it to a different
> array. It used to be 126 GB but is probably half that now.
> - The main person who uses this hasn't commented that performance has
> improved, but we've not generated any performance stats to see what's
> going on.
> UserDb1 mdf
> - 2 x 146 GB 10k, raid 1, 61% free
> - this is a copy of a userDb that resides on another server.
> - it's backed up and restored to this reporting server each night in
> order to build the reporting data. The data is also available for use
> in reports during the day.
> - it's not written to, but I went for raid 1 because it's around 50 GB
> and restored from the network nightly. The restore times have got a
> lot faster since it was moved off the single big raid 5 array.
> UserDb2, UserDb3, etc mdfs
> - 2 x 72 GB 15k, raid 1, 67% free
> - this is a copy of various smaller userDbs that reside on other
> servers.
> - again, these dbs are backed up and restored to this reporting server
> each night.
> - a couple of very small ones (1 GB or less) are written to but the
> larger ones (between 4 and 9 GB) aren't.
> So, excluding the OS disks, I have 6 x 72 GB 15k rpm disks and 12 x
> 146 GB 10k disks. As of tomorrow I'll have 13 of the larger disks and
> 7 free slots.
> Priority number 1 is to improve the performance of the Reporting db.
> Today I moved the reporting mdf to the same array as the recently
> shrunken Adhoc Reporting mdf because this array has a RAID 10 config.
> I see this as a short term measure.
> I have, as I've said, 8 slots free. I can justify the purchase of 8
> more disks if I can more or less guarantee significant performance
> benefits.
> Would I be better off giving 3 of the db logs and tempdb their very
> own raid 1 arrays? The log files are small and this strikes me as
> rather wasteful.
> How about going back to the future so to speak and opting for a raid 5
> array with say 6 disks for the two reporting databases?
> I could go for a RAID 10 array for the two reporting databases with
> say 8 disks in total. Might this be a better option than having 2 x
> RAID 10 arrays of 4 disks each because the data will be striped over
> more disks?
> Perhaps opting for RAID 5 for the dbs that aren't written to (3 dbs of
> about 70 GB in total) makes sense? If a db is only read, does the log
> file need to be on a separate array? As I said earlier, the reason I
> didn't go down this track is that these dbs are restored from backup
> nightly so hence I went for raid 10. The thing is, the restore is
> over the network so presumably that will be the bottleneck?

Wednesday, March 28, 2012

IA64 bit Performance vs X64 - Update statistics

IA64 bit Performance vs X64 - Update statistics

I/O performance tuning

I/O performance tuning

help emergency

Blog Archive

About Me