최적화가 필요한 PlanCache 캐시쿼리로 확인(찾기)

 

인덱스가 없는것

/*
	최적화가 필요한 PlanCache 확인(찾기)
	인덱스가 없는 현재 모든 클랜 캐시를 찾음
*/
SELECT
    dec.usecounts, 
	dec.refcounts, 
	dec.objtype,
	dec.cacheobjtype, 
	des.dbid, 
	des.text,
	deq.query_plan
FROM 
	sys.dm_exec_cached_plans AS dec
CROSS APPLY 
	sys.dm_exec_sql_text(dec.plan_handle) AS des
CROSS APPLY 
	sys.dm_exec_query_plan(dec.plan_handle) AS deq
WHERE
	deq.query_plan.exist
	(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0
ORDER BY dec.usecounts DESC

go

이미 캐시쿼리 청소를 해버려서 아무것도 나오지 않는다리.....

 

 

 

 

인덱스가 있는것

--인덱스가 있는것.
WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

SELECT
    cp.query_hash,
	cp.query_plan_hash,
    ConvertIssue= operators.value('@ConvertIssue', 'nvarchar(250)'),
    Expression= operators.value('@Expression', 'nvarchar(250)'), 
	qp.query_plan
FROM 
	sys.dm_exec_query_stats cp
CROSS APPLY 
	sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY 
	query_plan.nodes('//Warnings/PlanAffectingConvert') rel(operators) --xml로 나옴

go

 

 

query_plan 링크를 누르면 

이런식의 XML형식이 뜬다.