Using DMVs to find Execution Plan of Currently Running Queries

We would check on currently running queries how do we find execution plan of the queries. For demo purpose would create some tables and try some long running queries

Compiled Plan 

• Compiled plan is product of query optimization
• Stored in object store or sql store
• Compiled plan would specify which table and indexes to access
• Multiple concurrently executing queries can share same compiled plan
• Can be shared between multiple sessions and users

 Executable Plan

• Adding Parameter, variable to compiled plan
• Information specific to one particular execution
• Runtime objects created when compiled plan is executed

Plan Handle

• Cached compiled plan retrieved using plan handle
• sys.dm_exec_cached_plans contains plan handle for every compiled plan
• Plan handle is hash value sql server derives from compiled plan

SQLhandle

• Actual text stored in SQL Manager Cache
• Transact SQL text cached in sql manager cache retrieved using sql_handle

SQL Handle: Plan handle - 1:N

Source: MSDN Blogs. I hope I learnt things right. Sql_Handle and Plan_Handle Explained

Session 1
Step 1
USE TEMPDB
CREATE TABLE DBO.TESTTable
(
Id INT Identity(10,10),
Name VARCHAR(20)
)
 
STEP 2
--Insert some records with below command. I am trying on SQL 2008 R2
INSERT INTO DBO.TESTTable(Name)
Values ('Testvalue')
GO 50

--STEP 3 --Lets try running below statement, This is a infinite loop

While 1 =1
BEGIN
      SELECT Top 1 Name FROM DBO.TESTTable
END
 
Open another query session and lets try to get details on this query from DMVs
Session 2
STEP 1
USE TEMPDB
--View the query and Plan of currently running queries
SELECT *
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
 
Output would be like below

Take the plan handle value displayed in the right and run below query with plan handle value
 
STEP 2
SELECT *
FROM sys.dm_exec_sql_text(0x060002003A882A10B820FF04000000000000000000000000)
--Plan Handle

You will see the running query details as below

You will see the running query details as below

STEP3

To view the execution plan of the query run the below query with the plan handle value

SELECT *

FROM sys.dm_exec_query_plan(0x0600020056218802B880F104000000000000000000000000)

This would show us the XML show plan as below

Take the plan and check further. More details on execution plan you can find on execution plan tagged posts.


More Reads
GOTCHA: SQL Server changes query plan without changing plan_handle


Happy Reading!!