| Author |
Topic |
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
 Posted - 2005-01-17 : 12:31:56
|
| I started up performance monitor on our main SQL Server to do some tuning.I added: LogicalDisk - % Disk TimePhysicalDisk - Avg. Disk Queue LengthProcessor - % Processor TimeWhile looking at the live feed of statistics I noticed that the % Disk Time was reading anywhere from 200 to 2400 and the Avg. Disk Queue Length was ranging from .001 to 47. The processor time was staying in low ranges from 1 to 60 for both processors.I'm very confused because the tuning books and sites talk about the disk queue target you want to shoot for is 2 and the % Disk Time I forget but I'm sure its not anywhere near 2400.The scale for % Disk Time is 1.000 and the scal for Avg. Disk Queue Length is 100.000. Both scales were default when added them into perfmon. Do I need to change the scale settings or is my I/O subsystem not handling some of the SQL requests?Thanks,Daniel |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-17 : 12:48:03
|
| % Disk Time will be multipled by the number of disks in a RAID array, so you need to divide the readings you get by that number. I don't know if Disk Queue Length is also affected by this, but I do know it can vary over a large range like what you've seen. It's not a huge concern unless you see sustained readings above the threshold, especially at times when you know the server is not being hammered. |
 |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-01-17 : 13:03:20
|
| Is the scale of 100.000 for the disk queue correct?Thanks. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-01-17 : 13:12:28
|
| It should be. I must say I'm not a perfmon guru, I'd recommend the standard place for such things:http://www.sql-server-performance.com/ |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-01-17 : 14:18:02
|
| With Disk Queue, you want it to be pretty flat, with some spikes to 2.0 or so. If you are seeing it stay above 2 for any sustained period of time, or seeing spikes as large as 47, you need to get more / faster disks.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-01-17 : 14:27:58
|
| What is your current disk setup?How many disks? What type of disks? What RAID Type?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-01-17 : 14:56:25
|
quote:
Originally posted by MichaelP
What is your current disk setup?How many disks? What type of disks? What RAID Type?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
I believe that array is five 10k rpm disks in a raid 10 setup. While continuing to watch the server I notice that when the processor hit 100% on both CPU's the %disktime shot up to 4800 and the diskqueue went up to 54; but they only stayed there for a brief second. I added in the diskwritequeue and diskreadqueue and noticed that about 97% of the activity was from the diskreadqueue.Thanks,DanielDanielSQL Server DBA |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-01-17 : 15:00:20
|
| five disk raid 1/0? That's not possible. RAID 1/0 arrays must be built on an even number of disks. Maybe you have 4 disks + 1 hot spare?Do you have your TX Logs and Data all on that one drive? What you probably need to do is move your TXLogs onto a RAID 1 set, and change your RAID 1/0 set to a RAID 5 and just put your Data files on it.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2005-01-17 : 15:15:17
|
quote:
Originally posted by MichaelP
five disk raid 1/0? That's not possible. RAID 1/0 arrays must be built on an even number of disks. Maybe you have 4 disks + 1 hot spare?Do you have your TX Logs and Data all on that one drive? What you probably need to do is move your TXLogs onto a RAID 1 set, and change your RAID 1/0 set to a RAID 5 and just put your Data files on it.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Yeah its 1 hot spare. There are two other arrays on that server. One for the O/S & backups (partitioned to C & D). I think that is just a standard mirror. Then the other array is the same as the one discribed above.I think the problem is that someone moved some production Access databases out to the data array and is using them there. LOL  .Regards,Daniel |
|
|
|
|
Performance tuning with perf mon?