{"id":68773,"date":"2016-10-18T14:02:33","date_gmt":"2016-10-18T14:02:33","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=68773"},"modified":"2021-08-24T13:39:28","modified_gmt":"2021-08-24T13:39:28","slug":"retrieving-sql-server-query-execution-plans","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/retrieving-sql-server-query-execution-plans\/","title":{"rendered":"Retrieving SQL Server Query Execution Plans"},"content":{"rendered":"

One of the most useful tools in SQL Server for analyzing and troubleshooting a query\u2019s performance is the execution plan. An execution plan can tell you how the database engine intends to execute a query, as determined by the query optimizer, or how the database engine actually executed the query.<\/p>\n

In most cases, the estimated plan and actual plan are one in the same. The optimizer generates the plan and hands the plan off to the engine, which uses it executes the query. When you view the actual plan, you\u2019re essentially looking at the estimated plan generated by the optimizer, with a few runtime statistics added in for good measure.<\/p>\n

The actual plan will differ from estimated plan only if something happens in between the estimating and processing operations that forces SQL Server to recompile the associated query and the optimizer to produce a completely new plan. For example, someone might modify an underlying table or recompile a stored procedure.<\/p>\n

The execution plan breaks the execution process down into hierarchical steps that show each operation needed to access the target data or perform computations against that data. For each operation, the execution plan provides detailed information such as the operation type, output columns, number of rows, and estimated CPU and I\/O usage. The details also include the estimated cost of an operation, as determined by the optimizer, relative to the rest of the query\u2019s operations.<\/p>\n

SQL Server provides a number of methods for accessing a query\u2019s execution plan, ranging from clicking a button in SQL Server Management Studio (SSMS) to setting up extended events that capture execution plans based on specific criteria. In this article, we look at the various methods for accessing execution plans and provide some of the necessary basics for how to find the information they provide.<\/p>\n

Using SSMS to access execution plans<\/h2>\n

The simplest and quickest way to access a query\u2019s execution plan is to use SSMS to view a graphical representation of either the estimated or actual plan. To demonstrate how this works, let\u2019s start with the following SELECT<\/strong> statement, which joins two tables in the AdventureWorks2014<\/strong> database (installed on a local instance of SQL Server 2014):<\/p>\n

SELECT sh.SalesOrderID, sh.CustomerID, sh.OrderDate, \r\n  sd.ProductID, sd.LineTotal\r\nFROM Sales.SalesOrderHeader sh JOIN Sales.SalesOrderDetail sd \r\n  ON sh.SalesOrderID = sd.SalesOrderID \r\nWHERE sd.LineTotal BETWEEN 1000 AND 1500;\r\n<\/pre>\n
The SELECT<\/strong> statement simply joins the SalesOrderHeader<\/strong> table with the SalesOrderDetail<\/strong> table and returns a range of values based on the LineTotal<\/strong> column in the SalesOrderDetail<\/strong> table. The LineTotal<\/strong> column is a computed column that derives its values from the UnitPrice<\/strong>, UnitPriceDiscount<\/strong>, and OrderQty<\/strong> columns in the same table.<\/p>\n
To display a query\u2019s execution plan, you must first type (or copy-and-paste) the query into a new query tab in SSMS or open a T-SQL script file that contains the query. You can also type the query into an existing tab that contains other queries, but if you do, you must highlight the query before trying to view the plan, just as you would if running a single T-SQL statement from a group of statements.<\/p>\n
With the query in place, and selected if necessary, click the Display<\/strong> Estimated<\/strong> Execution<\/strong> Plan<\/strong> button on the SSMS toolbar to view its estimated plan, or click the Include<\/strong> Actual<\/strong> Execution<\/strong> Plan<\/strong> button to view the actual plan.<\/p>\n
The Display<\/strong> Estimated<\/strong> Execution<\/strong> Plan<\/strong> button is a one-time event, which means you must click it each time you want to view the estimated plan for a selected query. If you select this option, the database engine generates the plan without running the query.<\/p>\n
The Include<\/strong> Actual<\/strong> Execution<\/strong> Plan<\/strong> button is a toggle that is either on or off. If on, the database engine executes the query and generates an actual plan. SSMS will continue to return the execution plan for each executed query until you explicitly toggle the button off.<\/p>\n
Regardless of which option you choose, SSMS displays the plan on its own results tab, separate from any query results or messages. The following figure shows the execution plan after turning the Include<\/strong> Actual<\/strong> Execution<\/strong> Plan<\/strong> option on.<\/p>\n
\"%20screenshots\/stExecPlans_fig01.png\"<\/p>\n
In this case, the actual plan displayed is the same as the estimated plan, which indicates that the optimizer likely did not generate a new plan when it came time to processing the query. More often than not, this will be the situation you\u2019ll run into, particularly for simple queries such as our example. That said, there will be times when they differ. For example, if a table\u2019s statistics are not current as a result of inserting data into an IDENTITY<\/strong> column, SQL Server will update the statistics next time you run a query against that table and then recompile the plan.<\/p>\n
Whether you\u2019re working with the estimated plan or the actual plan, SSMS presents the information in a similar manner, displaying a specific icon, or operator, for each operation type. The execution plan for our sample SELECT<\/strong> query shows five operations, but only three different types:<\/p>\n