Slow SQL Querying
<p>I have a script that includes 3 inner joins. It seems that when I add the 3rd inner join, the script runs extremely slow. The first 2 tables have around 3-4 thousand rows, the 3rd has 633 thousand rows, and the last table has only 36 rows. I've attempted indexing, but I don't notice a change. I'm including my script before and after indexing. It takes around 12 minutes to return only the top 5 entries. If there's more information needed, please ask. I'm new to database management.</p>

<p>BEFORE INDEXING:</p>

<pre><code>SELECT DISTINCT TOP 5 sn.NAME, cd.ASSIGNEDPOLICY, cd.COMPANYPOLICYNUMBER, ad.COVERAGECODE, co."DESCRIPTION"
From SNAMES sn
inner join TCUSDTL cd on sn.NAMEID = cd.NAMEID 
inner join TPOLACTD ad on sn.NAMEID = ad.NAMEID 
inner join SCOVTBL co on ad.COVERAGECODE = co.COVERAGE
ORDER BY NAME
</code></pre>

<p>AFTER INDEXING:</p>

<pre><code>SELECT DISTINCT TOP 5 sn.NAME, cd.ASSIGNEDPOLICY, cd.COMPANYPOLICYNUMBER, ad.COVERAGECODE, co."DESCRIPTION"
From SNAMES sn WITH(INDEX(SNAMES_names))
inner join TCUSDTL cd WITH(INDEX(TCUSDTL_policies)) on sn.NAMEID = cd.NAMEID 
inner join TPOLACTD ad WITH(INDEX(TPOLACTD_coverage)) on sn.NAMEID = ad.NAMEID 
inner join SCOVTBL co WITH(INDEX(SCOVTBL_coverage)) on ad.COVERAGECODE = co.COVERAGE
ORDER BY NAME
</code></pre>

<p>EXECUTION PLAN:</p>

