Monitor SQL Performance

Here is a list of Windows’ performance counters to use in monitoring performance of an SQL server.

Create Performance Collection rules targeted to a SQL Server computer group for the following performance counters:

  • LogicalDisk(*)Avg Disk sec/Read
    Should be under 20ms. Beyond 50ms is very bad
  • LogicalDisk(*)Avg Disk sec/Write
    Should be under 20ms. Beyond 50ms is very bad
  • LogicalDisk(*)Disk Read Bytes/sec
  • LogicalDisk(*)Disk Reads/sec
    The Reads and Read Bytes/sec counters can be used on conjunction with the Writes and Write Bytes/sec counters to see the ratio of Reads/Writes your database is doing. This will help determine the optimal RAID configuration to optimize for reads, writes, or a balance of both.
  • LogicalDisk(*)Disk Write Bytes/sec
  • LogicalDisk(*)Disk Writes/sec
  • MSSQL:Buffer MangerBuffer cache hit ratio
    This should be as close to 100% as possible. Below 97-98% indicates SQL server needs more physical memory. If you are much below that, the SQL Server needs more memory.
  • MSSQL:Buffer MangerPage Lookups/sec
  • MSSQL:Buffer MangerPage reads/sec
  • MSSQL:Buffer MangerPage writes/sec
    You can use this to see how many writes you are performing to your disk. Each Page is 8KB.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.