By Justin


2011-09-09 09:37:21 8 Comments

In Microsoft SQL Server how can I get a query execution plan for a query / stored procedure?

10 comments

@Abd Rmdn 2019-07-14 18:45:20

Explaining execution plan can be very detailed and takes up quite a reading time, but in summary if you use 'explain' before the query it should give you a lot of info including which parts were executed first and so. if you wanna read a bit more details about this, I compiled a small blog about this which points you as well to the right refs. https://medium.com/swlh/jetbrains-datagrip-explain-plan-ac406772c470

@Daan 2018-08-25 11:03:56

Like with SQL Server Management Studio (already explained), it is also possible with Datagrip as explained here.

  1. Right-click an SQL statement, and select Explain plan.
  2. In the Output pane, click Plan.
  3. By default, you see the tree representation of the query. To see the query plan, click the Show Visualization icon, or press Ctrl+Shift+Alt+U

@alkoln 2018-06-05 02:22:51

Here's one important thing to know in addition to everything said before.

Query plans are often too complex to be represented by the built-in XML column type which has a limitation of 127 levels of nested elements. That is one of the reasons why sys.dm_exec_query_plan may return NULL or even throw an error in earlier MS SQL versions, so generally it's safer to use sys.dm_exec_text_query_plan instead. The latter also has a useful bonus feature of selecting a plan for a particular statement rather than the whole batch. Here's how you use it to view plans for currently running statements:

SELECT p.query_plan
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_text_query_plan(
                r.plan_handle,
                r.statement_start_offset,
                r.statement_end_offset) AS p

The text column in the resulting table is however not very handy compared to an XML column. To be able to click on the result to be opened in a separate tab as a diagram, without having to save its contents to a file, you can use a little trick (remember you cannot just use CAST(... AS XML)), although this will only work for a single row:

SELECT Tag = 1, Parent = NULL, [ShowPlanXML!1!!XMLTEXT] = query_plan
FROM sys.dm_exec_text_query_plan(
                -- set these variables or copy values
                -- from the results of the above query
                @plan_handle,
                @statement_start_offset,
                @statement_end_offset)
FOR XML EXPLICIT

@vCillusion 2018-05-25 15:26:03

Starting from SQL Server 2016+, Query Store feature was introduced to monitor performance. It provides insight into query plan choice and performance. It’s not a complete replacement of trace or extended events, but as it’s evolving from version to version, we might get a fully functional query store in future releases from SQL Server. The primary flow of Query Store

  1. SQL Server existing components interact with query store by utilising Query Store Manager.
  2. Query Store Manager determines which Store should be used and then passes execution to that store (Plan or Runtime Stats or Query Wait Stats)
    • Plan Store - Persisting the execution plan information
    • Runtime Stats Store - Persisting the execution statistics information
    • Query Wait Stats Store - Persisting wait statistics information.
  3. Plan, Runtime Stats and Wait store uses Query Store as an extension to SQL Server.

enter image description here

  1. Enabling the Query Store: Query Store works at the database level on the server.

    • Query Store is not active for new databases by default.
    • You cannot enable the query store for the master or tempdb database.
    • Available DMV

      sys.database_query_store_options (Transact-SQL)

  2. Collect Information in the Query Store: We collect all the available information from the three stores using Query Store DMV (Data Management Views).

NOTE: Query Wait Stats Store is available only in SQL Server 2017+

@Marcin Czyz 2017-07-17 12:51:36

Beside the methods described in previous answers, you can also use a free execution plan viewer and query optimization tool ApexSQL Plan (which I’ve recently bumped into).

You can install and integrate ApexSQL Plan into SQL Server Management Studio, so execution plans can be viewed from SSMS directly.

Viewing Estimated execution plans in ApexSQL Plan

  1. Click the New Query button in SSMS and paste the query text in the query text window. Right click and select the “Display Estimated Execution Plan” option from the context menu.

New Query button in SSMS

  1. The execution plan diagrams will be shown the Execution Plan tab in the results section. Next right-click the execution plan and in the context menu select the “Open in ApexSQL Plan” option.

Execution Plan

  1. The Estimated execution plan will be opened in ApexSQL Plan and it can be analyzed for query optimization.

Estimated execution plan

Viewing Actual execution plans in ApexSQL Plan

To view the Actual execution plan of a query, continue from the 2nd step mentioned previously, but now, once the Estimated plan is shown, click the “Actual” button from the main ribbon bar in ApexSQL Plan.

click the “Actual” button from the main ribbon bar

Once the “Actual” button is clicked, the Actual execution plan will be shown with detailed preview of the cost parameters along with other execution plan data.

Actual execution plan

