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?