asked
Index Usage stats
Hi experts! To find the unused index, i use to check for the columns User_Seeks = 0 and User_scans = 0 and User_lookups = 0 of "
sys.dm db index usage stats" DMV, but here i can see system_seek, system_scan, System_Lookups column also. My question is, does i need to care system_seek, system_scan, System_Lookups columns also? what does system_seek, system_scan, System_Lookups columns mean and its importance?
Cyborg, these are internal counters for when the index is being maintained by the system. You can safely ignore these values when looking at index usage for possible unused indexes. You are basically only interested in the user based columns. See also: [
http://ask.sqlservercentral.com/questions/16793/what-causes-system_scans-in-sysdm_db_index_usage_stats-to-count-up][1] *I wrote a short note about this on my blog too. [LINK][2]* [1]:
http://ask.sqlservercentral.com/questions/16793/what-causes-system_scans-in-sysdm_db_index_usage_stats-to-count-up [2]:
http://williamdurkin.wordpress.com/2010/08/03/who-accessed-my-indexes/
Also remember that these usage counters are reset when SQL Server is restarted, so be careful when interpreting the numbers. Please also be careful when the system has been running long, as the usage stats are also not necessarily a good base of analysis. An index that used to be hammered/well used 3 months ago, may now be unused but still reporting the high usage of 3 months ago if the system has been online that long. Another also: If you have added an index/ removed an index,the other indexes will have different usage patterns that are maybe not apparent until later on. It can be useful to save the information into a separate table and keep track of the usage that way. You need to keep notes of when table/index structure are changed, so that you can analyse the system properly. *This is a manual process, extremely long winded and specialed per system and should be performed very carefully.*
I post the updated and see you've made the point 36 seconds ahead of it... some days it just doesn't pay.
Not bad form at all. give us a link. if its relevant to the question of course ;)
What WilliamD said. Just remember that this DMO is reset when the server is started, the database is detached, or when the database is closed. So before you start dropping indexes based on the information provided, be sure it's covering an adequate period of time so you don't drop indexes that are going to be needed in overnight or weekly reports or something along those lines.
