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 ?
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?
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