One customer owns half the orders, while most customers own a handful. Filtered statistics can describe those groups separately, giving the optimizer a more useful picture than one broad histogram. They help estimates, but they do not create an access path or cure every reused plan.
See the Skew Before Adding Anything
A statistic on CustomerID summarizes the whole table in a histogram. When one value dominates, estimates for small values can be pulled toward a plan that suits the heavy value, especially when parameters and cached plans enter the picture. Start with actual row counts by customer and the existing histogram. I check the data first because a story about skew can be repeated for years after the distribution has changed.
SELECT CustomerID, COUNT_BIG(*) AS order_count
FROM dbo.Orders
GROUP BY CustomerID
ORDER BY order_count DESC;
DBCC SHOW_STATISTICS (N'dbo.Orders', N'ST_Orders_CustomerID')
WITH HISTOGRAM;Replace the statistic name with one that exists on your table. The row-count query can be costly on a very large orders table, so run it in a controlled window or use a representative sample for initial exploration. DBCC SHOW_STATISTICS reveals range steps and density information. A histogram has limited steps, so it is not a perfect list of every customer.
Create Filtered Statistics for the Heavy Value
This kind of statistic summarizes only the rows that satisfy its WHERE clause. If CustomerID 42 is the heavy value, create one for that value and one for the rest. This gives the optimizer separate distributions to consider. The exact filter must match the real data and query logic. Use a test copy first, and make sure the statistics names do not already exist.
CREATE STATISTICS ST_Orders_Customer42
ON dbo.Orders (CustomerID)
WHERE CustomerID = 42;
CREATE STATISTICS ST_Orders_OtherCustomers
ON dbo.Orders (CustomerID)
WHERE CustomerID <> 42;These objects store statistical summaries; they do not store rows in a searchable index. If a query scans because it has no useful index, better estimates can change join and memory choices but cannot make missing access pages appear. That distinction matters when someone proposes one as a substitute for a filtered index. Sometimes both are useful, but each has a cost and a separate purpose.
Inspect Both Filtered Histograms
Use DBCC SHOW_STATISTICS on each new object. The heavy-value histogram should represent that slice, and the rest should exclude it. Read Rows and Rows Sampled as well as the histogram. A sampled statistic on a highly skewed set can still be misleading if sampling misses important values. A full scan is an option for a deliberate one-time test, but it can be expensive on a large table.
DBCC SHOW_STATISTICS (N'dbo.Orders', N'ST_Orders_Customer42')
WITH STAT_HEADER, HISTOGRAM;
DBCC SHOW_STATISTICS (N'dbo.Orders', N'ST_Orders_OtherCustomers')
WITH STAT_HEADER, HISTOGRAM;I save the output beside the query plan. A better-looking histogram is only useful if the optimizer chooses it for the statement in question. The actual plan's optimizer statistics usage can show which statistics were loaded during compilation. Compare estimated and actual rows at the first operator affected by CustomerID. That is where a bad estimate starts to change the plan.
Test Literal Queries Before Parameters
Run one query for the heavy customer and one for a small customer. Keep the projection and joins the same as the application statement. Capture actual plans and STATISTICS IO. A literal gives the optimizer a clear value that can imply the filtered predicate. A parameterized statement does not always provide that proof for every execution, especially when a reusable plan must also be valid for values outside the filter.
SELECT SUM(OrderAmount) AS total_amount
FROM dbo.Orders
WHERE CustomerID = 42
OPTION (RECOMPILE);
SELECT SUM(OrderAmount) AS total_amount
FROM dbo.Orders
WHERE CustomerID = 7
OPTION (RECOMPILE);The example assumes OrderAmount exists; replace it with a column in the real table. OPTION (RECOMPILE) isolates each literal in a test, but it is not automatically the right production choice. Compilation has a cost. More important, the production call can pass a parameter whose value changes between executions. Test that call as sent, not just a hand-written equivalent.
Why a Parameter Can Miss Filtered Statistics
For WHERE CustomerID = @CustomerID, a cached plan needs to work for values other than the one seen at compilation. The optimizer can avoid a filtered object when the predicate cannot be proven to stay inside its filter. Parameter sniffing can also reuse a plan suited to the heavy customer for a small one. Filtered statistics improve information when they are used; they do not force the right plan for every future parameter.
Try representative heavy and small values through the stored procedure. Compare Query Store plans and runtime rows. If one plan cannot serve both, consider parameter-sensitive plan features on supported versions, a deliberate branch for the exceptional value, or a targeted recompile. Do not add an index hint to hide an estimate problem. The solution should survive the next customer value, not just the test value.
Keep Filtered Statistics Fresh
AUTO_UPDATE_STATISTICS covers these objects too when enabled. Check last_updated, rows, rows_sampled, and modification_counter with sys.dm_db_stats_properties. A small slice can change rapidly even when the whole table changes slowly. Use a targeted UPDATE STATISTICS after a large load when the automatic threshold and workload timing leave the slice summary stale.
SELECT s.name, p.last_updated, p.rows,
p.rows_sampled, p.modification_counter
FROM sys.stats AS s
OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS p
WHERE s.object_id = OBJECT_ID(N'dbo.Orders')
AND s.has_filter = 1;Do not update every statistic in the database because one customer had a busy day. Refresh the object that supports the affected query, then compare estimates and plan shape. I also check whether CustomerID 42 remains the heavy value. A filtered object named for a value that no longer dominates becomes a maintenance souvenir.
Decide Whether the Plan Improved
A successful test shows the query's estimated rows moving closer to actual rows and a sensible downstream plan. Record logical reads, CPU, duration, and the statistics used. If the estimate improves but the query still scans too much, inspect indexes and predicates. If the estimate remains poor, inspect the exact filter implication and parameter behavior.
The small customer example is intentionally simple. Real workloads join orders to details, customers, and payments, where a bad estimate can choose the wrong join or memory grant. Follow the first wrong row estimate through that plan. A filtered statistic is valuable because it describes a meaningful slice, not because its name contains the word filtered.
Which values in your data deserve their own summary, and do the queries actually use it?
Related reading on this blog: Understanding Incremental Statistics and Find Oldest Updated Statistics: Outdated Statistics.
A filtered statistic is not a small-table cure, it is an estimate for a relevant data slice.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
