Top 10 SQL Server Counters for Monitoring SQL Server Performance

Do you have a list of SQL Server Counters you review when monitoring your SQL Server environment? Counters allow you a method to measure current performance, as well as performance over time. Identifying the metrics you like to use to measure SQL Server performance and collecting them over time gives you a quick and easy way to identify SQL Server problems, as well as graph your performance trend over time.

Below is my top 10 list of SQL Server counters in no particular order. For each counter I have described what it is, and in some cases I have described the ideal value of these counters. This list should give you a starting point for developing the metrics you want to use to measure database performance in your SQL Server environment.

1. SQLServer: Buffer Manager: Buffer cache hit ratio

The buffer cache hit ratio counter represents how often SQL Server is able to find data pages in its buffer cache when a query needs a data page. The higher this number the better, because it means SQL Server was able to get data for queries out of memory instead of reading from disk. You want this number to be as close to 100 as possible. Having this counter at 100 means that 100% of the time SQL Server has found the needed data pages in memory. A low buffer cache hit ratio could indicate a memory problem.

2. SQLServer: Buffer Manager: Page life expectancy

The page life expectancy counter measures how long pages stay in the buffer cache in seconds. The longer a page stays in memory, the more likely SQL Server will not need to read from disk to resolve a query. You should watch this counter over time to determine a baseline for what is normal in your database environment. Some say anything below 300 (or 5 minutes) means you might need additional memory.

3. SQLServer: SQL Statistics: Batch Requests/Sec

Batch Requests/Sec measures the number of batches SQL Server is receiving per second. This counter is a good indicator of how much activity is being processed by your SQL Server box. The higher the number, the more queries are being executed on your box. Like many counters, there is no single number that can be used universally to indicate your machine is too busy. Today’s machines are getting more and more powerful all the time and therefore can process more batch requests per second. You should review this counter over time to determine a baseline number for your environment.

4. SQLServer: SQL Statistics: SQL Compilations/Sec

The SQL Compilations/Sec measure the number of times SQL Server compiles an execution plan per second. Compiling an execution plan is a resource-intensive operation. Compilations/Sec should be compared with the number of Batch Requests/Sec to get an indication of whether or not complications might be hurting your performance. To do that, divide the number of batch requests by the number of compiles per second to give you a ratio of the number of batches executed per compile. Ideally you want to have one compile per every 10 batch requests.

5. SQLServer: SQL Statistics: SQL Re-Compilations/Sec

When the execution plan is invalidated due to some significant event, SQL Server will re-compile it. The Re-compilations/Sec counter measures the number of time a re-compile event was triggered per second. Re-compiles, like compiles, are expensive operations so you want to minimize the number of re-compiles. Ideally you want to keep this counter less than 10% of the number of Compilations/Sec.

6. SQLServer: General Statistics: User Connections

The user connections counter identifies the number of different users that are connected to SQL Server at the time the sample was taken. You need to watch this counter over time to understand your baseline user connection numbers. Once you have some idea of your high and low water marks during normal usage of your system, you can then look for times when this counter exceeds the high and low marks. If the value of this counter goes down and the load on the system is the same, then you might have a bottleneck that is not allowing your server to handle the normal load. Keep in mind though that this counter value might go down just because less people are using your SQL Server instance.

7. SQLServer: Locks: Lock Waits / Sec: _Total

In order for SQL Server to manage concurrent users on the system, SQL Server needs to lock resources from time to time. The lock waits per second counter tracks the number of times per second that SQL Server is not able to retain a lock right away for a resource. Ideally you don't want any request to wait for a lock. Therefore you want to keep this counter at zero, or close to zero at all times.

8. SQLServer: Access Methods: Page Splits / Sec

This counter measures the number of times SQL Server had to split a page when updating or inserting data per second. Page splits are expensive, and cause your table to perform more poorly due to fragmentation. Therefore, the fewer page splits you have the better your system will perform. Ideally this counter should be less than 20% of the batch requests per second.

9. SQLServer: General Statistic: Processes Block

The processes blocked counter identifies the number of blocked processes. When one process is blocking another process, the blocked process cannot move forward with its execution plan until the resource that is causing it to wait is freed up. Ideally you don't want to see any blocked processes. When processes are being blocked you should investigate.

10. SQLServer: Buffer Manager: Checkpoint Pages / Sec

