about use ratio and the index useful or useless

  • 892717952

    SSC-Addicted

    Points: 440

    1. how to check the use ratio of one index on the table?
    2.  and how to know one index useful or useless? and is there a value of index use ratio to show the index useless?

     

  • Joe Torre

    SSChampion

    Points: 10239

    Research [sys].[dm_db_index_usage_stats]. It has information on usage.

  • 892717952

    SSC-Addicted

    Points: 440

    yes, I know there are some information in sys.dm_db_index_usage_stats, but how to evaluate this index is useful or useless based on the information of sys.dm_db_index_usage_stats? and is there any other way to identify to know the index useful or useless

  • Joe Torre

    SSChampion

    Points: 10239

    If you have 0 or low numbers in the columns user_seeks and user_lookups,  and to a lesser extent, user_scans, then the index isn't very helpful. The column user_updates logs the number of DML commands against the index or in other words some indication of the overhead of maintaining the index. If your overhead is higher and usage is lower it should likely be dropped.

  • ZZartin

    SSC-Dedicated

    Points: 30383

    Just because an index has low usage doesn't necessarily mean it's useless.  And the data in dm_db_index_usage_stats refreshes every time the instance restarts so there might be an index that supports some super important query people only run once a month that looks like it's not used.

  • Joe Torre

    SSChampion

    Points: 10239

    Good points ZZartin. To do a more thorough analysis you could persist that data by collecting it into a user table.  Dm_db_index_usage_stats is the a good place to start in your analysis. For example, if I know in my organization a lot of reporting takes place the nth day of the month and my instance has restarted since the last nth of the month I would have inadequate data collected to make a good decision regarding the usefulness of indexes.

  • 892717952

    SSC-Addicted

    Points: 440

    Thanks Joe Torre and ZZartin kind help and patience !

Viewing 7 posts - 1 through 7 (of 7 total)

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