A Spectacular Case of 100% CPU Caused by Online Reindexing

  • gloewen-1069636

    SSC Enthusiast

    Points: 104

    Thanks for the article. I'm not a DBA and I was able to follow the gist of it - so, good writing. I'm in the frustrating position of working for a software development company who's DBA doesn't seem to understand any of this stuff. 😉

  • jaincs

    SSC Eights!

    Points: 982

    Nice example. One word for this scenario is "parameter sniffing".

  • joeroshan

    SSChampion

    Points: 10377

    Nice article. I had been in production DBA role for long, I understand the thrill of the chasing a performance issue and feeling exited finding the real culprit. Most often the learning might not be completely new but would be reinforcing the same in a different practical scenario, or viewing it from a different angle.

    You are a good DBA since you know where to pick leads. And you are talented enough to present the case putting each revelation one by one without loosing the readers interest

    Cheers!!

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Divine Flame

    SSCoach

    Points: 15941

    Very well written. Although checking for the outdated stats is first thing that we DBAs do when we see a query that starts performing badly *all of a sudden*.


    Sujeet Singh

  • mohan_rv

    SSC Journeyman

    Points: 88

    Thanks for sharing the article. It is an excellent article with step by step by description of issue and the solution. I have one question, how did you calculate total CPU power for the timeframe as (54*60*32)? Is it per minute?

    Thanks,

    Mohan

  • vgorod

    Valued Member

    Points: 71

    Good points, Old Hand. Also thanks for mentioning the plan guides. It's exactly the solution to address the subject of "it is a vendor code and I can't touch it". There is however one little issue with plan guides on top of all the implications of using hints: with plan guides the hints are hidden. Say, a DBA added a plan guide with the option RECOMPILE to avoid sticking with a bad plan (can be some other hint, it's just an example). Over time the number of the query/SP executions significantly increased and the DBA who inherited the application tries to investigate what causes such high volume of recompilations. He checks all codes and can't find any RECOMPILEs there. He runs a trace and can't catch no clue either. It might take awhile to discover the ghost. Plan guides are not something he'd check first of all.

  • vgorod

    Valued Member

    Points: 71

    54*60*32 is the total number of CPU seconds in the timeframe. 54*60*32 = "# of minutes" * "secs in one min" * "# of CPUs".

  • fthurber

    SSC Journeyman

    Points: 90

    I am not a DBA, just a programmer, but I could follow most of this wonderful detective story. Some excellent sleuthing. This was a great read.

    Please excuse my naïveté; I am new to SQL Server. Could you supply some details about what you use for the "active process report"?

  • Bryant McClellan

    SSCarpal Tunnel

    Points: 4215

    Whether you intended to or not you again illustrated the fallacy in "...and the execution plan showed all index seeks and no scans...". Seek good/scan bad is not a valid assumption. Indeed, the numbers you got from an "actual" plan likely showed those CI seeks were indeed table scans if all rows in the table were read. When a seek reads an entire table, it is a scan.

    As for actual versus estimated plans, all plans are estimated. What is referred to as an actual plan is an estimated plan accompanied by execution-time metrics. See Grant Fritchey https://www.scarydba.com/2019/09/09/get-the-last-actual-plan-with-sys-dm_exec_query_plan_stats/

    ------------
    Buy the ticket, take the ride. -- Hunter S. Thompson

Viewing 9 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply