Home About Us SQL Interview Book Contact Us RSS
Articles
Tools
Code Snippets
Links
Tips & Tricks
FAQ
Resources
Articles
Code Snippets
Links
FAQ
Resources

Saravana Kumar
Vinod Kumar


Understanding SQL Performance Counters

SQL Server 2000 does a good job in optimizing using the present hardware setup. But more often than not it becomes important for DBA's to analyze how healthy SQL Server is from time to time. No wonder DBAs get paid really good all around the world.

In this article I would list some of the interesting counters you can ping to get the health on various parameters of SQL Server. These are not the exhaustive list and it is not possible to enumerate all in one go. Use Perfmon tools to get these parameters logged.

 

Performance Counter Name

Comments

1

SQLServer:Access Methods - Full Scans / sec

Value greater than 1 or 2 indicates that we are having table / Index page scans. We need to analyze how this can be avoided.

2

SQLServer:Access Methods - Page Splits/sec

Interesting counter that can lead us to our table / index design. This value needs to be low as possible.

3

SQLServer:Access Methods - Table Lock Escalations/sec

This gives us the number of times a table lock was asked for in a second. A high number needs a revisit to the query and the indexes on the table.

4

SQL Server:Buffer Manager - Buffer cache hit ratio

The percentage of pages that were found in the memory. Higher the value the better. Preferred around the 90% mark. This included the availability of the procedure and the data cache in the system.

5

SQL Server:Buffer Manager - Database pages

This number shows the number of pages that constitute the SQL data cache. A large changes in this value indicates the database is swapping cache values from the cache. We need to either increase the memory of the system or the max server memory parameter.

6

SQL Server:Buffer Manager - Procedure cache pages

This indicates the number of procedures are present in the cache. This is the location where the compiled queries are stored.

7

SQL Server:Buffer Manager - Stolen pages

This is the number of pages that were stolen from the buffer cache to satisfy other memory requests.

8

SQL Server:Cache Manager - Cache hit ratio

The ratio between the cache hits and misses. This counter is a good indicator of our caching mechanism in SQL Server. This value needs to be high in the system.

9

SQL Server:Databases - Active Transactions

The number of currently active transactions in the system.

10

SQL Server:Databases - Log growths

The number of times the log files have been extended. If there is lot of activity in this counter we need to allocate static and large enough space for our log files.

11

SQL Server:Databases - Transactions/sec

This number indicates how active our SQL Server system is. A higher value indicates more activity is occurring.

12

SQL Server:General Statistics - User Connections

The number of users currently connected to the SQL Server.

13

SQL Server:Locks - Lock Requests/sec

Number of requests for a type of lock per second.

14

SQL Server:Locks - Average Wait Time

This is the average wait time in milliseconds to acquire a lock. Lower the value the better it is.

15

SQL Server:Locks - Number of Deadlocks/sec

The number of lock requests that resulted in a deadlock.

16

SQL Server:Memory Manager - Optimizer Memory

The amount of memory in KB that the server is using for query optimization. We need to have a steady value in this counter. A large variation in the value suggests there is lot of Dynamic SQL is getting executed.

17

SQL Server:Memory Manager - Connection Memory

Amount of memory in KB used to maintain the connections.

18

SQL Server:SQL Statistics - SQL Compilations/sec

The number of times per second that SQL Server compilations have occurred. This value needs to be as low as possible.

19

SQL Server:SQL Statistics - SQL Re-Compilations/sec

This needs to be nil in our system as much as possible. A recompile can cause deadlocks and compile locks that are not compatible with any locking type.

 

 

 

* Other generic performance counters are not added to this list. This will include %Processor and Disk monitoring counters. Some of them have been outlined below.

 

20

Processor - %Processor Time

The percentage of time the processor spent executing a non-idle thread. This value is subtracted from the time the processor was idle 100 percent. This is an indicator to the overall CPU utilization in the system.

21

Processor - %Interrupt Time

The percentage of time the processor spent servicing hardware interrupts. This can be any activity on the server machine. This needs to be 0 while our testing process.

22

Processor - Processor Queue Length

This counter indicates the number of threads that are waiting in the processor queue. It can be also interpreted as the number of threads that are waiting to be run by the processor. If this value is greater than the number of processor then we have aCPU bottleneck in the system.

23

Processor - Context Switches/sec

This is an interesting counter. A typical context switch occurs when the OS or the application is forced to change the executing thread on one processor to another thread executed on another processor. This value has to be as small as possible. Context switches are not avoidable in multi-processor machines. Hence any value below 10000 is fine.

24

PhysicalDisk - %Disk Read Time

Time spent by the disk for read operation. We can have disk by disk analysis also to narrow down any read IO bottlenecks.

25

PhysicalDisk - %Disk Write Time

Time spent by the disk for write operation. We can have disk by disk analysis also to narrow down any write IO bottlenecks.

26

PhysicalDisk - Avg. Disk Queue Length

Average number of requests that waited for the read and write requests in the particular disk. A high value suggests we have a IO bottleneck.

27

Memory - Page Faults/sec

Total number of faulted pages handled by the processor per second. This value needs to as small as possible.

28

Memory - Pages/sec

The number of pages written to disk or from disk to resolve page faults. This would be the sum of page reads/sec and page writes/sec counter.

These are some of the performance counters I depend on while at my work for SQL Server 2000. These performance couters are of ultimate use in our every day activity for SQL Server health.