SQL Server 100 cpu diagnose

17 April,2017 by Jack Vamvas

Question: Users are reporting long delays in query responses. I checked on the server and noticed CPU counter at 100 % on Performance Monitor. There are 4 cores associated to this server and all the cores were hovering around 100%. How can I diagnose the underlying cause ?

  100_cpu

Answer: It’s a common problem faced by DBAs , where the server is sitting at 100% for prolonged periods.

Check there are no other processes outside of SQL Server which are causing the server to be at 100%. Typically security scans, virus scans and monitoring can be root causes. I have also seen external pressure from outside of the VM where the underlying host has overcommitted on CPU and this caused underlying CPU to seriously constrained. If you identify any processes which may be causing the issue, work with the administrators to clear this issue

 Once you are confident any underlying causes are resolved , you are left with checking SQL Server. Maintain an open –mind when analysing the problem. It is possible the problem is not one long running query , but it may be thousands of very short queries with a very high execution count

       > First focus on fixing the problem – allowing users to get a quicker response time, giving you some breathing space . There are multiple methods to identify queries causing high CPU . Try to understand the context of the problem - is it a Datawarehouse server with lots of long running queries or is it an OLTP server with thousands of small queries?    

                                   CPU 100% - Check for Parallel Queries

                                  Performance Top Queries by Total CPU

                                  Current queries executing on SQL Server

                                  How to find Longest running queries in SQL Server     

                                 How to troubleshoot a slow running query in SQL Server   

        Once you've identified the query or queries or whatever else causing the 100 % CPU - work with the application owner to understand the impact of killing a query.

 

       > Second focus on root cause

                              SQL Server - How to improve Execution Plan Reuse - SQL Server DBA

                              Optimize query for LIKE (SQL Server DBA)

                              Last time Index was rebuilt (SQL Server DBA)

                             Missing Indexes. Equality and Inequality on sys.dm_db_missing_index_details

                            SQL Server Query Optimizer and Statistics

                            Archiving data into a separate database

                                  


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server 100 cpu diagnose


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer