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