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.

3 comments:

Louis vuitton handbags are some of the rolex replica most fashionable and desirable bags that you can buy. There desirability, nevertheless, has meant that the black market within the sale regarding replica Louis vuitton totes has flourished as dishonest traders make an attempt to create a fast profit by lying to buyers directly into thinking that their own Louis vuitton handbag will be the genuine article. It is usually devastating to get a buyer who may have probably saved trey blog long and hard to replica watches get their brand new Louis vuitton bag, for you to suddenly learn that its not necessarily the genuine article. Obviously, its not simply Louis vuitton who suffer through the sale regarding counterfeit as well as replica excellent, all of the primary designer labeling such as Chanel, Gucci as well as Versace also experience this problem. Obviously, there are some measures you can and will take to ensure your prized ownership doesn't come to be a phony.

You must purchase these Louis vuitton Handbags from well established retailers. Some recognized names happen to be in the market. They may be selling all of them from numerous years. This is why you can trust these people in getting these items. You can purchase them at any nearby keep of good status. Nowadays, you can even place make an online purchase for getting these. It is a best option, because in this case you will get these people directly from seen designers. You are going to prada replica feel safer about their good quality.

Also, the quality, form, size as well as clarity of ubout replica the logo should also be checked while carefully as is possible. Some bogus handbags utilized some misspelled logo design names which usually look like the important donny blog brands for you to fool individuals eyes. Louis vuitton has an inborn sense of creative imagination and invention which has produced numerous distinctive, stylish and also eye-catching handbags.

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.






Trên thế giới có nhiều loại đông trùng hạ thảo, có loại đông trùng hạ thảo rất quý như đông trùng hạ thảo tây tạng của Việt Nam ở nước chúng ta. Đông trùng hạ thảo là loại có dưỡng chất tốt nhất.
cửa hàng đông trùng hạ thảo hàn quốc Vĩnh Long
cửa hàng đông trùng hạ thảo hàn quốc Vĩnh Phúc
cửa hàng đông trùng hạ thảo hàn quốc Yên Bái
cửa hàng đông trùng hạ thảo hàn quốc Phú Yên

Post a Comment