Skip to content

Instantly share code, notes, and snippets.

@jjradha

jjradha/procedure DenormV2.udpProductTaxRateGet - execution plan.xml

Last active August 9, 2016 15:05
Show Gist options
  • Save jjradha/f87cb617053bbe45ae0abb90bdc25bde to your computer and use it in GitHub Desktop.
Save jjradha/f87cb617053bbe45ae0abb90bdc25bde to your computer and use it in GitHub Desktop.
Download ZIP
Raw
procedure DenormV2.udpProductTaxRateGet - execution plan.xml
<?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="4" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.0259727" StatementText="INSERT INTO @Addresses(TaxRegionId)&#xD;&#xA;SELECT DISTINCT TaxRegionId&#xD;&#xA;FROM dbo.[ShipTaxAddress]&#xD;&#xA;WHERE [CountryCode] = @countryCode&#xD;&#xA;AND [AddressFormatID] = @addressFormatID&#xD;&#xA;AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine1]), ISNULL(@addressLine1, '')) = ISNULL(@addressLine1, '')&#xD;&#xA;AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine2]), ISNULL(@addressLine2, '')) = ISNULL(@addressLine2, '')&#xD;&#xA;AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine3]), ISNULL(@addressLine3, '')) = ISNULL(@addressLine3, '')&#xD;&#xA;AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine4]), ISNULL(@addressLine4, '')) = ISNULL(@addressLine4, '')&#xD;&#xA;AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine5]), ISNULL(@addressLine5, '')) = ISNULL(@addressLine5, '')&#xD;&#xA;AND ISNULL (CONVERT(nvarchar(50),[MatchAddressLine6]), ISNULL(@addressLine6, '')) = ISNULL(@addressLine6, '')&#xD;&#xA;AND @postalcode Like ISNULL ( CONVERT(nvarchar(20),[MatchPostalCode]), @postalcode)" StatementType="INSERT" QueryHash="0x8026FFD599B52DAE" QueryPlanHash="0x84F88D236DC8376" 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" MemoryGrant="1024" CachedPlanSize="48" CompileTime="2" CompileCPU="2" CompileMemory="712">
<Warnings>
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(nvarchar(50),[US16HSMMProduct_ORIGINAL].[dbo].[ShipTaxAddress].[MatchAddressLine6],0)" />
<PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(nvarchar(20),[US16HSMMProduct_ORIGINAL].[dbo].[ShipTaxAddress].[MatchPostalCode],0)" />
</Warnings>
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="640" RequiredMemory="512" DesiredMemory="640" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="417953" EstimatedPagesCached="104488" EstimatedAvailableDegreeOfParallelism="2" />
<RelOp AvgRowSize="9" EstimateCPU="1E-06" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="0.0259727">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Table="[@Addresses]" Storage="RowStore" />
<SetPredicate>
<ScalarOperator ScalarString="[TaxRegionId] = [US16HSMMProduct_ORIGINAL].[dbo].[ShipTaxAddress].[TaxRegionId]">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Column="TaxRegionId" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ShipTaxAddress]" Column="TaxRegionId" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="11" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0159717">
<OutputList>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ShipTaxAddress]" Column="TaxRegionId" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="11" EstimateCPU="0.000100011" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Distinct Sort" NodeId="2" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0159716">
<OutputList>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ShipTaxAddress]" Column="TaxRegionId" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="true">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ShipTaxAddress]" Column="TaxRegionId" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="165" EstimateCPU="0.0003605" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0034855" TableCardinality="185">
<OutputList>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ShipTaxAddress]" Column="TaxRegionId" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ShipTaxAddress]" Column="TaxRegionId" />
</DefinedValue>
</DefinedValues>
<Object Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ShipTaxAddress]" Index="[PK_ShipTaxAddress]" IndexKind="Clustered" Storage="RowStore" />
<Predicate>
<ScalarOperator ScalarString="[US16HSMMProduct_ORIGINAL].[dbo].[ShipTaxAddress].[AddressFormatId]=[@addressFormatID] AND [US16HSMMProduct_ORIGINAL].[dbo].[ShipTaxAddress].[CountryCode]=[@countryCode] AND isnull(CONVERT(nvarchar(50),[US16HSMMProduct_ORIGINAL].[dbo].[ShipTaxAddress].[MatchAddressLine1],0),isnull([@addressLine1],N''))=isnull([@addressLine1],N'') AND isnull(CONVERT(nvarchar(50),[US16HSMMProduct_ORIGINAL].[dbo].[ShipTaxAddress].[MatchAddressLine2],0),isnull([@addressLine2],N''))=isnull([@addressLine2],N'') AND isnull(CONVERT(nvarchar(50),[US16HSMMProduct_ORIGINAL].[dbo].[ShipTaxAddress].[MatchAddressLine3],0),isnull([@addressLine3],N''))=isnull([@addressLine3],N'') AND isnull(CONVERT(nvarchar(50),[US16HSMMProduct_ORIGINAL].[dbo].[ShipTaxAddress].[MatchAddressLine4],0),isnull([@addressLine4],N''))=isnull([@addressLine4],N'') AND isnull(CONVERT(nvarchar(50),[US16HSMMProduct_ORIGINAL].[dbo].[ShipTaxAddress].[MatchAddressLine5],0),isnull([@addressLine5],N''))=isnull([@addressLine5],N'') AND isnull(CONVERT(nvarchar(50),[US16HSMMProduct_ORIGINAL].[dbo].[ShipTaxAddress].[MatchAddressLine6],0),isnull([@addressLine6],N''))=isnull([@addressLine6],N'') AND [@postalCode] like isnull(CONVERT(nvarchar(20),[US16HSMMProduct_ORIGINAL].[dbo].[ShipTaxAddress].[MatchPostalCode],0),[@postalCode])">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ShipTaxAddress]" Column="AddressFormatId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@addressFormatID" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ShipTaxAddress]" Column="CountryCode" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@countryCode" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Convert DataType="nvarchar" Length="100" Style="0" Implicit="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ShipTaxAddress]" Column="MatchAddressLine1" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1005">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@addressLine1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N''" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1006">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@addressLine1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N''" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Convert DataType="nvarchar" Length="100" Style="0" Implicit="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ShipTaxAddress]" Column="MatchAddressLine2" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1007">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@addressLine2" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N''" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1008">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@addressLine2" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N''" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Convert DataType="nvarchar" Length="100" Style="0" Implicit="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ShipTaxAddress]" Column="MatchAddressLine3" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1009">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@addressLine3" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N''" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1010">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@addressLine3" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N''" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Convert DataType="nvarchar" Length="100" Style="0" Implicit="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ShipTaxAddress]" Column="MatchAddressLine4" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1011">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@addressLine4" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N''" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1012">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@addressLine4" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N''" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Convert DataType="nvarchar" Length="100" Style="0" Implicit="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ShipTaxAddress]" Column="MatchAddressLine5" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1013">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@addressLine5" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N''" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1014">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@addressLine5" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N''" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Convert DataType="nvarchar" Length="100" Style="0" Implicit="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ShipTaxAddress]" Column="MatchAddressLine6" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1015">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@addressLine6" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N''" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1016">
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@addressLine6" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N''" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Intrinsic FunctionName="like">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@postalCode" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Convert DataType="nvarchar" Length="40" Style="0" Implicit="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ShipTaxAddress]" Column="MatchPostalCode" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@postalCode" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Sort>
</RelOp>
</Top>
</RelOp>
</Update>
</RelOp>
<ParameterList>
<ColumnReference Column="@postalCode" ParameterCompiledValue="N'02142'" ParameterRuntimeValue="N'190537838'" />
<ColumnReference Column="@addressLine6" ParameterCompiledValue="N'MA'" ParameterRuntimeValue="N'PA'" />
<ColumnReference Column="@addressLine5" ParameterCompiledValue="N'CAMBRIDGE'" ParameterRuntimeValue="N'FEASTERVILLE TREVOSE'" />
<ColumnReference Column="@addressLine4" ParameterCompiledValue="N''" ParameterRuntimeValue="N''" />
<ColumnReference Column="@addressLine3" ParameterCompiledValue="N' 1 BROADWAY'" ParameterRuntimeValue="N'114 FORGE LN'" />
<ColumnReference Column="@addressLine2" ParameterCompiledValue="N''" ParameterRuntimeValue="N''" />
<ColumnReference Column="@addressLine1" ParameterCompiledValue="N''" ParameterRuntimeValue="N''" />
<ColumnReference Column="@addressFormatID" ParameterCompiledValue="(2)" ParameterRuntimeValue="(2)" />
<ColumnReference Column="@countryCode" ParameterCompiledValue="'US'" ParameterRuntimeValue="'US'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
<Batch>
<Statements>
<StmtSimple StatementCompId="5" StatementEstRows="1" StatementId="2" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.0212208" StatementText="SELECT DISTINCT ISNULL(pst.TaxCode, '') as TaxCode&#xD;&#xA; , ISNULL(pst.TaxRate, 0) as TaxRate&#xD;&#xA;FROM dbo.[ProductShipTax] pst&#xD;&#xA; INNER JOIN&#xD;&#xA; @Addresses a&#xD;&#xA; ON pst.TaxRegionId = a.TaxRegionId&#xD;&#xA;WHERE pst.[ItemNo] = @itemNo&#xD;&#xA;AND @calculateDate BETWEEN pst.[DateFrom] AND pst.[DateTo]" StatementType="SELECT" QueryHash="0x35389A6E702E3AC2" QueryPlanHash="0x5E82B49C159115A4" 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" MemoryGrant="1024" CachedPlanSize="40" CompileTime="2" CompileCPU="2" CompileMemory="464">
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="417953" EstimatedPagesCached="104488" EstimatedAvailableDegreeOfParallelism="2" />
<RelOp AvgRowSize="30" EstimateCPU="1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Aggregate" NodeId="0" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.0212208">
<OutputList>
<ColumnReference Column="Expr1003" />
<ColumnReference Column="Expr1004" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<StreamAggregate>
<DefinedValues />
<GroupBy>
<ColumnReference Column="Expr1003" />
<ColumnReference Column="Expr1004" />
</GroupBy>
<RelOp AvgRowSize="39" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0212198">
<OutputList>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="TaxRate" />
<ColumnReference Column="Expr1003" />
<ColumnReference Column="Expr1004" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<Predicate>
<ScalarOperator ScalarString="@Addresses.[TaxRegionId] as [a].[TaxRegionId]=[US16HSMMProduct_ORIGINAL].[dbo].[ProductShipTax].[TaxRegionId] as [pst].[TaxRegionId]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Table="@Addresses" Alias="[a]" Column="TaxRegionId" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="TaxRegionId" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
<RelOp AvgRowSize="43" EstimateCPU="0.000100043" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Sort" NodeId="2" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0179323">
<OutputList>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="TaxRate" />
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="TaxRegionId" />
<ColumnReference Column="Expr1003" />
<ColumnReference Column="Expr1004" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Column="Expr1003" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="TaxRate" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="43" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00657096">
<OutputList>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="TaxRate" />
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="TaxRegionId" />
<ColumnReference Column="Expr1003" />
<ColumnReference Column="Expr1004" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1003" />
<ScalarOperator ScalarString="isnull([US16HSMMProduct_ORIGINAL].[dbo].[ProductShipTax].[TaxCode] as [pst].[TaxCode],'')">
<Intrinsic FunctionName="isnull">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="TaxCode" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="''" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1004" />
<ScalarOperator ScalarString="[US16HSMMProduct_ORIGINAL].[dbo].[ProductShipTax].[TaxRate] as [pst].[TaxRate]">
<Identifier>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="TaxRate" />
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="28" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657086">
<OutputList>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="TaxRate" />
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="TaxCode" />
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="TaxRegionId" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="ProductShipTaxID" />
</OuterReferences>
<RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="60057">
<OutputList>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="ProductShipTaxID" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Column="Expr1006" />
<ColumnReference Column="Expr1007" />
<ColumnReference Column="Expr1005" />
</OuterReferences>
<RelOp AvgRowSize="19" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="6" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0">
<OutputList>
<ColumnReference Column="Expr1006" />
<ColumnReference Column="Expr1007" />
<ColumnReference Column="Expr1005" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ValueVector>
<ColumnReference Column="Expr1006" />
<ColumnReference Column="Expr1007" />
<ColumnReference Column="Expr1005" />
</ValueVector>
<ScalarOperator ScalarString="GetRangeWithMismatchedTypes(NULL,[@calculateDate],(42))">
<Intrinsic FunctionName="GetRangeWithMismatchedTypes">
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@calculateDate" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(42)" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="0" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Constant Scan" NodeId="7" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="0">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<ConstantScan />
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="11" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="60057">
<OutputList>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="ProductShipTaxID" />
</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_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="ProductShipTaxID" />
</DefinedValue>
</DefinedValues>
<Object Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Index="[IX_ProductShipTax_ITemNo_DateFrom_DateTo]" Alias="[pst]" IndexKind="NonClustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="ItemNo" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[@itemNo]">
<Identifier>
<ColumnReference Column="@itemNo" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
<StartRange ScanType="GT">
<RangeColumns>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="DateFrom" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Expr1006]">
<Identifier>
<ColumnReference Column="Expr1006" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</StartRange>
<EndRange ScanType="LT">
<RangeColumns>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="DateFrom" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[Expr1007]">
<Identifier>
<ColumnReference Column="Expr1007" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</EndRange>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[@calculateDate]&lt;=[US16HSMMProduct_ORIGINAL].[dbo].[ProductShipTax].[DateTo] as [pst].[DateTo]">
<Compare CompareOp="LE">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@calculateDate" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="DateTo" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="34" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="13" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="60057">
<OutputList>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="TaxRate" />
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="TaxCode" />
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="TaxRegionId" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="TaxRate" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="TaxCode" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="TaxRegionId" />
</DefinedValue>
</DefinedValues>
<Object Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Index="[PK_ProductShipTax]" Alias="[pst]" TableReferenceId="-1" IndexKind="Clustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="ProductShipTaxID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[US16HSMMProduct_ORIGINAL].[dbo].[ProductShipTax].[ProductShipTaxID] as [pst].[ProductShipTaxID]">
<Identifier>
<ColumnReference Database="[US16HSMMProduct_ORIGINAL]" Schema="[dbo]" Table="[ProductShipTax]" Alias="[pst]" Column="ProductShipTaxID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</ComputeScalar>
</RelOp>
</Sort>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Table Scan" NodeId="26" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
<OutputList>
<ColumnReference Table="@Addresses" Alias="[a]" Column="TaxRegionId" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Table="@Addresses" Alias="[a]" Column="TaxRegionId" />
</DefinedValue>
</DefinedValues>
<Object Table="[@Addresses]" Alias="[a]" Storage="RowStore" />
</TableScan>
</RelOp>
</NestedLoops>
</RelOp>
</StreamAggregate>
</RelOp>
<ParameterList>
<ColumnReference Column="@calculateDate" ParameterCompiledValue="'2016-08-08 13:03:03.980'" ParameterRuntimeValue="'2016-08-08 13:01:46.760'" />
<ColumnReference Column="@itemNo" ParameterCompiledValue="'35796069'" ParameterRuntimeValue="'35638956'" />
</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