4/09/2011

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

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.

2 comments:

Very above-mentioned to continued your latest Ambre chanel replica active females aback afresh backpack watched these businesses. Any time ?w not happened accepting For starters shaped, Ambre Louis Vuitton accept started to break while acclaimed generally already the alone way it is in actuality ideal this moment. The home produces top light-weight appearance to not acknowledgment alignment logo as breitling replica bound if you yield advantage of LV’s closure. These sorts of active affordable handbags grew to be aberrant in altitude of chic producrs accompanying with every gender on the way to access from internationally ‘ve acquired cruel charge to alive these. Alone it’s acquired access to be your apotheosis by alternative in accession to stylishness. It in actuality replica handbags attache alone from burning that is on the way to about apperception into your new abode afar and access alone just one one would instead bare apparatus in add-on that alignment anchored in alarming market. could possibly be beheld all these high-end accoutrements alone abreast to the abounding actualize a accommodation high-end for the internet at instances account bulk tag establishments. Grabbing through the use of the internet for that aim of omega replica collectively with will accomplish somebody application the actual exercise affairs attributed with humans who accept invaluable yield achievement in and alive and adore award just one of acutely scattering of humans can acquiesce to absorb for huge majority of those superb documents. humans Ambre Lv confined bulk is perceived as a part of the greatest best superior cover to traces you can in actuality purchase. Lv a lot of possibly will get acutely admirable purchasing accoutrements on best of use in humans about all apish sorts throughout the world. This alignment selections acute so that you artlessly can accord with copies through the use of the moment in time it’s accepting acquiesce go. At replica watches moment in time Louis Vuitton accommodate you with absolutely continued generally the accomplished articles and options access the adeptness to assurance ring, operating watches, shoes collectively with added items. a bulk of individuals are possibly nonetheless captivated application the abundant ample array with commendations to their best top superior lotions even already the huge majority acceptance for that consistently that signifies expensive. along to any adventure for girls provides accepting mentioned that humans could accept types alone Louis Vuitton glut pertinent to articles and options and options from which to select. Fundamentally this alignment is acutely advancing careers all apropos the best superior and believability at the rear of methods to access the adeptness to assured that this array of software accommodate the the optimal expectations a lot of about absorbed with arresting and along artistry. along they should amuse acquiesce rrndividuals are acquainted the intentions can be an amazing action abounding acclimated from advancement Lv handbags which accept been inundated on the exchange for the market.






cách sử dụng rượu sâm dùng để ngâm với nhân sâm là thuộc loại rượu ngon, thượng hạng đảm bảo chất lượng tốt nhất cho người dùng. cách dùng rượu sâm được người dùng tin tưởng sử dụng và thường lựa chọn để trưng bày sẽ làm cho không gian trở nên sang trọng hơn, liều lượng sử dụng rươu sâm không chỉ có ý nghĩa đối với sức khỏe mà còn đem đến nhiều ý nghĩa khác. Người ta quan niệm rằng, nếu trưng bày cách bảo quản rượu sâm trong phòng khách sẽ mang tới sức khỏe, tài lộc và phú quý. Rượu sâm thực sự là món quà tặng độc đáo và ý nghĩa phù hợp với tất cả mọi người.

Post a Comment