More information about viewing execution plans can be found by following this link.

@Justin 2011-09-09 09:37:30

There are a number of methods of obtaining an execution plan, which one to use will depend on your circumstances. Usually you can use SQL Server Management Studio to get a plan, however if for some reason you can't run your query in SQL Server Management Studio then you might find it helpful to be able to obtain a plan via SQL Server Profiler or by inspecting the plan cache.

Method 1 - Using SQL Server Management Studio

SQL Server comes with a couple of neat features that make it very easy to capture an execution plan, simply make sure that the "Include Actual Execution Plan" menu item (found under the "Query" menu) is ticked and run your query as normal.

Include Action Execution Plan menu item

If you are trying to obtain the execution plan for statements in a stored procedure then you should execute the stored procedure, like so:

exec p_Example 42

When your query completes you should see an extra tab entitled "Execution plan" appear in the results pane. If you ran many statements then you may see many plans displayed in this tab.

Screenshot of an Execution Plan

From here you can inspect the execution plan in SQL Server Management Studio, or right click on the plan and select "Save Execution Plan As ..." to save the plan to a file in XML format.

Method 2 - Using SHOWPLAN options

This method is very similar to method 1 (in fact this is what SQL Server Management Studio does internally), however I have included it for completeness or if you don't have SQL Server Management Studio available.

Before you run your query, run one of the following statements. The statement must be the only statement in the batch, i.e. you cannot execute another statement at the same time:

SET SHOWPLAN_TEXT ON
SET SHOWPLAN_ALL ON
SET SHOWPLAN_XML ON
SET STATISTICS PROFILE ON
SET STATISTICS XML ON -- The is the recommended option to use

These are connection options and so you only need to run this once per connection. From this point on all statements run will be acompanied by an additional resultset containing your execution plan in the desired format - simply run your query as you normally would to see the plan.

Once you are done you can turn this option off with the following statement:

SET <<option>> OFF

Comparison of execution plan formats

Unless you have a strong preference my recommendation is to use the STATISTICS XML option. This option is equivalent to the "Include Actual Execution Plan" option in SQL Server Management Studio and supplies the most information in the most convenient format.

  • SHOWPLAN_TEXT - Displays a basic text based estimated execution plan, without executing the query
  • SHOWPLAN_ALL - Displays a text based estimated execution plan with cost estimations, without executing the query
  • SHOWPLAN_XML - Displays an XML based estimated execution plan with cost estimations, without executing the query. This is equivalent to the "Display Estimated Execution Plan..." option in SQL Server Management Studio.
  • STATISTICS PROFILE - Executes the query and displays a text based actual execution plan.
  • STATISTICS XML - Executes the query and displays an XML based actual execution plan. This is equivalent to the "Include Actual Execution Plan" option in SQL Server Management Studio.

Method 3 - Using SQL Server Profiler

If you can't run your query directly (or your query doesn't run slowly when you execute it directly - remember we want a plan of the query performing badly), then you can capture a plan using a SQL Server Profiler trace. The idea is to run your query while a trace that is capturing one of the "Showplan" events is running.

Note that depending on load you can use this method on a production environment, however you should obviously use caution. The SQL Server profiling mechanisms are designed to minimize impact on the database but this doesn't mean that there won't be any performance impact. You may also have problems filtering and identifying the correct plan in your trace if your database is under heavy use. You should obviously check with your DBA to see if they are happy with you doing this on their precious database!

  1. Open SQL Server Profiler and create a new trace connecting to the desired database against which you wish to record the trace.
  2. Under the "Events Selection" tab check "Show all events", check the "Performance" -> "Showplan XML" row and run the trace.
  3. While the trace is running, do whatever it is you need to do to get the slow running query to run.
  4. Wait for the query to complete and stop the trace.
  5. To save the trace right click on the plan xml in SQL Server Profiler and select "Extract event data..." to save the plan to file in XML format.

The plan you get is equivalent to the "Include Actual Execution Plan" option in SQL Server Management Studio.

Method 4 - Inspecting the query cache

If you can't run your query directly and you also can't capture a profiler trace then you can still obtain an estimated plan by inspecting the SQL query plan cache.

We inspect the plan cache by querying SQL Server DMVs. The following is a basic query which will list all cached query plans (as xml) along with their SQL text. On most database you will also need to add additional filtering clauses to filter the results down to just the plans you are interested in.

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)

Execute this query and click on the plan XML to open up the plan in a new window - right click and select "Save execution plan as..." to save the plan to file in XML format.

Notes:

Because there are so many factors involved (ranging from the table and index schema down to the data stored and the table statistics) you should always try to obtain an execution plan from the database you are interested in (normally the one that is experiencing a performance problem).

