2012-01-07 session script
1월 7일 발표한
tempdb repro script 입니다.
아티클을 참고해 공부하세요~
http://sqlcat.com/sqlcat/b/technicalnotes/archive/2011/01/25/table-valued-functions-and-tempdb-contention.aspx
The REMOVE parameter specifies which components to uninstall, like SQL Server, Reporting Services, or Analysis Services. This command must be used in conjunction with the INSTANCENAME parameter. For example, to remove the instance aware component AND any non-instance aware components, such as Client_Components, you would specify the following:
Important: |
---|
Do not use this parameter if you have several instances installed and you still want to use client tools. |
REMOVE can also be used during maintenance to remove components from an existing installation. For example, to remove Full-Text Search, specify the following:
Important: |
---|
Removing the parent feature removes both the parent and the child feature. |
REMOVE=
커서 유형 선택은 다음을 비롯한 여러 변수에 따라 달라집니다.
결과 집합의 크기
필요한 데이터 비율
열린 커서의 성능
스크롤이나 업데이트 등과 같은 커서 작업의 필요성
다른 사용자가 수행하는 데이터 수정의 가시성 수준
커서 유형을 선택할 때 따라야 하는 몇 가지 규칙은 다음과 같습니다.
기본 테이블 및 정방향 전용 커서는 정적 커서 또는 키 집합 커서보다 빨리 열립니다. 정적 커서 및 키 집합 커서가 열린 경우 내부 임시 작업 테이블을 작성해야 하지만 기본 테이블 및 정방향 전용 커서에는 이러한 테이블이 필요하지 않습니다.
정적 및 키 집합 커서의 경우 임시 데이터베이스의 사용량이 늘어납니다. 정적 서버 커서는 임시 데이터베이스에 전체 커서를 작성합니다. 키 집합 커서는 임시 데이터베이스에 키 집합을 작성합니다.
프로젝션이나 선택 없이 기본 테이블을 있는 그대로 검색할 경우에는 최소한의 오버헤드를 가지는 기본 테이블 커서를 사용하는 것이 좋습니다.
정적 커서는 데이터를 복제하기 때문에 가장 많은 리소스를 소비합니다.
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))
술한잔 벌었다......으흐흐흐흐