Total Pageviews

Sunday, June 16, 2013

Execution Plan and “SQL Sentry Plan Explorer” – it's like a hand of god!

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