실행계획... 에 어디까지 나오나?
http://www.devpia.com/Maeul/Contents/Detail.aspx?BoardID=41&MAEULNo=17&no=10732&ref=10732
댓글 수정하면 스크립트가 다 깨져서 블로그로 옮깁니다.
compute scalar 가 union all 을 수행하는 메타 데이터를 검색해야 하는데 이때 server_info 와 t_ipinfo 의 컬럼 정보를 가져와 merge 를 수행합니다. 그러므로 server_info 의 meta data와 호환되지 않는 잠금이 있다면 실행되지 않습니다. tablockx 를 holdlock 으로 걸어 계속 유지를 한다면, 해당 object 전체가 잠기게 됩니다. (schema 를 포함해서 모두 잠깁니다.)
그러므로, 2번째 쿼리는 compute scalar 이터레이터를 server_info 의 컬럼정보가 모두 잠겨있으니 union all 을 수행하지 못하고 대기하고 있는중입니다.
session 69 에서 실행한 쿼리
BEGIN TRAN
SELECT * FROM TBLX WITH (tablockx, holdlock)
session_id 68에서 실행한 쿼리
SELECT * FROM TBLX WHERE 1=0
UNION ALL
SELECT * FROM TBLY WHERE 1=1
select * from sys.dm_tran_locks
확인
object id mode request_status session id
245575913 0 X LOCK GRANT 69 <---- object X lock 이 걸림
245575913 0 IS LOCK WAIT 68 <---- 두번째 쿼리는 해당 object 에 IS 락을 얻기위해 대기합니다.
내부에 메터데이터를 unionall 하는것은 3가지 정도로 확인할 수 있습니다.
tablename object_id
--------------
tblx 245575913
tbly 261575970
테스트 쿼리
--------------
SELECT * FROM TBLX WHERE 1=0
UNION ALL
SELECT * FROM TBLY WHERE 1=1
1,
xml plan 에 outputlist 를 확인하면 아래와 같은것이 있을겁니다. 이는 메터를 확인하는 과정이 추가된다고 추측할 수 있습니다.
그러나, server_info 에 접근하는 것은 볼 수 없죠. (저는 tblx 가 server_info 이고 tbly 가 t_ipinfo 입니다. )
<ColumnReference Column="Union1008" />
<DefinedValue>
<ColumnReference Column="Union1008" />
<ScalarOperator ScalarString="[t].[dbo].[tbly].[cd]">
<Identifier>
<ColumnReference Database="[t]" Schema="[dbo]" Table="[tbly]" Column="cd" />
</Identifier>
</ScalarOperator>
</DefinedValue>
2,
메타에 접근하는지는 실제 lock 을 얻으러가는것을 조회해 보면 되는데 실제 테이블에 먼저 접근해 unionall 도 수행하고 server_info에 접근하는것도 볼 수 있습니다. session_id 는 바꾸어 테스트 하시길 바랍니다.
CREATE EVENT SESSION [XE_PLAN3] ON SERVER
ADD EVENT sqlos.wait_info(
ACTION(package0.callstack,sqlserver.sql_text,sqlserver.tsql_stack)
WHERE ([sqlserver].[session_id]=(68))),
ADD EVENT sqlserver.expression_compile_stop_batch_processing(
ACTION(package0.callstack,sqlserver.sql_text)
WHERE ([sqlserver].[session_id]=(68))),
ADD EVENT sqlserver.lock_acquired(
ACTION(package0.callstack,sqlserver.sql_text)
WHERE ([sqlserver].[session_id]=(68))),
ADD EVENT sqlserver.locks_lock_waits(
ACTION(package0.callstack,sqlserver.sql_text,sqlserver.tsql_stack)
WHERE ([sqlserver].[session_id]=(68))),
ADD EVENT sqlserver.query_post_compilation_showplan(
ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)
WHERE ([sqlserver].[session_id]=(68))),
ADD EVENT sqlserver.sql_batch_starting(SET collect_batch_text=(1)
ACTION(package0.callstack,sqlserver.sql_text)
WHERE ([sqlserver].[session_id]=(68)))
ADD TARGET package0.event_file(SET filename=N'XE_PLAN3'),
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=102400 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=1 SECONDS,MAX_EVENT_SIZE=307200 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
ALTER EVENT SESSION XE_PLAN3 ON SERVER STATE=START
GO
tblx 즉 server_info에 sch_s 락을 요구하는것은 확인할 수 있습니다.
<event name="lock_acquired" package="sqlserver" timestamp="2014-11-02T08:16:37.103Z">
<data name="resource_type">
<type name="lock_resource_type" packge="sqlserver"></type>
<value>5</value>
<text><![CDATA[OBJECT]]></text>
</data>
<data name="mode">
<type name="lock_mode" package="sqlserver"></type>
<value>1</value>
<text><![CDATA[SCH_S]]></text>
</data>
<data name="owner_type">
<type name="lock_owner_type" package="sqlserver"></type>
<value>1</value>
<text><![CDATA[Transaction]]></text>
</data>
<data name="transaction_id">
<type name="int64" package="package0"></type>
<value>151279</value>
</data>
<data name="database_id">
<type name="uint32" package="package0"></type>
<value>5</value>
</data>
<data name="lockspace_workspace_id">
<type name="ptr" package="package0"></type>
<value>0x00000004746c1770</value>
</data>
<data name="lockspace_sub_id">
<type name="uint32" package="package0"></type>
<value>1</value>
</data>
<data name="lockspace_nest_id">
<type name="uint32" package="package0"></type>
<value>1</value>
</data>
<data name="resource_0">
<type name="uint32" package="package0"></type>
<value>245575913</value> <--- 이부분이 tblx 입니다.
</data>
<data name="resource_1">
<type name="uint32" package="package0"></type>
<value>0</value>
</data>
<data name="resource_2">
<type name="uint32" package="package0"></type>
<value>0</value>
</data>
<data name="object_id">
<type name="int32" package="package0"></type>
<value>245575913</value>
</data>
<data name="associated_object_id">
<type name="uint64" package="package0"></type>
<value>245575913</value>
</data>
<data name="duration">
<type name="uint64" package="package0"></type>
<value>0</value>
</data>
<data name="resource_description">
<type name="unicode_string" package="package0"></type>
<value><![CDATA[]]></value>
</data>
<data name="database_name">
<type name="unicode_string" package="package0"></type>
<value><![CDATA[]]></value>
</data>
<action name="callstack" package="package0">
<type name="callstack" package="package0"></type>
<value><![CDATA[XeSqlPkg::lock_acquired::Publish+1ba [ @ 0+0x0
lck_ProduceExtendedEvent<XeSqlPkg::lock_acquired>+189 [ @ 0+0x0
lck_lockInternal+1402 [ @ 0+0x0
MDL::LockObjectLocal+443 [ @ 0+0x0
CMEDCacheEntryFactory::AcquireLock+48 [ @ 0+0x0
CMEDCatalogObject::GetCachedObjectById+157 [ @ 0+0x0
CMEDCatalogObject::GetCachedObjectByName+6b [ @ 0+0x0
CMEDProxySchema::GetObjectByName+6e [ @ 0+0x0
IMetadataAccess::GetMultiNameObjectHelper+6dd [ @ 0+0x0
CMEDAccess::GetMultiNameObject+c9 [ @ 0+0x0
CAlgTableMetadata::FPartialBind+712 [ @ 0+0x0
CAlgTableMetadata::Bind+31d [ @ 0+0x0
CRelOp_Get::BindTree+483 [ @ 0+0x0
COptExpr::BindTree+85 [ @ 0+0x0
CRelOp_FromList::BindTree+37 [ @ 0+0x0
COptExpr::BindTree+85 [ @ 0+0x0
CRelOp_Select::BindTree+32 [ @ 0+0x0
COptExpr::BindTree+85 [ @ 0+0x0
CRelOp_QuerySpec::BindTree+f0 [ @ 0+0x0
COptExpr::BindTree+85 [ @ 0+0x0
CRelOp_UnionAll::BindTree+118 [ @ 0+0x0
COptExpr::BindTree+85 [ @ 0+0x0
CRelOp_SelectQuery::BindTree+80 [ @ 0+0x0
COptExpr::BindTree+85 [ @ 0+0x0]]></value>
</action>
<action name="sql_text" package="sqlserver">
<type name="unicode_string" package="package0"></type>
<value><![CDATA[SELECT * FROM TBLX WHERE 1=0
UNION ALL
SELECT * FROM TBLY WHERE 1=1]]></value>
</action>
</event>
callstack 을 모니터링 하는 방법은 http://sqlsql.tistory.com/353 를 참조하시길 바랍니다.
3,
플랜의 노드를 좀 더 상세히 보면
dbcc traceon (3604)
dbcc traceon (7352) -- 쿼리 트리 노드의 추가정보 출력
SELECT * FROM TBLX WHERE 1=0
UNION ALL
SELECT * FROM TBLY WHERE 1=1
tblx 의 컬럼정보를 기준으로 union1008, 9, 10 이 생성되는 것을 볼 수 있습니다.
그러니 where 1=0 이 있다고 하더라도 컬럼정보는 본다고 하는것이 옳습니다.
Compute Scalar Compute Scalar (0)
[CALC:COL: Union1008 TI(int,Null,ML=4)] 0000000472C9A470 P N
[CALC:COL: Union1009 TI(varchar collate 53265,Null,Var,Trim,ML=10)] 0000000472C9A560 P N
[CALC:COL: Union1010 TI(varchar collate 53265,Null,Var,Trim,ML=10)] 0000000472C9A640 P N
Table Scan Table Scan (1)
[QCOL: [t].[dbo].[tbly].cd TI(int,Null,ML=4)] 0000000472C9A160 P N
[QCOL: [t].[dbo].[tbly].status TI(varchar collate 53265,Null,Var,Trim,ML=10)] 0000000472C9A2A0 P N
[QCOL: [t].[dbo].[tbly].c1 TI(varchar collate 53265,Null,Var,Trim,ML=10)] 0000000472C9A3D0 P N
-- 증명
create table abcd
(aaabbb int
,cccddd int
)
go
create table efgh
(aaabbb bigint
,cccddd bigint
)
go
dbcc traceon (3604)
dbcc traceon (7352)
select * from abcd where 1=1
union all
select * from efgh where 1=0
go
-- 실제 사용은 하지 않지만 efgh 테이블에서 bigint 컬럼을 기준으로 union 하는 과정을 볼 수 있음
Compute Scalar Compute Scalar (0)
[CALC:COL: Union1010 TI(bigint,Null,ML=8)] 0000000472496240 P N
[CALC:COL: Union1011 TI(bigint,Null,ML=8)] 0000000472496320 P N
Table Scan Table Scan (1)
[QCOL: [t].[dbo].[abcd].aaabbb TI(int,Null,ML=4)] 0000000472496060 P N
[QCOL: [t].[dbo].[abcd].cccddd TI(int,Null,ML=4)] 0000000472496190 P N
-- int 만 실행한 경우 (bigint convert 과정이 없다)
select * from abcd where 1=1
Table Scan Table Scan (0)
[QCOL: [t].[dbo].[abcd].aaabbb TI(int,Null,ML=4)] 0000000474A81B30 P N
[QCOL: [t].[dbo].[abcd].cccddd TI(int,Null,ML=4)] 0000000474A81C60 P N
실행계획에 모든것이 나오지는 않습니다.
위 댓글은 실행계획에서 잘 표시되지 않은 부분을 extended events로 찾고 실행계획에서 빠진 unionall 부분을 증명한 부분입니다.
아래 블로그는 paul white 라는 분이 작성한 글입니다. 잘 알려지지 않은 prefetch 를 설명하고 실행계획 그림에는 나타나지 않고 debug를 통해서 확인은 가능하다는것을 보여줍니다. prefetch 가 한번 생성된 실행계획은 cold 이든 warm 상태이든 나타나게 됩니다. 이것을 scalar function call 의 실행횟수를 보여주며 증명 합니다. 또한 실행계획의 노드 id 가 건너뛴것을 보면 일부 생략된것을 알 수 있습니다. 시간 나실때 paul white 님의 블로그를 보시면 sql server optimizer 에 대해 깊이있는 공부를 할 수 있을겁니다.
실행계획은 왼쪽에서 오른쪽 상위번호로 컨트롤 시그널을 보내고 상위번호에서 아래번호로 데이터를 페치합니다. 흔히들 오른쪽부터 읽는다라고 하지만 이는 조금 불완전한 설명입니다. 학원이나 강의에서 오른쪽 위부터 읽는다라고 가르치면 찬찬히 설명해 주시길 바랍니다. ;)