Objects never used in the database.

Question

Hi everyone!

Is it right to conclude that objects (tables and indexes) that do not have entries (records) in 'sys.dm_db_index_usage_stats' are objects never used in a database?

Hope I was clear enough.

Answer 1

Like Olaf mentioned you cannot completely reply on the DMV. I have a different approach here. When I have to find out what objects( database or tables of database) are not used for a while. I drop a mail to application owners and stakeholders that as my finding from the DMV following objects seems to be not used for a while. Can you confirm that the database or tables are not used. Mail this to all stakeholders someone would respond you if they use the object or database if not, go ahead and make DB offline. IN this case atleast as alibi you have mail to save your back

Answer 2

Not really.
First, the information are not persisted, on SQL Server restart the DMV values get cleared, so you could only say, indexes missing in the DMV aren't used since last reboot.
The you can have tables without an indexes or with useless indexes, so that tables will also not in the DMV list.

Answer 3

Hi Doria,

In fact, the objects with no entries in 'sys.dm_db_index_usage_stats' means that these objects have not been used after the SQL Server service is started this time, or the database back online. Not means never.

After querying some tables in a database, query the view 'sys.dm_db_index_usage_stats' , you will see the related entries(Determine the table by database_id and object_id). Then you can try to restart the SQL Server service, or taking the database offline and then online, query the view again, the entries will disappear.

And according to the sys.dm_db_index_usage_stats (Transact-SQL):
The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.

===============================================

If the response helped, do "Accept Answer" and upvote it.

Answer 4

Thanks guys for all the answers!