Post reply

Disk Performance Audit- important metrics??

  • 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

  • April 19, 2004 at 9:33 am

    #503366

    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

  • April 21, 2004 at 8:07 am

    #503645

    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

  • April 21, 2004 at 8:25 am

    #503649

    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

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

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