SQL SERVER – Lots of Runnable SPID – What Next?

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.

SQL SERVER - Lots of Runnable SPID - What Next? runnablespid-800x390

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.

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.

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)

, , , ,
Previous Post
SQL SERVER – Power Options, Power Plans and Database Performance
Next Post
SQL SERVER – Removing Extra TempDB Files

Related Posts

Leave a Reply

Menu