Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

migrated from stackoverflow.com Aug 12 at 9:56

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 8 down vote accepted

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.

share|improve this answer

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.

http://www.sqlskills.com/blogs/joe/exceptions-what-sys-dm_db_index_usage_stats-doesnt-tell-you/

You would also like reading

http://sqlblog.com/blogs/louis_davidson/archive/2007/07/22/sys-dm-db-index-usage-stats.aspx

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.

share|improve this answer
    
Please distil the content from those links into your answer. It doesn't stand by itself and those links will die. –  Colin 't Hart Aug 12 at 10:16
    
Thanks Colin, are you saying it would be better to put this as comment than answer I was trying to put it in comment(because it was already answered) but due to restriction I was not able to can you modify my answer. –  Shanky Aug 12 at 10:18
1  
No. It's definitely an answer. Just summarise the reasons from those two links and add them to your answer. The links should function purely as reference. –  Colin 't Hart Aug 12 at 10:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.