Created
August 3, 2016 13:53
-
-
Save jjradha/637c00eea7094b57ce69b21480b05db1 to your computer and use it in GitHub Desktop.
query plan procedure dbo.udpProductBulletPointSelectByTier1NoteTypeCode
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="8" StatementEstRows="60" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="123.48" StatementText="insert into @NoteTypeCodeTable ( NoteTypeCode ) 
 select BulletPoint.NoteTypeCode.value('.', 'varchar(50)') as NoteTypeCode 
 from @ListNoteTypeCodeXml.nodes('/BulletPoint/NoteTypeCode') as BulletPoint ( NoteTypeCode )" StatementType="INSERT" QueryHash="0x3A3C4752330FF5F1" QueryPlanHash="0xCEDD1B1B9CDA6E3C" 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="0" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="48" CompileTime="48" CompileCPU="48" CompileMemory="784"> | |
| <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" /> | |
| <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="417953" EstimatedPagesCached="104488" EstimatedAvailableDegreeOfParallelism="2" /> | |
| <RelOp AvgRowSize="9" EstimateCPU="6E-05" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="60" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="123.48"> | |
| <OutputList /> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" /> | |
| </RunTimeInformation> | |
| <Update DMLRequestSort="false"> | |
| <Object Table="[@NoteTypeCodeTable]" Storage="RowStore" /> | |
| <SetPredicate> | |
| <ScalarOperator ScalarString="[NoteTypeCode] = [Expr1020]"> | |
| <ScalarExpressionList> | |
| <ScalarOperator> | |
| <MultipleAssign> | |
| <Assign> | |
| <ColumnReference Column="NoteTypeCode" /> | |
| <ScalarOperator> | |
| <Identifier> | |
| <ColumnReference Column="Expr1020" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| </Assign> | |
| </MultipleAssign> | |
| </ScalarOperator> | |
| </ScalarExpressionList> | |
| </ScalarOperator> | |
| </SetPredicate> | |
| <RelOp AvgRowSize="36" EstimateCPU="6E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="60" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="123.47"> | |
| <OutputList> | |
| <ColumnReference Column="Expr1020" /> | |
| </OutputList> | |
| <ComputeScalar> | |
| <DefinedValues> | |
| <DefinedValue> | |
| <ColumnReference Column="Expr1020" /> | |
| <ScalarOperator ScalarString="[Expr1019]"> | |
| <Identifier> | |
| <ColumnReference Column="Expr1019" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| </DefinedValue> | |
| </DefinedValues> | |
| <RelOp AvgRowSize="36" EstimateCPU="0.0002508" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="60" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="123.47"> | |
| <OutputList> | |
| <ColumnReference Column="Expr1019" /> | |
| </OutputList> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" /> | |
| </RunTimeInformation> | |
| <NestedLoops Optimized="false"> | |
| <OuterReferences> | |
| <ColumnReference Table="[XML Reader with XPath filter]" Column="id" /> | |
| </OuterReferences> | |
| <RelOp AvgRowSize="17" EstimateCPU="6E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="60" LogicalOp="Top" NodeId="3" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="1.00406"> | |
| <OutputList> | |
| <ColumnReference Table="[XML Reader with XPath filter]" Column="id" /> | |
| </OutputList> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" /> | |
| </RunTimeInformation> | |
| <Top RowCount="true" IsPercent="false" WithTies="false"> | |
| <TopExpression> | |
| <ScalarOperator ScalarString="(0)"> | |
| <Const ConstValue="(0)" /> | |
| </ScalarOperator> | |
| </TopExpression> | |
| <RelOp AvgRowSize="17" EstimateCPU="5.6E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="60" LogicalOp="Filter" NodeId="4" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="1.00406"> | |
| <OutputList> | |
| <ColumnReference Table="[XML Reader with XPath filter]" Column="id" /> | |
| </OutputList> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" /> | |
| </RunTimeInformation> | |
| <Filter StartupExpression="true"> | |
| <RelOp AvgRowSize="17" EstimateCPU="1.004" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="200" LogicalOp="Table-valued function" NodeId="5" Parallel="false" PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="1.004"> | |
| <OutputList> | |
| <ColumnReference Table="[XML Reader with XPath filter]" Column="id" /> | |
| </OutputList> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" /> | |
| </RunTimeInformation> | |
| <TableValuedFunction> | |
| <DefinedValues> | |
| <DefinedValue> | |
| <ColumnReference Table="[XML Reader with XPath filter]" Column="id" /> | |
| </DefinedValue> | |
| </DefinedValues> | |
| <Object Table="[XML Reader with XPath filter]" /> | |
| <ParameterList> | |
| <ScalarOperator ScalarString="[@ListNoteTypeCodeXml]"> | |
| <Identifier> | |
| <ColumnReference Column="@ListNoteTypeCodeXml" /> | |
| </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> | |
| <Predicate> | |
| <ScalarOperator ScalarString="[@ListNoteTypeCodeXml] IS NOT NULL"> | |
| <Identifier> | |
| <ColumnReference Column="ConstExpr1021"> | |
| <ScalarOperator> | |
| <Compare CompareOp="IS NOT"> | |
| <ScalarOperator> | |
| <Identifier> | |
| <ColumnReference Column="@ListNoteTypeCodeXml" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| <ScalarOperator> | |
| <Const ConstValue="NULL" /> | |
| </ScalarOperator> | |
| </Compare> | |
| </ScalarOperator> | |
| </ColumnReference> | |
| </Identifier> | |
| </ScalarOperator> | |
| </Predicate> | |
| </Filter> | |
| </RelOp> | |
| </Top> | |
| </RelOp> | |
| <RelOp AvgRowSize="36" EstimateCPU="0.0004325" EstimateIO="0" EstimateRebinds="59" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Aggregate" NodeId="12" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="122.466"> | |
| <OutputList> | |
| <ColumnReference Column="Expr1019" /> | |
| </OutputList> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" /> | |
| </RunTimeInformation> | |
| <StreamAggregate> | |
| <DefinedValues> | |
| <DefinedValue> | |
| <ColumnReference Column="Expr1019" /> | |
| <ScalarOperator ScalarString="MIN(CASE WHEN [@ListNoteTypeCodeXml] IS NULL THEN NULL ELSE CASE WHEN datalength([Expr1016])>=(128) THEN CONVERT_IMPLICIT(varchar(50),[Expr1017],0) ELSE CONVERT_IMPLICIT(varchar(50),[Expr1016],0) END END)"> | |
| <Aggregate AggType="MIN" Distinct="false"> | |
| <ScalarOperator> | |
| <IF> | |
| <Condition> | |
| <ScalarOperator> | |
| <Compare CompareOp="IS"> | |
| <ScalarOperator> | |
| <Identifier> | |
| <ColumnReference Column="@ListNoteTypeCodeXml" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| <ScalarOperator> | |
| <Const ConstValue="NULL" /> | |
| </ScalarOperator> | |
| </Compare> | |
| </ScalarOperator> | |
| </Condition> | |
| <Then> | |
| <ScalarOperator> | |
| <Const ConstValue="NULL" /> | |
| </ScalarOperator> | |
| </Then> | |
| <Else> | |
| <ScalarOperator> | |
| <IF> | |
| <Condition> | |
| <ScalarOperator> | |
| <Compare CompareOp="GE"> | |
| <ScalarOperator> | |
| <Intrinsic FunctionName="datalength"> | |
| <ScalarOperator> | |
| <Identifier> | |
| <ColumnReference Column="Expr1016" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| </Intrinsic> | |
| </ScalarOperator> | |
| <ScalarOperator> | |
| <Const ConstValue="(128)" /> | |
| </ScalarOperator> | |
| </Compare> | |
| </ScalarOperator> | |
| </Condition> | |
| <Then> | |
| <ScalarOperator> | |
| <Convert DataType="varchar" Length="50" Style="0" Implicit="true"> | |
| <ScalarOperator> | |
| <Identifier> | |
| <ColumnReference Column="Expr1017" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| </Convert> | |
| </ScalarOperator> | |
| </Then> | |
| <Else> | |
| <ScalarOperator> | |
| <Convert DataType="varchar" Length="50" Style="0" Implicit="true"> | |
| <ScalarOperator> | |
| <Identifier> | |
| <ColumnReference Column="Expr1016" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| </Convert> | |
| </ScalarOperator> | |
| </Else> | |
| </IF> | |
| </ScalarOperator> | |
| </Else> | |
| </IF> | |
| </ScalarOperator> | |
| </Aggregate> | |
| </ScalarOperator> | |
| </DefinedValue> | |
| </DefinedValues> | |
| <RelOp AvgRowSize="8045" EstimateCPU="0.00072" EstimateIO="0" EstimateRebinds="59" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="720" LogicalOp="UDX" NodeId="13" Parallel="false" PhysicalOp="UDX" EstimatedTotalSubtreeCost="122.44"> | |
| <OutputList> | |
| <ColumnReference Column="Expr1016" /> | |
| <ColumnReference Column="Expr1017" /> | |
| </OutputList> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" /> | |
| </RunTimeInformation> | |
| <Extension UDXName="XQUERY DATA"> | |
| <DefinedValues> | |
| <DefinedValue> | |
| <ColumnReference Column="Expr1014" /> | |
| </DefinedValue> | |
| <DefinedValue> | |
| <ColumnReference Column="Expr1015" /> | |
| </DefinedValue> | |
| <DefinedValue> | |
| <ColumnReference Column="Expr1016" /> | |
| </DefinedValue> | |
| <DefinedValue> | |
| <ColumnReference Column="Expr1017" /> | |
| </DefinedValue> | |
| <DefinedValue> | |
| <ColumnReference Column="Expr1018" /> | |
| </DefinedValue> | |
| </DefinedValues> | |
| <UsedUDXColumns> | |
| <ColumnReference Table="[XML Reader]" Column="id" /> | |
| <ColumnReference Table="[XML Reader]" Column="nid" /> | |
| <ColumnReference Table="[XML Reader]" Column="tid" /> | |
| <ColumnReference Table="[XML Reader]" Column="value" /> | |
| <ColumnReference Table="[XML Reader]" Column="lvalue" /> | |
| <ColumnReference Table="[XML Reader]" Column="lvaluebin" /> | |
| <ColumnReference Column="Expr1010" /> | |
| <ColumnReference Table="[XML Reader]" Column="id" /> | |
| </UsedUDXColumns> | |
| <RelOp AvgRowSize="8551" EstimateCPU="9E-05" EstimateIO="0" EstimateRebinds="59" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="900" LogicalOp="Compute Scalar" NodeId="14" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="122.397"> | |
| <OutputList> | |
| <ColumnReference Column="Expr1010" /> | |
| <ColumnReference Table="[XML Reader]" Column="id" /> | |
| <ColumnReference Table="[XML Reader]" Column="nid" /> | |
| <ColumnReference Table="[XML Reader]" Column="tid" /> | |
| <ColumnReference Table="[XML Reader]" Column="value" /> | |
| <ColumnReference Table="[XML Reader]" Column="lvalue" /> | |
| <ColumnReference Table="[XML Reader]" Column="lvaluebin" /> | |
| </OutputList> | |
| <ComputeScalar> | |
| <DefinedValues> | |
| <DefinedValue> | |
| <ColumnReference Column="Expr1010" /> | |
| <ScalarOperator ScalarString="0x58"> | |
| <Const ConstValue="0x58" /> | |
| </ScalarOperator> | |
| </DefinedValue> | |
| </DefinedValues> | |
| <RelOp AvgRowSize="8099" EstimateCPU="0.003762" EstimateIO="0" EstimateRebinds="59" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="900" LogicalOp="Inner Join" NodeId="15" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="122.391"> | |
| <OutputList> | |
| <ColumnReference Table="[XML Reader]" Column="id" /> | |
| <ColumnReference Table="[XML Reader]" Column="nid" /> | |
| <ColumnReference Table="[XML Reader]" Column="tid" /> | |
| <ColumnReference Table="[XML Reader]" Column="value" /> | |
| <ColumnReference Table="[XML Reader]" Column="lvalue" /> | |
| <ColumnReference Table="[XML Reader]" Column="lvaluebin" /> | |
| </OutputList> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" /> | |
| </RunTimeInformation> | |
| <NestedLoops Optimized="false"> | |
| <OuterReferences> | |
| <ColumnReference Table="[XML Reader]" Column="id" /> | |
| <ColumnReference Column="Expr1022" /> | |
| </OuterReferences> | |
| <RelOp AvgRowSize="469" EstimateCPU="9E-05" EstimateIO="0" EstimateRebinds="59" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="16" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="61.0854"> | |
| <OutputList> | |
| <ColumnReference Table="[XML Reader]" Column="id" /> | |
| <ColumnReference Column="Expr1022" /> | |
| </OutputList> | |
| <ComputeScalar> | |
| <DefinedValues> | |
| <DefinedValue> | |
| <ColumnReference Column="Expr1022" /> | |
| <ScalarOperator ScalarString="getdescendantlimit(XML Reader.[id])"> | |
| <Intrinsic FunctionName="getdescendantlimit"> | |
| <ScalarOperator> | |
| <Identifier> | |
| <ColumnReference Table="[XML Reader]" Column="id" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| </Intrinsic> | |
| </ScalarOperator> | |
| </DefinedValue> | |
| </DefinedValues> | |
| <RelOp AvgRowSize="17" EstimateCPU="1.018" EstimateIO="0" EstimateRebinds="59" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="900" LogicalOp="Table-valued function" NodeId="17" Parallel="false" PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="61.08"> | |
| <OutputList> | |
| <ColumnReference Table="[XML Reader]" Column="id" /> | |
| </OutputList> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRebinds="0" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" /> | |
| </RunTimeInformation> | |
| <TableValuedFunction> | |
| <DefinedValues> | |
| <DefinedValue> | |
| <ColumnReference Table="[XML Reader]" Column="id" /> | |
| </DefinedValue> | |
| </DefinedValues> | |
| <Object Table="[XML Reader]" /> | |
| <ParameterList> | |
| <ScalarOperator ScalarString="[@ListNoteTypeCodeXml]"> | |
| <Identifier> | |
| <ColumnReference Column="@ListNoteTypeCodeXml" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| <ScalarOperator ScalarString="(15)"> | |
| <Const ConstValue="(15)" /> | |
| </ScalarOperator> | |
| <ScalarOperator ScalarString="XML Reader with XPath filter.[id]"> | |
| <Identifier> | |
| <ColumnReference Table="[XML Reader with XPath filter]" Column="id" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| <ScalarOperator ScalarString="XML Reader with XPath filter.[id]"> | |
| <Identifier> | |
| <ColumnReference Table="[XML Reader with XPath filter]" Column="id" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| </ParameterList> | |
| </TableValuedFunction> | |
| </RelOp> | |
| </ComputeScalar> | |
| </RelOp> | |
| <RelOp AvgRowSize="8099" EstimateCPU="1.018" EstimateIO="0" EstimateRebinds="59" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="900" LogicalOp="Table-valued function" NodeId="25" Parallel="false" PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="61.08"> | |
| <OutputList> | |
| <ColumnReference Table="[XML Reader]" Column="id" /> | |
| <ColumnReference Table="[XML Reader]" Column="nid" /> | |
| <ColumnReference Table="[XML Reader]" Column="tid" /> | |
| <ColumnReference Table="[XML Reader]" Column="value" /> | |
| <ColumnReference Table="[XML Reader]" Column="lvalue" /> | |
| <ColumnReference Table="[XML Reader]" Column="lvaluebin" /> | |
| </OutputList> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRebinds="0" ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" /> | |
| </RunTimeInformation> | |
| <TableValuedFunction> | |
| <DefinedValues> | |
| <DefinedValue> | |
| <ColumnReference Table="[XML Reader]" Column="id" /> | |
| </DefinedValue> | |
| <DefinedValue> | |
| <ColumnReference Table="[XML Reader]" Column="nid" /> | |
| </DefinedValue> | |
| <DefinedValue> | |
| <ColumnReference Table="[XML Reader]" Column="tid" /> | |
| </DefinedValue> | |
| <DefinedValue> | |
| <ColumnReference Table="[XML Reader]" Column="value" /> | |
| </DefinedValue> | |
| <DefinedValue> | |
| <ColumnReference Table="[XML Reader]" Column="lvalue" /> | |
| </DefinedValue> | |
| <DefinedValue> | |
| <ColumnReference Table="[XML Reader]" Column="lvaluebin" /> | |
| </DefinedValue> | |
| </DefinedValues> | |
| <Object Table="[XML Reader]" /> | |
| <ParameterList> | |
| <ScalarOperator ScalarString="[@ListNoteTypeCodeXml]"> | |
| <Identifier> | |
| <ColumnReference Column="@ListNoteTypeCodeXml" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| <ScalarOperator ScalarString="(7)"> | |
| <Const ConstValue="(7)" /> | |
| </ScalarOperator> | |
| <ScalarOperator ScalarString="XML Reader.[id]"> | |
| <Identifier> | |
| <ColumnReference Table="[XML Reader]" Column="id" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| <ScalarOperator ScalarString="[Expr1022]"> | |
| <Identifier> | |
| <ColumnReference Column="Expr1022" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| </ParameterList> | |
| </TableValuedFunction> | |
| </RelOp> | |
| </NestedLoops> | |
| </RelOp> | |
| </ComputeScalar> | |
| </RelOp> | |
| </Extension> | |
| </RelOp> | |
| </StreamAggregate> | |
| </RelOp> | |
| </NestedLoops> | |
| </RelOp> | |
| </ComputeScalar> | |
| </RelOp> | |
| </Update> | |
| </RelOp> | |
| <ParameterList> | |
| <ColumnReference Column="@ListNoteTypeCodeXml" ParameterRuntimeValue="N''" /> | |
| </ParameterList> | |
| </QueryPlan> | |
| </StmtSimple> | |
| </Statements> | |
| </Batch> | |
| <Batch> | |
| <Statements> | |
| <StmtSimple StatementCompId="9" StatementEstRows="1" StatementId="2" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.00657106" StatementText="select pbp.Tier1, pbp.LanguageId, pbp.NoteText, pbp.NoteTypeCode, 
 pbp.NoteGroup, pbp.SortOrder 
from dbo.ProductBulletPoint pbp 
join @NoteTypeCodeTable ntc on pbp.NoteTypeCode = ntc.NoteTypeCode 
where Tier1 = @Tier1 and 
 LanguageId = @LanguageID and
 (
 SeasonItemId = @SeasonItemID 
 or
 @SeasonItemID is null
 )" StatementType="SELECT" QueryHash="0x396EA36353A93C64" QueryPlanHash="0x94A278EC39333C09" 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="32" CompileTime="1" CompileCPU="1" CompileMemory="328"> | |
| <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" /> | |
| <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="417953" EstimatedPagesCached="104488" EstimatedAvailableDegreeOfParallelism="2" /> | |
| <RelOp AvgRowSize="321" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657106"> | |
| <OutputList> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="Tier1" /> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="LanguageId" /> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="SortOrder" /> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="NoteText" /> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="NoteTypeCode" /> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="NoteGroup" /> | |
| </OutputList> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" /> | |
| </RunTimeInformation> | |
| <NestedLoops Optimized="false"> | |
| <OuterReferences> | |
| <ColumnReference Table="@NoteTypeCodeTable" Alias="[ntc]" Column="NoteTypeCode" /> | |
| </OuterReferences> | |
| <RelOp AvgRowSize="36" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Table Scan" NodeId="1" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0"> | |
| <OutputList> | |
| <ColumnReference Table="@NoteTypeCodeTable" Alias="[ntc]" Column="NoteTypeCode" /> | |
| </OutputList> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" /> | |
| </RunTimeInformation> | |
| <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> | |
| <DefinedValues> | |
| <DefinedValue> | |
| <ColumnReference Table="@NoteTypeCodeTable" Alias="[ntc]" Column="NoteTypeCode" /> | |
| </DefinedValue> | |
| </DefinedValues> | |
| <Object Table="[@NoteTypeCodeTable]" Alias="[ntc]" Storage="RowStore" /> | |
| </TableScan> | |
| </RelOp> | |
| <RelOp AvgRowSize="328" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="2" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="12882"> | |
| <OutputList> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="Tier1" /> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="LanguageId" /> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="SortOrder" /> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="NoteText" /> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="NoteTypeCode" /> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="NoteGroup" /> | |
| </OutputList> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" /> | |
| </RunTimeInformation> | |
| <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> | |
| <DefinedValues> | |
| <DefinedValue> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="Tier1" /> | |
| </DefinedValue> | |
| <DefinedValue> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="LanguageId" /> | |
| </DefinedValue> | |
| <DefinedValue> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="SortOrder" /> | |
| </DefinedValue> | |
| <DefinedValue> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="NoteText" /> | |
| </DefinedValue> | |
| <DefinedValue> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="NoteTypeCode" /> | |
| </DefinedValue> | |
| <DefinedValue> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="NoteGroup" /> | |
| </DefinedValue> | |
| </DefinedValues> | |
| <Object Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Index="[IDX_ProductBulletPoint_K6_K1_K3_2_4_5_7_INCL]" Alias="[pbp]" IndexKind="NonClustered" Storage="RowStore" /> | |
| <SeekPredicates> | |
| <SeekPredicateNew> | |
| <SeekKeys> | |
| <Prefix ScanType="EQ"> | |
| <RangeColumns> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="NoteTypeCode" /> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="Tier1" /> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="LanguageId" /> | |
| </RangeColumns> | |
| <RangeExpressions> | |
| <ScalarOperator ScalarString="@NoteTypeCodeTable.[NoteTypeCode] as [ntc].[NoteTypeCode]"> | |
| <Identifier> | |
| <ColumnReference Table="@NoteTypeCodeTable" Alias="[ntc]" Column="NoteTypeCode" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| <ScalarOperator ScalarString="[@Tier1]"> | |
| <Identifier> | |
| <ColumnReference Column="@Tier1" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| <ScalarOperator ScalarString="[@LanguageID]"> | |
| <Identifier> | |
| <ColumnReference Column="@LanguageID" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| </RangeExpressions> | |
| </Prefix> | |
| </SeekKeys> | |
| </SeekPredicateNew> | |
| </SeekPredicates> | |
| <Predicate> | |
| <ScalarOperator ScalarString="[US16HSMMProduct_AFTER_CHANGES].[dbo].[ProductBulletPoint].[SeasonItemId] as [pbp].[SeasonItemId]=[@SeasonItemID] OR [@SeasonItemID] IS NULL"> | |
| <Logical Operation="OR"> | |
| <ScalarOperator> | |
| <Compare CompareOp="EQ"> | |
| <ScalarOperator> | |
| <Identifier> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductBulletPoint]" Alias="[pbp]" Column="SeasonItemId" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| <ScalarOperator> | |
| <Identifier> | |
| <ColumnReference Column="@SeasonItemID" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| </Compare> | |
| </ScalarOperator> | |
| <ScalarOperator> | |
| <Identifier> | |
| <ColumnReference Column="ConstExpr1003"> | |
| <ScalarOperator> | |
| <Compare CompareOp="IS"> | |
| <ScalarOperator> | |
| <Identifier> | |
| <ColumnReference Column="@SeasonItemID" /> | |
| </Identifier> | |
| </ScalarOperator> | |
| <ScalarOperator> | |
| <Const ConstValue="NULL" /> | |
| </ScalarOperator> | |
| </Compare> | |
| </ScalarOperator> | |
| </ColumnReference> | |
| </Identifier> | |
| </ScalarOperator> | |
| </Logical> | |
| </ScalarOperator> | |
| </Predicate> | |
| </IndexScan> | |
| </RelOp> | |
| </NestedLoops> | |
| </RelOp> | |
| <ParameterList> | |
| <ColumnReference Column="@SeasonItemID" ParameterCompiledValue="'16HSM'" ParameterRuntimeValue="'16HSM'" /> | |
| <ColumnReference Column="@LanguageID" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" /> | |
| <ColumnReference Column="@Tier1" ParameterCompiledValue="'AR740'" ParameterRuntimeValue="'AR740'" /> | |
| </ParameterList> | |
| </QueryPlan> | |
| </StmtSimple> | |
| </Statements> | |
| </Batch> | |
| <Batch> | |
| <Statements> | |
| <StmtSimple StatementCompId="10" StatementEstRows="1" StatementId="3" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.0032831" StatementText="select @CacheDuration = Duration 
from dbo.CacheDuration 
where [Key] = 'Product'" StatementType="SELECT" QueryHash="0x8FB2676DD9C7F379" QueryPlanHash="0xCA8A5919DE22E73F" 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="16" CompileTime="0" CompileCPU="0" CompileMemory="160"> | |
| <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" /> | |
| <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="417953" EstimatedPagesCached="104488" EstimatedAvailableDegreeOfParallelism="2" /> | |
| <RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="0" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="4"> | |
| <OutputList> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[CacheDuration]" Column="Duration" /> | |
| </OutputList> | |
| <RunTimeInformation> | |
| <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" /> | |
| </RunTimeInformation> | |
| <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"> | |
| <DefinedValues> | |
| <DefinedValue> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[CacheDuration]" Column="Duration" /> | |
| </DefinedValue> | |
| </DefinedValues> | |
| <Object Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[CacheDuration]" Index="[IDX_CacheDuration_kEY]" IndexKind="NonClustered" Storage="RowStore" /> | |
| <SeekPredicates> | |
| <SeekPredicateNew> | |
| <SeekKeys> | |
| <Prefix ScanType="EQ"> | |
| <RangeColumns> | |
| <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[CacheDuration]" Column="Key" /> | |
| </RangeColumns> | |
| <RangeExpressions> | |
| <ScalarOperator ScalarString="'Product'"> | |
| <Const ConstValue="'Product'" /> | |
| </ScalarOperator> | |
| </RangeExpressions> | |
| </Prefix> | |
| </SeekKeys> | |
| </SeekPredicateNew> | |
| </SeekPredicates> | |
| </IndexScan> | |
| </RelOp> | |
| <ParameterList> | |
| <ColumnReference Column="@CacheDuration" ParameterCompiledValue="NULL" ParameterRuntimeValue="(86400)" /> | |
| </ParameterList> | |
| </QueryPlan> | |
| </StmtSimple> | |
| </Statements> | |
| </Batch> | |
| </BatchSequence> | |
| </ShowPlanXML> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment