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.

SQL SERVER - 3 Different Ways to Explore Actual Execution Plans 3executionplan-800x200

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.

SQL SERVER - 3 Different Ways to Explore Actual Execution Plans executionplan1

Solarwinds

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

SQL SERVER - 3 Different Ways to Explore Actual Execution Plans executionplan2

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.

SQL SERVER - 3 Different Ways to Explore Actual Execution Plans executionplan3

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.

SQL SERVER - 3 Different Ways to Explore Actual Execution Plans executionplan4

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.

SQL SERVER - 3 Different Ways to Explore Actual Execution Plans executionplan5

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)

Solarwinds
, , , ,
Previous Post
SQL SERVER – Identify Database File with Maximum IO Issues
Next Post
SQL SERVER – Identify Read Heavy Workload or Write Heavy Workload Type by Counters

Related Posts

Leave a Reply

Menu