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.