<pre><code>&lt;?xml version="1.0" encoding="utf-16"?&gt;
&lt;ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.5" Build="13.0.703.859" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"&gt;
  &lt;BatchSequence&gt;
    &lt;Batch&gt;
      &lt;Statements&gt;
        &lt;StmtSimple StatementCompId="1" StatementEstRows="1000" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="5270.92" StatementText="SELECT DISTINCT TOP 1000 sn.NAME, cd.ASSIGNEDPOLICY, cd.COMPANYPOLICYNUMBER, bd.COVERAGECODE, sd.&amp;quot;DESCRIPTION&amp;quot;&amp;#xD;&amp;#xA;FROM SNAMES sn&amp;#xD;&amp;#xA;inner join TCUSDTL cd on sn.NAMEID = cd.NAMEID &amp;#xD;&amp;#xA;inner join TPOLACTD bd on sn.NAMEID = bd.NAMEID &amp;#xD;&amp;#xA;inner join SCOVTBL sd on bd.COVERAGECODE = sd.COVERAGE&amp;#xD;&amp;#xA;ORDER BY NAME" StatementType="SELECT" QueryHash="0x5C4838D1FC1E530E" QueryPlanHash="0x854BE2475051DE74" RetrievedFromCache="true" SecurityPolicyApplied="false"&gt;
          &lt;StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /&gt;
          &lt;QueryPlan NonParallelPlanReason="EstimatedDOPIsOne" CachedPlanSize="104" CompileTime="27" CompileCPU="26" CompileMemory="1144"&gt;
            &lt;MemoryGrantInfo SerialRequiredMemory="3584" SerialDesiredMemory="7383960" /&gt;
            &lt;OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="11744005" EstimatedPagesCached="734000" EstimatedAvailableDegreeOfParallelism="1" /&gt;
            &lt;RelOp AvgRowSize="94" EstimateCPU="0.0001" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1000" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="5270.92"&gt;
              &lt;OutputList&gt;
                &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SNAMES]" Alias="[sn]" Column="NAME" /&gt;
                &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TCUSDTL]" Alias="[cd]" Column="ASSIGNEDPOLICY" /&gt;
                &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TCUSDTL]" Alias="[cd]" Column="COMPANYPOLICYNUMBER" /&gt;
                &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TPOLACTD]" Alias="[bd]" Column="COVERAGECODE" /&gt;
                &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SCOVTBL]" Alias="[sd]" Column="DESCRIPTION" /&gt;
              &lt;/OutputList&gt;
              &lt;Top RowCount="false" IsPercent="false" WithTies="false"&gt;
                &lt;TopExpression&gt;
                  &lt;ScalarOperator ScalarString="(1000)"&gt;
                    &lt;Const ConstValue="(1000)" /&gt;
                  &lt;/ScalarOperator&gt;
                &lt;/TopExpression&gt;
                &lt;RelOp AvgRowSize="94" EstimateCPU="5177.31" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1000" LogicalOp="Distinct Sort" NodeId="1" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="5270.92"&gt;
                  &lt;OutputList&gt;
                    &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SNAMES]" Alias="[sn]" Column="NAME" /&gt;
                    &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TCUSDTL]" Alias="[cd]" Column="ASSIGNEDPOLICY" /&gt;
                    &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TCUSDTL]" Alias="[cd]" Column="COMPANYPOLICYNUMBER" /&gt;
                    &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TPOLACTD]" Alias="[bd]" Column="COVERAGECODE" /&gt;
                    &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SCOVTBL]" Alias="[sd]" Column="DESCRIPTION" /&gt;
                  &lt;/OutputList&gt;
                  &lt;MemoryFractions Input="0.985219" Output="1" /&gt;
                  &lt;Sort Distinct="true"&gt;
                    &lt;OrderBy&gt;
                      &lt;OrderByColumn Ascending="true"&gt;
                        &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SNAMES]" Alias="[sn]" Column="NAME" /&gt;
                      &lt;/OrderByColumn&gt;
                      &lt;OrderByColumn Ascending="true"&gt;
                        &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TCUSDTL]" Alias="[cd]" Column="ASSIGNEDPOLICY" /&gt;
                      &lt;/OrderByColumn&gt;
                      &lt;OrderByColumn Ascending="true"&gt;
                        &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TCUSDTL]" Alias="[cd]" Column="COMPANYPOLICYNUMBER" /&gt;
                      &lt;/OrderByColumn&gt;
                      &lt;OrderByColumn Ascending="true"&gt;
                        &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TPOLACTD]" Alias="[bd]" Column="COVERAGECODE" /&gt;
                      &lt;/OrderByColumn&gt;
                      &lt;OrderByColumn Ascending="true"&gt;
                        &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SCOVTBL]" Alias="[sd]" Column="DESCRIPTION" /&gt;
                      &lt;/OrderByColumn&gt;
                    &lt;/OrderBy&gt;
                    &lt;RelOp AvgRowSize="94" EstimateCPU="75.5786" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="44400500" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="93.6068"&gt;
                      &lt;OutputList&gt;
                        &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SNAMES]" Alias="[sn]" Column="NAME" /&gt;
                        &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TCUSDTL]" Alias="[cd]" Column="ASSIGNEDPOLICY" /&gt;
                        &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TCUSDTL]" Alias="[cd]" Column="COMPANYPOLICYNUMBER" /&gt;
                        &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TPOLACTD]" Alias="[bd]" Column="COVERAGECODE" /&gt;
                        &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SCOVTBL]" Alias="[sd]" Column="DESCRIPTION" /&gt;
                      &lt;/OutputList&gt;
                      &lt;MemoryFractions Input="0.816955" Output="0.0120753" /&gt;
                      &lt;Hash&gt;
                        &lt;DefinedValues /&gt;
                        &lt;HashKeysBuild&gt;
                          &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TCUSDTL]" Alias="[cd]" Column="NAMEID" /&gt;
                        &lt;/HashKeysBuild&gt;
                        &lt;HashKeysProbe&gt;
                          &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TPOLACTD]" Alias="[bd]" Column="NAMEID" /&gt;
                        &lt;/HashKeysProbe&gt;
                        &lt;ProbeResidual&gt;
                          &lt;ScalarOperator ScalarString="[DC02001].[dbo].[TCUSDTL].[NAMEID] as [cd].[NAMEID]=[DC02001].[dbo].[TPOLACTD].[NAMEID] as [bd].[NAMEID]"&gt;
                            &lt;Compare CompareOp="EQ"&gt;
                              &lt;ScalarOperator&gt;
                                &lt;Identifier&gt;
                                  &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TCUSDTL]" Alias="[cd]" Column="NAMEID" /&gt;
                                &lt;/Identifier&gt;
                              &lt;/ScalarOperator&gt;
                              &lt;ScalarOperator&gt;
                                &lt;Identifier&gt;
                                  &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TPOLACTD]" Alias="[bd]" Column="NAMEID" /&gt;
                                &lt;/Identifier&gt;
                              &lt;/ScalarOperator&gt;
                            &lt;/Compare&gt;
                          &lt;/ScalarOperator&gt;
                        &lt;/ProbeResidual&gt;
                        &lt;RelOp AvgRowSize="33" EstimateCPU="0.283312" EstimateIO="2.24609" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="257414" LogicalOp="Table Scan" NodeId="3" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="2.5294" TableCardinality="257414"&gt;
                          &lt;OutputList&gt;
                            &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TCUSDTL]" Alias="[cd]" Column="NAMEID" /&gt;
                            &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TCUSDTL]" Alias="[cd]" Column="ASSIGNEDPOLICY" /&gt;
                            &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TCUSDTL]" Alias="[cd]" Column="COMPANYPOLICYNUMBER" /&gt;
                          &lt;/OutputList&gt;
                          &lt;TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"&gt;
                            &lt;DefinedValues&gt;
                              &lt;DefinedValue&gt;
                                &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TCUSDTL]" Alias="[cd]" Column="NAMEID" /&gt;
                              &lt;/DefinedValue&gt;
                              &lt;DefinedValue&gt;
                                &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TCUSDTL]" Alias="[cd]" Column="ASSIGNEDPOLICY" /&gt;
                              &lt;/DefinedValue&gt;
                              &lt;DefinedValue&gt;
                                &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TCUSDTL]" Alias="[cd]" Column="COMPANYPOLICYNUMBER" /&gt;
                              &lt;/DefinedValue&gt;
                            &lt;/DefinedValues&gt;
                            &lt;Object Database="[DC02001]" Schema="[dbo]" Table="[TCUSDTL]" Alias="[cd]" IndexKind="Heap" Storage="RowStore" /&gt;
                          &lt;/TableScan&gt;
                        &lt;/RelOp&gt;
                        &lt;RelOp AvgRowSize="86" EstimateCPU="7.96164" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1405060" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="15.4988"&gt;
                          &lt;OutputList&gt;
                            &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SNAMES]" Alias="[sn]" Column="NAME" /&gt;
                            &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TPOLACTD]" Alias="[bd]" Column="NAMEID" /&gt;
                            &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TPOLACTD]" Alias="[bd]" Column="COVERAGECODE" /&gt;
                            &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SCOVTBL]" Alias="[sd]" Column="DESCRIPTION" /&gt;
                          &lt;/OutputList&gt;
                          &lt;MemoryFractions Input="0.179818" Output="0.00265786" /&gt;
                          &lt;Hash&gt;
                            &lt;DefinedValues /&gt;
                            &lt;HashKeysBuild&gt;
                              &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SNAMES]" Alias="[sn]" Column="NAMEID" /&gt;
                            &lt;/HashKeysBuild&gt;
                            &lt;HashKeysProbe&gt;
                              &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TPOLACTD]" Alias="[bd]" Column="NAMEID" /&gt;
                            &lt;/HashKeysProbe&gt;
                            &lt;ProbeResidual&gt;
                              &lt;ScalarOperator ScalarString="[DC02001].[dbo].[SNAMES].[NAMEID] as [sn].[NAMEID]=[DC02001].[dbo].[TPOLACTD].[NAMEID] as [bd].[NAMEID]"&gt;
                                &lt;Compare CompareOp="EQ"&gt;
                                  &lt;ScalarOperator&gt;
                                    &lt;Identifier&gt;
                                      &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SNAMES]" Alias="[sn]" Column="NAMEID" /&gt;
                                    &lt;/Identifier&gt;
                                  &lt;/ScalarOperator&gt;
                                  &lt;ScalarOperator&gt;
                                    &lt;Identifier&gt;
                                      &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TPOLACTD]" Alias="[bd]" Column="NAMEID" /&gt;
                                    &lt;/Identifier&gt;
                                  &lt;/ScalarOperator&gt;
                                &lt;/Compare&gt;
                              &lt;/ScalarOperator&gt;
                            &lt;/ProbeResidual&gt;
                            &lt;RelOp AvgRowSize="56" EstimateCPU="0.0416996" EstimateIO="0.221644" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="37766" LogicalOp="Index Scan" NodeId="5" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.263343" TableCardinality="37766"&gt;
                              &lt;OutputList&gt;
                                &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SNAMES]" Alias="[sn]" Column="NAMEID" /&gt;
                                &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SNAMES]" Alias="[sn]" Column="NAME" /&gt;
                              &lt;/OutputList&gt;
                              &lt;IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"&gt;
                                &lt;DefinedValues&gt;
                                  &lt;DefinedValue&gt;
                                    &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SNAMES]" Alias="[sn]" Column="NAMEID" /&gt;
                                  &lt;/DefinedValue&gt;
                                  &lt;DefinedValue&gt;
                                    &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SNAMES]" Alias="[sn]" Column="NAME" /&gt;
                                  &lt;/DefinedValue&gt;
                                &lt;/DefinedValues&gt;
                                &lt;Object Database="[DC02001]" Schema="[dbo]" Table="[SNAMES]" Index="[SNAMES_names]" Alias="[sn]" IndexKind="NonClustered" Storage="RowStore" /&gt;
                              &lt;/IndexScan&gt;
                            &lt;/RelOp&gt;
                            &lt;RelOp AvgRowSize="46" EstimateCPU="4.94636" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="702122" LogicalOp="Inner Join" NodeId="6" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="7.27381"&gt;
                              &lt;OutputList&gt;
                                &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TPOLACTD]" Alias="[bd]" Column="NAMEID" /&gt;
                                &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TPOLACTD]" Alias="[bd]" Column="COVERAGECODE" /&gt;
                                &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SCOVTBL]" Alias="[sd]" Column="DESCRIPTION" /&gt;
                              &lt;/OutputList&gt;
                              &lt;MemoryFractions Input="0.00322675" Output="4.7694E-05" /&gt;
                              &lt;Hash&gt;
                                &lt;DefinedValues /&gt;
                                &lt;HashKeysBuild&gt;
                                  &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SCOVTBL]" Alias="[sd]" Column="COVERAGE" /&gt;
                                &lt;/HashKeysBuild&gt;
                                &lt;HashKeysProbe&gt;
                                  &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TPOLACTD]" Alias="[bd]" Column="COVERAGECODE" /&gt;
                                &lt;/HashKeysProbe&gt;
                                &lt;ProbeResidual&gt;
                                  &lt;ScalarOperator ScalarString="[DC02001].[dbo].[SCOVTBL].[COVERAGE] as [sd].[COVERAGE]=[DC02001].[dbo].[TPOLACTD].[COVERAGECODE] as [bd].[COVERAGECODE]"&gt;
                                    &lt;Compare CompareOp="EQ"&gt;
                                      &lt;ScalarOperator&gt;
                                        &lt;Identifier&gt;
                                          &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SCOVTBL]" Alias="[sd]" Column="COVERAGE" /&gt;
                                        &lt;/Identifier&gt;
                                      &lt;/ScalarOperator&gt;
                                      &lt;ScalarOperator&gt;
                                        &lt;Identifier&gt;
                                          &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TPOLACTD]" Alias="[bd]" Column="COVERAGECODE" /&gt;
                                        &lt;/Identifier&gt;
                                      &lt;/ScalarOperator&gt;
                                    &lt;/Compare&gt;
                                  &lt;/ScalarOperator&gt;
                                &lt;/ProbeResidual&gt;
                                &lt;RelOp AvgRowSize="37" EstimateCPU="0.0001966" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="36" LogicalOp="Index Scan" NodeId="7" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0033216" TableCardinality="36"&gt;
                                  &lt;OutputList&gt;
                                    &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SCOVTBL]" Alias="[sd]" Column="COVERAGE" /&gt;
                                    &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SCOVTBL]" Alias="[sd]" Column="DESCRIPTION" /&gt;
                                  &lt;/OutputList&gt;
                                  &lt;IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"&gt;
                                    &lt;DefinedValues&gt;
                                      &lt;DefinedValue&gt;
                                        &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SCOVTBL]" Alias="[sd]" Column="COVERAGE" /&gt;
                                      &lt;/DefinedValue&gt;
                                      &lt;DefinedValue&gt;
                                        &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[SCOVTBL]" Alias="[sd]" Column="DESCRIPTION" /&gt;
                                      &lt;/DefinedValue&gt;
                                    &lt;/DefinedValues&gt;
                                    &lt;Object Database="[DC02001]" Schema="[dbo]" Table="[SCOVTBL]" Index="[SCOVTBL_coverage]" Alias="[sd]" IndexKind="NonClustered" Storage="RowStore" /&gt;
                                  &lt;/IndexScan&gt;
                                &lt;/RelOp&gt;
                                &lt;RelOp AvgRowSize="21" EstimateCPU="0.6973" EstimateIO="1.62683" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="633766" LogicalOp="Index Scan" NodeId="8" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="2.32413" TableCardinality="633766"&gt;
                                  &lt;OutputList&gt;
                                    &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TPOLACTD]" Alias="[bd]" Column="NAMEID" /&gt;
                                    &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TPOLACTD]" Alias="[bd]" Column="COVERAGECODE" /&gt;
                                  &lt;/OutputList&gt;
                                  &lt;IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore"&gt;
                                    &lt;DefinedValues&gt;
                                      &lt;DefinedValue&gt;
                                        &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TPOLACTD]" Alias="[bd]" Column="NAMEID" /&gt;
                                      &lt;/DefinedValue&gt;
                                      &lt;DefinedValue&gt;
                                        &lt;ColumnReference Database="[DC02001]" Schema="[dbo]" Table="[TPOLACTD]" Alias="[bd]" Column="COVERAGECODE" /&gt;
                                      &lt;/DefinedValue&gt;
                                    &lt;/DefinedValues&gt;
                                    &lt;Object Database="[DC02001]" Schema="[dbo]" Table="[TPOLACTD]" Index="[TPOLACTD_coverage]" Alias="[bd]" IndexKind="NonClustered" Storage="RowStore" /&gt;
                                  &lt;/IndexScan&gt;
                                &lt;/RelOp&gt;
                              &lt;/Hash&gt;
                            &lt;/RelOp&gt;
                          &lt;/Hash&gt;
                        &lt;/RelOp&gt;
                      &lt;/Hash&gt;
                    &lt;/RelOp&gt;
                  &lt;/Sort&gt;
                &lt;/RelOp&gt;
              &lt;/Top&gt;
            &lt;/RelOp&gt;
          &lt;/QueryPlan&gt;
        &lt;/StmtSimple&gt;
      &lt;/Statements&gt;
    &lt;/Batch&gt;
  &lt;/BatchSequence&gt;
&lt;/ShowPlanXML&gt;
</code></pre>