January 4, 2013 at 7:24 am
Yesterday one of our SQL 2008 R2 instances had it's CPU spike to 95-100% for over a half hour. Using SP_WHO & SP_WHO2 I was able to see that only normal application activity and replication activity (it's a subscriber) were going on, yet at the OS level the task manager showed that sqlsrv.exe was the process consuming all of the CPU. What query or tool could I use to show me exactly what process/command/query is causing the CPU to spike like that? Could a flurry of activity at the publisher instance cause replication to get so busy that it spikes the CPU?
January 4, 2013 at 10:34 am
After the fact it's going to be hard to track down. You can try using sys.dm_exec_query_stats to see which queries have the most worker_time. That will give you some indications of queries using the CPU. But that's assuming it's queries that are the cause. During the event you can look at sys.dm_exec_requests and combine that sys.dm_tran_locks and sys.dm_os_waiting_tasks to see which processes are waiting and what they're waiting on.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 4, 2013 at 11:50 am
What if I can catch it while its occurring? What query would show me while its occurring?
January 4, 2013 at 11:55 am
Like I said, sys.dm_exec_requests combined with the locking and transaction dynamic management objects.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply