반응형
sql server 의 성능 튜닝을 하거나, 실행 계획이 xml 형태로 추출되는 경우가 있습니다. SSMS 에서 실행 계획을 보고 싶은 쿼리에서 Ctrl+L 이나 Ctrl+M 을 누르면 예상 실행 계획, 실제 실행 계획을 GUI 로 그려주는데, 결국 xml 로 추출된 정보를 파싱하여 그래픽적으로 표시해주는 것입니다.
이런 과정을 설명하는 이유는 SSMS 는 윈도우 OS 에서만 동작하고, 몇몇 기능은 SSMS 가 아니면 사용할 수 없고, SSMS 를 사용하지 않는 개발자가 실행 계획을 SSMS 수준으로 보고 싶을 때 등 입니다.
그럼 xml 로 추출되는 실행 계획은 어떤 형태인지, 이것을 어떻게 그래픽으로 전환하여 볼 수 있는지 등을 알아보겠습니다.
쿼리에 대한 실행 계획을 XML 로 얻는 방법
아래와 같은 명령어를 사용해서 실행할 쿼리에 대한 실행 계획을 출력할 수 있습니다.
SET SHOWPLAN_XML ON;
위 명령어를 실행한 이후, 실행 계획을 보고 싶은 쿼리를 실행하면, 쿼리가 실제 실행되는 것이 아닌, 실행 계획을 xml 포맷으로 얻을 수 있습니다.
SELECT * FROM sys.objects
WHERE type <> 'S';
예를 들어 위와 같은 쿼리를 실행하면, 아래와 같은 XML 을 얻을 수 있습니다.
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.518" Build="13.0.5893.48"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT * FROM sys.objects
WHERE type <> 'S'" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0639527" StatementEstRows="501" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x77F485DDEB8F41B4" QueryPlanHash="0xEAB53BC0ABF34472" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" ParameterizedText="(@1 varchar(8000))SELECT * FROM [sys].[objects] WHERE [type]<>@1"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="120" CompileTime="7" CompileCPU="7" CompileMemory="968"><MemoryGrantInfo SerialRequiredMemory="2048" SerialDesiredMemory="2144"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="629145" EstimatedPagesCached="157286" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="15374536"></OptimizerHardwareDependentProperties><RelOp NodeId="0" PhysicalOp="Hash Match" LogicalOp="Right Outer Join" EstimateRows="501" EstimateRowsWithoutRowGoal="3732.84" EstimateIO="0" EstimateCPU="0.0479122" AvgRowSize="154" EstimatedTotalSubtreeCost="0.0639527" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid"></ColumnReference><ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="name"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Expr1008"></ColumnReference></OutputList><MemoryFractions Input="0.5" Output="0.5"></MemoryFractions><Hash><DefinedValues></DefinedValues><HashKeysBuild><ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="value"></ColumnReference></HashKeysBuild><HashKeysProbe><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type"></ColumnReference></HashKeysProbe><ProbeResidual><ScalarOperator ScalarString="[mssqlsystemresource].[sys].[syspalnames].[value] as [n].[value]=[wholedoc].[sys].[sysschobjs].[type] as [o].[type]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="value"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></ProbeResidual><RelOp NodeId="1" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="31" EstimatedRowsRead="31" EstimateIO="0.003125" EstimateCPU="0.0001911" AvgRowSize="75" EstimatedTotalSubtreeCost="0.0033161" TableCardinality="143" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="value"></ColumnReference><ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="name"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="value"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="name"></ColumnReference></DefinedValue></DefinedValues><Object Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Index="[cl]" Alias="[n]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[mssqlsystemresource]" Schema="[sys]" Table="[syspalnames]" Alias="[n]" Column="class"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="'OBTY'"><Const ConstValue="'OBTY'"></Const></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp><RelOp NodeId="2" PhysicalOp="Hash Match" LogicalOp="Right Outer Join" EstimateRows="501" EstimateRowsWithoutRowGoal="3732.84" EstimateIO="0" EstimateCPU="0.0376987" AvgRowSize="94" EstimatedTotalSubtreeCost="0.0373804" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Expr1008"></ColumnReference></OutputList><MemoryFractions Input="0.5" Output="0.5"></MemoryFractions><Hash><DefinedValues></DefinedValues><HashKeysBuild><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="depid"></ColumnReference></HashKeysBuild><HashKeysProbe><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id"></ColumnReference></HashKeysProbe><RelOp NodeId="3" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="1" EstimatedRowsRead="335" EstimateIO="0.003125" EstimateCPU="0.0005255" AvgRowSize="20" EstimatedTotalSubtreeCost="0.0036505" TableCardinality="335" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="depid"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid"></ColumnReference></OutputList><IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="depid"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="indepid"></ColumnReference></DefinedValue></DefinedValues><Object Database="[wholedoc]" Schema="[sys]" Table="[syssingleobjrefs]" Index="[nc1]" Alias="[r]" IndexKind="NonClustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[wholedoc].[sys].[syssingleobjrefs].[class] as [r].[class]=(97) AND [wholedoc].[sys].[syssingleobjrefs].[depsubid] as [r].[depsubid]=(0)"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="class"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(97)"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[syssingleobjrefs]" Alias="[r]" Column="depsubid"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp><RelOp NodeId="4" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="501" EstimateRowsWithoutRowGoal="3732.84" EstimateIO="0" EstimateCPU="0.00715904" AvgRowSize="90" EstimatedTotalSubtreeCost="0.0129946" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Expr1008"></ColumnReference></OutputList><Filter StartupExpression="0"><RelOp NodeId="5" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="501" EstimateRowsWithoutRowGoal="3732.84" EstimateIO="0" EstimateCPU="0.0003808" AvgRowSize="92" EstimatedTotalSubtreeCost="0.0120338" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Expr1008"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="CONVERT(char(2),CASE WHEN [wholedoc].[sys].[sysschobjs].[type] as [o].[type]='ET' THEN 'U' ELSE [wholedoc].[sys].[sysschobjs].[type] as [o].[type] END,0)"><Convert DataType="char" Length="2" Style="0" Implicit="0"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="'ET'"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="'U'"></Const></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type"></ColumnReference></Identifier></ScalarOperator></Else></IF></ScalarOperator></Convert></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="CONVERT(bit,[wholedoc].[sys].[sysschobjs].[status] as [o].[status]&(1),0)"><Convert DataType="bit" Style="0" Implicit="0"><ScalarOperator><Arithmetic Operation="BIT_AND"><ScalarOperator><Identifier><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Arithmetic></ScalarOperator></Convert></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1007"></ColumnReference><ScalarOperator ScalarString="CONVERT(bit,[wholedoc].[sys].[sysschobjs].[status] as [o].[status]&(16),0)"><Convert DataType="bit" Style="0" Implicit="0"><ScalarOperator><Arithmetic Operation="BIT_AND"><ScalarOperator><Identifier><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(16)"></Const></ScalarOperator></Arithmetic></ScalarOperator></Convert></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1008"></ColumnReference><ScalarOperator ScalarString="CONVERT(bit,[wholedoc].[sys].[sysschobjs].[status] as [o].[status]&(64),0)"><Convert DataType="bit" Style="0" Implicit="0"><ScalarOperator><Arithmetic Operation="BIT_AND"><ScalarOperator><Identifier><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(64)"></Const></ScalarOperator></Arithmetic></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="6" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="501" EstimateRowsWithoutRowGoal="3732.84" EstimatedRowsRead="3808" EstimateIO="0.0638657" EstimateCPU="0.0043458" AvgRowSize="93" EstimatedTotalSubtreeCost="0.0119826" TableCardinality="3808" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created"></ColumnReference><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified"></ColumnReference></OutputList><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="name"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsid"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="status"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pid"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="created"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="modified"></ColumnReference></DefinedValue></DefinedValues><Object Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Index="[clst]" Alias="[o]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[wholedoc].[sys].[sysschobjs].[nsclass] as [o].[nsclass]=(0) AND [wholedoc].[sys].[sysschobjs].[pclass] as [o].[pclass]=(1) AND CONVERT(char(2),CASE WHEN [wholedoc].[sys].[sysschobjs].[type] as [o].[type]='ET' THEN 'U' ELSE [wholedoc].[sys].[sysschobjs].[type] as [o].[type] END,0)<>'S'"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="nsclass"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="pclass"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="NE"><ScalarOperator><Convert DataType="char" Length="2" Style="0" Implicit="0"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="'ET'"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="'U'"></Const></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="type"></ColumnReference></Identifier></ScalarOperator></Else></IF></ScalarOperator></Convert></ScalarOperator><ScalarOperator><Const ConstValue="'S'"></Const></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp></ComputeScalar></RelOp><Predicate><ScalarOperator ScalarString="has_access('CO',[wholedoc].[sys].[sysschobjs].[id] as [o].[id])=(1)"><Compare CompareOp="EQ"><ScalarOperator><Intrinsic FunctionName="has_access"><ScalarOperator><Const ConstValue="'CO'"></Const></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[wholedoc]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue=""></Const></ScalarOperator><ScalarOperator><Const ConstValue=""></Const></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></Hash></RelOp></Hash></RelOp><ParameterList><ColumnReference Column="@1" ParameterDataType="varchar(8000)" ParameterCompiledValue="'S'"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
실행 계획을 보는 것이 아닌 쿼리를 실행하는 방법은 SHOWPLAN_XML 값을 다시 OFF 로 되돌리면 됩니다.
SET SHOWPLAN_XML OFF;
자, 그럼 위에서 추출한 XML 은 그냥 눈으로 확인하기에는 불편한 구조로 되어 있습니다.
이것을 편하게 SSMS 의 실행 계획 수준으로 보여주는 프로젝트가 있습니다.
https://github.com/JustinPealing/html-query-plan/releases/latest
Release v2.6.1 · JustinPealing/html-query-plan
Fixes #93
github.com
위 링크를 따라가 zip 파일을 다운로드하고, 적당한 위치에 압축을 해제하면 여러 파일이 보이는데, examples 폴더를 열어, 여러 예시중에 자신에게 맞는 것을 사용하면 되지만, 간단하게 xml 정보를 복사, 붙여넣기 해서 볼 수 있는 index.html 파일을 아래 적어놓겠습니다. 아래 내용을 압축을 해제한 폴더에 파일을 생성하고 사용하면 됩니다.
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="UTF-8">
<title>html-query-plan</title>
<link rel="stylesheet" type="text/css" href="./css/qp.css" />
<script src="./dist/qp.js"></script>
<style>
body {
font-family: Arial, sans-serif;
margin: 0;
padding: 0;
background-color: #f4f4f4;
}
h3 {
text-align: center;
margin: 10px 0px;
color: #333;
}
#container {
width: 90%;
margin: 20px;
padding: 20px;
background-color: #fff;
box-shadow: 0px 0px 5px rgba(0, 0, 0, 0.2);
}
#textInput {
width: 90%;
margin: 20px;
padding: 20px;
border: 1px solid #ccc;
border-radius: 5px;
resize: vertical;
}
#processButton {
display: block;
margin: 0px auto;
padding: 10px 20px;
background-color: #007bff;
color: #fff;
border: none;
border-radius: 5px;
cursor: pointer;
}
#processButton:hover {
background-color: #0056b3;
}
</style>
</head>
<body>
<h3>HTML QUERY PLAN</h3>
<textarea id="textInput" rows="10" cols="60" placeholder="여기에 ShowPlanXml 붙여넣기"></textarea>
<button id="processButton">출력</button>
<div id="container"></div>
<script>
document.getElementById('processButton').addEventListener('click', function () {
var inputText = document.getElementById('textInput').value;
QP.showPlan(document.getElementById("container"), inputText);
});
</script>
</body>
</html>
위 index.html 를 실행하면, 브라우저에서 아래처럼 표시됩니다.
이제 위에서 추출한 XML 정보를 복사, 붙여넣기 해서 출력을 눌러보겠습니다.
위와 같이, SSMS 에서 확인할 수 있는 실행 계획과 동일한 수준으로 실행 계획을 확인할 수 있습니다. 각 요소별 마우스 오버 시 추가적은 정보도 출력됩니다. 이렇게 SSMS 를 사용할 수 없는 환경에서도 SSMS 와 동등한 수준의 실행 계획을 파악할 수 있는 방법을 알아보았습니다.
반응형
