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)
