January 6, 2014 at 10:05 am
We have high CPU Usage around 100% from SQL Server and they happen at various times throughout the day.I am interested in finding out what are the possible queries that could be running at that time that caused high CPU Utilization.I dont want to run profiler and cause more load on the server. I dont thing top 10 or 20 queries will help to resolve this issue?
Thanks
January 6, 2014 at 10:41 am
I would recommend that you use Adam Machanic's sp_whoisactive to see what is going on during these times of high CPU usage. You could also setup a server side trace to capture high CPU usage queries along with Extended Events, but sp_WhoIsActive is a great start.
January 6, 2014 at 2:44 pm
Chapter 3 - https://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 6, 2014 at 10:56 pm
Since this is posted in the 2008 forum, you can use extended events to capture the query metrics at an extremely low cost to the server. That's what I would do to understand which queries are impacting me the most. I agree with not using trace events, but extended events are much better.
"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