Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ajbuster
Starting Member
17 Posts |
 Posted - 2006-11-13 : 12:20:41
|
| Hello,I recently used PerfMon to monitor a SQL Server machine that showed the following stats over a 10 hour period (the most busy time for the SQL Server):Physical Disk: % Disk Time = 520 avg.Physical Disk: Avg. Disk Queue Length = 10 avg.Here are the specs of the machine:1. Windows 2003 Server2. SQL Server 2000 Standard3. 4 GB Memory4. 2 Xeon 3 GHz w/ Hyper-threading Processors5. 273 GB of storage for the SQL Server on a RAID 10 Array*2 Stripe Sets - 2 146 GB 10,000 RPM SCSI Drives making up each stripe set. The two stripe sets are then mirrored together to present 1 273 GB (Usable) drive.6. OS on a separate RAID 1 ArrayThe memory and processor counters all seemed normal and within range, but these Disk values seem quite high (especially according to http://www.sql-server-performance.com/sql_server_performance_audit2.asp). Anybody care to give some opinions on if these values are normal considering the disk arrangement or if there is a cause for concern here? Thanks in advance. |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2006-11-13 : 12:32:41
|
| It's hard to say based on just one flat number. I would do the following:1. Break down the disk queue length. Run the same test, but have read and write queue length listed seperately in profiler. Also, look at both the logical and physical. Were your memory and cache counters okay?2. Look at what was happening during the observation period. Did you have batch processes running? Were backups or restores running? What did the counters look like when you were not running batch or backups and restores?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-11-13 : 13:45:44
|
| Average Disk Queue Length of 10 over a period of 10 hours? That does seem a little high, but it shouldn't be TOO bad. Generally anything over 2 for sustained periods of time is not good, but 10 should just make your system seem a bit "slow" and not criple it.I agree with Derrick that you probably need to look into some other counters (read vs writes, SQL User Connections). You should see around 60-70% read and 30-40% write depending on your system. I like to look at the Disk Read Bytes per second and Disk Write Bytes per Second for this, but the averages should work just as well.How big is this database? Also, approximatly how many connections do your have open? Judging by your disk config, you are probably a bit short on spindles. For a 4 disk RAID 10 array, only 2 disks are actually "doing work" while the other two are performing the mirroring. Depending on your IO pattern and number of users, I suspect you'll be needing some more disks or possibly a reconfiguration of the disks you have. Since you are most likly doing way more reads than writes, you may be able to get away with changing your RAID 10 array to a RAID 5 array without adding more disks. This would give you higher read performance for the cost of a late night reconfiguring your RAID array. If you have the budget to add more disks, that would be a good upgrade path as well.I hope that helps!Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2006-11-13 : 14:37:33
|
| Adding.....When you break down the counters, also look at each LUN (drive letter). This should really help pin this to a specific area to focus on.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
ajbuster
Starting Member
17 Posts |
Posted - 2006-11-13 : 15:05:34
|
| Thanks for the insight and assistance! I'm gathering some more info (the DB and server are not where I am), but I am going to have them run a new PerfMon and break down the counters by disk. I'm also going to have them move the .ldf to the OS drive as currently both data and log are on the same RAID 10 partition.To answer some of these questions:* Memory and cache counters were fine. No processes like backups/restores were running, only the nightly connections to the remote locations that are dumping data up to HQ and downloading new info to their remote DB.* There were a max of 12 connections open, and an average of 9.* I'll check on adding more disks. This may or may not be an option.Thanks again! |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2006-11-13 : 18:49:09
|
| Remember by disk AND by read and write queue lengths.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-11-13 : 20:07:31
|
| Wait a sec, you've got 12 max connections and you've still got that high of a disk queue? That would lead me to belive that you've got some poorly performing queries. You probably want to run profiler and log that data to a table so you can find what the problem queries might be.Moving the TXLogs to the OS drive may help, but I can't imagine that would really solve your problem.How large are the databases in question?Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
|
|
|
|
|
Any hardware perfmon troubleshooters out there?!