December 11, 2014 at 9:06 am
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. 😉
December 11, 2014 at 9:09 am
Nice example. One word for this scenario is "parameter sniffing".
December 11, 2014 at 11:45 pm
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!!
December 12, 2014 at 8:00 am
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*.
December 12, 2014 at 9:38 am
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
December 13, 2014 at 3:11 pm
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.
December 13, 2014 at 3:14 pm
54*60*32 is the total number of CPU seconds in the timeframe. 54*60*32 = "# of minutes" * "secs in one min" * "# of CPUs".
December 19, 2014 at 8:28 am
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"?
October 10, 2019 at 12:37 pm
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 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply