A Spectacular Case of 100% CPU Caused by Online Reindexing

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

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

  • 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


    Roshan Joe

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

  • 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

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



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

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

  • 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"?

  • 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