Table Use Report

Overview

The Table Use report places every user table by how recently anything read it, ranked by the space it holds – so the big table nobody queries stands out.

That combination is the finding. Either half on its own is much less useful: a list of unread tables tells you nothing about whether they matter, and a list of large tables tells you nothing about whether they are needed.

A recency band across the top puts every table into one of five groups, from read-today through never-read, and clicking a group filters the page to it. Below that, bars sized by reserved space and coloured by recency.

What this fixes

  • The pie encoded an ordered scale – today, this week, last month – with unrelated hues that reshuffled on every refresh.
  • The query never touched partition stats, so a 4 GB archive table and an empty scratch table looked identical, and the report could not answer its own question: which big tables does nobody read.
  • Usage counters only exist since the last restart, and the report never said so. Its bottom group was labelled “Unknown”, which read as a data problem rather than as the finding it is.

Where to find it

A database level report. Expand the server → expand the database → Real Time → Table Use.

The restart caveat

Read counts come from sys.dm_db_index_usage_stats, which is emptied by a service restart. The subtitle always says when the counters started, because a table in the never-read group on an instance that restarted this morning has not been proved unused – it has only been unread since breakfast.

The never-read group is a real finding, not an “Unknown” bucket, provided the counters have been accumulating long enough to mean something.

The Table Use report
The whole report. The recency band across the top filters the page when clicked.
Bars sized by reserved space, coloured by recency
Size and recency together – the big table nobody reads is the one that stands out.

Reading the grid

The table use grid
Created next to Last Queried is worth reading as a pair.
Column What it is
Table Schema-qualified table name.
Rows Row count.
Size Reserved space – what you get back if it goes.
Created When the table was created.
Last Queried When anything last read it.
Days Idle How long since that read.
Group The recency band.

Created next to Last Queried is worth reading as a pair. A table created two years ago and never queried is a different conversation from one created last week.

The toolbar

Group Buttons
Top N Top 25 · Top 100 · Top 500
Rank by By size · By last use
Refresh

The rank toggle re-sorts without re-querying. By size answers “what is big and unread”; By last use answers “what has been dormant longest”.

How to read the report

  1. Check when the counters started. The subtitle says. Everything below depends on it.
  2. Click the never-read band to filter to the candidates.
  3. Rank by size. Big and unread is where the space is.
  4. Check Created. Recently created tables have not had a chance to be read.
  5. Confirm before acting. Read counters do not see a table read only by a quarterly job.

Common patterns

A large table in the never-read group, created years ago. The classic finding – an archive or migration leftover nobody removed.

Several tables with similar names and dates, all unread. Backup copies made during a deployment. Orders_20240115 and friends.

Everything in the never-read group right after a restart. The counters are young, not the tables. The subtitle says how young.

A big table read today with few rows. Wide rows, LOB data, or heavy fragmentation. Worth a look, but not an unused-table finding.

Report Why you would go there
Unused Indexes The same idea one level down.
Databases by Size Where the database’s space goes overall.
Unclustered Tables Whether the unread table is also a heap.

Frequently asked questions

Does never-read mean safe to drop? No. It means nothing has read it since the counters started. A quarterly report, a disaster-recovery path, or an external tool may still need it. It means investigate.

Why does the report need partition stats? Because without size, “unread” cannot be prioritised. The old query did not read them, so a 4 GB archive and an empty scratch table looked the same.

What was the Unknown group? The old report’s label for tables with no usage row. That is not unknown – it is never-read, which is the finding.

Why does ranking change nothing on screen sometimes? The toggle re-sorts the same data without re-querying. If the top rows are both the largest and the most idle, the order will not move.