dbcc rule on off 로 플랜 작성 후 planguide 로 고정 시키기
hash join 으로 풀리나 merge join 이 발생하도록 하고 planguide 로 고정 시켰다.
음 이러한 planguide 는 cursor 계획에도 적용 가능하다.
커서는 실행계획이 일반 쿼리 계획과 다르게 나올 수 있다.
커서 타입이나 락 타입 로케이션 등에 따라 다양하게 플랜이 바뀔 수 있고, 이때 비 효율적인 플랜이 선택 된다면
planguide 로 어플리케이션 변경없이 플랜을 가이드 할 수 있다.
또한 fn_validate_plan_guide 를 이용해 planguide 가 에러가 나면, 에러의 원인을 알 수 있다.
요지는 rule 컨트롤로 쿼리 변경 없이 plan 을 만들어 내고 고정 할 수 있다는 것이다.
if object_id ('tblx') is not null
drop table tblx
go
if object_id ('tbly') is not null
drop table tbly
go
create table tblx (cint int, cvarchar varchar(100))
create table tbly (cint int, cvarchar varchar(100))
dbcc freeproccache
go
drop table t_before
go
drop table t_after
go
select * into t_before from sys.dm_exec_query_transformation_stats
go
select * from tblx a join tblx b on a.cint = b.cint where a.cint < 1
go
select * into t_after from sys.dm_exec_query_transformation_stats
go
select *
from t_before a
join t_after b
on a.name = b.name
and a.succeeded <> b.succeeded
go
dbcc ruleoff('JNtoHS')
-- 플랜을 프로파일러에서 받아내고,
dbcc ruleon('JNtoHS')
-- 받아낸 플랜을 적용하고 다시 룰을 원상 복구한다.
-- 복구된 상황에서 플랜 가이드에 의해 올바르게 의도한 플랜으로 풀렸는지 확인한다.
exec sp_create_plan_guide
@name = N'Guide',
@stmt = N'select * from tblx a join tblx b on a.cint = b.cint where a.cint < 1',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION(USE PLAN N''<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.50.2500.0"><BatchSequence><Batch><Statements><StmtSimple><QueryPlan CachedPlanSize="24" CompileTime="2" CompileCPU="2" CompileMemory="160"><RelOp NodeId="0" PhysicalOp="Merge Join" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0.000313" EstimateCPU="0.00564738" AvgRowSize="121" EstimatedTotalSubtreeCost="0.0352532" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[a]" Column="cint"/><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[a]" Column="cvarchar"/><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[b]" Column="cint"/><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[b]" Column="cvarchar"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0"/></RunTimeInformation><Merge ManyToMany="1"><InnerSideJoinColumns><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[a]" Column="cint"/></InnerSideJoinColumns><OuterSideJoinColumns><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[b]" Column="cint"/></OuterSideJoinColumns><Residual><ScalarOperator ScalarString="[SQLTAG2008R2].[dbo].[tblx].[cint] as [b].[cint]=[SQLTAG2008R2].[dbo].[tblx].[cint] as [a].[cint]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[b]" Column="cint"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[a]" Column="cint"/></Identifier></ScalarOperator></Compare></ScalarOperator></Residual><RelOp NodeId="1" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1" EstimateIO="0.0112613" EstimateCPU="0.000100065" AvgRowSize="65" EstimatedTotalSubtreeCost="0.0146449" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[b]" Column="cint"/><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[b]" Column="cvarchar"/></OutputList><MemoryFractions Input="1" Output="0.5"/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" ActualRebinds="0" ActualRewinds="0" ActualEndOfScans="0" ActualExecutions="0"/></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[b]" Column="cint"/></OrderByColumn></OrderBy><RelOp NodeId="2" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="65" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[b]" Column="cint"/><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[b]" Column="cvarchar"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0"/></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[b]" Column="cint"/></DefinedValue><DefinedValue><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[b]" Column="cvarchar"/></DefinedValue></DefinedValues><Object Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[b]" IndexKind="Heap"/><Predicate><ScalarOperator ScalarString="[SQLTAG2008R2].[dbo].[tblx].[cint] as [b].[cint]<(1)"><Compare CompareOp="LT"><ScalarOperator><Identifier><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[b]" Column="cint"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Compare></ScalarOperator></Predicate></TableScan></RelOp></Sort></RelOp><RelOp NodeId="3" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1" EstimateIO="0.0112613" EstimateCPU="0.000100065" AvgRowSize="65" EstimatedTotalSubtreeCost="0.0146449" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[a]" Column="cint"/><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[a]" Column="cvarchar"/></OutputList><MemoryFractions Input="0.5" Output="0.5"/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" ActualRebinds="0" ActualRewinds="0" ActualEndOfScans="0" ActualExecutions="0"/></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[a]" Column="cint"/></OrderByColumn></OrderBy><RelOp NodeId="4" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="65" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"><OutputList><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[a]" Column="cint"/><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[a]" Column="cvarchar"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0"/></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0"><DefinedValues><DefinedValue><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[a]" Column="cint"/></DefinedValue><DefinedValue><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[a]" Column="cvarchar"/></DefinedValue></DefinedValues><Object Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[a]" IndexKind="Heap"/><Predicate><ScalarOperator ScalarString="[SQLTAG2008R2].[dbo].[tblx].[cint] as [a].[cint]<(1)"><Compare CompareOp="LT"><ScalarOperator><Identifier><ColumnReference Database="[SQLTAG2008R2]" Schema="[dbo]" Table="[tblx]" Alias="[a]" Column="cint"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Compare></ScalarOperator></Predicate></TableScan></RelOp></Sort></RelOp></Merge></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>'')'
select plan_guide_id from sys.plan_guides
select * from sys.fn_validate_plan_guide(65545)
EXEC sp_control_plan_guide N'DROP', N'Guide'
option (loop join, table hint (th_, index(d_1f0000018000000f), forceseek))
술한잔 벌었다......으흐흐흐흐