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

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)