SQL SERVER – 3 Different Ways to Explore Actual Execution Plans

In this blog post, we will explore 3 different ways to explore Actual Execution Plans. During the recent Comprehensive Database Performance Health Check, I had discussed this with clients.

In SQL Server there are three primary ways you can see the actual execution plan. You can use SQL Server Management Studio (SSMS) to see all the three kinds of execution plans.

Method 1: Graphical Actual Execution Plans

You can enable Graphical actual execution plan via typing keyboard shortcut CTRL+M.

In SSMS you can also enable a graphical execution plan via clicking on the icon on the toolbar.

If you go to the Main Menu on the top go-to Query menu and click on the Include Actual Execution Plan.

Once you follow any one of the above advice, you can run any query in the query editor in SSMS and it will display actual execution plan in the separate window in your SSMS resultset.

Once you are looking at your execution plan, you can follow the same method to disable the execution plan.

Method 2: Text Plan

This is another method to see the execution plan. We will use the STATISTICS PROFILE to see the actual execution plan.

You can execute the following query and in the resultset area, you will see the execution plan in the text format.

-- Text Execution Plans
SET STATISTICS PROFILE ON
GO
SELECT *
FROM WideWorldImporters.Sales.Invoices
GO
SET STATISTICS PROFILE OFF
GO

You need to scroll down bellow the execution plan to see the text-based execution plan which will be displayed in the grid format.

Method 3: XML Plan

The XML execution plan is very popular as we can save them and send to another person and they can easily see the exact same operations which we see on our machine. We will use the STATISTICS XML to see the actual execution plan.

You can execute the following query and in the resultset area, you will see the execution plan in the XML format. You can also click on the XML execution plan and open the graphical execution plan as well.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Menu
Exit mobile version