Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

Database User and Programming Tips

Posted April 2, 2018

How to Show the Execution Plan of a Running Query

By Greg Larsen

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

Database User and Programming Tips Archives