Large Tables
Overview
The Large Tables report answers one question across the whole server: which tables, anywhere on this instance, are actually large?
It is a flat ranking. The biggest table on the server is row one, whatever database it happens to live in. The second biggest is row two, and so on – database boundaries do not break the list up.
Every row also carries a size profile – Major, Large, Medium or Small – based not on the table’s absolute size but on how much of its own database it occupies. Those are two different facts and both matter. A 400 GB table is a big table. A 400 GB table that is 60% of its database is a restore plan, a migration plan and an archiving conversation.
What this fixes
The report used to draw one bar per database, segmented into that database’s biggest tables. That is composition, not ranking, and it answered a different question than the report’s name:
- A 40 GB table that happens to be the biggest thing in a small database drew the same size segment as a 400 GB table sitting inside a much larger one.
- The 1%-of-database filter was already being applied by the query and was never stated anywhere on screen, so the list looked complete when it was not.
- Nothing said how much had been scanned, how many tables cleared the floor, or how big the biggest one was.
Where to find it
An instance-level report. Right-click the server in the tree and open Instance Reports → Large Tables. The page title reads Large Tables for <server name>.
No database needs to be selected first – the report reads every database on the instance itself.
Reading the chart
The chart is the shortlist: the heaviest 15 tables. The grid underneath carries the whole Top N, and a line under the last bar says how many more are down there.
Each row carries, left to right:
| Element | What it is |
|---|---|
| Coloured stripe and chip | The size profile – Major, Large, Medium, Small. |
| Table name | Schema-qualified, e.g. [dbo].[Orders]. |
| Second line | Which database it is in, and its estimated row count. |
| Bar | Reserved space, scaled against the biggest table on the chart. |
| Value | The size, then the share of its own database that size represents. |
Hovering a row gives the full picture: profile and what it means, reserved size, the share of its database and that database’s size, the row count, and the share of everything scanned on the instance.
The size profiles
The profile is share of its own database, not absolute size:
| Profile | Share of its database | What it means |
|---|---|---|
| Major (red) | 25% or more | Dominates its database – a quarter or more of its space. |
| Large (amber) | 10% or more | A large share – worth knowing before a restore or migration. |
| Medium (blue) | 5% or more | A medium share. |
| Small (grey) | 1% to 5% | Over the floor, but a small share of its database. |
This is the column that turns a size list into something you can act on. A Major table is the one that decides how long your restore takes and how big your backup is. A Small table at the top of the ranking is a big table in a big database, which is usually just what a big database looks like.
The header lines
Above the bars, the summary line counts the whole filtered set – how many tables cleared the floor, how many are Major and Large, and the size of the single biggest table found.
The subtitle line underneath is the caveat line: how many databases were scanned, what the inclusion floor is, that sizes come from partition stats, and – when the Top N is smaller than the candidate set – how many of how many were actually fetched.
Both totals are computed over the whole filtered set, not the Top N, so the headline does not move when you change the Top N button.
Reading the grid
| Column | What it is |
|---|---|
| Size Profile | Major, Large, Medium or Small – tinted, except Small. |
| Size | Reserved space, with an in-cell bar scaled against the biggest table found. |
| Rows | Estimated row count from partition stats. |
| % of DB | Share of its own database. This is what sets the size profile. |
| DB Size | How big that database is in total. |
| % of Instance | That database’s share of everything scanned. |
| Database | Which database the table is in. |
| Table | Schema-qualified table name. |
Small rows are left untinted on purpose. Colouring every row would be a wall of colour with nothing standing out of it.
Clicking a bar selects the matching grid row, and selecting a grid row highlights its bar – the two views stay in step.
The toolbar
| Button | What it does |
|---|---|
Top 25 · Top 100 · Top 500 |
How many tables the grid carries. Default is Top 100. |
Changing the Top N re-runs the report. The expensive part of the scan does not depend on it
- the whole instance is read either way, and the Top N only decides how much of the result is fetched back.
Drilling in
Double-click a bar or a grid row to jump to that table’s database – specifically to its Disk Space panel, which is where the next question usually goes.
Right-click the chart for the chart-wide actions, including copying the chart to the clipboard as an image.
Requirements
This is a live report. It reads catalog metadata on the instance as it is now.
There is no Real Time / Historic pair and no restart notice on this page, unlike most instance reports. Table size comes from partition stats – catalog metadata rather than usage counters – so there is no collection window to caveat and nothing resets when SQL Server restarts.
The login needs to be able to read catalog views in each database it is meant to report on. A database it cannot read is skipped rather than failing the report.
What gets scanned, and what does not
Two filters shape the list. Both were already there; neither used to be stated.
Databases under 0.1% of the instance total are dropped before the scan. These are almost always the near-empty system databases, and every table in them would otherwise report as 90%+ of “its database” for having nothing else to share it with.
Tables under 1% of their own database are not included. That is the floor the report has always used, and it is now spelled out on the subtitle line rather than applied silently.
Only ONLINE databases are scanned. A database that is offline, restoring, or unreadable to this login is skipped in its own error handler, so one unreadable database does not derail the whole scan.
Only user tables are counted – nothing Microsoft shipped, and no system objects.
How to read the report
- Read the summary line first. How many tables cleared the floor, and how many of those are Major, is the scale of the situation before any row is read.
- Look at the size profiles, not just the sizes. The top of the ranking is the biggest table; the red chips are the tables that dominate something.
- A Major table is a restore and backup conversation. If one table is a quarter or more of a database, it sets that database’s restore time.
- Check the row count against the size. Enormous size with a modest row count means wide rows, LOB data, or a lot of index space rather than a lot of data.
- Compare
% of DBagainst% of Instance. A Major table in a database that is 0.2% of the instance is a small problem. A Major table in the database that is 60% of the instance is the instance’s biggest object. - Double-click through to Disk Space to see whether that database’s files have room for what the table is about to do next.
Common patterns
One Major table in every application database. Usually the fact table, the audit table, or the log table. Normal, and worth knowing which one so archiving conversations start in the right place.
A Major table nobody recognises. Very often an audit or history table that was switched on once and never given a retention policy. These are the cheapest wins on the whole page.
Huge size, very few rows. Wide rows, LOB columns, or index space rather than data. Big Clustered Indexes and Unused Indexes are where that goes next – a wide clustering key is duplicated into every nonclustered index on the table.
Many rows, modest size. A narrow, well-indexed table doing its job.
The same table listed more than once. Sizes are grouped by the row count reported for each index, so a table carrying a filtered index – which covers fewer rows than the table has – can contribute more than one line. Add the sizes together to get the table’s real total.
Nothing at all in the list. No table on the instance reaches 1% of its own database. On a server of many similarly sized tables that is a real and healthy answer, not an error.
Where the data comes from
sys.partitions and sys.allocation_units for reserved space, joined to sys.tables, sys.indexes and sys.schemas in each database, with sys.master_files for database sizes.
Each database is scanned in its own pass through a cursor, with every catalog view qualified by the database name rather than switching context – and each pass wrapped so an unreadable database is skipped rather than aborting the scan.
The whole scan runs under READ UNCOMMITTED, so it does not block anything and is not blocked by anything.
This is the heaviest query on the instance report list, because it reads every table in every database. It is allowed three minutes before it gives up. On a server with a great many databases, that is the one report you may have to run twice.
Related reports
| Report | Why you would go there |
|---|---|
| File Utilization | Whether the files holding these tables have room left. |
| Database By Size | The same question one level up – which databases, rather than which tables. |
| Disk Space | What the double-click goes to: the volume underneath. |
| Big Clustered Indexes | Why a large table may be larger than its data. |
| Unused Indexes | Index space on a large table that nothing reads. |
Frequently asked questions
Why is a table with a small percentage at the top of the list? Because the ranking is absolute size and the profile is relative share. The biggest table on the instance is row one even if its database is bigger still. Both facts are on the row.
Why do the counts at the top not change when I switch Top N? Because they are totals over the whole filtered set, not the fetched rows. The Top N decides how much of the list you see, not what was found.
Why are only 15 tables on the chart? Past that the bars are hairlines nobody can read or click. The grid carries the whole Top N, and the line under the last bar says how many more are there.
Are the row counts exact? No. They come from partition stats, which are maintained by the engine and are accurate enough for sizing but are not a COUNT(*).
Why is a database missing? Either it is under 0.1% of the instance total, or it is not ONLINE, or this login cannot read its catalog views. All three are skipped quietly so one of them cannot break the whole scan.
Why did the report time out? It reads every table in every database, and three minutes was not enough. Press F5 to run it again – a server with a great many databases sometimes needs the second attempt.
Is reserved space the same as the data in the table? No. Reserved space includes indexes, LOB allocations and free space inside allocated pages. A table that is large mostly because of its indexes is a different problem than one that is large because of its rows.