May 5, 2016 at 10:15 am
Hello All,
in activity monitor for sql server 2012 I have my CPU being used up to the max without dropping. I have analysed a few expensive queries but there has been no significant change to this. After restarting the sql service the CPU usage drops but picks up to max after a few seconds (30s).
I need your help ASAP. thanks
May 5, 2016 at 10:36 am
1) If you need help "ASAP" to get a production server running acceptably the best way BY FAR to accomplish that is with a consultant. There are a number on these forums that can VPN into your system on short notice to get you fixed up.
2) Did this just start happening or has it been going on? If the former what changed? If the latter, why is it all of a sudden an ASAP problem? 😀
Did you happen to switch from rotating disks to SSDs by chance?
3) Try sp_whoisactive to see what is running right now and consuming what resources. Try Glenn Berry's SQL Server Diagnostic Scripts to find bad performers overall.
You should also do a differential file IO stall and wait stats analysis.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 5, 2016 at 10:53 am
Identify the queries using the most CPU and tune them. This may not be the most expensive individual queries, probably isn't. The query that runs once an hour for 5 minutes is way less of a problem overall than the query that runs 10 times a second and takes 0.5 seconds.
Or get someone in if you're not sure how to do this and it's really urgent..
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
May 5, 2016 at 11:03 am
GilaMonster (5/5/2016)
Identify the queries using the most CPU and tune them. This may not be the most expensive individual queries, probably isn't. The query that runs once an hour for 5 minutes is way less of a problem overall than the query that runs 10 times a second and takes 0.5 seconds.Or get someone in if you're not sure how to do this and it's really urgent..
Aggregate profiler trace is the way I find those short-running, frequently-called server killers. Qure by DBSophic is an awesome free tool for that!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 5, 2016 at 1:06 pm
I'd recommend extended events on 2012. Add in the QueryHash column and the aggregation is trivial to do in T-SQL, no need for string comparisons for ad-hoc queries any longer.
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
May 5, 2016 at 7:16 pm
GilaMonster (5/5/2016)
I'd recommend extended events on 2012. Add in the QueryHash column and the aggregation is trivial to do in T-SQL, no need for string comparisons for ad-hoc queries any longer.
XEs still can't touch the amazing flexibility and power of Qure though, at least not any tool I have seen (and certainly not as nicely done as Qure by using TSQL). Slice and dice and filtering and ordering in a GUI. You can see total or average work done and ALSO drill down to see the actual parameters called - still aggregated by parameter set. Oh AND you can do a comparative analysis where you fix stuff and capture another workload and it will automatically tie together before and after runs. Nothing else comes close, IMHO.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 6, 2016 at 10:08 am
hello all,
Thanks for your contribution. but upon checking some expensive queries and analysis there were no significant changes that affected the CPU usage in activity monitor.
we took some steps as per our action plan
initially we installed service pack 3 for sql server 2012 but transactions was till slow.
deleted and archived old data.
and rebuild indexes..
May 6, 2016 at 12:38 pm
1) Buy bigger hardware? Or configure a bigger VM? What are the specs now?
2) You did not answer question 2 from my initial response.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 6, 2016 at 12:45 pm
Did you look at just expensive individual queries, or ones that, on aggregate, use a lot of resources? If the former, that's not going to get you very much, you need to look at the aggregate picture.
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
May 9, 2016 at 1:43 am
Same hardware.
And Inretrospect, it occurred all of a sudden
May 9, 2016 at 1:51 am
the aggregate picture? okay will look that ...
May 9, 2016 at 2:40 am
developerghana (5/9/2016)
the aggregate picture?
Yes, the approach that was suggested 4 days ago.
GilaMonster (5/5/2016)
Identify the queries using the most CPU and tune them. This may not be the most expensive individual queries, probably isn't. The query that runs once an hour for 5 minutes is way less of a problem overall than the query that runs 10 times a second and takes 0.5 seconds.Or get someone in if you're not sure how to do this and it's really urgent..
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
May 9, 2016 at 6:34 am
developerghana (5/9/2016)
Same hardware.And Inretrospect, it occurred all of a sudden
And you didn't provide the information I asked initially if that was the case: WHAT CHANGED?!?! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply