Has it ever happened to you that you have plenty of the CPU available event though there is a huge queue of Ruunable SPID? Just another day I worked on a consulting engagement Comprehensive Database Performance Health Check, and once we completed the task the server started to run amazingly fast. The reason was the server had lots of available CPU but there was another resource bottleneck which was making the server to underutilizing the CPU. Once we fixed this issue, we ended up on a very different issue where we had lots of runnable SPID and CPU was still underutilizing. Here is how we fixed the situation of SPID.
CPU Pressure – SPID
The first thing is to check if there is a CPU pressure or not. It is very simple to do so by running the query which I have listed in the following three blog posts.
- SQL SERVER – Measure CPU Pressure – Detect CPU Pressure
- SQL SERVER – Detecting CPU Pressure with Wait Statistics
- SQL SERVER – Changing Max Worker Count for Performance
Once we identify it had CPU pressure the next task was to look for the queries which are using lots of CPU and very little other resources.
Expensive Queries
Here is the blog post which lists all the expensive queries.
- SQL SERVER – Identify Most Resource Intensive Queries – SQL in Sixty Seconds #028 – Video
- SQL SERVER – SSMS: Top Queries by CPU and IO
- SQL SERVER – Activity Monitor – Active Expensive Queries
- SQL SERVER – Find Most Expensive Queries Using DMV
Once you identify which queries are creating the performance issues, the next step is to work on the queries to tune them. There are three things which I focus on when I have to start tuning the queries.
- Re-write the operator which is expensive
- Re-write the logic to use least IO
- Removal of indexes (I rarely create new indexes for a single query)
Once I complete the task, the next thing is to focus on server health.
Server Health Checks
Here is a brief list of activities which we will do for a performance health check:
- Index Analysis
- Index Optimization
- Index Maintenance
- Server/Instance Level Configuration Check
- I/O distribution Analysis
- SQL Server Resource Wait Stats Analysis
- TempDB Review
- Database Files (MDF, NDF) and Log File Inspection
- Log Reviews (Windows Event Logs, SQL Server Error Logs and Agent Logs)
- DBCC Best Practices Implementations
So far in most of the cases, when we reach the end of the list of the Server Health Check, the Runnable SPIDs will be decreased to next to nothing. In the rare case, we have to investigate further.
Reference: Pinal Dave (https://blog.sqlauthority.com)
