Earlier I wrote a blog post about SQL SERVER – Troubleshooting High CPU. I got lots of responses to this blog and people wanted me to create a video for this topic. Well, there you go, I have a video for CPU Running 100%.
In this video, I explain in three steps how you can identify which process is actually offending the CPU and making the CPU run 100%.
CPU Running 100%
- Step 1: Process Using CPU
- Step 2: Current Queries Using CPU
- Step 3: Historical Queries Using CPU
I have included the script of all three scripts in this blog post.
Once you find the culprit you can apply step 4: Fixing Query Performance. Here are few methods how you can fix poor performing queries.
- Wait statistics of the session
- Scheduler workload
- IO stalling queries
- Memory grant for session
- Blocking scenarios
- Optional Max degree of parallelism for query
- Execution plan operators consuming a lot of CPU
- Ad-hoc workload of the server
- Parameter sniffing configuration
- etc.
If you have any questions, you can always reach out to me on Twitter. Query tuning is not in the scope of this blog post, however, you are interested in this subject, you can watch my videos on YouTube about query tuning. If at any point in time, you are stuck or not able to move forward with your query tuning exercise, you can always reach out to me to help you. I usually answer very quickly to all the emails if I am not busy with Comprehensive Database Performance Health Check.
Reference:Â Pinal Dave (http://blog.SQLAuthority.com)
2 Comments. Leave new
Hi,We have a table around 30gb size with these many rows 97816380, During index rebuild which takes 40mins, Application always gets time out error during the duration of index rebuild,Is there a way we can improve index rebuild which can help us,Any suggestion will be very helpful.Thanks.
same here. index rebuilds can be very painful sometimes.