Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
query plan procedure dbo.udpProductBulletPointSelectByTier1NoteTypeCode
<?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 ) &#xD;&#xA; select BulletPoint.NoteTypeCode.value('.', 'varchar(50)') as NoteTypeCode &#xD;&#xA; 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])&gt;=(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, &#xD;&#xA; pbp.NoteGroup, pbp.SortOrder &#xD;&#xA;from dbo.ProductBulletPoint pbp &#xD;&#xA;join @NoteTypeCodeTable ntc on pbp.NoteTypeCode = ntc.NoteTypeCode &#xD;&#xA;where Tier1 = @Tier1 and &#xD;&#xA; LanguageId = @LanguageID and&#xD;&#xA; (&#xD;&#xA; SeasonItemId = @SeasonItemID &#xD;&#xA; or&#xD;&#xA; @SeasonItemID is null&#xD;&#xA; )" 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 &#xD;&#xA;from dbo.CacheDuration &#xD;&#xA;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
You can’t perform that action at this time.