locked
Index_usage_stats questions RRS feed

  • Question

  • Hi all,

    I am using index_usage_stats dmv to find out usage of indexes and have some questions. Let me provide you a little screenshot for One of my biggest table's indexes.

    As most of us are aware of Index IDs, 1 represents clustered index.

    1) Is it good to have this much user_lookups on clustered index? I have fair bit of idea that User_lookups happen when index(Clustered and Non-clustered) doesnt cover all the columns which query is looking for.

    2) Might be a stupid question, does lookup occure only in Clustered index(or heap)?

    3) Should i think about dropping index 7(just based upon stats)? for sure i have to remove its usage in the code as well.

    4) How come index IDs 63, 64...i just have 10 indexes all together on the table. Is it because they have been created by users?

    Thanks,


    Dinkar Chalotra

    Tuesday, April 17, 2012 2:12 AM

Answers

  • 1) Is it good to have this much user_lookups on clustered index? I have fair bit of idea that User_lookups happen when index(Clustered and Non-clustered) doesnt cover all the columns which query is looking for.

    user_lookups are bookmark lookups (a.k.a. key lookups).  Those occur when a non-clustered index doesn't cover the query so the data row is retrieved via the CI key.  It looks like well over 90% of your non-clustered index seeks result in a lookup so you probably don't have many covering operations.  I can't say if that's good or bad without knowing more about your workload.

    2) Might be a stupid question, does lookup occure only in Clustered index(or heap)?

    Only on a clustered index.

    3) Should i think about dropping index 7(just based upon stats)? for sure i have to remove its usage in the code as well.

    Yes, it's a candate to be dropped based on this data.

    4) How come index IDs 63, 64...i just have 10 indexes all together on the table. Is it because they have been created by users?

    You may also have/had stats on the table so some of the index_id values are higher than expected.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Tuesday, April 17, 2012 2:42 AM
    Answerer
  • So would it be better to not to use Index Hint option in the coding(i.e. X++)?

    If not using Hint in the code(application wide), how fast query can find its covering index(in case not using index hint in the coding at all)?

    Yes, it is best to avoid hints unless required to coerce a better execution plan.  SQL Server uses a cost-based optimizer to generate an optimal query plan based on heuristics as well as available indexes and statistics.  It can evaluate whether a given index or a scan is the best way to data access path in a given query.  When you specify a hint, you tie the hands of the optimizer.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Wednesday, April 18, 2012 2:53 AM
    Answerer

All replies

  • 1) Is it good to have this much user_lookups on clustered index? I have fair bit of idea that User_lookups happen when index(Clustered and Non-clustered) doesnt cover all the columns which query is looking for.

    user_lookups are bookmark lookups (a.k.a. key lookups).  Those occur when a non-clustered index doesn't cover the query so the data row is retrieved via the CI key.  It looks like well over 90% of your non-clustered index seeks result in a lookup so you probably don't have many covering operations.  I can't say if that's good or bad without knowing more about your workload.

    2) Might be a stupid question, does lookup occure only in Clustered index(or heap)?

    Only on a clustered index.

    3) Should i think about dropping index 7(just based upon stats)? for sure i have to remove its usage in the code as well.

    Yes, it's a candate to be dropped based on this data.

    4) How come index IDs 63, 64...i just have 10 indexes all together on the table. Is it because they have been created by users?

    You may also have/had stats on the table so some of the index_id values are higher than expected.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Tuesday, April 17, 2012 2:42 AM
    Answerer
  • Thanks for clearing the clouds Dan.

    Much Appreciated.


    Dinkar Chalotra

    Tuesday, April 17, 2012 2:46 AM
  • Sorry One more,

    About user_lookups-- If query is not finding its covering columns in the index hint(coding doing this) then its next destiantion to look for them is Clustered Index? Does it look for them in other exisintg non-clustered indexes before going to seek into Clustered index?


    Dinkar Chalotra


    • Edited by Dinkar Chalotra Tuesday, April 17, 2012 6:30 AM spelling mistake
    Tuesday, April 17, 2012 6:29 AM
  • About user_lookups-- If query is not finding its covering columns in the index hint(coding doing this) then its next destiantion to look for them is Clustered Index? Does it look for them in other exisintg non-clustered indexes before going to seek into Clustered index?

    A table index hint limits the indexes the optimizer considers to optimize the query so a query with an index hint won't evaluate alternative data access paths, even if a more optimal one exists.  So a query will use the specified index with a key lookup even though a covering index might exist.  This is one reason why it's generally best to avoid hints (and make sure stats are up-to-date) and let the optimizer do it's job.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Tuesday, April 17, 2012 12:00 PM
    Answerer
  • So would it be better to not to use Index Hint option in the coding(i.e. X++)?

    If not using Hint in the code(application wide), how fast query can find its covering index(in case not using index hint in the coding at all)?


    Dinkar Chalotra

    Wednesday, April 18, 2012 2:45 AM
  • So would it be better to not to use Index Hint option in the coding(i.e. X++)?

    If not using Hint in the code(application wide), how fast query can find its covering index(in case not using index hint in the coding at all)?

    Yes, it is best to avoid hints unless required to coerce a better execution plan.  SQL Server uses a cost-based optimizer to generate an optimal query plan based on heuristics as well as available indexes and statistics.  It can evaluate whether a given index or a scan is the best way to data access path in a given query.  When you specify a hint, you tie the hands of the optimizer.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Wednesday, April 18, 2012 2:53 AM
    Answerer
  • Thanks Dan,

    Really appreciate your time.


    Dinkar Chalotra

    Wednesday, April 18, 2012 2:56 AM