Key SQL performance counters – identify bottlenecks

I was planning for a long time to write this post but time is never enough and timing is always not the right one 🙂
This time I managed to gather all the info and present a simple to follow, brief and explanatory list of most important performance counters used in SQL monitoring. I came up with it, by studying a lot of other similar documents from important SQL professionals and adding personal insight on each of them.

Let’s begin by saying that these counters are helpful when are collected throughout the whole day with a refresh interval set at 1 minute. It can be done easily if you go to: Control Panel\Administrative Tools\Performance Monitor\Data Collector Sets\User defined. Right click and open: New–> Data Collector Set –> check on Create Manually –> check on Performance counters –> start adding performance counters.

Analyze Disk

1. Logical Disk

Disk Transfers/sec

- slow, needs attention : < (less than) than 80 I/O’s per second when
"Physical Disk\Avg. Disk sec/Transfer" is > (greater than) 25ms.
This may indicate too many virtual LUNs using the same physical disks on a SAN.

2. Physical Disk

Avg. Disk sec/Read – average time, in seconds, of a read of data to the disk

To determine the impact of excessive paging on disk activity,
multiply the values of the "Physical Disk\Avg. Disk sec/Transfer"
and "Memory\Pages/sec counters. If the product of these counters
exceeds 0.1, paging is taking more than 10 percent of disk access time,
which indicates that you need more RAM. If this occurs over a long
period, you probably need more memory.
However, "Page Life Expectancy" counter must be checked as well.

Disk Bytes/sec – total number of bytes sent to the disk (write) and retrieved from the disk (read) over a period of one second.
If the Perfmon capture interval is set for anything greater than one second, the average of the values captured is presented.

Disk Read Bytes/sec

Disk Write Bytes/sec

Disk Reads/sec

Disk Writes/sec

The above 5 are useful in analyzing any ups or downs for a certain period. It will help you know the nr of reads/writes in normal behavior and if a problem occurs you can link several values and see if the difference is notable.

Analyze Memory

1. Memory

Available MBytes – amount of physical memory available to processes running on the computer, in Megabytes

- low– < than 10% available
- very low – less than 5% available

Page file usage %

- good - < 70 %

Pages input/sec - the rate at which pages are read from disk to resolve hard page faults. Hard page faults occur when a process refers to a page in virtual memory that is not in its working set or elsewhere in physical memory, and must be retrieved from disk.

It should be = (Total amount of RAM in the server) - (RAM used by Windows Server)
- (RAM used by SQL Server) - (and any utilities you have running on the server).
If much lower, than it may be possible that you aren't allowing SQL Server to dynamically
allocate RAM, and instead have accidentally specified that SQL Server uses less RAM
than it should have for optimal performance. This is not a critical counter for memory
leak but it's worth looking at it.

3. SQL Server : Buffer Manager

Buffer cache hit ratio - how oftem SQL Server can get data from buffer rather than disk

- good - > 90 % OLAP systems
- good - > 95% OLTP systems

Free list stalls/sec - the frequency of requests for database buffer pages are suspended because there's no buffer available

Hint: Queries with missing indexes or too many rows requested will have a large number of logical reads and an increased CPU time.
For OLTP applications, optimize for more index searches and less scans (preferably, 1 full scan for every 1000 index searches). Index and table scans are expensive I/O operations.

Pages splits / sec - Occurs when a 8k page fills and must be split to a new 8k page

- fair - < 20 per 100 batch requests/sec

Hint: If higher, than you have a problem that can be solved by clustered index, good fill factor.
An increased fill factor helps to reduce page splits because there is more room in data pages before it fills up and a page split has to occur.

2. SQL Statistics

Batch requests/sec - shows you how busy is your server.

- busy server - > 1000
- very busy - > 3000

Hint: A typical 100 Mbs network card is only able to handle about 3000 batch requests per second. If you have a server that is this busy, you may need
to have two or more network cards, or go to a 1 Gbs network card.

SQL Compilations/Sec - The number of times per second that SQL Server compilations have occurred.

- good - as low as possible.
- bad - > 100

SQL ReCompilations/Sec - The number of times per second that SQL Server recompilations have occurred.

So basically these counters should give you an overall view if your system is low on CPU, Memory or Disk resources or if you should re-design some your queries, tables, indexes. The perfmon counters are a very good indication of how tuned is your system. However, these should be seen together with SQL statistics that can be gathered from the multitude of dynamic views available starting SQL 2005 + editions. In a next post I will write about those important SQL counters, how I prefer to call them. If you start collecting windows counters and SQL counters you should have no problem in identifying a certain bottleneck happened in a timeframe you where not at office or skipped the critical period and couldn't analyze on time everything.

2 Comments.

Your post is interesting, I’m now working on tuning and migration of an old Sql 2000/Win server 2003 .. migrating Dts to SSIS and in the meantime trying to optimize their execution .. there is a step that is creating 40 indexes on a table with 329k rows that is taking 3,5 hours to complete .. on a newer server (Sql 2008/Win server 2008) it takes approx 2 minutes .. I’ll try to use your suggestions to check the performances.

About me…

I am from Bucharest, Romania and I am working with SQL Servers since 2006. I have been working closely with all SQL versions starting with SQL Server 2000. I am a senior DBA at OpenSky company and spend my free time with my husband and my son.
See more about me and why I started this blog.