How to Show the Execution Plan of a Running Query

When you are trying to tune a query, you can look at the query execution plan associated with the TSQL code.  By looking at the execution plan you can  determine which steps are used to resolve the query.  But what if you just want to find out what the query plans are for only the queries that are currently running?

SQL Server first compiles the TSQL that needs to be executed and stores the query execution plan in the procedure cache.  Therefore, by looking in the procedure cache one can identify the execution plan of a given query.  To see all the execution plans for executing queries I can run the following TSQL code:

SELECT QP.query_plan as [Query Plan], 
       ST.text AS [Query Text]
FROM sys.dm_exec_requests AS R
   CROSS APPLY sys.dm_exec_query_plan(R.plan_handle) AS QP
   CROSS APPLY sys.dm_exec_sql_text(R.plan_handle) ST;

The above code identified the commands that are running by using the sys.dm_exec_requests DMV.  I then use the CROSS APPLY operator to join the plan_handle of the executing queries with query plans in the procedure cache by using the sys.dm_exec_query_plan DMF.  Finally I use another CROSS APPLY operator to find the sql text using  the sys.dm_exec_sql_text DMF .   When I run the code above on my instance of SQL Server it displays the “Query Plan” and “Query Text” as shown below.


Query Plan and Query Text
Query Plan and Query Text

By double clicking on the XML shown in the Query Plan column it will bring up the execution plan for the query that is running.

See all articles by Greg Larsen

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles