Is Information in sys.dm_db_index_usage_stats reliable

Question

I'm archiving data from an old system that has no documentation. Lucky me...

I'd like to find out when the tables were created, last accessed etc. Can I trust that this query will give me the correct answer or is there some parameters that I first need to check? SQL Server 2008 R2:

 SELECT t.Name AS Tabelname, p.rows AS NoOfRows, MAX(us.last_user_lookup) AS LastUsed, t.create_date AS CreatedDate
FROM sys.tables t
INNER JOIN 
 sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
 sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
LEFT JOIN --A lot of the tables did not have any records in this table
 sys.dm_db_index_usage_stats as us ON t.OBJECT_ID = us.OBJECT_ID
GROUP BY t.Name, p.rows, create_date
ORDER BY MAX(us.last_user_lookup) DESC

Answer 1

No.

The sys.dm_db_index_usage_stat view reflects only, at best, data since the last database startup (last instance startup, or last time the DB was brought online). Furthermore the entries may clear under memory pressure. It will give accurate positives (if a table has non-zero stats then it is used)) but may give false negatives (0 usage in stats may not reflect actual usage). Also there are many systems that use certain tables only once per week, once per month or even once per year.

Answer 2

Here is one more reason why you cannot blindly rely on DMV sys.dm_index_usage_stats. sys.dm_db_index_usage_stats does not get updated after an index is used for just the statistics associated with the indexed columns. Paul showed this in his link.

What sys.dm_index_usage_stats DMV does not tells you

You would also like reading

This article about index usage stats DMV

Moreover IMO I dont think by using DMV there is PERFECT way to find out when was table last used. I Belive using Profiler trace or Extended event trace would be more better option. Although profiler can cause load but server side trace is a good option.