How inaccurate is sys.dm_db_partition_stats?

Question

I am looking at ways to count rows in several tables on several databases. In this particular case I am looking for unused and abandoned items.

Several sources source1, source2 recommend using sys.dm_db_partition_stats everything agrees that the count is not perfectly accurate. MSDN describes row_count as

approximate number of rows in the partition.

I understand if the value is always changing, it might be off by a bit. There are some good arguments on why this small discrepancy really doesn't matter. But if the value is near zero or none, will it be accurate?

If I use sys.dm_db_partition_stats and I am getting no rows found, do I need to run a second query using COUNT (or something else) to make sure the table is really empty or is the inaccuracy limited to minor fluctuations over minor periods of time?

Clarify I don't own the data, so I am ethically prohibited from looking at it. If sys.dm_db_partition_stats tells me a table is empty I can't just go open it and see if it really is empty. I need to know if I can depend on the results returned from it. As pointed out in answers, more information than just this will be required, before deleting. The table may be used as a temp table, maybe it gets rows written to it and removed, and just always happens to be empty when I look at it.

Answer 1

If you're looking for unused or abandoned tables, the number of rows won't be enough. You'd better look into index usage stats:

select database_id, object_id, index_id, 
    last_user_seek, last_user_scan, last_user_lookup, last_user_update
from sys.dm_db_index_usage_stats

If you are interested in several methods for tracking table usage, I wrote about it some months ago.

Answer 2

I think source1 has the answer. He (Aaron) states that even when you get an "accurate" count, you could be blocking users trying to write to the table. As soon as you put your eyes on the result, it could be immediately inaccurate as writers were trying to put data in your table while you blocked them for the accurate count. So, I believe using partition_stats works fine for what you want. I wouldn't be afraid to test it though and it might be an interesting test at that. You could create a db of 1000 empty tables. Then a routine that constantly inserts and deletes data in random tables. Then run your partition query and run your count query and compare.
I think what might work best is to use partitions and accumulate your data in a table and over periods of days or weeks, any table with consistently zero results is unused or abandoned.