Post reply

Question on included columns in non-clustered index

  • May 8, 2014 at 9:35 am

    #307151

    So... I am trying to tune a process that is running slowly. I analyzed the process using the Database Engine Tuning Advisor, and it recommended the creation of 3 indexes, all non-clustered:

    1) ColA, include ColB

    2) ColA, include ColC

    3) ColA, include ColD

    So... I created a single non-clustered index on:

    4) ColA, include ColB, ColC, ColD

    That should do the same thing, right? A look at my execution plan shows that the index I created is being scanned -- 3 times. What is puzzling me, though, is that the Database Engine Tuning Advisor is still recommending I create these 3 separate indexes, even with the index (4) that I created in existence. Can someone explain this to me?

    If it matters, ColA, ColB, ColC and ColD are all int FKs.

    Thanks in advance for any advice and insight. Please let me know if I can provide any additional info.

    --Mandy

  • May 8, 2014 at 10:30 am

    #1711953

    My suggestion would be to examine the execution plans for your queries before and after you apply your index. Just because the DTA suggested it, doesn't mean it's a good idea.

    Assuming the index helps with your queries at all, your approach is the right one.

    You're seeing exactly why I never trust the DTA and tell others the same.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • May 8, 2014 at 10:39 am

    #1711956

    Thanks, Grant, for reminding me that it's OK to trust my instincts. 🙂

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply