Created
August 19, 2016 15:42
-
-
Save jjradha/c3c571a79e0787a16eaecd0cb2a3adb8 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?xml version="1.0" encoding="utf-16"?> | |
| <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.2000.8" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> | |
| <BatchSequence> | |
| <Batch> | |
| <Statements> | |
| <StmtSimple StatementCompId="7" StatementEstRows="1000070" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="24.5016" StatementText="select t.*
from RADHE_01 t
where t.MYXML.exist('*') = 0 OR t.MYXML IS NULL" StatementType="SELECT" QueryHash="0x78CFEF157D3D2516" QueryPlanHash="0x831670FA8C31EE5A" RetrievedFromCache="true"> | |
| <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> | |
| <QueryPlan DegreeOfParallelism="1" CachedPlanSize="24" CompileTime="1" CompileCPU="1" CompileMemory="312"> | |
| <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" /> | |
| <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="417953" EstimatedPagesCached="104488" EstimatedAvailableDegreeOfParallelism="2" /> | |
| <RelOp AvgRowSize="4039" EstimateCPU="4.18031" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1000070" LogicalOp="Left Semi Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="24.5016"> | |
| <OutputList> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="i" /> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="MYXML" /> | |
| </OutputList> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRows="9361" ActualEndOfScans="1" ActualExecutions="1" /> | |
| </RunTimeInformation> | |
| <NestedLoops Optimized="false"> | |
| <OuterReferences> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="MYXML" /> | |
| </OuterReferences> | |
| <RelOp AvgRowSize="4039" EstimateCPU="1.10024" EstimateIO="17.6409" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1000070" LogicalOp="Clustered Index Scan" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="18.7411" TableCardinality="1000070"> | |
| <OutputList> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="i" /> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="MYXML" /> | |
| </OutputList> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRows="1000074" ActualEndOfScans="1" ActualExecutions="1" /> | |
| </RunTimeInformation> | |
| <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> | |
| <DefinedValues> | |
| <DefinedValue> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="i" /> | |
| </DefinedValue> | |
| <DefinedValue> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="MYXML" /> | |
| </DefinedValue> | |
| </DefinedValues> | |
| <Object Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Index="[PK__RADHE_01__3BD01996A3797134]" Alias="[t]" IndexKind="Clustered" Storage="RowStore" /> | |
| </IndexScan> | |
| </RelOp> | |
| <RelOp AvgRowSize="9" EstimateCPU="2E-07" EstimateIO="0" EstimateRebinds="999074" EstimateRewinds="999.037" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Concatenation" NodeId="2" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="1.58012"> | |
| <OutputList /> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRows="9361" ActualEndOfScans="990713" ActualExecutions="1000074" /> | |
| </RunTimeInformation> | |
| <Concat> | |
| <DefinedValues /> | |
| <RelOp AvgRowSize="9" EstimateCPU="4.8E-07" EstimateIO="0" EstimateRebinds="999074" EstimateRewinds="999.037" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Filter" NodeId="3" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="1.48011"> | |
| <OutputList /> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRebinds="1000074" ActualRewinds="0" ActualRows="821" ActualEndOfScans="999253" ActualExecutions="1000074" /> | |
| </RunTimeInformation> | |
| <Filter StartupExpression="true"> | |
| <RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="1000070" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Constant Scan" NodeId="4" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="1.00007"> | |
| <OutputList /> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRows="821" ActualEndOfScans="0" ActualExecutions="821" /> | |
| </RunTimeInformation> | |
| <ConstantScan /> | |
| </RelOp> | |
| <Predicate> | |
| <ScalarOperator ScalarString="[radhe2].[dbo].[RADHE_01].[MYXML] as [t].[MYXML] IS NULL"> | |
| <Compare CompareOp="IS"> | |
| <ScalarOperator> | |
| <Identifier> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="MYXML" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| <ScalarOperator> | |
| <Const ConstValue="NULL" /> | |
| </ScalarOperator> | |
| </Compare> | |
| </ScalarOperator> | |
| </Predicate> | |
| </Filter> | |
| </RelOp> | |
| <RelOp AvgRowSize="9" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="999074" EstimateRewinds="999.037" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Left Anti Semi Join" NodeId="6" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="24.5016"> | |
| <OutputList /> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRows="8540" ActualEndOfScans="990713" ActualExecutions="999253" /> | |
| </RunTimeInformation> | |
| <NestedLoops Optimized="false"> | |
| <RelOp AvgRowSize="9" EstimateCPU="4.8E-07" EstimateIO="0" EstimateRebinds="999074" EstimateRewinds="999.037" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Filter" NodeId="7" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="1.48011"> | |
| <OutputList /> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRebinds="999253" ActualRewinds="0" ActualRows="999253" ActualEndOfScans="990713" ActualExecutions="999253" /> | |
| </RunTimeInformation> | |
| <Filter StartupExpression="true"> | |
| <RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="1000070" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Constant Scan" NodeId="8" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="1.00007"> | |
| <OutputList /> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRows="999253" ActualEndOfScans="990713" ActualExecutions="999253" /> | |
| </RunTimeInformation> | |
| <ConstantScan /> | |
| </RelOp> | |
| <Predicate> | |
| <ScalarOperator ScalarString="[radhe2].[dbo].[RADHE_01].[MYXML] as [t].[MYXML] IS NOT NULL"> | |
| <Compare CompareOp="IS NOT"> | |
| <ScalarOperator> | |
| <Identifier> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="MYXML" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| <ScalarOperator> | |
| <Const ConstValue="NULL" /> | |
| </ScalarOperator> | |
| </Compare> | |
| </ScalarOperator> | |
| </Predicate> | |
| </Filter> | |
| </RelOp> | |
| <RelOp AvgRowSize="9" EstimateCPU="1.004" EstimateIO="0" EstimateRebinds="999074" EstimateRewinds="999.037" EstimatedExecutionMode="Row" EstimateRows="200" LogicalOp="Table-valued function" NodeId="10" Parallel="false" PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="24.5016"> | |
| <OutputList /> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRebinds="999253" ActualRewinds="0" ActualRows="990713" ActualEndOfScans="8540" ActualExecutions="999253" /> | |
| </RunTimeInformation> | |
| <TableValuedFunction> | |
| <DefinedValues /> | |
| <Object Table="[XML Reader with XPath filter]" /> | |
| <ParameterList> | |
| <ScalarOperator ScalarString="[radhe2].[dbo].[RADHE_01].[MYXML] as [t].[MYXML]"> | |
| <Identifier> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="MYXML" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| <ScalarOperator ScalarString="(0)"> | |
| <Const ConstValue="(0)" /> | |
| </ScalarOperator> | |
| <ScalarOperator ScalarString="NULL"> | |
| <Const ConstValue="NULL" /> | |
| </ScalarOperator> | |
| <ScalarOperator ScalarString="NULL"> | |
| <Const ConstValue="NULL" /> | |
| </ScalarOperator> | |
| </ParameterList> | |
| </TableValuedFunction> | |
| </RelOp> | |
| </NestedLoops> | |
| </RelOp> | |
| </Concat> | |
| </RelOp> | |
| </NestedLoops> | |
| </RelOp> | |
| </QueryPlan> | |
| </StmtSimple> | |
| </Statements> | |
| </Batch> | |
| <Batch> | |
| <Statements> | |
| <StmtSimple StatementCompId="14" StatementEstRows="1499.68" StatementId="2" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="18.9197" StatementText="select t.*
from RADHE_01 t
where DATALENGTH(t.MYXML) = 5 OR t.MYXML IS NULL" StatementType="SELECT" QueryHash="0xC30E805056088476" QueryPlanHash="0xF605B725DBEB3FDB" RetrievedFromCache="true"> | |
| <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> | |
| <QueryPlan DegreeOfParallelism="4" MemoryGrant="72" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="192"> | |
| <ThreadStat Branches="1" UsedThreads="4"> | |
| <ThreadReservation NodeId="0" ReservedThreads="4" /> | |
| </ThreadStat> | |
| <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" RequiredMemory="72" DesiredMemory="72" RequestedMemory="72" GrantWaitTime="0" GrantedMemory="72" MaxUsedMemory="72" /> | |
| <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="417953" EstimatedPagesCached="104488" EstimatedAvailableDegreeOfParallelism="2" /> | |
| <RelOp AvgRowSize="4039" EstimateCPU="0.238672" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1499.68" LogicalOp="Gather Streams" NodeId="0" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="18.9197"> | |
| <OutputList> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="i" /> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="MYXML" /> | |
| </OutputList> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRows="9361" ActualEndOfScans="1" ActualExecutions="1" /> | |
| </RunTimeInformation> | |
| <Parallelism> | |
| <RelOp AvgRowSize="4039" EstimateCPU="0.440033" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1499.68" LogicalOp="Filter" NodeId="1" Parallel="true" PhysicalOp="Filter" EstimatedTotalSubtreeCost="18.6811"> | |
| <OutputList> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="i" /> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="MYXML" /> | |
| </OutputList> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="4" ActualRows="1481" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" /> | |
| <RunTimeCountersPerThread Thread="3" ActualRows="2272" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" /> | |
| <RunTimeCountersPerThread Thread="2" ActualRows="2752" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" /> | |
| <RunTimeCountersPerThread Thread="1" ActualRows="2856" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" /> | |
| <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" /> | |
| </RunTimeInformation> | |
| <Filter StartupExpression="false"> | |
| <RelOp AvgRowSize="4043" EstimateCPU="0.0500037" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1000070" LogicalOp="Compute Scalar" NodeId="2" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="18.241"> | |
| <OutputList> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="i" /> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="MYXML" /> | |
| <ColumnReference Column="Expr1001" /> | |
| </OutputList> | |
| <ComputeScalar> | |
| <DefinedValues> | |
| <DefinedValue> | |
| <ColumnReference Column="Expr1001" /> | |
| <ScalarOperator ScalarString="datalength([radhe2].[dbo].[RADHE_01].[MYXML] as [t].[MYXML])"> | |
| <Intrinsic FunctionName="datalength"> | |
| <ScalarOperator> | |
| <Identifier> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="MYXML" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| </Intrinsic> | |
| </ScalarOperator> | |
| </DefinedValue> | |
| </DefinedValues> | |
| <RelOp AvgRowSize="4039" EstimateCPU="0.550119" EstimateIO="17.6409" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1000070" LogicalOp="Clustered Index Scan" NodeId="3" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="18.191" TableCardinality="1000070"> | |
| <OutputList> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="i" /> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="MYXML" /> | |
| </OutputList> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="3" ActualRows="243108" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" /> | |
| <RunTimeCountersPerThread Thread="4" ActualRows="158172" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" /> | |
| <RunTimeCountersPerThread Thread="2" ActualRows="293748" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" /> | |
| <RunTimeCountersPerThread Thread="1" ActualRows="305046" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" /> | |
| <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" /> | |
| </RunTimeInformation> | |
| <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> | |
| <DefinedValues> | |
| <DefinedValue> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="i" /> | |
| </DefinedValue> | |
| <DefinedValue> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="MYXML" /> | |
| </DefinedValue> | |
| </DefinedValues> | |
| <Object Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Index="[PK__RADHE_01__3BD01996A3797134]" Alias="[t]" IndexKind="Clustered" Storage="RowStore" /> | |
| </IndexScan> | |
| </RelOp> | |
| </ComputeScalar> | |
| </RelOp> | |
| <Predicate> | |
| <ScalarOperator ScalarString="[Expr1001]=(5) OR [radhe2].[dbo].[RADHE_01].[MYXML] as [t].[MYXML] IS NULL"> | |
| <Logical Operation="OR"> | |
| <ScalarOperator> | |
| <Compare CompareOp="EQ"> | |
| <ScalarOperator> | |
| <Identifier> | |
| <ColumnReference Column="Expr1001" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| <ScalarOperator> | |
| <Const ConstValue="(5)" /> | |
| </ScalarOperator> | |
| </Compare> | |
| </ScalarOperator> | |
| <ScalarOperator> | |
| <Compare CompareOp="IS"> | |
| <ScalarOperator> | |
| <Identifier> | |
| <ColumnReference Database="[radhe2]" Schema="[dbo]" Table="[RADHE_01]" Alias="[t]" Column="MYXML" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| <ScalarOperator> | |
| <Const ConstValue="NULL" /> | |
| </ScalarOperator> | |
| </Compare> | |
| </ScalarOperator> | |
| </Logical> | |
| </ScalarOperator> | |
| </Predicate> | |
| </Filter> | |
| </RelOp> | |
| </Parallelism> | |
| </RelOp> | |
| </QueryPlan> | |
| </StmtSimple> | |
| </Statements> | |
| </Batch> | |
| </BatchSequence> | |
| </ShowPlanXML> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment