How do I obtain a Query Execution Plan in SQL Server

Question

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

User · Answer

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   SQL Server existing components interact with query store by utilising Query Store Manager  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   Plan  Runtime Stats and Wait store uses Query Store as an extension to SQL Server       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    Collect Information in the Query Store  We collect all the available information from the three stores using Query Store DMV  Data Management Views     Query Plan Store   Persisting the execution plan information and it is accountable for capturing all information that is related to query compilation      sys query store query  Transact-SQL    sys query store plan  Transact-SQL    sys query store query text  Transact-SQL   Runtime Stats Store   Persisting the execution statistics information and it is probably the most frequently updated store  These statistics represent query execution data      sys query store runtime stats  Transact-SQL   Query Wait Stats Store   Persisting and capturing wait statistics information      sys query store wait stats  Transact-SQL      NOTE  Query Wait Stats Store is available only in SQL Server 2017

User · Answer

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

User · Answer

You can also do it via powershell using SET STATISTICS XML ON to get the actual plan  I ve written it so that it merges multi-statement plans into one plan                  BEGIN   SCRIPT VARIABLES                            string  server      MySQLServer       string  database    MyDatabase       string  sqlCommand    EXEC sp ExampleSproc       string  XMLOutputFileName    sp ExampleSproc       string  XMLOutputPath    C  SQLDumps ActualPlans                  END     SCRIPT VARIABLES                             Set up connection      connectionString    Persist Security Info False Integrated Security true Connection Timeout 0 Initial Catalog  database Server  server       connection   new-object system data SqlClient SQLConnection  connectionString        Set up commands      command   new-object system data sqlclient sqlcommand  sqlCommand  connection       command CommandTimeout   0      commandXMLActPlanOn   new-object system data sqlclient sqlcommand  SET STATISTICS XML ON   connection       commandXMLActPlanOff   new-object system data sqlclient sqlcommand  SET STATISTICS XML OFF   connection        connection Open         Enable session XML plan      result    commandXMLActPlanOn ExecuteNonQuery         Execute SP and return resultsets into a dataset      adapter   New-Object System Data sqlclient sqlDataAdapter  command      dataset   New-Object System Data DataSet      adapter Fill  dataSet    Out-Null       Set up output file name and path      string  fileNameDateStamp   get-date -f yyyyMMdd HHmmss      string  XMLOutputFilePath     XMLOutputPath XMLOutputFileName   fileNameDateStamp sqlplan        Pull XML plans out of dataset and merge into one multi-statement plan      int  cntr   1     ForEach  table in  dataset Tables                    if  table Columns 0  ColumnName -eq  Microsoft SQL Server 2005 XML Showplan                                   string  fullXMLPlan    Table rows 0   Microsoft SQL Server 2005 XML Showplan                   if  cntr -eq 1                                              regex  rx      lt ShowPlanXML xmlns    1    lt Statements  gt                        string  startXMLPlan    rx Match  fullXMLPlan  Value                      regex  rx      lt   Statements  gt   1    lt   ShowPlanXML  gt                        string  endXMLPlan    rx Match  fullXMLPlan  Value                       startXMLPlan   out-file -Append -FilePath  XMLOutputFilePath                                          regex  rx      lt StmtSimple  1    lt   StmtSimple  gt                    string  bodyXMLPlan    rx Match  fullXMLPlan  Value                   bodyXMLPlan   out-file -Append -FilePath  XMLOutputFilePath                   cntr    1                            endXMLPlan   out-file -Append -FilePath  XMLOutputFilePath       Disable session XML plan      result    commandXMLActPlanOff ExecuteNonQuery         connection Close

User · Answer

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   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       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       The Estimated execution plan will be opened in ApexSQL Plan and it can be analyzed for query optimization      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     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     More information about viewing execution plans can be found by following this link

User · Answer

Like with SQL Server Management Studio  already explained   it is also possible with Datagrip as explained here         Right-click an SQL statement  and select Explain plan    In the Output pane  click Plan    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

User · Answer

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

User · Answer

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

User · Answer

Estimated execution plan The estimated execution plan is generated by the Optimizer without running the SQL query  In order to get the estimated execution plan  you need to enable the SHOWPLAN ALL setting prior to executing the query  SET SHOWPLAN ALL ON  Now  when executing the following SQL query  SELECT p id FROM post p WHERE EXISTS     SELECT 1   FROM post comment pc   WHERE     pc post id   p id AND     pc review    Bingo    ORDER BY p title OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY  SQL Server will generate the following estimated execution plan    NodeId   Parent   LogicalOp              EstimateRows   EstimateIO    EstimateCPU   AvgRowSize   TotalSubtreeCost   EstimateExecutions    -------- -------- ---------------------- -------------- ------------- ------------- ------------ ------------------ --------------------    1        0        NULL                   10             NULL          NULL          NULL         0 03374284         NULL                   2        1        Top                    10             0             3 00E-06      15           0 03374284         1                      4        2        Distinct Sort          30             0 01126126    0 000504114   146          0 03373984         1                      5        4        Inner Join             46 698         0             0 00017974    146          0 02197446         1                      6        5        Clustered Index Scan   43             0 004606482   0 0007543     31           0 005360782        1                      7        5        Clustered Index Seek   1              0 003125      0 0001581     146          0 0161733          43                    After running the query we are interested in getting the estimated execution plan  you need to disable the SHOWPLAN ALL as  otherwise  the current database session will only generate estimated execution plan instead of executing the provided SQL queries  SET SHOWPLAN ALL OFF  SQL Server Management Studio estimated plan In the SQL Server Management Studio application  you can easily get the estimated execution plan for any SQL query by hitting the CTRL L key shortcut   Actual execution plan The actual SQL execution plan is generated by the Optimizer when running the SQL query  If the database table statistics are accurate  the actual plan should not differ significantly from the estimated one  To get the actual execution plan on SQL Server  you need to enable the STATISTICS IO  TIME  PROFILE settings  as illustrated by the following SQL command  SET STATISTICS IO  TIME  PROFILE ON  Now  when running the previous query  SQL Server is going to generate the following execution plan    Rows   Executes   NodeId   Parent   LogicalOp              EstimateRows   EstimateIO    EstimateCPU   AvgRowSize   TotalSubtreeCost    ------ ---------- -------- -------- ---------------------- -------------- ------------- ------------- ------------ ------------------    10     1          1        0        NULL                   10             NULL          NULL          NULL         0 03338978           10     1          2        1        Top                    1 00E 01       0             3 00E-06      15           0 03338978           30     1          4        2        Distinct Sort          30             0 01126126    0 000478783   146          0 03338679           41     1          5        4        Inner Join             44 362         0             0 00017138    146          0 02164674           41     1          6        5        Clustered Index Scan   41             0 004606482   0 0007521     31           0 005358581          41     41         7        5        Clustered Index Seek   1              0 003125      0 0001581     146          0 0158571            SQL Server parse and compile time     CPU time   8 ms  elapsed time   8 ms     10 row s  affected  Table  Worktable   Scan count 0  logical reads 0  physical reads 0  read-ahead reads 0  lob logical reads 0  lob physical reads 0  lob read-ahead reads 0  Table  post   Scan count 0  logical reads 116  physical reads 0  read-ahead reads 0  lob logical reads 0  lob physical reads 0  lob read-ahead reads 0  Table  post comment   Scan count 1  logical reads 5  physical reads 0  read-ahead reads 0  lob logical reads 0  lob physical reads 0  lob read-ahead reads 0     6 row s  affected    SQL Server Execution Times     CPU time   0 ms   elapsed time   1 ms   After running the query we are interested in getting the actual execution plan  you need to disable the STATISTICS IO  TIME  PROFILE ON settings like this  SET STATISTICS IO  TIME  PROFILE OFF  SQL Server Management Studio actual plan In the SQL Server Management Studio application  you can easily get the estimated execution plan for any SQL query by hitting the CTRL M key shortcut

User · Answer

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  lt  lt option gt  gt  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    Open SQL Server Profiler and create a new trace connecting to the desired database against which you wish to record the trace  Under the  Events Selection  tab check  Show all events   check the  Performance  -   Showplan XML  row and run the trace  While the trace is running  do whatever it is you need to do to get the slow running query to run  Wait for the query to complete and stop the trace  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

User · Answer

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     lt -- 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

User · Answer

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

User · Answer

Assuming you re using Microsoft SQL Server Management Studio    For Estimated Query Plan you can press Ctrl   L or the following button       For Actual Query Plan  you can press Ctrl   M or the following button before executing query       For Live Query Plan   only in SSMS 2016  use the following button before executing query

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

Examples related to sql

Examples related to sql-server

Examples related to performance

Examples related to tsql

Examples related to sql-execution-plan