Post reply

can i solve lokups of non clusterd using clustering index, or i should use the cols in key of nonclusted or includes

  • April 9, 2015 at 3:13 am

    #398299

    Q1) I have a table which hase clustered on first col (unique identifyer col)

    I have an other non clust on few cols. after checking the plan i show it is using lookup(clustered). so

    i thought to include thoes cols in non clustered to remove ridlookup. but then i show i have clustered index, so it will not help me much , please suggest me in general.

    yours sincerely

  • April 9, 2015 at 3:32 am

    #1789794

    You want to add the columns as include in the nonclustered index to avoid lookups.

    Bear in mind, lookups may be fine, it depends how many rows are affected by the query and how often it runs

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 9, 2015 at 7:10 am

    #1789845

    Adding INCLUDE columns to a nonclustered index doesn't affect the clustered index, it's use or maintenance. They only affect the nonclustered indexes.

    "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

  • April 9, 2015 at 9:33 am

    #1789902

    You need to review the choice of clustering key; the clustering key is critical to performance, so never be lazy enough to allow the clustering key to default to identity. The majority of time, identity is not the best clustering key. If you review it, and identity is the best clus key, great, but never just default the clus key.

    At a minimum, review the missing index stats and the index usage stats. Another good indicator that you may have the wrong clustering key is if you have a lot of non-clustered indexes that all start with the same column(s).

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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