Viewing Execution Plans in SSMS

When doing query tuning, interrogating the execution plan for the query can be useful for getting insights into improving the query.

There is actually different types of execution plans and a number of ways to retrieve those plans.

We can get plans from SQL Server Management Studio (SSMS) interface, the plan cache, the query store, the profiler and/or extended events. However, in this article we are going to focus on how to retrieve execution plans from the SSMS interface.

Brief Overview of the Types of Execution Plans

I plan to go into more detail about the differences between these plans in another article but for now it should suffice to have a brief overview.

Estimated
This plan is generated without executing the query. SQL Server creates this plan based on statistics and metadata that it has. Since this plan is created without running the query, it can be misleading or inaccurate. Query results are not returned.

Actual
This plan is generated by executing the query. It is more accurate than an estimated execution plan. Query results are returned.

The Two Buttons

Enabling these execution plans is very easy in SQL Server Management Studio.

Simply click one of the below two buttons when trying to display the execution plan you want.

Estimated Execution Plan  (Ctrl + L)

Actual Execution Plan (Ctrl + M)

This will allow you to have a deeper look into the behaviour of your queries in SQL Server. In a later blog post I will dive deeper into the differences between these plans and also how to interpret them.

Advertisement