You can't capture an execution plan for encrypted stored procedures.

"actual" vs "estimated" execution plans

An actual execution plan is one where SQL Server actually runs the query, whereas an estimated execution plan SQL Server works out what it would do without executing the query. Although logically equivalent, an actual execution plan is much more useful as it contains additional details and statistics about what actually happened when executing the query. This is essential when diagnosing problems where SQL Servers estimations are off (such as when statistics are out of date).

How do I interpret a query execution plan?

This is a topic worthy enough for a (free) book in its own right.

See also:

@Roimer 2013-10-16 13:46:09

A note for future readers: put SET STATISTICS XML ON ant the beginning of the query, and SET STATISTICS XML OFF|ON surrounding areas you don't want to be shown in the plan output: I found this useful when the query contains a iteration (WHILE) you don't want/need to see in the execution plan (otherwise it would too heavy and long for SQL SERVER to displaying it).

@bmarks 2015-02-20 19:38:45

@MonsterMMORPG you can use method 4 and then SELECT it. For example, using <a href="github.com/StackExchange/dapper-dot-net">Dapper.net</…‌​> connection.Query<string>("SELECT query_plan FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) CROSS APPLY sys.dm_exec_query_plan(plan_handle) WHERE TEXT LIKE N'%Your Original Query Goes Here%'"); The %'s are if you only use a subset of your query.

@Abdul 2016-09-22 14:22:42

@Justin the 2nd edition of the book you linked to, for interpreting a query execution plan, is dated from 2009. Would you still say that it is a really good resource for that purpose in 2016?

@thelem 2017-01-17 13:59:24

@Abdul The same author, Grant Fritchey, has a newer book called SQL Server Query Performance Tuning which covers newer versions of SQL Server.

@Tigerjz32 2016-09-09 19:25:22

Assuming you're using Microsoft SQL Server Management Studio

  • For Estimated Query Plan you can press Ctrl + L or the following button.

enter image description here

  • For Actual Query Plan, you can press Ctrl + M or the following button before executing query.

enter image description here

  • For Live Query Plan, (only in SSMS 2016) use the following button before executing query.

enter image description here

@Alexander Abakumov 2014-06-09 07:32:52

My favourite tool for obtaining and deeply analyzing query execution plans is SQL Sentry Plan Explorer. It's much more user-friendly, convenient and comprehensive for the detail analysis and visualization of execution plans than SSMS.

Here is a sample screen shot for you to have an idea of what functionality is offered by the tool:

SQL Sentry Plan Explorer window screen shot

It's only one of the views available in the tool. Notice a set of tabs to the bottom of the app window, which lets you get different types of your execution plan representation and useful additional information as well.

In addition, I haven't noticed any limitations of its free edition that prevents using it on a daily basis or forces you to purchase the Pro version eventually. So, if you prefer to stick with the free edition, nothing forbids you from doing so.

UPDATE: (Thanks to Martin Smith) Plan Explorer now is free! See http://www.sqlsentry.com/products/plan-explorer/sql-server-query-view for details.

@basher 2015-12-02 21:19:35

Who was speaking of third-party tools?

@Alexander Abakumov 2015-12-02 22:53:37

@basher: OP didn't limit the means with MS tools or somehow else. So what makes you think an answer involving a third-party tool is an inappropriate one?

@basher 2015-12-02 23:54:15

Was just joking around with how you phrased the start of your answer Speaking of third-party tools when nobody mentioned third party tools.

@Alexander Abakumov 2015-12-03 11:54:43

@basher: Oh, nice catch! Thank you! I've rewritten my answer. Please feel free to give a feedback and/or upvote it if you like.

@Martin Smith 2016-09-10 11:47:58

@vCillusion 2018-06-02 21:34:56

Starting from SQL Server 2016+, Query Store feature was introduced to monitor performance. It provides insight into query plan choice and performance. stackoverflow.com/a/50532505/1688718

@Dave Mason 2015-11-11 19:06:33

Query plans can be obtained from an Extended Events session via the query_post_execution_showplan event. Here's a sample XEvent session:

/*
    Generated via "Query Detail Tracking" template.
*/
CREATE EVENT SESSION [GetExecutionPlan] ON SERVER 
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(package0.event_sequence,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)),

/* Remove any of the following events (or include additional events) as desired. */
ADD EVENT sqlserver.error_reported(
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.module_end(SET collect_statement=(1)
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.rpc_completed(
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1)
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(package0.event_sequence,sqlserver.client_app_name,sqlserver.database_id,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))) 
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO

