Cardinality Report
Overview
The Missing Indexes report tells you which columns the optimizer wanted an index on. It does not tell you what is in those columns, and that is what decides whether the index is any good.
An index key on a column with three distinct values across ten million rows is almost never worth having. An index whose leading column has three distinct values and whose second column has two million is worth having with the columns the other way round. The optimizer’s suggestion carries no such judgement, because the optimizer lists equality columns in the order it happened to meet them.
The Cardinality Report is the answer to that. For each column in a suggested index key, it runs a distinct-value count and shows:
- How many distinct values the column holds – the row count of its list.
- How the rows are spread across those values – the counts down the list, and the treemap.
Where to find it
It is a drill down from Missing Indexes, not a page in the tree.
| Route | How |
|---|---|
| Missing Indexes (database level) | Right-click a suggestion → Show Cardinality Report |
| Missing Indexes (instance level) | Right-click a suggestion → Show Cardinality Report |
The page title reads Cardinality Report for <schema.table>.
The report is built from the suggestion you right-clicked: the table is the suggestion’s table, and the columns are its equality and inequality columns together, in the order the suggestion listed them.
Requirements
SELECTon the table, because the report counts the real rows rather than reading statistics.- Patience on a large table. Each column is a
GROUP BYover the whole table, and there is one of them per column in the key. On a very large table this is a real scan and it will take as long as a real scan takes.
Up to six columns get a list. A suggestion with more than six columns shows the first six, which is more than enough to make the decision. Each list stops at 100,000 rows, and the page says so above the lists.
Reading the page
The page is a treemap across the top and a row of lists underneath it – one list per column in the key.
The lists
Each list is one column, with two columns of its own:
| Column | What it is |
|---|---|
| Count | How many rows hold that value. |
| <column name> | The value itself, or NULL. |
The lists are ordered by count, largest first, so the top of each list is the most common value in that column.
What to read off a list:
- How long it is. A list with a handful of entries is a low cardinality column. A list that runs to the 100,000 row cap is a high cardinality column.
- How the counts fall. A first entry that holds most of the table and a long tail of ones is a skewed column, and skew is what makes a single plan wrong for half the parameter values that get passed to it.
- Whether
NULLis at the top. A column that is mostly NULL is a filtered index candidate, not a leading key column.
The treemap
The treemap draws the top twenty values of one column, sized by row count, heat coloured. It starts on the first column of the key.
Above each list is a Show On Chart button. Click it to redraw the treemap from that column, which is how the columns get compared: switch between them and watch the map go from a few large tiles to many small ones, or the other way round.
A treemap that is one enormous tile and a few slivers is a badly skewed column. A treemap of twenty roughly equal tiles is an evenly distributed one.
How to use it
- Open it from the suggestion you are actually considering creating, not from the biggest one on the report.
- Compare the list lengths. The column with the most distinct values is usually the one that belongs first in the key, because that is the one that eliminates the most rows first.
- Look at the top counts. A leading column whose most common value covers a third of the table will not eliminate much, however many distinct values it has in total.
- Use
Show On Charton each column in turn to see the shape rather than the numbers. - Decide three things: whether the index is worth creating at all, what order the key columns should go in, and whether any column belongs in the
INCLUDElist instead of the key.
Common patterns
A leading column with two or three values. A status or flag column. Almost never belongs first in a key. Either move it later or leave it out and consider a filtered index on the value you actually query.
One value holding most of the table, with a long tail. Classic skew. The index may still be very useful for the rare values and useless for the common one, and a single cached plan cannot be right for both. Query Consistency and the parameter sniffing reports are the follow-up.
Every list running to the row cap. Every column is highly selective. The key order matters less here; put the column the queries filter on most first.
A column that is nearly all NULL. A filtered index on WHERE column IS NOT NULL is usually smaller and better than including the column in the key.
Where the data comes from
A GROUP BY per column against the table itself, in the database the suggestion came from. Nothing is stored, and nothing is installed on the monitored instance.
These are real counts, not statistics estimates. That is the point of the report – the optimizer’s estimates are what produced the suggestion in the first place – but it is also why it costs a scan per column.
Related reports
| Report | Why you would go there |
|---|---|
| Missing Indexes | Where this report is opened from, and where the suggestion came from. |
| Statistics | Whether the optimizer’s own picture of these columns is current. |
| Duplicate Indexes | Whether an index that would serve the query already exists. |
| Index Fragmentation | The cost side of adding another index. |
| Table Use | Whether the table is read enough to justify the index at all. |
Frequently asked questions
Why does it take so long? Because it counts real rows. Each column in the key is a GROUP BY over the whole table.
Why are only some of the columns shown? Six lists is the maximum, and it is more than enough to decide a key order.
Why does a list stop partway down? Each list caps at 100,000 rows. Anything past that is already a very high cardinality column and the exact number is not going to change the decision.
Why is NULL in the list? Because NULL is a value the index will store, and a column that is mostly NULL is a different kind of index candidate.
Can I open it without going through Missing Indexes? No. The report is built from a suggestion’s table and columns, so it needs one to start from.