Cardinality Report
The Cardinality Report is a feature within the Missing Indexes section of Database Health Monitor. It helps you evaluate whether SQL Server’s missing index suggestions are actually worth implementing, based on the uniqueness—or cardinality—of the suggested columns.
When SQL Server recommends a missing index, it includes details such as the table name, equality columns, and inequality columns. However, not all recommended indexes provide performance benefits. The Cardinality Report helps assess these columns to determine if an index is likely to be useful.
How the Cardinality Report Works:
- The report displays the distinct values found in a suggested index column.
- A visual chart shows each unique value and how many times it appears.
- Below the chart, a grid lists the value counts in detail.
This helps you assess how selective (or not) the column is.
Why Cardinality Matters:
- High Cardinality (many distinct values): Ideal for indexing. The SQL Server optimizer can use these indexes efficiently.
- Low Cardinality (few distinct values): Often poor choices for indexing. These may not help performance and can slow down writes (INSERT, UPDATE, DELETE).
In the screenshot above, the column agent_id has a total of 1750 rows, but all have the same value of 22. This is very low cardinality. Adding an index here would offer little to no performance improvement and might even hurt performance due to overhead.
When to Use the Cardinality Report:
Use this tool any time you’re reviewing missing index suggestions in Database Health Monitor. It gives critical context that SQL Server’s suggestions alone don’t provide—helping you avoid implementing indexes that won’t help (or could even hurt) performance.
Need Help?
The team at Stedman Solutions are here to assist with your SQL Server needs. Get help today by contacting Stedman Solutions through the free 30-minute consultation form.
Download Database Health Monitor here.