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 !)
- 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.
You MUST HAVE .NET 4.0 PRE-INSTALLED to install and use the Plan Explorer. Download .NET 4.0 here.
SQL Sentry Plan Explorer x64
SQL Sentry Plan Explorer x86
SQL Sentry Plan Explorer Documentation
SQL Sentry Plan Explorer Change List
If you want detailed description , please check here.