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!