Monitor SQL Performance

This content 7 years old. Please, read this page keeping its age in mind along with the fact technology changes fast and the information on this page me be outdated, not best practice, or plain wrong.

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.

Speak Your Mind

*

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