I've got a high-demand transactional database which i think has ended-indexed. Initially, it did not have indexes whatsoever, so adding some for common processes designed a massive difference. However, with time, we have produced indexes to accelerate individual queries, and probably the most popular tables have 10-15 different indexes in it, and perhaps, the indexes are just slightly not the same as one another, or are identical posts inside a different order.
It is possible to straightforward method to watch database activity and know if any indexes aren't hit any longer, or what their usage percentage is? I am concerned that indexes were produced to accelerate whether single daily/weekly query, or perhaps a query that isn't being run any longer, however the index still needs to be stored current each time the information changes.
Within the situation from the high-traffic tables, this is a dozen occasions/second, and I wish to eliminate indexes which are weighing lower data updates while supplying only marginal improvement.
Consider the quantity of user seeks/scans/searches and last user seek/scan/research in sys.dm_db_index_usage_stats. These stats are totally reset at server launch, so you'd need to check following the server was ready to go another load for enought time.
If you are in SQL Server 2005/2008, you need to use the Database Tuning advisor.
You are able to specify it to operate for any given period of time, and it'll collect statistics on which has been used and what is not. In the finish from the run, it'll develop some very helpful findings on how you can optimize your indexing strategy.
This script will consider the DMV's (dynamic management sights) and discover individuals indices that weren't used.
DECLARE @dbid INT SELECT @dbid = DB_ID(DB_NAME()) SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID), INDEXNAME = I.NAME, I.INDEX_ID FROM SYS.INDEXES I JOIN SYS.OBJECTS O ON I.OBJECT_ID = O.OBJECT_ID WHERE OBJECTPROPERTY(O.OBJECT_ID, 'IsUserTable') = 1 AND I.INDEX_ID NOT IN (SELECT S.INDEX_ID FROM SYS.DM_DB_INDEX_USAGE_STATS S WHERE S.OBJECT_ID = I.OBJECT_ID AND I.INDEX_ID = S.INDEX_ID AND DATABASE_ID = @dbid) ORDER BY OBJECTNAME, I.INDEX_ID, INDEXNAME ASC
Actually - the Department of motor vehicles are dynamic - e.g. they get totally reset to "nothing" any time you restart your SQL Server services. Don't check individuals in case your server continues to be up for just a couple of minutes! Just about all indices can have up inside your result set......
But when you are able to monitor the end result group of this question with time, you need to certainly obtain a sense of which indices aren't getting used ever. Very handy indeed !
The Database Tuning Wizard is going to be useful here. Make use of the Profiler to record a typical group of queries during the period of a few hrs, and employ that trace file within the Tuning Wizard to recognize possible redundant indexes.