February 4, 2014 at 3:35 am
Hi
We just had higher than normal cpu utilization for approx 1 hour. Cpu normally runs between 20 - 30% capacity but today it went to approx 80% for about an hour.
I was continually running sp_whoisactivce constantly but it did not return any queries that seemed to be burning the cpu. Typically each time I ran it I would see one query, then when I ran it again literally a second later there would be maybe a different query and the previous one would have finished.
How can I now try and start to find out what it was that was causing the issue.
I can confirm that it was the process 'sqlservr.exe' that was causing the issue.
We do use transactional replication as well, so i was wondering if there is anything I can check in that area that may be the culprit ?
Thanks
February 4, 2014 at 4:38 am
Are you using this box as its own distributor? If so, this means the logreader agent is running locally and could potentially consume the CPU if there has been a significant change of data. The logreader would need to scan the transaction logs and would not necessarily show as a CPU consumer from with SQL Server.
Its been a while since I used replication so you'd need to confirm this was the case.
February 4, 2014 at 4:40 am
Was there any backup process occurring at the time?
February 4, 2014 at 5:09 am
Are you using this box as its own distributor?
No this box is just used as a subscriber. We have 2 separate servers for both the Distributor and the Publisher.
Also there where no backups happening at the time ( I've been bitten by that one before but not this time ! )
February 4, 2014 at 6:00 am
After the fact like this... You can try querying sys.dm_exec_query_stats to see if you have any queries that stand out as using a lot of CPU. Otherwise, the best bet would have been to have trace or extended events capturing queries as they went by.
"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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply