the XML execution plan for the query that touches [mydatabase].[sys].[sysschobjs].[type]

<?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="2" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="70" StatementSubTreeCost="0.00657048" StatementText="select&#xD;&#xA;                t2.Tier1 as [StyleCode],&#xD;&#xA;                t2.Tier1Descr as [StyleDescription],&#xD;&#xA;                coalesce(t1.URL, t2.URL) as [StyleUrl],&#xD;&#xA;                t2.Tier2 as [ColourCode],&#xD;&#xA;                t2.Tier2Descr as [ColourDescription],&#xD;&#xA;                t2.URL as [ColourUrl],&#xD;&#xA;                t2.ClassID,&#xD;&#xA;                t2.SegmentID,&#xD;&#xA;                t2.GroupID,&#xD;&#xA;                t2.SeasonItemID&#xD;&#xA;                from dbo.ProductItemDetailsDenorm t2 &#xD;&#xA;                    INNER JOIN dbo.ProductItemDetailsDenorm t1 &#xD;&#xA;                                 on t2.Tier1      = t1.Tier1&#xD;&#xA;                                and t2.ClassID    = t1.ClassID &#xD;&#xA;                                and t2.SegmentID  = t1.SegmentID &#xD;&#xA;                                and t2.GroupID    = t1.GroupID &#xD;&#xA;                                and t2.LanguageID = t1.LanguageID&#xD;&#xA;                                and t1.Tier2 = ''&#xD;&#xA;&#xD;&#xA;                    where   t2.Tier1      = @StyleCode&#xD;&#xA;                        and t2.ClassID    = @ClassId&#xD;&#xA;                        and t2.SegmentID  = @SegmentId&#xD;&#xA;                        and t2.GroupID    = @GroupId&#xD;&#xA;                        and t2.LanguageID = @LanguageId&#xD;&#xA;                        and t2.Tier2      &lt;&gt; ''&#xD;&#xA;                        and t2.Tier3      = ''" StatementType="SELECT" QueryHash="0x1C0535A69650FAFA" QueryPlanHash="0x7206D512BAA07C27" 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="56" CompileTime="15" CompileCPU="15" CompileMemory="1360">
            <UnmatchedIndexes>
              <Parameterization>
                <Object Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Index="[IDX_Tier2_Tier3_INC_Tier1]" />
                <Object Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Index="[idxF_ProductGroupGet]" />
              </Parameterization>
            </UnmatchedIndexes>
            <Warnings UnmatchedIndexes="true" />
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="417953" EstimatedPagesCached="104488" EstimatedAvailableDegreeOfParallelism="2" />
            <RelOp AvgRowSize="1047" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00657048">
              <OutputList>
                <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="ClassID" />
                <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="SegmentID" />
                <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="GroupID" />
                <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier1" />
                <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier2" />
                <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="URL" />
                <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="SeasonItemID" />
                <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier1Descr" />
                <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier2Descr" />
                <ColumnReference Column="Expr1002" />
              </OutputList>
              <ComputeScalar>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1002" />
                    <ScalarOperator ScalarString="CASE WHEN [US16HSMMProduct_AFTER_CHANGES].[dbo].[ProductItemDetailsDenorm].[URL] as [t1].[URL] IS NOT NULL THEN [US16HSMMProduct_AFTER_CHANGES].[dbo].[ProductItemDetailsDenorm].[URL] as [t1].[URL] ELSE [US16HSMMProduct_AFTER_CHANGES].[dbo].[ProductItemDetailsDenorm].[URL] as [t2].[URL] END">
                      <IF>
                        <Condition>
                          <ScalarOperator>
                            <Compare CompareOp="IS NOT">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t1]" Column="URL" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Const ConstValue="NULL" />
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </Condition>
                        <Then>
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t1]" Column="URL" />
                            </Identifier>
                          </ScalarOperator>
                        </Then>
                        <Else>
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="URL" />
                            </Identifier>
                          </ScalarOperator>
                        </Else>
                      </IF>
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>
                <RelOp AvgRowSize="1047" 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.00657038">
                  <OutputList>
                    <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="ClassID" />
                    <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="SegmentID" />
                    <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="GroupID" />
                    <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier1" />
                    <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier2" />
                    <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="URL" />
                    <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="SeasonItemID" />
                    <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier1Descr" />
                    <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier2Descr" />
                    <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t1]" Column="URL" />
                  </OutputList>
                  <RunTimeInformation>
                    <RunTimeCountersPerThread Thread="0" ActualRows="2" ActualEndOfScans="1" ActualExecutions="1" />
                  </RunTimeInformation>
                  <NestedLoops Optimized="false">
                    <RelOp AvgRowSize="261" 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="127053">
                      <OutputList>
                        <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t1]" Column="URL" />
                      </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="[ProductItemDetailsDenorm]" Alias="[t1]" Column="URL" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Index="[IDX_ClassID_Tier1_Tier2_LanguageID_INC_all_columns]" Alias="[t1]" IndexKind="NonClustered" Storage="RowStore" />
                        <SeekPredicates>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <Prefix ScanType="EQ">
                                <RangeColumns>
                                  <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t1]" Column="ClassID" />
                                  <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t1]" Column="Tier1" />
                                  <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t1]" Column="Tier2" />
                                  <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t1]" Column="LanguageID" />
                                  <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t1]" Column="SegmentID" />
                                  <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t1]" Column="GroupID" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="[@ClassId]">
                                    <Identifier>
                                      <ColumnReference Column="@ClassId" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="[@StyleCode]">
                                    <Identifier>
                                      <ColumnReference Column="@StyleCode" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="''">
                                    <Const ConstValue="''" />
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="[@LanguageId]">
                                    <Identifier>
                                      <ColumnReference Column="@LanguageId" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="[@SegmentId]">
                                    <Identifier>
                                      <ColumnReference Column="@SegmentId" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="[@GroupId]">
                                    <Identifier>
                                      <ColumnReference Column="@GroupId" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </Prefix>
                            </SeekKeys>
                          </SeekPredicateNew>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                    <RelOp AvgRowSize="795" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Index Seek" NodeId="3" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="127053">
                      <OutputList>
                        <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="ClassID" />
                        <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="SegmentID" />
                        <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="GroupID" />
                        <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier1" />
                        <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier2" />
                        <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="URL" />
                        <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="SeasonItemID" />
                        <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier1Descr" />
                        <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier2Descr" />
                      </OutputList>
                      <RunTimeInformation>
                        <RunTimeCountersPerThread Thread="0" ActualRows="2" 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="[ProductItemDetailsDenorm]" Alias="[t2]" Column="ClassID" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="SegmentID" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="GroupID" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier1" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier2" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="URL" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="SeasonItemID" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier1Descr" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier2Descr" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Index="[IDX_ClassID_SegmentID_GroupID_Tier1_Tier3_LanguageIDTier2_INC_URL_SeasonItemID_Tier1Descr_Tier2Descr]" Alias="[t2]" IndexKind="NonClustered" Storage="RowStore" />
                        <SeekPredicates>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <Prefix ScanType="EQ">
                                <RangeColumns>
                                  <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="ClassID" />
                                  <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="SegmentID" />
                                  <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="GroupID" />
                                  <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier1" />
                                  <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier3" />
                                  <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="LanguageID" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="[@ClassId]">
                                    <Identifier>
                                      <ColumnReference Column="@ClassId" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="[@SegmentId]">
                                    <Identifier>
                                      <ColumnReference Column="@SegmentId" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="[@GroupId]">
                                    <Identifier>
                                      <ColumnReference Column="@GroupId" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="[@StyleCode]">
                                    <Identifier>
                                      <ColumnReference Column="@StyleCode" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="''">
                                    <Const ConstValue="''" />
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="[@LanguageId]">
                                    <Identifier>
                                      <ColumnReference Column="@LanguageId" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </Prefix>
                              <EndRange ScanType="LT">
                                <RangeColumns>
                                  <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier2" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="''">
                                    <Const ConstValue="''" />
                                  </ScalarOperator>
                                </RangeExpressions>
                              </EndRange>
                            </SeekKeys>
                          </SeekPredicateNew>
                          <SeekPredicateNew>
                            <SeekKeys>
                              <Prefix ScanType="EQ">
                                <RangeColumns>
                                  <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="ClassID" />
                                  <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="SegmentID" />
                                  <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="GroupID" />
                                  <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier1" />
                                  <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier3" />
                                  <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="LanguageID" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="[@ClassId]">
                                    <Identifier>
                                      <ColumnReference Column="@ClassId" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="[@SegmentId]">
                                    <Identifier>
                                      <ColumnReference Column="@SegmentId" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="[@GroupId]">
                                    <Identifier>
                                      <ColumnReference Column="@GroupId" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="[@StyleCode]">
                                    <Identifier>
                                      <ColumnReference Column="@StyleCode" />
                                    </Identifier>
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="''">
                                    <Const ConstValue="''" />
                                  </ScalarOperator>
                                  <ScalarOperator ScalarString="[@LanguageId]">
                                    <Identifier>
                                      <ColumnReference Column="@LanguageId" />
                                    </Identifier>
                                  </ScalarOperator>
                                </RangeExpressions>
                              </Prefix>
                              <StartRange ScanType="GT">
                                <RangeColumns>
                                  <ColumnReference Database="[US16HSMMProduct_AFTER_CHANGES]" Schema="[dbo]" Table="[ProductItemDetailsDenorm]" Alias="[t2]" Column="Tier2" />
                                </RangeColumns>
                                <RangeExpressions>
                                  <ScalarOperator ScalarString="''">
                                    <Const ConstValue="''" />
                                  </ScalarOperator>
                                </RangeExpressions>
                              </StartRange>
                            </SeekKeys>
                          </SeekPredicateNew>
                        </SeekPredicates>
                      </IndexScan>
                    </RelOp>
                  </NestedLoops>
                </RelOp>
              </ComputeScalar>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@LanguageId" ParameterRuntimeValue="(2)" />
              <ColumnReference Column="@GroupId" ParameterRuntimeValue="(362)" />
              <ColumnReference Column="@SegmentId" ParameterRuntimeValue="(1)" />
              <ColumnReference Column="@ClassId" ParameterRuntimeValue="(1)" />
              <ColumnReference Column="@StyleCode" ParameterRuntimeValue="'MB149'" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

