August 15, 2019 at 2:44 am
August 15, 2019 at 8:38 pm
Research [sys].[dm_db_index_usage_stats]. It has information on usage.
August 16, 2019 at 12:56 am
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
August 19, 2019 at 2:57 pm
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.
August 19, 2019 at 4:25 pm
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.
August 19, 2019 at 5:02 pm
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.
August 20, 2019 at 2:55 am
Thanks Joe Torre and ZZartin kind help and patience !
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply