Sql-server – Determining which SQL Server tables are accessed over a week

sql servertable

I inherited a large, pretty-much undocumented SQL Server 2008 R2 general purpose database, used by dozens of different applications.

We know many of the tables are no longer in use.

Any suggestions on how to determine what tables are still being routinely accessed over a weeks time? Or how to best configure Profiler to gather that info?

Best Answer

One possible way is to look into index usage statistics, such as sys.dm_db_index_usage_stats() (and several others with similar names).

Two possible catches here are:

  1. Their data isn't persisted between server restarts, so make sure that uptime is sufficiently long.
  2. If you have scheduled maintenance jobs, for example, regular index rebuilds, they will spoil the statistics. But at least you will be able to separate your objects into 2 categories: one with similar access counts across all of them and the rest is well above that.

Overall it's not very reliable, but at least will give you some quick initial insight. The second step could be, for example, denying all permissions on suspected tables and watch for your work phone / email box :).

Related Question