and this is the query that generates the execution plan:

declare 
    @StyleCode varchar(10),
    @ClassId int,
    @SegmentId int,
    @GroupId int,
    @LanguageId smallint

select
   @StyleCode ='MB149',
   @GroupID = 362,
   @ClassId = 1,
   @SegmentId = 1,
   @LanguageId =2 



            select
                t2.Tier1 as [StyleCode],
                t2.Tier1Descr as [StyleDescription],
                coalesce(t1.URL, t2.URL) as [StyleUrl],
                t2.Tier2 as [ColourCode],
                t2.Tier2Descr as [ColourDescription],
                t2.URL as [ColourUrl],
                t2.ClassID,
                t2.SegmentID,
                t2.GroupID,
                t2.SeasonItemID
                from dbo.ProductItemDetailsDenorm t2 
                    INNER JOIN dbo.ProductItemDetailsDenorm t1 
                                 on t2.Tier1      = t1.Tier1
                                and t2.ClassID    = t1.ClassID 
                                and t2.SegmentID  = t1.SegmentID 
                                and t2.GroupID    = t1.GroupID 
                                and t2.LanguageID = t1.LanguageID
                                and t1.Tier2 = ''

                    where   t2.Tier1      = @StyleCode
                        and t2.ClassID    = @ClassId
                        and t2.SegmentID  = @SegmentId
                        and t2.GroupID    = @GroupId
                        and t2.LanguageID = @LanguageId
                        and t2.Tier2      <> ''
                        and t2.Tier3      = ''