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
- Adding Parameter, variable to compiled plan
- Information specific to one particular execution
- Runtime objects created when compiled plan is executed
- 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
- Actual text stored in SQL Manager Cache
- Transact SQL text cached in sql manager cache retrieved using sql_handle
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
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!!
No comments:
Post a Comment