The checkpoint pages per second counter measures the number of pages written to disk by a checkpoint operation. You should watch this counter over time to establish a baseline for your systems. Once a baseline value has been established you can watch this value to see if it is climbing. If this counter is climbing, it might mean you are running into memory pressures that are causing dirty pages to be flushed to disk more frequently than normal.

Top 10 performance issues with a Database

Here is a list of top 10 performance issues with a Database and their most probable solutions

Too many calls to the DB - There might be multiple trips to a single DB from various middleware components and any of the following scenarios will occur
1. More data is requested than necessary, primarily for faster rendering (but in the slowing down the entire performance )
2. Multiple applications requesting for the same data.
3. Multiple queries are executed which in the end return the same result
This kind of problem generally arises when there is too much object orientation. The key is to strike a balance between how many objects to create and what to put in each object. Object oriented programing may be good for maintenance, but it surely degrades performance if they are not handled correctly

Too much synchronization – Most developers tend to over-synchronize, large pieces of code are synchronized by writing even larger pieces of code. It is generally fine when there is low load, under high load, the performance of the application will definitely take a beating. How to determine if the application has sync issues. The easiest way (but not 100% fool proof) is to chart CPU time and Execution time
CPU Time – is the time spent on the CPU by the executed code
Execution time - This is the total time the method takes to execute. It includes all times including CPU, I/O, waiting to enter sync block, etc
Generally the gap between the two times gives the waiting time. If our trouble making method does not make an I/O call nor an external call, then it’s most probably a sync issue that is causing the slowness.

Joining too many tables – The worst kind of SQL issues creep up when too many tables are joined and the data is to be extracted from it. Sometimes it is just unfortunate that so many tables have to be necessarily joined to be able to pull out the necessary data.
There are two ways to attack this problem
1) Is it possible to denormalize a few tables to have more data?
2) Is it possible to create a summary table with most of the information that will be updated periodically?
Returning a large result set - Generally no user will go through thousands of records in the result set. Most users will generally limit to only the first few hundreds (or the first 3 -4 pages). By returning all the results, the developer is not only slowing the database but also chocking the network. Breaking the result set into batches (on the database side) will generally solve this issue (though not possible always)

Joining tables in the middleware – SQL is a fantastic language for data manipulation and retrieval. There is simply no need to move data to a middle tier and join tables there. Generally by joining data in the middle tier:
1. Unnecessary load on the network as it has to transport data back and forth
2. Increasing memory requirements on the application server to handle the extra load
3. Drop in server performance as the app tier is mainly held up with processing large queries
The best way to approach this problem is to user Inner and Outer joins right in the database itself. By this, the all the power of SQL and the database is utilized for processing the query.

Ad hock queries – just because SQL gives the privilege to create and use ad-hock queries, there is no point in abusing them. In quite a few cases it is seen that ad-hock queries create more mess than advantage they bring. The best way is to use stored procedures. This is not always possible. Sometimes it is necessary to use ad-hock queries, then there is no option but to use them, but whenever possible, it is recommended to use stored procedures. The main advantage with stored procedures is
1. Pre compiled and ready
2. Optimized by DB
3. Stored procedure in on the DB server, i.e. no network transmission of large SQL request.

Lack of indices – You see that the data is not large, yet the DB seems to be taking an abnormally long time to retrieve the results. The most possible cause for this problem could be lack of or misconfigured index. At first sight it might seem trivial, but when the data grows large, then it plays a significant role. There can be significant hit in performance if the indices are not configured properly.

Fill factor – One of the other things to consider along with index is fill factor. MSDN describes fill factor as a percentage that indicates how much the Database Engine should fill each index page during index creation or rebuild. The fill-factor setting applies only when the index is created or rebuilt. Why is this so important? If the fill factor is too high, if a new record is inserted and index rebuilt, then the DB will more often than not split the index (page splitting) into a new page. This is very resource intensive and causes fragmentation. On the other hand having a very low value for fill factor means that lots of space is reserved for index alone. The easiest way to overcome this is to look at the type of queries that come to the DB; if there are too many SELECT queries, then it is best to leave the default fill factor. On the other hand if there are lots of INSERT, UPDATE and DELETE operations, a nonzero fill factor other than 0 or 100 can be good for performance if the new data is evenly distributed throughout the table.

My Query was fine last week but it is slow this week?? – We get to see a lot of this. The load test ran fine last week but this week the search page is taking a long time. What is wrong with the database? The main issue could be that the execution plan (the way the query is going to get executed on the DB) has changed. The easiest way to get the current explain plan the explain plan for the previous week, compare them and look for the differences.

