System resource contention, bad database schema, bottle-necking, and excessive time procedures or queries are the major reasons that cause performance issues of an SQL server.
SQL server is one of the mostly used databases in all kinds of industries all around the globe. It is a very important responsibility of a database administrator to maintain the performance of SQL server professionally. The close monitoring of different critical and service affecting counters/parameters of SQL server not only increase the system up-time, efficiency and effectiveness but also chance to achieve the desired business goals of any organization.
The causes of the performance degradation of SQL server can be classified into three broad categories – bad configuration, shortage of resources and malfunctioning of different processes. Specifically speaking, we can say that the insufficient server resources, bad configuration, excessive query compilations/re-compilations, memory bottle-necking, bad execution plans and database schema designs, and CPU usage pressure can create service effecting impact on the performance of SQL server directly. A close monitoring of different parameters of SQL server can help diagnose and resolve the performance related issues very fast, and thus, help improve the performance of SQL server in an enterprise ecosystem.
The main counters of SQL – if monitored closely – can help improve its performance are given below with detailed description and their recommended range of values.
1. Buffer Cache Hit Ratio
This is a very critical counter in all databases including SQL database. It represents the rate of how often an SQL server can find the desired data page in the buffer cache rather than going to hard disk for the same. It is recommended to maintain a higher rate above 95% for better performance of the SQL server. It is closely related with the size of memory of the server. If the buffer cache hit ratio is less than recommended range, quickly increase RAM or check for other issues because performance starts degrading rapidly.
2. Batch Requests/Sec
The batch of requests per second counter is the number of batches handled by the SQL server in one second. It indicates how busy the SQL server CPU is. The value of this counter is arbitrary and depends on different parameters such as speed of the network link, capacity of the processor, and other server resources. Ideally, a normal SQL server with 100 Mb link can handle up to 3000 batch requests per second. So, you should monitor this counter very closely in relation with the resources of your server to get better insight.
3. Plan Cache Hit Ratio
This is a percentile ratio of the plan-cache use. A higher ratio indicates that your server is working efficiently and effectively without creating new plans for every incoming request; the lower ratio indicates that server is struggling to do more work than it is desired to do. So, try to find out the reason immediately and resolve the issue to increase the performance. This counter should also be analyzed in the light of plan cache reuse counter for a better perspective.
4. SQL Compilation/Sec
This counter indicates the number of times the execution plan has been compiled by the SQL server. It should always be kept as low as possible. The higher value indicates that there is a huge pressure on your server resources such as memory, processor and others. This ratio should also be compared with the batch request per second value for a deeper perspective. The rule of thumb is that each compilation should accomplish at least 10 batch requests. The higher ratio may also be indicative to the fact that adhoc queries are using resources excessively, and should be re-written those queries for a better performance.
5. Page Life Expectancy (Sec)
Page life expectancy is the duration (in seconds) of a data page to stay in the buffer cache. The value of this counter should be longer for the better performance of an SQL server. Many experts believe that any value of this counter less than 300 seconds is not good for server performance; but this value is not a standard one too. It is an arbitrary value that depends on existing server environment. There should be a close monitoring of this parameter to maintain the performance of the server.
6. Full Scans/Sec
The ‘full scans per second’ counter indicates the total number of scans made by server for database tables or indexes. A higher value of this counter may be the cause of missing indexes, requests for too many data records or very small data tables. Sudden increase in this value may be due to reaching of threshold value of indexes, or any other uneven condition. Meanwhile, de-fragmentation of indexes should also be done on a regular basis to improve the performance of the server.
7. Lock Waits/Sec
This counter pertains to the management of concurrent users in the SQL environment. The number of times per second the SQL server has to wait to lock resources for the request is called lock-waits/sec. Ideally, the value of this counter should be zero, because no request should wait for resource in an industry grade performance environment of SQL server. The lock wait time counter is another useful counter that can help you understand the lock waits per second much clearer. Any increase in this counter should immediately be addressed to keep the SQL performance high.
This counter is closely associated with the lock waits per second. The number of lock-waits per second that resulted in the deadlocks is called the deadlocks per second. It should be kept at zero ideally, but sometimes a smaller value (less than 1 per second) for a very short period may be okay, but it lasts for longer duration, then you should take immediate action.
9. Page Splits/Sec
SQL server splits the pages when it inserts or updates any page due to the overflowing of index pages. The number of page splits performed in a second is called page splits per second. It should be always kept low to maintain the high performance of database server because it requires huge resources for splitting a page for the insertion of data into the tables. This problem occurs due to the bad configuration of tables and indexes. To decrease page splits modify tables and indexes to lower non-sequential insertions of data into the pages. You can also use pad_index and fill factor to create more empty space.
10. Checkpoint Pages/Sec
The dirty pages are flushed back to disk by the checkpoint operation of an SQL server. This counter measures the total number of dirty pages flushed to disk in a single second. This is an arbitrary value and depends on different parameters especially on memory. It is recommended to maintain as low value as possible of this counter. Any abrupt increase in this value is indication of pressure on the memory use. So, always monitor this SQL parameter closely to maintain high performance of the server.
An automated SQL server monitoring does not only increases performance of your SQL server but also improves the business performance of your organization by achieving great customer satisfaction. To know more about an enterprise level SQL server monitoring service, click here.