• 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.

    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)