Partitioned Tables
Overview
The Partitioned Tables report answers the question partitioning is supposed to be judged on: where did the rows actually land?
Creating a partition function and a partition scheme is easy. Having them do something useful is not. A table can be partitioned ninety-six ways and still have every row sitting in one partition – which means you are paying every cost of partitioning and collecting none of the benefit, and there is nothing that can usefully be switched out.
The chart draws one lane per table with its rows spread across the partitions in partition order, so the shape of the lane tells you immediately whether the partitioning works.
Nothing on this page runs anything against the database. Splitting a partition function or switching a partition out is a maintenance window somebody plans, not a click. The right-click menu gives you scripts and stops there.
What this fixes
The old report was a pie chart of partition counts over a five-column grid, and it was answering a question nobody has:
- The pie was scaled by partition count, drawn as a share of every partition in the database. So it answered “what fraction of this database’s partitions belong to this table”. A ninety-six partition table holding twelve rows drew the biggest slice on the page.
- No row count was fetched at all. Whether partitioning had done anything – whether the rows were spread or all in one partition – was invisible unless you double-clicked into the advisor dialog, one table at a time.
- The chart stopped at twenty tables silently.
objectIdwas the second column of the grid, 45 pixels wide.- The query inner-joined
sys.index_columns, so a table whose partitioning column would not resolve – a partitioned heap being the ordinary case – was dropped from the report entirely rather than shown with an unknown key.
Where to find it
A database-level report. Select a database in the tree, then open Partitioned Tables.
If the database has no partitioned tables, the page says so – and notes that table partitioning required Enterprise or Developer edition before SQL Server 2016 SP1, which is the usual reason a database that was expected to have some does not.
Reading the chart
Each table is a lane divided into segments, one per partition, in partition order – so the lane reads left to right the way the partition function does. Each segment’s width is that partition’s share of the table’s rows, which is what makes an even lane and a lopsided one tell themselves apart at a glance.
Segments are coloured by what that individual partition is:
| Segment | Meaning |
|---|---|
| Blue | An ordinary partition. |
| Hatched | An empty partition – including the empty tail a healthy sliding window keeps ready. |
| Amber | Oversized – over 40% of the table’s rows. |
| Red | Holds nearly the whole table. |
That makes the shapes immediately distinguishable:
| Lane shape | What it means |
|---|---|
| Even blue segments with a hatched one at the right | Partitioning is working, with room to slide. |
| One amber segment among thin blue ones | Skewed – one partition is doing most of the work. |
| A single red segment | Effectively unpartitioned. |
| Amber at the far right, no hatching | The sliding-window mistake – the tail is collecting rows. |
Each lane also carries its partition key and type (EventDate (datetime2) · RANGE RIGHT), the partition function name, the boundary values at each end, and on the right the row count with largest holds 19.6% · no empty tail.
Above the chart, the headline states the finding in words – “5 partitioned tables are skewed towards one partition” – and names the worst offender underneath it with the specifics: “dbo.PartitionTest_EventLog holds 19.6% of 15K rows in partition 1 of 14, and has no empty partition to switch into”.
The chart draws the worst 15 tables; the grid carries every one. Sorting is worst-first, so the top lane is the table most worth looking at. An empty table sorts last, because it is information rather than a finding.
The verdicts
Each table gets one of four bands, with stated thresholds rather than a judgement call:
| Verdict | Rule | What it means |
|---|---|---|
| Spread | Rows across several partitions, with an empty partition ready at the end. | Healthy. This is what a working sliding window looks like. |
| Skewed | One partition holds 40% or more of the rows – or the last partition is not empty. | Partition elimination still helps some queries, but one partition is doing most of the work. |
| Effectively unpartitioned | One partition holds 90% or more – or a non-empty tail holds 25% or more. | Every cost of partitioning and none of the benefit. |
| Empty | No rows at all. | Informational, not a failure. |
A table can fail in two unrelated ways, which is why the verdict takes both into account: all the rows in one place, and a tail with no room left. Neither is derivable from the other.
An empty table gets its own band rather than a green one. A staging table with no rows is fine, but calling it healthy would be claiming the partitioning works when nothing has ever tested it. It is also excluded from the skew arithmetic, which would otherwise be dividing by zero.
Each lane carries a chip naming which way it is unhealthy – Tail filling for a table with no empty partition left to switch into, 1 empty where one is still waiting – so the verdict is never just a colour.
Note that a table can be Skewed on the tail rule alone. The tables above hold under 20% in their largest partition, well inside the 40% skew threshold, and are still reported as skewed because there is no empty tail left. That is the finding: the distribution is fine, but the next SPLIT will move data.
The tail rule is the one worth understanding
A partitioned table normally has an empty last partition – the unbounded one at the top of the range. That empty partition is what makes the next SPLIT a metadata-only operation.
Once the tail starts collecting rows, the next SPLIT moves data instead. That turns a routine maintenance step into a long, logged, blocking operation – usually discovered during the maintenance window rather than before it. A non-empty tail past 25% of the table is reported as effectively unpartitioned for exactly this reason: there is no room left to slide.
Reading the grid
| Column | What it is |
|---|---|
| Schema · Table | The table. |
| Partitions | How many partitions it has. |
| Rows | How many rows, in total. The number the old report never fetched. |
| Largest % | The share held by the biggest partition, with the same gauge the chart draws. This is the skew number. |
| Empty | How many partitions hold nothing. |
| Filegroups | How many filegroups the partitions spread across. One filegroup means no I/O benefit from the layout. |
| Unaligned | Non-clustered indexes not on the table’s partition scheme. |
| Partition Key | The partitioning column – shown as unknown rather than dropping the table when it cannot be resolved. |
| Type | RANGE RIGHT or RANGE LEFT. |
| Function | The partition function. |
Unaligned is the column that will bite you. An unaligned index silently blocks SWITCH
- and nothing else in the product tells you so. A partition switch that fails during a maintenance window because of an index nobody remembered is a bad way to find out.
The toolbar
| Group | Buttons |
|---|---|
| Filter | All · Skewed · One partition · Empty |
Refresh |
The filters narrow both the chart and the grid without requerying, so moving between them is instant.
If a filter leaves nothing, the page says which filter emptied it and points back at All, rather than showing a blank panel.
Right-click actions
| Item | What it does |
|---|---|
| Copy partition layout script | The current layout – partitions, boundaries, row counts and filegroups. |
| Copy boundary values script | Just the boundary values. |
| Copy sliding window checklist script | Offered only where there is a window to slide. A table with one partition has no boundaries to move. |
| Copy index alignment check script | Finds the indexes that would block a SWITCH. |
| Copy table name | Just the name. |
Everything here is clipboard-only. The report never alters a partition function, a scheme, or an index.
Requirements
This is a live report reading catalog metadata – partitions, schemes, functions, boundary values and index alignment – for the selected database.
Row counts come from partition statistics, so they are engine-maintained estimates rather than a COUNT(*). That is accurate enough to judge distribution, which is what the page is for.
Before SQL Server 2016 SP1, table partitioning required Enterprise or Developer edition. From 2016 SP1 onward it is available in all editions.
How to read the report
- Read the top lane first. Sorting is worst-first, so it is the table where partitioning is least likely to be earning its cost.
- Look at the shape before the numbers. One filled block is a different problem from a filled tail, and the lane shows which.
- Check the Empty column against the tail. A healthy sliding window has an empty partition waiting at the end. Zero empty partitions on a busy table is a maintenance problem in waiting.
- Read the Unaligned column on anything you plan to switch. This is the check that saves a maintenance window.
- Look at Filegroups. Partitions all on one filegroup still give you partition elimination and switching, but no I/O distribution – worth knowing if I/O was the reason for partitioning.
- Take the scripts, then plan the work. Nothing on this page should be done ad hoc.
Common patterns
Everything in the last partition. The sliding window stopped being maintained. New data keeps arriving in the unbounded tail, and the next SPLIT will move all of it. This is the most common finding on the page.
Everything in the first partition, RANGE RIGHT on a date. Usually a boundary set in the future, or a load that wrote everything before the first boundary. Partition elimination is doing nothing.
One partition at 90%+ with the rest evenly small. Often correct for a current-period table – but confirm it is intentional rather than a boundary that stopped being advanced.
A partitioned heap. Now visible. The old report dropped these entirely because the partitioning column would not resolve through the index join.
Unaligned indexes on a table you switch monthly. The switch is going to fail. Take the alignment script and fix it before the window, not during.
Every table Empty. A staging or template database. Informational – but nothing here has been tested by real data.
Related reports
| Report | Why you would go there |
|---|---|
| Large Tables | Whether a partitioned table is one of the biggest objects on the instance. |
| Files for Database | The filegroups the partitions are spread across. |
| Big Clustered Indexes | The clustering key is duplicated into every aligned index. |
| Table Use | Whether the partitioned table is read the way the partitioning assumes. |
| Index Fragmentation | Fragmentation is per partition on a partitioned table. |
Frequently asked questions
My table is partitioned but the report says effectively unpartitioned. Ninety percent or more of its rows are in one partition, or its tail has filled past 25%. The partition scheme exists but is not distributing anything.
Why is an empty table not green? Because nothing has tested the partitioning. A staging table with no rows is fine, but calling it healthy would claim something the data does not support.
What is an unaligned index? A non-clustered index that is not built on the table’s partition scheme. It silently blocks SWITCH, which is usually discovered mid-maintenance-window.
Why does the last partition need to be empty? Because that is what makes the next SPLIT metadata-only. Once the tail holds rows, splitting it physically moves them.
Why did a table appear that was not on the old report? Most likely a partitioned heap. The old query inner-joined sys.index_columns, so any table whose partitioning column would not resolve was dropped rather than shown.
Are the row counts exact? No – they come from partition statistics. They are engine-maintained and accurate enough to judge distribution.
Can I split or switch from this page? No, deliberately. You get the scripts. Partition maintenance is a planned operation, not a click.
Why does the report show nothing at all? There are no partitioned tables in this database. Before SQL Server 2016 SP1, partitioning required Enterprise or Developer edition.