Statistics, Fragmentation, and the Cost of Writes

Three maintenance topics that are routinely prioritized backwards. In order of how much they actually affect query performance: statistics, then write cost, then fragmentation — a long way behind.

Statistics matter more than fragmentation

Say this plainly, because a great deal of received wisdom disagrees.

Statistics determine the plan. A stale histogram produces a wrong row estimate, which produces the wrong join type, the wrong index choice and an undersized memory grant. The difference between a good and bad plan is commonly 100x.

Fragmentation affects how contiguous the pages are on disk. On modern storage — SSD, SAN, any cloud block storage — the penalty for non-sequential reads is small, and once pages are in the buffer pool, fragmentation is irrelevant entirely. The difference is commonly a few percent.

The reason fragmentation gets so much attention is historical (spinning disks, where seek time dominated) and procedural (it produces a tidy number that goes down when you run a job). Nightly index rebuilds on large tables are widely over-prioritized: they consume a maintenance window, generate substantial transaction log, and can push a database into a fully-logged growth spurt.

The one genuinely valuable side effect of a rebuild is that ALTER INDEX ... REBUILD updates statistics with a full scan. That is worth having — but you can get it directly, far more cheaply, with UPDATE STATISTICS.

Checking statistics health

SELECT  OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
        OBJECT_NAME(s.object_id)        AS table_name,
        s.name                          AS stats_name,
        sp.last_updated,
        sp.rows,
        sp.rows_sampled,
        CAST(100.0 * sp.rows_sampled / NULLIF(sp.rows,0) AS decimal(5,2)) AS pct_sampled,
        sp.modification_counter,
        CAST(100.0 * sp.modification_counter / NULLIF(sp.rows,0) AS decimal(9,2)) AS pct_modified,
        sp.steps
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
  AND sp.rows > 10000
ORDER BY pct_modified DESC;

What to look at:

• last_updated older than the data is fresh. Statistics from three months ago on a table that grows daily describe a table that no longer exists.
• pct_sampled — auto-update samples a fraction of large tables, sometimes under 1%. If a column's distribution is skewed, a 0.5% sample can miss it entirely. WITH FULLSCAN fixes this for the statistics that matter.
• modification_counter — rows changed since the last update.
• steps — the histogram is capped at 200 steps regardless of table size. On a fact table with five years of daily data, 200 steps means each step spans about nine days. That granularity limit is why current-day queries on a huge table often estimate badly, and why the filtered statistics from the previous lesson help.

Auto-update thresholds

SQL Server updates statistics automatically when enough rows change. The threshold depends on compatibility level:

• Compatibility level 120 and below (legacy): 500 rows plus 20% of the table. On a 50-million-row table that is 10 million modifications before anything happens. Statistics are effectively never current on a large table.
• Compatibility level 130+ (SQL Server 2016 and later): a dynamic, square-root-based threshold — roughly SQRT(1000 × rows). Far more responsive on large tables. This alone is a good reason to raise a legacy database's compatibility level, carefully and with testing.

Check where you stand:

SELECT name, compatibility_level, is_auto_update_stats_on, is_auto_update_stats_async_on
FROM sys.databases WHERE name = DB_NAME();

is_auto_update_stats_async_on = 0 (the default) means a query that triggers a stats update waits for it before compiling — an occasional unexplained pause on an otherwise fast query. Turning async on removes that stall at the cost of the triggering query using the old statistics once more.

Updating statistics

-- One statistic, full scan (targeted, what you usually want while tuning)
UPDATE STATISTICS dbo.Orders IX_Orders_Region_Status_OrderDate WITH FULLSCAN;

-- Every statistic on one table
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

-- Whole database, default sample (heavier; a maintenance-window operation)
EXEC sp_updatestats;

While diagnosing a bad plan, updating statistics on the tables involved is the cheapest experiment available. Note that it invalidates the cached plans that depend on them, so the next execution recompiles — which is usually what you want, but it does mean you cannot cleanly attribute the improvement to the statistics rather than the recompile. Test the recompile separately (OPTION (RECOMPILE)) if the distinction matters.

Fragmentation, in proportion

SELECT  OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
        OBJECT_NAME(ips.object_id)        AS table_name,
        i.name                            AS index_name,
        ips.avg_fragmentation_in_percent,
        ips.page_count,
        ips.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
JOIN sys.indexes AS i
     ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.page_count > 1000         -- below ~1000 pages it does not matter at all
ORDER BY ips.avg_fragmentation_in_percent DESC;

A defensible policy: ignore indexes under 1,000 pages entirely; reorganize between roughly 10% and 30%; rebuild above 30% if you have a maintenance window and the index is large enough to matter. avg_page_space_used_in_percent is arguably the more useful column — low page density means you are reading more pages than the data requires, which does cost real I/O.

Do not schedule nightly rebuilds of everything. Do schedule statistics updates.

The write cost of indexes

Every nonclustered index is a copy of a slice of the table that must be maintained. An INSERT writes to the clustered index plus every nonclustered index. An UPDATE touching an indexed column rewrites those index entries. A DELETE removes from all of them.

Ten indexes on a table means an insert does eleven write operations. On an OLTP table that a report also reads, adding indexes to speed the report directly slows the transactional workload — and that trade-off is usually invisible until someone complains about a different thing entirely.

Find the ones you are paying for and not using:

SELECT  OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
        OBJECT_NAME(i.object_id)        AS table_name,
        i.name                          AS index_name,
        ISNULL(s.user_seeks,0)   AS seeks,
        ISNULL(s.user_scans,0)   AS scans,
        ISNULL(s.user_lookups,0) AS lookups,
        ISNULL(s.user_updates,0) AS writes,
        (SELECT SUM(p.rows) FROM sys.partitions p
          WHERE p.object_id = i.object_id AND p.index_id = i.index_id) AS rows,
        (SELECT sqlserver_start_time FROM sys.dm_os_sys_info) AS stats_since
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS s
       ON s.object_id = i.object_id AND s.index_id = i.index_id AND s.database_id = DB_ID()
WHERE i.type_desc = 'NONCLUSTERED'
  AND OBJECTPROPERTY(i.object_id,'IsUserTable') = 1
  AND ISNULL(s.user_seeks,0) + ISNULL(s.user_scans,0) + ISNULL(s.user_lookups,0) = 0
ORDER BY ISNULL(s.user_updates,0) DESC;

Before dropping anything from that list: confirm the instance has been up long enough to be representative, check that the index does not enforce a constraint, and consider that it may serve a month-end or year-end report that has not run during the sampled period. Disabling an index (ALTER INDEX ... DISABLE) is a reversible middle step — the definition remains, so you can rebuild it if something breaks.

Rule of thumb

Statistics drive the plan; fragmentation mostly does not matter on modern storage once pages are in the buffer pool. So prioritize statistics updates over index rebuilds — the main benefit of a rebuild is the full-scan statistics update it does as a side effect, and you can get that directly for far less cost. Check the histogram sample rate too, because auto-update on a big table can sample under one percent and miss a skew completely.