How to Obtain a Query Execution Plan in SQL Server

How do I obtain a Query Execution Plan in SQL Server?

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.

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.

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).

• Estimated and Actual execution plan revisited

How do I interpret a query execution plan?

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

See also:

• Execution Plan Basics
• SHOWPLAN Permission and Transact-SQL Batches
• SQL Server 2008 – Using Query Hashes and Query Plan Hashes
• Analyzing the SQL Server Plan Cache

Get actual execution plan for query taking more than hours?

@VikrantMore, here is the query to check the queries which are using tempdb and it's execution plan. See this can helps you.

SELECT
t1.session_id
, t1.request_id
, task_alloc_GB = CAST((t1.task_alloc_pages * 8./1024./1024.) AS NUMERIC(10,1))
, task_dealloc_GB = CAST((t1.task_dealloc_pages * 8./1024./1024.) AS NUMERIC(10,1))
, host= CASE WHEN t1.session_id <= 50 then 'SYS' else s1.host_name end
, s1.login_name
, s1.status
, s1.last_request_start_time
, s1.last_request_end_time
, s1.row_count
, s1.transaction_isolation_level
, query_text=
    COALESCE((SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
      (CASE WHEN statement_end_offset = -1
          THEN LEN(CONVERT(nvarchar(MAX),text)) * 2
               ELSE statement_end_offset
          END - t2.statement_start_offset)/2)
    FROM sys.dm_exec_sql_text(t2.sql_handle)) , 'Not currently executing')
, query_plan=(SELECT query_plan FROM sys.dm_exec_query_plan(t2.plan_handle))
FROM
(SELECT session_id, request_id
, task_alloc_pages=SUM(internal_objects_alloc_page_count +   
user_objects_alloc_page_count)
, task_dealloc_pages = SUM (internal_objects_dealloc_page_count + 
user_objects_dealloc_page_count)
FROM sys.dm_db_task_space_usage
GROUP BY session_id, request_id) AS t1
LEFT JOIN sys.dm_exec_requests AS t2 ON
t1.session_id = t2.session_id
and t1.request_id = t2.request_id
LEFT JOIN sys.dm_exec_sessions AS s1 ON
t1.session_id=s1.session_id
WHERE
t1.session_id > 50 -- ignore system unless you suspect there's a problem there
and t1.session_id <> @@SPID -- ignore this request itself
ORDER BY t1.task_alloc_pages DESC;
GO 

How to view execution plans in SQL Server on Linux

Microsoft released a new tool called SQL Operations studio,this is similar to SSMS,but is available on Windows,Linux,Macos.

location for download:

https://docs.microsoft.com/en-us/sql/sql-operations-studio/download

Below is a screenshot of how it looks like

To view actual execution plan using sqlopsstudio(steps same for all platforms)

• Press CTRL+SHIFT+P
• Type run query with actual execution plan as shown below and select the highlighted, you will get an actual execution plan

To view estimated execution plan :

Just press the ICON shown below

You can also use a keybinding to view actual execution plan .Below are the steps

1.Press CTRL+SHIFT+P

2.Type keyboard shortcuts

3.In the search plan type actual as shown below


4.Right click actual query plan shortcut and say add key binding with a key of your choice(for me it is CTRL+M

Below Part of the answer was written during the time when SQLOPS studio is not available.This can ben helpfull for any one who don't have SQLopsstudio:

Currently viewing execution plan is supported only if you are on Windows,using SSMS or some third party tool like SQLSentry..

There is a feature request being tracked here :Return ShowPlan data as Text or XML with Query Execution

one more option is to connect using VSCODE on linux and set show plan xml as shown in screenshot below..this provides xml of execution plan

SET showplan_xml ON;

you can take that xml and upload it Paste The Plan website and can view plans

Below is a screenshot of above XML

you can also view it in SQLSENTRY plan explorer as well(Windows only) for more indepth analysis

How do you get a SQL Server Query execution plan in VS Code?

Sorry to say, but you do not.

Getting the raw data is not that complicated..

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

But that deos NOT give you the visualization, and that is simply not built into VS Code. An extension WOULD possibly do it - but noone has to my knowledge written that one.

Sorry, sometimes the answer is just "not happening".

Getting query / execution plan for dynamic sql in SQL Server

Just press ctrl + m in SQL Server Management Studio to activate "Include Actual Execution Plan". This will show the real execution plan just as with any ordinary query.

---

Related Topics