Disk Performance Audit- important metrics??

Question

Disk Performance Audit- important metrics??

rhunt

SSCertifiable

Points: 5868
More actions
April 19, 2004 at 8:32 am

#160812

Hello All,
I am trying to perform a performance audit on a SQL Server where I am positive my most likely bottle neck is the disks. I assume the primary metrics I should be interested in are %Disk Activity and Disk Queue Length, right?
What are the actually guidelines for these metrics? Also, Are there other perf mons I should be running against the disks?
Thanks In Advance.
Ryan Hunt

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

roberthamilton Ten Centuries Points: 1002 More actions · Answer 1

I find the Page Reads/Sec and Page Writes/Sec very useful - they're in the Buffer Manager list.

They report at the SQL read/write level, rather than on physical disk activity, so they're obviously showing you rather different data, but they can be great as an aid to spotting badly optimised queries.

Regards

Rob

beechpilot Right there with Babe Points: 779 More actions · Answer 2

In addition - if using the MS Performance Monitor - add "Physical Disk" and check:

Disk Queue - it should be zero or a low number. If this is *consistently* high, then you have operations in the queue waiting for the disk. The write and read queues can give you an idea as to what operations are "waiting".
Disk Transfer - This also should be low - if it's high, then you have some disk thrashing going on while data is being transferred to disk.
Reads/Writes for each Disk Volume: If activity is on one drive, and others have little or no activity, consider moving databases to another drive. If there is no available drive, consider adding one and moving one or more databases to the new drive - either will "even out" the disk access.
Another thing, if another drive is available, consider placing the log file on a separate drive from the data drive. This way, the log entries go to another drive, thus reducing the activity on the regular database drive.

We also use a 5-drive array for our databases (out of a 6-drive array - drive 0 is the boot drive - "Drive C" - drives 1-5 are "Drive D" for some databases, and anotehr 5-drive array (Drive E) for other databases. We keep corresponding logs on the opposite drive from the data (ie: DB1 data on "D", log is on "E".

Good Luck!

-- Joe

EdVassie SSC Guru Points: 60468 More actions · Answer 3

The System Monitor disk counters can give misleading results with SQL Server.

SQL continually monitors how long it takes for a disk read request to complete. If I-O time is slow, SQL will reduce the number of I-O requests per second. This increases the time required for your query in the same way as a long disk queue would slow down your query. However, because SQL has throttled the number of I-O requests your disk subsystem may not look under much pressure.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara