Statistics Histograms, Density, Auto Create/Update Stats, Incremental Stats, and Sampling

Learning outcomes

The optimizer cannot inspect every row during every compilation. It relies heavily on statistics: compact summaries of data distribution. ServiceHub's skewed regions and customers make a useful laboratory because a histogram can describe the leading statistics column in detail while density information summarizes distinctness and cross-column combinations more coarsely. This lesson connects those structures to estimates without promoting “FULLSCAN everything” as maintenance policy.

Lab bootstrap for an independently runnable lesson

If the Chapter 10 table is absent, this setup creates it without touching the permanent ops schema.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab10') IS NULL EXEC(N'CREATE SCHEMA lab10 AUTHORIZATION dbo;');IF OBJECT_ID(N'lab10.WorkOrderFact',N'U') IS NULLBEGIN  CREATE TABLE lab10.WorkOrderFact  (    work_order_id bigint IDENTITY(1,1) NOT NULL,    customer_id int NOT NULL,    region_code char(3) NOT NULL,    status varchar(12) NOT NULL,    priority tinyint NOT NULL,    opened_at datetime2(0) NOT NULL,    closed_at datetime2(0) NULL,    amount decimal(12,2) NOT NULL,    notes varchar(200) NULL,    CONSTRAINT PK_lab10_WorkOrderFact PRIMARY KEY CLUSTERED(work_order_id)  );  ;WITH n AS  (    SELECT TOP (30000)           ROW_NUMBER() OVER (ORDER BY a.object_id,b.object_id) AS n    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b  )  INSERT lab10.WorkOrderFact(customer_id,region_code,status,priority,opened_at,closed_at,amount,notes)  SELECT CASE WHEN n <= 18000 THEN 1 ELSE 2 + n % 1999 END,         CASE WHEN n <= 21000 THEN 'N01' WHEN n <= 27000 THEN 'W02' ELSE 'E03' END,         CASE WHEN n % 20 = 0 THEN 'ESCALATED' WHEN n % 5 = 0 THEN 'CLOSED' ELSE 'OPEN' END,         CASE WHEN n <= 21000 THEN 1 ELSE 5 END,         DATEADD(minute,n,'2026-01-01T00:00:00'),         CASE WHEN n % 5=0 THEN DATEADD(minute,n+90,'2026-01-01T00:00:00') END,         CAST(20 + (n % 5000) / 10.0 AS decimal(12,2)),         CASE WHEN n % 37=0 THEN REPLICATE('x',160) END  FROM n;  CREATE INDEX IX_lab10_RegionStatusOpened    ON lab10.WorkOrderFact(region_code,status,opened_at)    INCLUDE(customer_id,priority,amount);  CREATE INDEX IX_lab10_Customer    ON lab10.WorkOrderFact(customer_id)    INCLUDE(region_code,status,opened_at,amount);END;GO

1. Statistics summarize distributions; they are not indexes

An index normally creates associated statistics on its key columns, and SQL Server can auto-create single-column statistics for predicates when AUTO_CREATE_STATISTICS is enabled. A statistics object contains metadata, a histogram on the first statistics key, and density information that can describe distinctness for key prefixes. It does not provide a physical access path by itself.

USE ServiceHubLab;GOSELECT OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,       OBJECT_NAME(s.object_id) AS table_name,       s.stats_id,s.name,s.auto_created,s.user_created,s.has_filter,       sp.last_updated,sp.rows,sp.rows_sampled,sp.steps,sp.modification_counterFROM sys.stats AS sOUTER APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) AS spWHERE s.object_id=OBJECT_ID(N'lab10.WorkOrderFact')ORDER BY s.stats_id;GO

last_updated can be NULL when the statistics blob has not yet been populated. The modification counter is useful context but not a universal “update now” threshold independent of table size, workload, and automatic update behavior.

2. Read the histogram and density vector directly

DBCC SHOW_STATISTICS exposes STAT_HEADER, DENSITY_VECTOR, and HISTOGRAM. The histogram has a maximum of 200 steps and describes only the first key column. For the index (region_code,status,opened_at), the histogram is on region_code; later key columns do not get their own histogram within the same statistics object.

DBCC SHOW_STATISTICS  (N'lab10.WorkOrderFact',N'IX_lab10_RegionStatusOpened')WITH STAT_HEADER,DENSITY_VECTOR,HISTOGRAM;GO

