November 4, 2017 at 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
November 4, 2017 at 6:32 am
saum70 - Saturday, November 4, 2017 5:00 AMHi,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
Is the cardinality of the data set changing? What about memory pressure, have you checked?
😎
Can you please post the actual execution plan and any other relevant information please?
November 5, 2017 at 1:19 am
Eirikur Eiriksson - Saturday, November 4, 2017 6:32 AMsaum70 - Saturday, November 4, 2017 5:00 AMHi,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 VoraIs the cardinality of the data set changing? What about memory pressure, have you checked?
😎
Can you please post the actual execution plan and any other relevant information please?
Well its an order table so yes it keeps on changing. Will not be able to give atcual execution plan due to company policy.
November 5, 2017 at 7:04 pm
saum70 - Saturday, November 4, 2017 5:00 AMHi,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
Change is inevitable... Change for the better is not.
November 13, 2017 at 5:56 am
Jeff Moden - Sunday, November 5, 2017 7:04 PMsaum70 - Saturday, November 4, 2017 5:00 AMHi,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 VoraWithout 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?
Hi,
My answer as below
did someone add, drop, or modify an index on the related tables even if the don't seem related --> No
Did someone accidently rebuild the indexes with a 0 or 100% fill factor --> we have given the script that reindexes the tables
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 --> Need to look into this... in live env. Automatic Statistics Update is Off
Have you had a disk crash or memory failure that no one knows about --> None occurred
Have you checked sp_who2 during the high CPU episodes to see if "KILLED/ROLLBACK" is present --> Need to check it
Has anyone messed around with the max memory, cpu affinity, or any other server or database level settings --> No
Another question
I queried sys.dm_db_index_physical_stats for one of the tables after rebuilding it (did 'DETAILED'). The table is having Nonclustered Primary key and no other indexes. The avg_fragmentation_in_perc for leaf level 0 for both HEAP and NONCLUSTERED INDEX is showing as 2.94 and 1.53 respectively. However for the leaf level > 0, the value of fragmentation showed 100. What should be interpreted in this case
November 13, 2017 at 6:03 am
saum70 - Monday, November 13, 2017 5:56 AMJeff Moden - Sunday, November 5, 2017 7:04 PMsaum70 - Saturday, November 4, 2017 5:00 AMHi,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 VoraWithout 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?
Hi,
My answer as below
did someone add, drop, or modify an index on the related tables even if the don't seem related --> No
Did someone accidently rebuild the indexes with a 0 or 100% fill factor --> we have given the script that reindexes the tables
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 --> Need to look into this... in live env. Automatic Statistics Update is Off
Have you had a disk crash or memory failure that no one knows about --> None occurred
Have you checked sp_who2 during the high CPU episodes to see if "KILLED/ROLLBACK" is present --> Need to check it
Has anyone messed around with the max memory, cpu affinity, or any other server or database level settings --> NoAnother question
I queried sys.dm_db_index_physical_stats for one of the tables after rebuilding it (did 'DETAILED'). The table is having Nonclustered Primary key and no other indexes. The avg_fragmentation_in_perc for leaf level 0 for both HEAP and NONCLUSTERED INDEX is showing as 2.94 and 1.53 respectively. However for the leaf level > 0, the value of fragmentation showed 100. What should be interpreted in this case
There have to be very good reasons for not having a clustered index on a table, bear this in mind.
Can you provide an actual execution plan for the query? Attached as a .sqlplan file attachment? Thanks.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 13, 2017 at 8:06 am
November 13, 2017 at 8:31 am
saum70 - Monday, November 13, 2017 5:56 AMJeff Moden - Sunday, November 5, 2017 7:04 PMsaum70 - Saturday, November 4, 2017 5:00 AMHi,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 VoraWithout 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?
Hi,
My answer as below
did someone add, drop, or modify an index on the related tables even if the don't seem related --> No
Did someone accidently rebuild the indexes with a 0 or 100% fill factor --> we have given the script that reindexes the tables
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 --> Need to look into this... in live env. Automatic Statistics Update is Off
Have you had a disk crash or memory failure that no one knows about --> None occurred
Have you checked sp_who2 during the high CPU episodes to see if "KILLED/ROLLBACK" is present --> Need to check it
Has anyone messed around with the max memory, cpu affinity, or any other server or database level settings --> NoAnother question
I queried sys.dm_db_index_physical_stats for one of the tables after rebuilding it (did 'DETAILED'). The table is having Nonclustered Primary key and no other indexes. The avg_fragmentation_in_perc for leaf level 0 for both HEAP and NONCLUSTERED INDEX is showing as 2.94 and 1.53 respectively. However for the leaf level > 0, the value of fragmentation showed 100. What should be interpreted in this case
I don't understand what you mean by "we have given the script that reindexes the tables"
Based on your answers, I'd suggest that someone added some data that may be causing "accidental cross joins" in the form of many-to-many joins. Does the query happen to use either the DISTINCT or GROUP BY keywords?
Other than that and without the actual execution plan, and without you checking to see if KILLED/ROLLBACKs are present during the 90% CPU usage episodes, I'm pretty much out of ideas except that you might want to try clearing procedure cache in case it's a simple case of bad parameter sniffing.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply