SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CPU spike when executing a query in SQL


CPU spike when executing a query in SQL

Author
Message
saum70
saum70
SSC-Addicted
SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)

Group: General Forum Members
Points: 474 Visits: 129
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
Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)SSC Guru (159K reputation)

Group: General Forum Members
Points: 159528 Visits: 23340
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

Is the cardinality of the data set changing? What about memory pressure, have you checked?
Cool
Can you please post the actual execution plan and any other relevant information please?

saum70
saum70
SSC-Addicted
SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)

Group: General Forum Members
Points: 474 Visits: 129
Eirikur Eiriksson - Saturday, November 4, 2017 6:32 AM
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

Is the cardinality of the data set changing? What about memory pressure, have you checked?
Cool
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.

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)

Group: General Forum Members
Points: 872346 Visits: 47480

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
saum70
saum70
SSC-Addicted
SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)

Group: General Forum Members
Points: 474 Visits: 129
Jeff Moden - Sunday, November 5, 2017 7:04 PM

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?

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

ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)

Group: General Forum Members
Points: 165560 Visits: 21618
saum70 - Monday, November 13, 2017 5:56 AM
Jeff Moden - Sunday, November 5, 2017 7:04 PM

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?

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

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.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Arsh
Arsh
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5091 Visits: 998

Can also check if the Parallelism settings have been changed.

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)

Group: General Forum Members
Points: 872346 Visits: 47480
saum70 - Monday, November 13, 2017 5:56 AM
Jeff Moden - Sunday, November 5, 2017 7:04 PM

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?

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


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

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search