Home Forums SQL Server 2005 SQL Server 2005 Performance Tuning Why are user_lookups > 0 on Clustered Index? RE: Why are user_lookups > 0 on Clustered Index?

  • December 31, 2009 at 12:47 am

    #1097765

    digitalox (12/30/2009)

    Seems to be it would be more useful if the user_lookup values were populated in each instance of the NC indexes instead so that you could identify the offending ones rather than just have them lumped in the clustered/heap.

    But this is the index_usage_stats dmv. It shows how the indexes are being used. The cluster is the one used for the lookups, not the nonclusters. Hence it would have to show up against the cluster

    Technically, the nonclusters don't do the lookups. The optimiser adds a lookup step to a plan if it needs columns that the nonclustered index(es) didn't provide

    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