May 12, 2015 at 1:10 am
Hi,
There are too many indexes built on DB. As per the naming convention it seems the indexes are built as per the suggestions provided from execution plan. I presume most of the indexes are used only once in a month for the reports but are hampering the performance of daily running queries. These are also occupying a lot of space.
To confirm on this I have used the below query to know & identify the unused indexes.
I have recorded the counters before and after the huge operations and I observed NO CHANGE in any of the values.
Could you please let me know what the below values exactly indicate and when do they change?
Is it good to delete the indexes having low USER_SEEKS, USER_SCANS, USER_LOOKUPS?
Please suggest me on this.
Query:
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,
USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
AND S.database_id = DB_ID()
May 12, 2015 at 3:49 am
Hi,
Hopefully this will help http://sqlblog.com/blogs/louis_davidson/archive/2007/07/22/sys-dm-db-index-usage-stats.aspx
If you see there is an index which is updated a lot but never used in a seek or scan then that is costing the server more resources than it is saving.
Also look at the last time it was used because it may have been used a lot but that could have been a long time ago and it has since been replaced by a better index in which case it's no longer needed.
Always test if you can
Thanks
Matt
May 12, 2015 at 4:00 am
sys.dm_db_index_usage_stats is the way to go for this information. However, it's worth noting that this will only be good information back to the last time the server was restarted or the database was attached to the server. You may have indexes that are only used once a year or so, but if you reboot your server, they won't show up in this DMV as having any use. I would suggest scripting out and keeping on the side all the indexes that you drop, just in case.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply