By: Greg Robidoux | Comments | Related: 1 | 2 | 3 | 4 | 5 | 6 | > Query Plans
Problem
One nice feature of SQL Server is being able to display graphical query plans. The problem with graphical query plans 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 feature of SSMS 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 option from the File menu list.
Below is an example of an estimated query plan for the stored procedure "HumanResources.uspUpdateEmployeePersonalInfo" found in the AdventureWorks database. If we select Ctrl+L or use the menu Query > Display Estimated Execution Plan 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 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 the following or the menu View > Full Screen.
- 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.
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
