4/09/2011

Efficient SQL server query tuning with SQL sentry Plan Explorer : Zoom in Zoom out SQL plan

Saturday, April 09, 2011  subhash  No comments

A day back I had to fine tune a sql query which was way too complex to analyze with a glance. It would take endless time to do that by analyzing just the statements as I was dealing with a couple of million records..

So I decided to start using the tools available in Sql server Management Studio (SSMS) for fine tuning the query. For finding the cost associated with my query, I ran my query with my actual execution Plan set to On. When I ran the query, SSMS displayed the execution plan in the bottom pane of SSMS. Since My query is complex, so my execution plan looked like a train with 100 coaches :)...Navigating through the plan displayed in SSMS was so difficult. I could not make anything out of it. So I tried to find out tools which would help me to understand this plan in a efficient way and do some tuning with it.

I came across a wonderful tool ( Freeware ) which helped me in fine tuning my sql query easily.


SQL Sentry Plan Explorer is a lightweight standalone app that builds a graphical view using SQL query plan so that developers can understand it very easily. It does not require a collector service or database.


The image below explains how well the graphical view is constructed. Please click on the image to enlarge it

It helps us understand which elements in the plan costs more by displaying them in different colors ( Red for high cost, yellow for medium cost...)... It even displays the amount of data that flows between different elements to help you understand where you have more data. All you need to do is tweak accordingly and check the costs again.

How to load your plan into SQL sentry Plan explorer ?

There are different ways through which you can load your execution plan into SQL sentry plan explorer.

  • In SSMS, right-click a graphical plan and select “Show Execution Plan XML”, then copy and paste the plan XML into the Plan Explorer. It doesn’t matter which pane is active, the clipboard handler is global and will auto-detect the plan XML.
  • Save an execution plan from SSMS to a .sqlplan file, then open the file using the Open toolbar button, File->Open menu, or Ctrl + O. Raw plan XML files and .QueryAnalysis files (our own proprietary format) are also supported.
  • Right-click an existing .sqlplan file in Windows Explorer and select “Open with -> SQL Sentry Plan Explorer”.
  • Drag-and-drop a plan file onto the application (The easiest way !)
The sentry plan explorer offers you different views of the plan tree namely :
  • Plan Diagram
  • Plan Tree - A tree representation of the plan which shows all operations and associated metrics
  • Top Operations - List of all plan operations, sorted by total estimated cost by default in descending Order
  • Query Columns - List of all columns accessed by the query along with the table, operation, and index used.
The best part of this is that you can zoom in and zoom out SQL query execution plan which lets you to view your plan in a glance. Its definitely lot better than SSMS plan viewer.

Need more asp.net tips and tricks ? For more Subscribe here or click here to get updates via email

If you want detailed description , please check here.

Posted in: ,

0 comments:

Post a Comment