Managing SQL server performance can be a hectic task, considering the performance of the server depends on many things. It could be either hardware or software or both. So it’s best to have an active eye on these parameters to prevent any potential downgrade of the performance. The first step for resolving the performance problem with the SQL server is to determine the problem. Then find out the cause, and ultimately resolve the issue. The Database Administrators (DBAs) should also make sure to prevent those situations in future.
To further elaborate the process, let’s start with diagnosing the issue.
Detecting SQL performance problems
Before we start repairing the issue, we need to check for the symptoms like CPU, memory and network bottlenecks. It be slow execution of queries as well.
CPU bottlenecks
The problem is caused by insufficient hardware resource. It can be identified when the processor usage is at its peak for most of the time. In some cases increasing the capacity of the processor or upgrading it might repair sql server performance issue. But that might not be the case always. Sometimes optimizing the query statements and process flow can help to solve the issue. For SQL server it’s also recommended to a have dedicated server without running any other software in that.
Memory bottleneck
It can be identified by slow response of the application. In some cases it could cause an application crash as well owing to insufficient memory problem. To resolve this issue it’s recommended to check which application is consuming most of the memory. If any process is blocking some other process to execute. This problem can be fixed by tuning the queries, reconfiguring the memory allocation. In some cases increasing the physical memory will be helpful as well.
Network bottlenecks
This issue is sometimes trickier to identify. As in first instance it may seem like an issue with the SQL server, but in reality, it could be slow network connection or missing packets. Make sure to check the network ping to identify and resolve the issue.
Slow execution of queries
It can be caused by various factors like not indexing the database, poor execution plans, not well-designed schema design, etc.
One of the method to optimize and maintain the SQL server performance is monitoring databases. To keep a record of the performance metrics for all possible actions. This will provide us with a good idea to identify and separate the process. One that takes longer time to execute and then we would be able to debug and repair sql server. Let’s discuss in details.
Monitoring performance
Our first index to follow is % Processor Time which could be found under Performance Monitor. A constant high % like 85% signals that the processor is under pressure. The processor pressure can further be identified by parameters such as
- Batch Requests/sec
- SQL Compilations/sec
- SQL Recompilations/sec
These counters are available in Performance Monitor and in the sys.dm_os_performance_counters view.
The following example returns all performance counters that display snapshot counter values.
SELECT object_name, counter_name, instance_name, cntr_value, cntr_type FROM sys.dm_os_performance_counters WHERE cntr_type = 65792 OR cntr_type = 272696320 OR cntr_type = 537003264;
Ideally, the Batch Requests/sec value should be under 1000.
SQL Compilations/sec should be less than 10% of the Batch Requests/sec.
And Re-Compilations/sec should be less than 10% of the Compilations/sec.
Memory Available KB can help us to identify the memory problems in the SQL server. Recommended value is over 200MB. If it’s lower than 100MB then, it indicates low memory of the server.
Pages/sec shows the rate at which the pages are written from disk to RAM and read from RAM to disk. Recommended value is less than 50. Higher value can cause SQL Server performance degradation.
Lazy writes/sec value should be less than 20. If the free space in the buffer cache between two checkpoints is low, a lazy write will occur to flush the pages from buffer.
Page Life Expectancy counter can be handy when Lazy writes/sec constantly crosses the threshold. Ideally values less than 300 seconds points to pressure on memory.
Buffer Cache Hit Ratio recommended value is over 90. It shows the ratio of the data pages found and read from the SQL Server buffer cache.
Conclusion
There are many factors that could cause SQL Server performance. So before we can start repairing sql server, we need to know how to diagnose. What are the ideal values for the performance counters and to choose a tool that can give enough data for analyzing and troubleshooting the issues.
