Maximizing your view into SQL Query Plans

By: Greg Robidoux | Read Comments (1) | Print Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com. Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More

Problem
One nice feature of SQL Server is being able to display graphical query plans.  The problem with graphical query plans though is that they are sometimes extremely large and often difficult to maneuver through the plan to identity where the issues are within your statement or statements.  Within SQL Server Management Studio you have the ability to zoom in and zoom as well as the ability to find certain parts of the query plan by hovering over a reduced version of the query plan, but often you are constrained by the size of the results pane to view the plan and it is still quite difficult to find the information you are looking for.  Are there any other ways of making navigation of a query plan easier?

Solution
A new feature of SQL Server 2005 is the ability to save the graphical query plan for future use.  Whether you display the actual or estimated query plan you have the ability to save the plan to a *.sqlplan file.  To save the query plan, right click in the Execution Plan tab and select "Save Execution Plan As..." or choose this same option from the File menu list.

Here is an example of an estimated query plan for the stored procedure "HumanResources.uspUpdateEmployeePersonalInfo" found in the AdventureWorks database.   If we select Ctrl+L we can see the estimated query plan as shown below.  This stored procedure actually has 4 different queries displayed. In this view it is hard to really get a good idea of what is going on.  You can drag the sections within in each query to get a bigger view, but it still is quite cumbersome.  So to make viewing a bit easier we going to save the plan for Query 2, so we can see this information easier. 

At this point right click anywhere within the Query 2 plan and select "Save Execution Plan As..." and save the file.

One thing to note is that depending on what you are looking at (one statement or a stored procedure with multiple statements), there may be one or more execution plans, so you will need to save each one of these individually to a separate query plan file. The "Save Execution Plan As..." option does not save all of the plans in one step.

Once you save the file you can then open the file to view the execution plan using SQL Server Management Studio and also have additional real estate on your screen to look at the query plan.  If we open up our saved query plan you should get a screen such as the following:  In this view we can now see just the one query and we also get a lot more space to maneuver through the query plan.  In addition, all of the features that exist when you initially look at the query plan still exist in this saved version, such as the pop-up window when hovering over an object and also the properties information for each object in the plan.

To get even more real estate on your screen you can use the Full Screen mode.  To do this use:

• Shift + Alt +Enter

This will remove all of the toolbars and some of the menu items on the screen to give you the most real estate possible when viewing query plans or just about anything with Management Studio.

Next Steps

• Next time you have a large query plan, use the save plan option or the full screen option to get more screen space to view the plan
• As you are doing performance tuning on your system and want to see before and after affects of changes you are making, use the "save plan" option to save your query plans, so you can reference them for later use to see if the changes you have made were effective.

Share:

Share

Tweet

Free SQL Server Learning Introduction to SQL Azure Join Herve Roggero on Wednesday, June 13 @ 3:00 PM EST to learn more.

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code