High CPU and Memory Utilization on the DB – There is a high CPU and high Memory utilization on the database server. There could be a multitude of possible reasons for this.
1. See if there are full table scans happening (soln: create index and update stats)
2. See if there is too much context switching (soln: increase the memory)
3. Look for memory leaks (in terms of tables not being freed even after their usage is complete) (soln: recode!)

There can be many more reasons, but there are the most common ones.

Low CPU and Memory utilization yet poor performance – This is also another case (though not frequent). The CPU and memory are optimally used yet the performance is still slow. The only reason why this can be is for two reasons:
1. Bad network – the database server is waiting for a socket read or write
2. Bad disk management – the database server is waiting for a disk controller to become free

As always these are only the most common database performance issues that might come up in any performance test. There are many more of them out there...

Connection Pooling

A cache of database connections maintained in the database's memory so that the connections can be reused when the database receives future requests for data.

Connection pools are used to enhance the performance of executing commands on a database. Opening and maintaining a database connection for each user, especially requests made to a dynamic database-driven Web application, is costly and wastes resources. In connection pooling, after a connection is created, it is placed in the pool and it is used over again so that a new connection does not have to be established. If all the connections are being used, a new connection is made and is added to the pool. Connection pooling also cuts down on the amount of time a user must wait to establish a connection to the database. 

Connection Pooling - what is it and why do we need it?

It's a technique to allow multiple clinets to make use of a cached set of shared and reusable connection objects providing access to a database. Connection Pooling feature is supported only on J2SDK 1.4 and later releases.

Opening/Closing database connections is an expensive process and hence connection pools improve the performance of execution of commands on a database for which we maintain connection objects in the pool. It facilitates reuse of the same connection object to serve a number of client requests. Every time a client request is received, the pool is searched for an available connection object and it's highly likely that it gets a free connection object. Otherwise, either the incoming requests are queued or a new connection object is created and added to the pool (depending upon how many connections are already there in the pool and how many the particular implementation and configuration can support). As soon as a request finishes using a connection object, the object is given back to the pool from where it's assigned to one of the queued requests (based on what scheduling algorithm the particular connection pool implementation follows for serving queued requests). Since most of the requests are served using existing connection objects only so the connection pooling approach brings down the average time required for the users to wait for establishing the connection to the database.

How is it used?

It's normally used in a web-based enterprise application where the application server handles the responsibilities of creating connection objects, adding them to the pool, assigning them to the incoming requests, taking the used connection objects back, returning them back to the pool, etc. When a dynamic web page of the web-based application explicitily creates a connection (using JDBC 2.0 pooling manager interfaces and calling getConnection() method on a PooledConnection object ... I'll discuss both the JDBC 1.0 and JDBC 2.0 approaches in a separate article) to the database and closes it after use then the application server internally gives a connection object from the pool itself on the execution of the statement which tries to create a connection (in this case it's called logical connection) and on execution of the statement which tries to close the connection, the application server simply returns the connection back to pool. Remember you can still use JDBC 1.0 / JDBC 2.0 APIs to obtain physical connections. This of course is used very rarely - probably in the cases where connection to that particular database is needed once in a while and maintaining a pool of connection is not really needed.

How many connections the Pool can handle? Who creates/releases them?

These days it's pretty configurable - the maximum connections, the minimum connections, the maximum number of idle connections, etc. these all parameters can be configured by the server administrator. On start up the server creates a fixed number (the configured minimum) of connection objects and adds them to the pool. Once all of these connection objects are exhausted by serving those many clinet requests then any extra request causes a new connection object to be created, added to the pool, and then to be assigned to server that extra request. This continues till the number of connection objects doesn't reach the configured maximum number of connection objects in the pool. The server keep on checking the number of idle connection objects as well and if it finds that there are more number of idle connection objects than the configured value of that parameter then the server simply closes the extra number of idle connections, which are subsequently garbage collected.

Traditional Connection Pooling vs Managed Connection Pooling

Connection Pooling is an open concept and its certainly not limited to the connection pooling we normally notice in the enterprise application i.e., the one managed by the Application Servers. Any application can use this concept and can manage it the way it wants. Connection Pooling simply means creating, managing, and maintaining connection objects in advance. A traditional application can do it manually, but as we can easily observe that as the scalability and reach of the application grows, it becomes more and more difficult to manage connections without having a defined and robust connection pooling mechanism. Otherwise it'll be extremely difficult to ensure the maintainability and availability of the connections and in turn the application.