What to do when your SQL CPU is at 100%

We’ve all had it happen, alerts start going off that the CPU on the SQL Server is now pegged. It’s an all hands on deck situation that you need to figure out quick. It helps to know the history of your SQL Server performance, but if you don’t there’s still hope using some troubleshooting techniques.

First Make sure it’s SQL Server

Sometimes there can be other processes (even backups) that are causing the CPU to push over that final edge. If you don’t have access to log into the box, the quickest way to determine how much CPU SQL Server is using is to run the following DMV:

[cc lang=”sql”]
DECLARE @ts_now bigint

SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info;

SELECT
record_id,
dateadd(ms, -1 * (@ts_now – [timestamp]), GetDate()) as EventTime,
SQLProcessUtilization,
SystemIdle,
100 – SystemIdle – SQLProcessUtilization as OtherProcessUtilization
FROM (
SELECT
record.value(‘(./Record/@id)[1]’, ‘int’) as record_id,
record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]’, ‘int’) as SystemIdle,
record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]’, ‘int’) as SQLProcessUtilization,
TIMESTAMP
FROM (
SELECT timestamp, CONVERT(xml, record) as record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’
AND record LIKE ‘%%’) as x
) as y
ORDER BY record_id DESC;
[/cc]

If the OtherProcessUtilization column has a high value then you know you need to log into the box, open task manager and figure out what is hosing your system. It should be noted that you really should only run SQL Server on SQL Boxes, and if this is your issue, you’ll learn that today.

If it is SQL Server, then it typically falls into one of three categories.

First Category

You have a procedure that has caught a bad execution plan hop and it either needs to be recompiled, or have a hint applied. To figure this out, you need to run sp_who3. Compile this procedure into your master database and run it. If after running it you see multiple spids (rows) with the same ObjectName or SQLStatement, then you have a bad plan. Consider recompiling the procedure using sp_recompile:

[cc lang=”sql”]
exec sp_recompile ‘pr_procedure_name’;
[/cc]

If that does not work, you may want to consider flushing the entire procedure cache. Take note of the procedure name before you do this, as it may not show up in subsequent sp_who3 executions in case you need to find it again. You will need ALTER SERVER STATE permissions to perform this:

[cc lang=”sql”]
DBCC FREEPROCCACHE
[/cc]

If that does not work, you may need to open the procedure and make a modification. Unfortunately that a big fish we won’t be able to fry in this article. Your options could range from creating a new index for the bad query, forcing an index for the bad query, or applying a force order hint (like loop join).

Second Category

If the above is not the issue, you may have a single process that has gone rogue. This is more of an issue if you don’t have many CPU cores. The more CPU cores you have, the less this will likely impact you UNLESS you have your Server instance’s max degree of parallelism setting set too high, along with Query threshold for parallelism. See this article to learn how to configure that. However, it you do have a single process that has gone rogue, use the same process above to recompile it, or kill it if necessary. To kill it you need to run sp_who3 to find out what the SPID is, and use the kill command to kill it. Do not kill a spid if it is 50 or lower. These are internal SPIDs used by SQL Server.

[cc lang=”sql”]
KILL 555
[/cc]

Third Category

You have a lot of missing indexes you have not taken care of, but viola, you now need to add them. This is usually the case in most SQL Server instances that do not receive a lot of love. Developers keep pushing code and all is well until TODAY. Now you need to add indexes. If this is the case, you will simply see a lot of SPIDs (rows) show up when you run sp_who3. Your best bet (if you should choose) is to deploy the top 3 missing indexes into your system to see if that has an effect. And if your system does not get a lot of love, it very likely will. As one DBA asked me after doing this for a client, “Did we just unplug all the webservers?” (Because CPU went down by like 60%). I linked to it already, but you can find the DMV script which will display the missing indexes here. Just copy the index, name it properly, and deploy it. You should add the WITH (ONLINE=ON) if you are running SQL Server Enterprise Edition in production.
[cc lang=”sql”]
CREATE INDEX ix_someindex ON tablename (column1,column2) INCLUDE (momo1, momo2) WITH (ONLINE=ON);
[/cc]
That’s it, hopefully that helped you with your issue.