Simple Inner join suggesting a Include index

Posted on

Question :

I have this simple inner join query and its execution plan master table has around 34K records and detail table has around 51K records. But this simple query is suggesting me an index with include (containing all master field that I include in select). I wasn’t expecting this what could be the reason and remedy.

   DECLARE    
    @StartDrInvDate Date ='2017-06-01',
    @EndDrInvDate Date='2017-08-31'

    SELECT
        Mastertbl.DrInvoiceID
       ,Mastertbl.DrInvoiceNo
       ,Mastertbl.DistributorInvNo
       ,PreparedBy
       ,detailtbl.BatchNo
       ,detailtbl.Discount
       ,detailtbl.TradePrice
       ,detailtbl.IssuedUnits
       ,detailtbl.FreeUnits
    FROM scmDrInvoices Mastertbl
    INNER JOIN scmDrInvoiceDetails detailtbl
        ON Mastertbl.DrInvoiceID = detailtbl.DrInvoiceID
    WHERE
    (Mastertbl.DrInvDate BETWEEN @StartDrInvDate AND @EndDrInvDate)

enter image description here

Here is the link for XML plan.

Answer :

Posting the ‘actual’ execution plan might help us provide a more complete answer.

As a guess, I’d say that Sql Server probably thinks (based on available statistics) that most rows from scmDrInvoices are going to fall in your WHERE date range. (Out of the 34k rows, how many rows fall between your @StartDrInvDate and @EndDrInvDate?)

The optimizer thinks a clustered index scan of that table (along with a table scan of scmDrInvoiceDetails) and a hash match is the most optimal solution (currently). It also seems that Sql Server is suggesting you create a ‘covering’ (A covering index is one which can satisfy all requested columns in a query without performing a further lookup into the clustered index.) index on scmDrInvoices, which may still result in an index scan, but the index will probably be much smaller.

You could try running UPDATE STATISTICS on both tables first to see if the optimizer generates a different plan.

Posting the actual execution plan may help us provide a more complete answer.

Leave a Reply

Your email address will not be published. Required fields are marked *