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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply