Tracking down CPU spikes using process explorer and DMVs

What is it about 4:45 pm on a Friday afternoon that makes CPUs spike, drives crash, databases corrupt? I wish I knew but I don’t. I do know that I have been on the receiving end a few times. Just shutting down the last applications when a call or a page comes in. Next minute you’re phoning home and dinner’s in the warmer.

On one such Friday afternoon, in the not too distant past, one of the DBA team noticed that CPU was running hot on our main production server. I could see from looking at task manager on the server that the SQL process was responsible for the CPU usage. But this is a busy OLTP production server and I needed to isolate the process or processes responsible. Looking at sys.sysprocesses it wasn’t immediately obvious what session could be responsible.

Next step was to run process explorer on the server to narrow down which CPU’s were spiking. If you haven’t already discovered it, process explorer is a great free lightweight diagnostic tool from the sys internals team. The executable can be downloaded and run without any installation.

http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx

Open process explorer and click on one of the small graphs near the top to get a detailed view.

procexp1

 

Tick the “Show one graph per CPU” option.

procexp2

 

Hovering over the individual graphs will give the CPU id.

procexp3

Note: The screenshots above are not from the actual issue. At the time I was too busy trying to fix the problem to take screenshots for later use 🙂

Using process explorer this way I found that CPU usage was jumping up and down but there were two CPUs that were sitting at 100% consistently. Armed with the ids I hit SQL Server and some DMVs.

Using sys.dm_os_schedulers with the ids of the two rogue CPUs gave me the scheduler addresses.

select scheduler_address from sys.dm_os_schedulers
where cpu_id in 
	([id1],[id2])

Putting the scheduler addresses into sys.dm_os_workers gave me task addresses.

select task_address from sys.dm_os_workers 
where scheduler_address in 
	([scheduler_address_1]
	,[scheduler_address_2])

And finally putting the task addresses into sys.dm_os_tasks gave me the session ids.

select session_id from sys.dm_os_tasks 
where task_address in 
	([task_address_1]
	,[task_address_2])

Or if you want to put it all together you get something like.

select
	s.cpu_id,
	w.[state],
	t.session_id 
from sys.dm_os_schedulers s
left join sys.dm_os_workers w
	on s.scheduler_address = w.scheduler_address
left join sys.dm_os_tasks t
	on w.task_address = t.task_address
where s.cpu_id in ([id1],[id2],...)
and w.[state] = 'RUNNING'

There’s a bunch of other columns that you might be interested in from these DMVs – but in my case I just wanted the session ids.

From there I could go back to sys.sysprocesses and see that in this case it was a system service broker process that was pegging the CPU. Restarting the service broker queue cleared the issue instantly.

ALTER QUEUE [my_queue]
WITH STATUS=OFF
 
ALTER QUEUE [my_queue]
WITH STATUS=ON
Advertisements
This entry was posted in Service Broker, SQL Server and tagged , , , . Bookmark the permalink.

3 Responses to Tracking down CPU spikes using process explorer and DMVs

  1. Pingback: (SFTW) SQL Server Links 17/10/14 - John Sansom

  2. cadarsh88sh says:

    Nice article!
    I have been having this concern, and my blog(http://cadarsh.blogspot.in/2014/09/capture-sql-processes-and-email-after.html) here is what I came up with. A powershell script to poll CPU usage, and trigger the processes that are vastly consuming CPU in real time. The entire script can be called from an SQL Agent job.

  3. JeffB says:

    Thank you or the excellent post. It is great to have a “methodology” for when you have an unexpected CPU spike. I will definitely use this. I might make one suggestion. After you have the SPID, you might quickly query sys.dm_exec_sessions to see what and who might be causing the issue.

    Thanks again.
    SELECT host_name,program_name, original_login_name, st.text
    FROM sys.dm_exec_sessions es
    INNER JOIN sys.dm_exec_connections ec
    ON es.session_id = ec.session_id
    CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
    WHERE ec.session_id = 557

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s