Identify Performance Bottlenecks with SQL 2005 and 2008 Perfmon Counters
Identifying SQL Server performance bottlenecks can be quite a complicated process. With so many counters required to assess where potential bottlenecks exist – both within SQL Server, it’s databases and the underlying operating system, disk subsystem etc.
Below we’ve listed just a handful of SQL Server Perfmon counters which you can use to assess some of the more obvious performance hot-spots. For your convenience I’ve separated this into 2 categories – the hard way (enter in everything yourself in manually), and the easy way (below) which consists of both a .htm (Windows 2003) and .xml (Windows 2008+) file that you can use to import directly into performance monitor.
The hard way…
Memory (Available Mbytes) > 100MB
Paging File (%Usage) < 70%
Process (sqlservr) (%Privileged Time) < 30% of %Processor Time (sqlservr)
Processor (%Privileged Time) < 30% of Total %Processor Time
PhysicalDisk (Avg. Disk Sec/Read) < 8ms
PhysicalDisk (Avg. Disk sec/Write) < 8ms (non cached) < 1ms (cached)
SQLServer:Access Methods (Forwarded Records/sec) < 10 per 100 Batch Requests/Sec
SQLServer:Access Methods (FreeSpace Scans/sec) <10 per 100 Batch Requests/Sec
SQLServer:Access Methods (Full Scans / sec):
(Index Searches/sec)/(Full Scans/sec) > 1000
SQLServer:Access Methods (Workfiles Created/Sec) < 20 per 100 Batch Requests/Sec
SQLServer:Access Methods (Worktables Created/Sec) < 20 per 100 Batch Requests/Sec
SQL Server:Buffer Manager (Buffer Cache hit ratio) > 90%
SQL Server:Buffer Manager (Free list stalls/sec) < 2
SQL Server:Buffer Manager (Lazy Writes/Sec) < 20
SQL Server:Buffer Manager (Page Life Expectancy) > 300
SQLServer:Buffer Manager (Page lookups/sec):
(Page lookups/sec) / (Batch Requests/sec) < 100
SQL Server:Locks (Lock Requests/sec):
(Lock Request/sec)/(Batch Requests/sec) < 500
SQLServer:SQL Statistics (SQL Compilations/sec) < 10% of the number of Batch Requests/Sec
SQLServer:SQL Statistics (SQL Re-Compilations/sec) < 10% of the number of SQL Compilations
The easy way…
These counters will collect everything you’ll need to know about the performance of your SQL Servers. Once collected, you can review these yourself, or contact us to get a comprehensive analysis done on this for you.
For more information about Performance Tuning SQL Server, please visit our main website!