Check my blog post on TechNet over Performance Monitoring with SQL Server 2008 here . Feel free to pass your comments.
SQL Counter: Part 1 (Processor Time) SQL Counter: Part 2 (Processor Queue Length) SQL Counter: Part 3 (Pages/sec) SQL Counter: Part 4 (Available MB) SQL Counter: Part 5 (Disk Queue Length) SQL Counter: Part 6 (Idle Time) SQL Counter: Part 7 (Disk Write/sec) SQL Counter: Part 8 (Disk Read/sec) SQL Counter...
SQL Server Latches Object -> Average Latch Wait Time This counter measures the average amount of time, in milliseconds, that a latch request had to wait before it was serviced. Over time it is a good indicator for a general performance problem or if a performance issue is specific to one user.
SQL Server Locks Object -> Average Wait Time This counter measures the average amount of time, in milliseconds, that a user is waiting for a lock. Over time it is a good indicator for a general performance problem or if a performance issue is specific to one user. Locks are inevitable but a sometimes...
SQL Server Buffer Manager Object -> Buffer Cache Hit Ratio This counter shows the percentage of pages that are found in SQL Server’s buffer pool without having to incur a read from disk. A well-balanced system will have hit ratio values greater than 80%. The hit ratio ought to be 90% or better for...
SQL Server Database Object -> Transactions/Sec This counter measures the number of transactions started per second. Transactions are the basis of everything in SQL Server, and most queries are implicit transactions. This measurement is extremely handy for determining if the load has substantially...
SQL Server Access Methods Object -> Full Scans/Sec This counter should always be captured. It shows how often a table index is not being used and results in sequential I/O. This is defined as the number of unrestricted full scans. These can be either base table or full index scans. Missing or incorrect...
Network Interface Object -> Bytes Total/Sec This counter measures the number of bytes being transmitted per second on the NIC. There will be one instance per NIC on the machine, including one for the loopback adapter.
PhysicalDisk Object -> Avg. Disk Sec/Read This counter measures the average time, in seconds, of a read of data from disk. If the average value increases over time, then it points to a potential read performance problem. Possible reasons can be that the load has increased over time, the ratio of read...
PhysicalDisk Object -> Avg. Disk Sec/Write This counter measures the average time, in seconds, of a write of data to disk. If the average value increases over time, then it points to a potential write performance problem. Possible reasons can be that the load has increased over time, the device is...