Read Rows and Rows Sampled before assuming the histogram is a full census. Histogram columns such as RANGE_HI_KEY, EQ_ROWS, RANGE_ROWS and DISTINCT_RANGE_ROWS describe sampled/derived distribution information that the cardinality estimator can use. Density is not “selectivity of the index”; it is a statistics measure used in estimation.

3. Sampling is a maintenance tradeoff, not an accuracy moral judgment

Automatic statistics updates usually sample data. A full scan can improve representation for some distributions, but it also reads more data and can consume more CPU/I/O and maintenance time. Updating statistics also invalidates/recompiles affected plans, so indiscriminate high-frequency updates can create compilation churn. The right question is whether current estimates for important workload shapes are materially wrong and whether a different sampling strategy improves them.

IF EXISTS (SELECT 1 FROM sys.stats WHERE object_id=OBJECT_ID(N'lab10.WorkOrderFact')  AND name=N'ST_lab10_CustomerStatus')  DROP STATISTICS lab10.WorkOrderFact.ST_lab10_CustomerStatus;GOCREATE STATISTICS ST_lab10_CustomerStatusON lab10.WorkOrderFact(customer_id,status);GODBCC SHOW_STATISTICS (N'lab10.WorkOrderFact',N'ST_lab10_CustomerStatus')WITH STAT_HEADER,DENSITY_VECTOR,HISTOGRAM;GO-- Optional measured comparison in this disposable lab only:UPDATE STATISTICS lab10.WorkOrderFact ST_lab10_CustomerStatus WITH FULLSCAN;GODBCC SHOW_STATISTICS (N'lab10.WorkOrderFact',N'ST_lab10_CustomerStatus')WITH STAT_HEADER;GO

The optional FULLSCAN is a controlled experiment, not the chapter's recommended global maintenance policy. Record time, reads, rows sampled, estimate quality, and subsequent recompiles before adopting a production strategy.

4. Automatic creation/update can be synchronous or asynchronous

With AUTO_UPDATE_STATISTICS_ASYNC=OFF—the default—an eligible compilation can wait while stale statistics are updated synchronously. With asynchronous updates enabled, the compiling query can proceed with existing statistics while a background request refreshes them for later compilations. This trades first-query wait time against the possibility that one or more compilations continue using older statistics.

SELECT name,compatibility_level,is_auto_create_stats_on,       is_auto_update_stats_on,is_auto_update_stats_async_onFROM sys.databasesWHERE database_id=DB_ID();GOSELECT name,value,value_for_secondaryFROM sys.database_scoped_configurationsWHERE name LIKE '%STATS%';GO

5. Incremental statistics are a partition-aware maintenance tool

On partitioned tables, incremental statistics can maintain per-partition statistics and merge them into global statistics, so a partition-focused maintenance operation need not rescan all historical partitions. They are not a way to make an unpartitioned table's histogram more detailed, and not every statistics/index arrangement supports them.

SELECT s.name,s.is_incremental,s.has_persisted_sample,       sp.last_updated,sp.rows,sp.rows_sampled,sp.stepsFROM sys.stats AS sOUTER APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) AS spWHERE s.object_id=OBJECT_ID(N'lab10.WorkOrderFact');GO-- On an appropriately partitioned table, an example is:-- CREATE STATISTICS ST_Orders_EventTime-- ON dbo.PartitionedOrders(event_time)-- WITH INCREMENTAL = ON;-- Then inspect per-partition properties with-- sys.dm_db_incremental_stats_properties.GO

The course does not create a production-style partitioning scheme merely to tick a syntax box. Chapter 19 and later operational work can revisit partitioning with a workload that actually benefits from it.

6. Wrong approach: update every statistic FULLSCAN every hour

That policy ignores table size, change rate, workload sensitivity, maintenance windows, compilation effects, and sampling quality. A better workflow is: find an estimate problem in a real plan; identify the statistics the optimizer loaded; inspect freshness/distribution; reproduce; update the smallest relevant statistics object with a measured method; then verify estimates and runtime. If no estimate problem exists, more maintenance can simply be more work.

Authoritative references

• Statistics — automatic creation/update and async behavior
• DBCC SHOW_STATISTICS — header, density vector and histogram
• Update statistics — maintenance/recompilation tradeoffs
• sys.dm_db_stats_properties — freshness and modification metadata
• SQL Server 2025 build versions — CU/build servicing baseline
• SSMS 22 release notes — current client-tool baseline