April 24, 2017 at 2:09 pm
So I have a nightly process where I import the index usage and missing index DMV's along with sys.objects, sys.indexes and sys.partitions into a central database and then run some SSRS reports against it for missing indexes, unused indexes and indexes that haven't been used in a few months.
It's not a daily thing to look at these reports but a few times a year I will delete a bunch of indexes mostly in replicated and reporting tables. We had a database run out of space recently which is why I was doing it today.
After deleting a few indexes I noticed that it seemed the reports had too little indexes and none for the database that ran out of space a few weeks ago. So I ran the raw DMV's and noticed that it's not reporting data for some tables in sys.dm_db_index_usage_stats. The tables are in sys.objects and the indexes are in sys.indexes but nothing for some tables in the index usage DMV.
I tried it on our production SQL 2005 server and the same on the pre-production copy of it with SQL 2012 that we're looking to turn up soon with the same restored copy of the original databases. From first glance it seems that it's mostly older tables that aren't used on a regular basis or maybe not at all that are affected.
April 24, 2017 at 2:14 pm
An index will only have an entry in that DMV if it's been used at least once, for anything, since SQL Server last started.
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
April 24, 2017 at 2:28 pm
See that now. I've always gone by when the last seek time was null or at least a few months in the past.
I ran a select on a table and it appeared in the DMV
What is strange is that we have a few replicated databases and I used to go around and delete dozens of indexes from them every few months after a replication resync assuming no one used those tables. I guess they only appeared in the report because of replication updating them.
April 24, 2017 at 3:32 pm
alen teplitsky - Monday, April 24, 2017 2:28 PMI guess they only appeared in the report because of replication updating them.
Yup.
The DMV gets flushed by a restart of SQL Server, hence it's very risky to depend just on this DMV to decide that indexes are never used. You can only say they're unused since the last time SQL started.
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
April 24, 2017 at 3:47 pm
thx
going to whip up a report tomorrow. We have two reporting servers that use close to 10TB of storage and it looks like most of the tables haven't been accessed for the last 6 months
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply