Run it in the database: columns whose data is lopsided enough that the normal statistics histogram misleads the optimiser, with the CREATE STATISTICS … WHERE statement that would fix it. It only suggests; it creates nothing. Read-only, changes nothing. Tested on SQL Server 2017 and 2025.
/*
08_Filtered_Statistics_Candidates.sql - run IN the database: columns whose data is lopsided enough that the
normal statistics histogram misleads the optimiser, and a FILTERED STATISTIC would help.
Clive Roberts DBA, 10 October 2026. Read-only (it only suggests statements). SQL Server 2016 SP1 CU2 or later.
BACKGROUND
A statistics histogram has at most 200 steps for the whole table. When one value holds most of the rows
(Status = 'Closed' for 99% of orders) or the table has millions of distinct values, the few rows you
actually query (Status = 'Open', last month's dates) get a rough average estimate. A filtered statistic
builds a separate 200-step histogram for just the part of the table that matters:
CREATE STATISTICS fs_Orders_NotClosed ON dbo.Orders (Status) WHERE Status <> 'Closed' WITH FULLSCAN;
The common real-world win is a filtered statistic on a SECOND column for the rare subset, e.g. when queries
filter on Status = 'Open' AND CustomerID = @x:
CREATE STATISTICS fs_Orders_Open_Customer ON dbo.Orders (CustomerID) WHERE Status = 'Open' WITH FULLSCAN;
CAVEATS (important)
- The optimiser only uses a filtered statistic when the query's WHERE clause matches its filter at compile
time: literals, or parameters with OPTION (RECOMPILE). A parameterised Status = @s won't use it.
- Filtered statistics go stale: they only auto-update on changes to the WHOLE table, so schedule
UPDATE STATISTICS ... WITH FULLSCAN for them (Ola's IndexOptimize does this with @UpdateStatistics = 'ALL').
- Always test with the actual plan before and after; drop it if the estimate doesn't improve.
HOW TO READ IT
Result 1 - one value dominates: top_value holds top_value_pct of the table; skew_ratio is how many times
more rows it has than the average value. suggestion:
- a column with few values (Status, Type, IsDeleted): its own histogram is exact, so the risk is
queries that ALSO filter on another column (Status = 'Open' AND CustomerID = 5). The suggestion
is a filtered statistic on that other column for the rarer value - fill in the column name.
- a column with many values: a filtered statistic for everything EXCEPT the dominant value.
Result 2 - the histogram is full (200 steps) and each step lumps together many values (avg_rows_per_step
in the millions): estimates for ranges inside a step are guesses. Consider filtered statistics
by range (e.g. recent dates) or check whether queries on this column misbehave (07_Cardinality_Estimate_Skew.sql).
*/
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @MinRows bigint = 100000; -- only tables with at least this many rows
DECLARE @MinTopPct decimal(5,2) = 50; -- result 1: a single value holding at least this % of the rows, OR
DECLARE @MinSkew decimal(18,1) = 1000; -- a value with this many times the average value's row count (many-valued columns)
IF OBJECT_ID('tempdb..#h') IS NOT NULL DROP TABLE #h;
SELECT s.object_id, s.stats_id, s.name AS stats_name, s.has_filter, s.filter_definition,
c.name AS leading_column, TYPE_NAME(c.user_type_id) AS column_type,
sp.rows AS table_rows, sp.rows_sampled, sp.steps, sp.last_updated,
h.step_number, h.range_high_key, h.equal_rows, h.range_rows, h.distinct_range_rows
INTO #h
FROM sys.stats AS s
JOIN sys.stats_columns AS sc ON sc.object_id = s.object_id AND sc.stats_id = s.stats_id AND sc.stats_column_id = 1
JOIN sys.columns AS c ON c.object_id = s.object_id AND c.column_id = sc.column_id
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
CROSS APPLY sys.dm_db_stats_histogram(s.object_id, s.stats_id) AS h
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1 AND OBJECTPROPERTY(s.object_id, 'IsMsShipped') = 0
AND sp.rows >= @MinRows AND s.has_filter = 0;
-- Result 1: one value dominates the column
;WITH per_stat AS (
SELECT object_id, stats_id, MAX(stats_name) AS stats_name, MAX(leading_column) AS leading_column, MAX(column_type) AS column_type,
MAX(table_rows) AS table_rows, MAX(steps) AS steps, MAX(last_updated) AS last_updated,
SUM(equal_rows + range_rows) AS hist_rows,
SUM(distinct_range_rows) + COUNT(*) AS distinct_values
FROM #h GROUP BY object_id, stats_id),
top_value AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY object_id, stats_id ORDER BY equal_rows DESC) AS rn FROM #h),
lit AS ( -- each histogram value written as a T-SQL literal
SELECT object_id, stats_id, rn, equal_rows, range_high_key,
CASE WHEN CONVERT(sysname, SQL_VARIANT_PROPERTY(range_high_key, 'BaseType')) IN (N'tinyint', N'smallint', N'int', N'bigint', N'decimal', N'numeric', N'float', N'real', N'money', N'smallmoney', N'bit')
THEN CONVERT(nvarchar(200), range_high_key)
WHEN CONVERT(sysname, SQL_VARIANT_PROPERTY(range_high_key, 'BaseType')) IN (N'date', N'datetime', N'datetime2', N'smalldatetime', N'datetimeoffset', N'time')
THEN N'''' + CONVERT(nvarchar(40), range_high_key, 126) + N''''
ELSE N'''' + REPLACE(CONVERT(nvarchar(200), range_high_key), N'''', N'''''') + N'''' END AS literal
FROM top_value WHERE rn <= 2)
SELECT TOP (50)
OBJECT_SCHEMA_NAME(p.object_id) + N'.' + OBJECT_NAME(p.object_id) AS [table], p.leading_column AS [column], p.stats_name,
p.table_rows, CONVERT(nvarchar(200), t.range_high_key) AS top_value,
CONVERT(decimal(5,1), 100.0 * t.equal_rows / NULLIF(p.hist_rows, 0)) AS top_value_pct,
CONVERT(bigint, p.distinct_values) AS distinct_values_approx,
CONVERT(decimal(18,1), t.equal_rows / NULLIF(p.hist_rows / NULLIF(p.distinct_values, 0), 0)) AS skew_ratio,
CONVERT(nvarchar(200), t2.range_high_key) AS next_value,
CONVERT(decimal(5,2), 100.0 * t2.equal_rows / NULLIF(p.hist_rows, 0)) AS next_value_pct,
p.steps, p.last_updated,
CASE WHEN p.distinct_values <= 200 AND t2.literal IS NOT NULL
-- few values: the histogram is exact for this column, so the risk is a query that ALSO filters on another
-- column. Put the filtered statistic on that other column, for the rarer value(s).
THEN N'CREATE STATISTICS ' + QUOTENAME(LEFT(N'fs_' + OBJECT_NAME(p.object_id) + N'_' + p.leading_column + N'_' + REPLACE(CONVERT(nvarchar(50), t2.range_high_key), N' ', N''), 128))
+ N' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + N'.' + QUOTENAME(OBJECT_NAME(p.object_id)) + N' (<the column queried together with ' + p.leading_column + N'>)'
+ N' WHERE ' + QUOTENAME(p.leading_column) + N' = ' + t2.literal + N' WITH FULLSCAN;'
-- many values, one dominates: a separate histogram for everything else
ELSE N'CREATE STATISTICS ' + QUOTENAME(LEFT(N'fs_' + OBJECT_NAME(p.object_id) + N'_' + p.leading_column + N'_not_top', 128))
+ N' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id)) + N'.' + QUOTENAME(OBJECT_NAME(p.object_id)) + N' (' + QUOTENAME(p.leading_column) + N')'
+ N' WHERE ' + QUOTENAME(p.leading_column) + N' <> ' + t.literal + N' WITH FULLSCAN;' END AS suggestion
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY object_id, leading_column ORDER BY last_updated DESC) AS col_rn FROM per_stat) AS p -- one row per column
JOIN lit AS t ON t.object_id = p.object_id AND t.stats_id = p.stats_id AND t.rn = 1
LEFT JOIN lit AS t2 ON t2.object_id = p.object_id AND t2.stats_id = p.stats_id AND t2.rn = 2
WHERE p.col_rn = 1 AND t.range_high_key IS NOT NULL
AND (100.0 * t.equal_rows / NULLIF(p.hist_rows, 0) >= @MinTopPct
OR t.equal_rows / NULLIF(p.hist_rows / NULLIF(p.distinct_values, 0), 0) >= @MinSkew)
ORDER BY top_value_pct DESC;
-- Result 2: full histograms where each step hides a lot of values
SELECT TOP (50)
OBJECT_SCHEMA_NAME(object_id) + N'.' + OBJECT_NAME(object_id) AS [table], MAX(leading_column) AS [column], MAX(stats_name) AS stats_name,
MAX(table_rows) AS table_rows, MAX(steps) AS steps,
CONVERT(bigint, SUM(range_rows) / NULLIF(COUNT(*), 0)) AS avg_rows_per_step,
CONVERT(bigint, MAX(range_rows)) AS biggest_step_rows,
CONVERT(bigint, SUM(distinct_range_rows) / NULLIF(COUNT(*), 0)) AS avg_values_per_step,
MAX(last_updated) AS last_updated,
CONVERT(decimal(5,1), 100.0 * MAX(rows_sampled) / NULLIF(MAX(table_rows), 0)) AS sample_pct
FROM #h
GROUP BY object_id, stats_id
HAVING MAX(steps) >= 195 AND SUM(range_rows) / NULLIF(COUNT(*), 0) >= 100000
ORDER BY avg_rows_per_step DESC;
-- Existing filtered statistics and how old they are (they need their own maintenance - see the caveats above)
SELECT OBJECT_SCHEMA_NAME(s.object_id) + N'.' + OBJECT_NAME(s.object_id) AS [table], s.name AS filtered_statistic,
s.filter_definition, sp.rows AS rows_in_filter, sp.rows_sampled, sp.last_updated, sp.modification_counter,
DATEDIFF(DAY, sp.last_updated, GETDATE()) AS days_old
FROM sys.stats AS s CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.has_filter = 1 AND OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
ORDER BY days_old DESC;
DROP TABLE #h;