RE: CPU spike when executing a query in SQL

SSC Guru

Points: 1003873

November 5, 2017 at 7:04 pm

#1966724

saum70 - Saturday, November 4, 2017 5:00 AM
Hi,
Our production environment recently have been witnessing spike in CPU utilization of more than 90% when executing some queries in a stored procedure. Though we are optimizing the stored procedure, but it will not be much of the help. Also the same stored procedure used to run properly till last week and hence we would like to know what other reason could be for such sudden spike. We have also updated the statistics and performed reindexing/rebuilding of the tables but still the high cpu utilization continues.
Kindly guide for further action.
Regards,
Saumik Vora

Without the actual execution plan and a copy of the stored procedure, there is little we can do to help you.

Since you've covered the obvious (statistics rebuilds), I have to ask some questions like did someone add, drop, or modify an index on the related tables even if the don't seem related? Did someone accidently rebuild the indexes with a 0 or 100% fill factor? Did one of the tables finally reach the point where an automatic statistics rebuild occur and it did like a 2% sample because that's the nature of SQL Server an you missed that stat during your rebuilds? Have you had a disk crash or memory failure that no one knows about? Have you checked sp_who2 during the high CPU episodes to see if "KILLED/ROLLBACK" is present? Has anyone messed around with the max memory, cpu affinity, or any other server or database level settings?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)