PLAN CACHE에대해 (예상실행계획)내부 결과를 반환하는 캐시쿼리

-- 그림을 표로 표시해줌.

 

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
    cp.query_hash,cp.query_plan_hash,
    PhysicalOperator= operators.value('@PhysicalOp','nvarchar(50)'),
    LogicalOp= operators.value('@LogicalOp','nvarchar(50)'),
    AvgRowSize= operators.value('@AvgRowSize','nvarchar(50)'),
    EstimateCPU= operators.value('@EstimateCPU','nvarchar(50)'),
    EstimateIO= operators.value('@EstimateIO','nvarchar(50)'),
    EstimateRebinds= operators.value('@EstimateRebinds','nvarchar(50)'),
    EstimateRewinds= operators.value('@EstimateRewinds','nvarchar(50)'),
    EstimateRows= operators.value('@EstimateRows','nvarchar(50)'),
    Parallel= operators.value('@Parallel','nvarchar(50)'),
    NodeId= operators.value('@NodeId','nvarchar(50)'),
    EstimatedTotalSubtreeCost= operators.value('@EstimatedTotalSubtreeCost','nvarchar(50)')
FROM 
	sys.dm_exec_query_stats AS cp
CROSS APPLY 
	sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY 
	query_plan.nodes('//RelOp') rel(operators)
go

 

 

아래 표는 예상실행계획을 표 형식으로 반환한것이다.

 

 

 

 

---

 

 

예시)

 

 

 

ADVENTUREWORKS2019에서

 

select 
 a.FirstName, a.LastName
from
person.person as a
where
a.FirstName = 'Ken'
go


select 
 a.FirstName, a.LastName
from
person.person as a
where
a.FirstName = 'Kim'
go

을 실행한다. 그럼 캐시에 내가 검색한 쿼리문이 TEXT로 올라간다.

 

 

올라간것들을 검색해본다.

select 
	st.text, qs.query_hash
from
	sys.dm_exec_query_stats as qs
cross apply
	sys.dm_exec_sql_text(qs.sql_handle) as st

검색해서 출력된 결과물에서 아래와 같이 방금 검색한 내용하나를 찾았다 (0xFBE0B11CE7751C89)

 

 

아래 예상실행계획(내부결과)를 반환하는 쿼리문을 실행하면

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
    cp.query_hash,cp.query_plan_hash,
    PhysicalOperator= operators.value('@PhysicalOp','nvarchar(50)'),
    LogicalOp= operators.value('@LogicalOp','nvarchar(50)'),
    AvgRowSize= operators.value('@AvgRowSize','nvarchar(50)'),
    EstimateCPU= operators.value('@EstimateCPU','nvarchar(50)'),
    EstimateIO= operators.value('@EstimateIO','nvarchar(50)'),
    EstimateRebinds= operators.value('@EstimateRebinds','nvarchar(50)'),
    EstimateRewinds= operators.value('@EstimateRewinds','nvarchar(50)'),
    EstimateRows= operators.value('@EstimateRows','nvarchar(50)'),
    Parallel= operators.value('@Parallel','nvarchar(50)'),
    NodeId= operators.value('@NodeId','nvarchar(50)'),
    EstimatedTotalSubtreeCost= operators.value('@EstimatedTotalSubtreeCost','nvarchar(50)')
FROM 
	sys.dm_exec_query_stats AS cp
CROSS APPLY 
	sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY 
	query_plan.nodes('//RelOp') rel(operators)
go

 

아래와 같이 결과가 나오는데 여기서 QUERY_HASH번호가 같은것으로 조회 할수 있다.

너무 많으면 엑셀창에 붙여넣고 찾기하면 된다.

 

 

(0xFBE0B11CE7751C89) 위와 동일한것을 찾았다!