After you create the session, (in SSMS) go to the Object Explorer and delve down into Management | Extended Events | Sessions. Right-click the "GetExecutionPlan" session and start it. Right-click it again and select "Watch Live Data".

Next, open a new query window and run one or more queries. Here's one for AdventureWorks:

USE AdventureWorks;
GO

SELECT p.Name AS ProductName, 
    NonDiscountSales = (OrderQty * UnitPrice),
    Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)
FROM Production.Product AS p 
INNER JOIN Sales.SalesOrderDetail AS sod
    ON p.ProductID = sod.ProductID 
ORDER BY ProductName DESC;
GO

After a moment or two, you should see some results in the "GetExecutionPlan: Live Data" tab. Click one of the query_post_execution_showplan events in the grid, and then click the "Query Plan" tab below the grid. It should look similar to this:

enter image description here

EDIT: The XEvent code and the screen shot were generated from SQL/SSMS 2012 w/ SP2. If you're using SQL 2008/R2, you might be able to tweak the script to make it run. But that version doesn't have a GUI, so you'd have to extract the showplan XML, save it as a *.sqlplan file and open it in SSMS. That's cumbersome. XEvents didn't exist in SQL 2005 or earlier. So, if you're not on SQL 2012 or later, I'd strongly suggest one of the other answers posted here.

@Martin Smith 2011-09-09 09:43:15

In addition to the comprehensive answer already posted sometimes it is useful to be able to access the execution plan programatically to extract information. Example code for this is below.

DECLARE @TraceID INT
EXEC StartCapture @@SPID, @TraceID OUTPUT
EXEC sp_help 'sys.objects' /*<-- Call your stored proc of interest here.*/
EXEC StopCapture @TraceID

Example StartCapture Definition

CREATE PROCEDURE StartCapture
@Spid INT,
@TraceID INT OUTPUT
AS
DECLARE @maxfilesize BIGINT = 5
DECLARE @filepath NVARCHAR(200) = N'C:\trace_' + LEFT(NEWID(),36)

EXEC sp_trace_create @TraceID OUTPUT, 0, @filepath, @maxfilesize, NULL 

exec sp_trace_setevent @TraceID, 122, 1, 1
exec sp_trace_setevent @TraceID, 122, 22, 1
exec sp_trace_setevent @TraceID, 122, 34, 1
exec sp_trace_setevent @TraceID, 122, 51, 1
exec sp_trace_setevent @TraceID, 122, 12, 1
-- filter for spid
EXEC sp_trace_setfilter @TraceID, 12, 0, 0, @Spid
-- start the trace
EXEC sp_trace_setstatus @TraceID, 1

Example StopCapture Definition

CREATE  PROCEDURE StopCapture
@TraceID INT
AS
WITH  XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as sql), 
      CTE
     as (SELECT CAST(TextData AS VARCHAR(MAX)) AS TextData,
                ObjectID,
                ObjectName,
                EventSequence,
                /*costs accumulate up the tree so the MAX should be the root*/
                MAX(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost
         FROM   fn_trace_getinfo(@TraceID) fn
                CROSS APPLY fn_trace_gettable(CAST(value AS NVARCHAR(200)), 1)
                CROSS APPLY (SELECT CAST(TextData AS XML) AS xPlan) x
                CROSS APPLY (SELECT T.relop.value('@EstimatedTotalSubtreeCost',
                                            'float') AS EstimatedTotalSubtreeCost
                             FROM   xPlan.nodes('//sql:RelOp') T(relop)) ca
         WHERE  property = 2
                AND TextData IS NOT NULL
                AND ObjectName not in ( 'StopCapture', 'fn_trace_getinfo' )
         GROUP  BY CAST(TextData AS VARCHAR(MAX)),
                   ObjectID,
                   ObjectName,
                   EventSequence)
SELECT ObjectName,
       SUM(EstimatedTotalSubtreeCost) AS EstimatedTotalSubtreeCost
FROM   CTE
GROUP  BY ObjectID,
          ObjectName  

-- Stop the trace
EXEC sp_trace_setstatus @TraceID, 0
-- Close and delete the trace
EXEC sp_trace_setstatus @TraceID, 2
GO

Related Questions

Sponsored Content

29 Answered Questions

37 Answered Questions

40 Answered Questions

[SOLVED] How to return only the Date from a SQL Server DateTime datatype

10 Answered Questions

[SOLVED] Improve INSERT-per-second performance of SQLite?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

40 Answered Questions

[SOLVED] Parameterize an SQL IN clause

8 Answered Questions

[SOLVED] How does database indexing work?

27 Answered Questions

16 Answered Questions

[SOLVED] How can I do an UPDATE statement with JOIN in SQL?

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

Sponsored Content