February 8, 2010 at 4:12 pm
Hi,
I am using sys.dm_db_index_usage_stats dynamic view to find out what indexes are not being used.
I filter out those which have the following fields equal to zero:
user_seeks
user_scans
user_lookups
There is another column called user_updates, but this one, I assume, would always have counts as the data in the table gets updates, the index gets updated.
Is this correct?
Thanks.
February 8, 2010 at 9:57 pm
Just bear in mind that the data in that DMV is since the last time the DB was started up. So if you restart the SQL instance frequently or have the DB taken offline/online or in autoclose, the data in there will only be since the last start.
Also, that DMV only shows indexes that have been used at least once somehow since the last start. Query sys.indexes and left join the DMV to see all indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 8, 2010 at 10:26 pm
GilaMonster (2/8/2010)
Just bear in mind that the data in that DMV is since the last time the DB was started up. So if you restart the SQL instance frequently or have the DB taken offline/online or in autoclose, the data in there will only be since the last start.Also, that DMV only shows indexes that have been used at least once somehow since the last start. Query sys.indexes and left join the DMV to see all indexes.
If I do weekly index rebuild for all indexes on user tables in all user databases, would this mean that all the indexes are going to show on this dmv? I understand index rebuild will generate something like "system scan"?
February 9, 2010 at 12:15 am
Roust_m (2/8/2010)
If I do weekly index rebuild for all indexes on user tables in all user databases, would this mean that all the indexes are going to show on this dmv? I understand index rebuild will generate something like "system scan"?
Should do, but you'll still only have usage stats since the last DB start. Be careful of dropping indexes without watching for a sufficient period.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 9, 2010 at 7:10 pm
GilaMonster (2/9/2010)
Roust_m (2/8/2010)
If I do weekly index rebuild for all indexes on user tables in all user databases, would this mean that all the indexes are going to show on this dmv? I understand index rebuild will generate something like "system scan"?Should do, but you'll still only have usage stats since the last DB start. Be careful of dropping indexes without watching for a sufficient period.
Yep, I am including the last SQL Server restart date into my report for unused indexes and reckon a week or two should be enough to make a decision. What sort of time frame you are using?
February 9, 2010 at 7:18 pm
In our environment we would have to compile it for three months. Some queries are only run quarterly and have enough of a positive impact on the query to warrant keeping it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 9, 2010 at 9:55 pm
Roust_m (2/9/2010)
Yep, I am including the last SQL Server restart date into my report for unused indexes and reckon a week or two should be enough to make a decision.
That's just enough time to miss the indexes that are only used for a month end report...
What sort of time frame you are using?
I never go by this alone. I use missing indexes as a rough idea, do further analysis on stored procs then consider dropping the index if tests show no performance degradation from removing it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply