There
are numerous articles that have been written on how out-dated statistics cause
severe performance issues. I am hesitant to add one more to that long list. However,
this time I’ll be using “SQL Sentry Plan Explorer” to present the issue. Please
note that I am not a sales agent or representative of “SQL Sentry”, I wanted to
share its simplistic interface and usability. This tool is free, extremely
powerful and it brings all the qualities a SQL Server Expert ever needs to analyze an Execution Plan; especially
the ones who work on query performance tuning.
Sometime
ago, I received a request from a source to help them out on a query performance
issue. The issue they were experiencing was that the duration of a business
critical query is degrading; and now it takes up to 5 minutes to complete!
I
asked them to send me the XML output of the actual execution plan for that
query. I opened the received query plan in both “SQL Sentry Plan Explorer” and “SSMS”;
and found the root cause almost immediately – which was cardinality estimation
issue due to “out-dated statistics”. So the ultimate recommendation was to
update statistics. After updating, the query took less than 4 seconds.
Here,
I am sharing the outputs from “SQL Sentry Plan Explorer” to show you how intuitive
the tool is to understand an execution plan and identify any plan quality
problems immediately. Following are a couple of screenshots you may find
interesting.
Visit: SQL Sentry Plan
Explorer
Some Screenshots:
No comments:
Post a Comment