powershelldba.de

Index Optimization and Statistics Update: Pros and Cons

Every DBA has been told: "Rebuild your indexes and update statistics weekly." But nobody explains the real cost versus benefit. Is that weekly job actually helping, or is it wasting CPU and disk I/O? The answer depends on your data and your environment.

Index Fragmentation: The Root Problem

What Is Fragmentation?

In a fresh, defragmented index, pages are stored in logical order on disk. Query performance is optimal: sequential I/O, minimal disk reads.

As data changes (INSERT, UPDATE, DELETE), pages split, become fragmented, and scatter across the disk. Now the same query requires more I/O, more CPU, and more memory.

Visualized

Ideal Index (0% fragmented):
Pages: [1] [2] [3] [4] [5] ... (sequential, contiguous)
Range scan: Fast! Read 1→2→3→4→5 in sequence.

Fragmented Index (80% fragmented):
Pages: [1] [X] [2] [X] [3] [X] [4] [X] [5] ...
(X = other data/free space)
Range scan: Slow! Read 1, jump to [X], read 2, jump to [X], etc.
Many random I/O operations instead of sequential.

Index Rebuild vs. Reorganize

Aspect REBUILD REORGANIZE
Fragmentation Fix 0% (perfect defrag) ~50-70% reduction
Time Slow (hours for huge indexes) Fast (minutes for most)
CPU Impact High Low
Disk I/O High Medium
Lock Duration Long (read table is locked) Brief (row-level locks only)
Transaction Log Minimal (minimal logging mode) Heavy (logged in detail)
Online Can be online (SQL 2012+) Always online
Best Used >30% fragmentation 10-30% fragmentation

When to Use Each

-- Check current fragmentation
SELECT
  object_name(ips.object_id) AS table_name,
  i.name AS index_name,
  ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i
  ON ips.object_id = i.object_id
  AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- Fragmentation < 10%: Do nothing (maintenance cost > benefit)
-- Fragmentation 10-30%: REORGANIZE (quick, online)
-- Fragmentation > 30%: REBUILD (worth the time investment)

Example: When Rebuild Pays Off

-- Large clustered index on frequently scanned table
-- Fragmentation: 85%

-- Rebuild will:
-- • Reduce fragmentation to 0%
-- • Cut I/O operations by ~85%
-- • Reduce page reads from 10,000 to ~1,500
-- • Query performance improves by 5-10x

-- Cost:
-- • 2 hours of rebuild time
-- • High CPU/disk during rebuild
// • But database remains available (if online rebuild used)

-- ROI: Massive! 2 hours of work for weeks of improved performance

Fragmentation Thresholds: Microsoft Recommendation

-- Microsoft's guidance (often cited, rarely questioned):

IF fragmentation < 10%
  -- Do nothing. Cost of maintenance > benefit.
  -- Random I/O overhead is negligible at low fragmentation.

IF fragmentation BETWEEN 10% AND 30%
  -- REORGANIZE recommended
  -- Quick operation, minimal locking, reasonable benefit

IF fragmentation > 30%
  -- REBUILD recommended
  -- High fragmentation severely impacts performance
  -- Rebuild time investment justified

-- BUT: This assumes your workload is I/O-bound.
-- If CPU is your bottleneck, index optimization may not help.

Statistics: The Other Side of the Coin

What Are Statistics?

Statistics are metadata about column data distribution. The query optimizer uses them to estimate row counts and choose execution plans.

Example statistic for column OrderDate:
  MIN value: 2010-01-01
  MAX value: 2024-12-31
  DENSITY: 0.0001 (how unique the values are)
  HISTOGRAM: [distribution of values by range]

Optimizer uses this to estimate:
  "WHERE OrderDate > '2024-01-01'" → ~150,000 rows
  (helps choose index scan vs. table scan)

If statistics are stale:
  Optimizer thinks: 150,000 rows
  Reality: 50,000,000 rows
  Result: Bad execution plan, slow query

AUTO_UPDATE_STATISTICS (Default ON)

-- SQL Server automatically updates statistics when:
-- • A significant percentage of rows change (default: 20% for 1000-row table)
-- • Triggered asynchronously in the background

SET STATISTICS ON;  -- This is default and recommended

-- Pros:
-- ✓ Automatic (no manual maintenance)
-- ✓ Statistics stay reasonably fresh
-- ✓ Minimal overhead (background job)

-- Cons:
-- ✗ Occasional delay (async update means old plan used first)
-- ✗ Not ideal for real-time oltp (momentary stale stats)
-- ✗ Can't control frequency (all or nothing)

AUTO_UPDATE_STATISTICS_ASYNC (SQL 2005+)

-- Deferred update: Update statistics in background
-- Query uses old statistics, then triggers async refresh

SET AUTO_UPDATE_STATISTICS_ASYNC ON;

-- Pros:
-- ✓ Query doesn't wait for statistics update
-- ✓ Improves query latency

-- Cons:
-- ✗ First query after statistics become stale uses bad plan
-- ✗ Requires async update task to be running

-- Use this if: You prioritize latency over perfect plans

Manual Statistics Update

-- Update statistics on specific table
UPDATE STATISTICS dbo.Orders;

-- Update specific statistic
UPDATE STATISTICS dbo.Orders IX_Orders_OrderDate;

-- Full rescan (more accurate, slower)
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

-- Sample (faster, slightly less accurate)
UPDATE STATISTICS dbo.Orders WITH SAMPLE 50 PERCENT;

-- Pros of manual:
-- ✓ Control frequency (weekly, daily, etc.)
-- ✓ Can use FULLSCAN for critical tables
// ✓ Predictable (scheduled maintenance)

-- Cons:
// ✗ Must remember to run
// ✗ Overhead (CPU, I/O during update)
// ✗ FULLSCAN can take hours on huge tables

Real-World Impact Analysis

Scenario 1: OLTP System (Small-Medium)

-- 10 databases, ~500 tables total
-- Typical row count: 10K - 1M
-- Workload: Many small queries, few large scans

Current maintenance: Weekly REBUILD all indexes

Analysis:
  • Most indexes 5-15% fragmented (not critical)
  • Rebuild jobs take 2 hours Sunday night
  • Queries don't notably improve Monday (fragmentation wasn't the bottleneck)

Recommendation: SWITCH to reorganize-only
  • Reorganize indexes > 20% fragmentation (rare)
  • Most weeks: nothing to do (5-15% is fine)
  • Saved: 1.5 hours weekly maintenance
  • Result: Same performance, less work

Statistics: AUTO_UPDATE_STATISTICS is sufficient
  • Small to medium tables benefit from auto-update
  • No need for weekly manual updates

Scenario 2: Data Warehouse (Massive)

-- 50 billion rows across fact/dimension tables
-- Workload: Large OLAP queries, complex joins
-- ETL runs nightly (massive data changes)

Current maintenance: Manual REBUILD all indexes nightly
  (100+ hours to complete)

Analysis:
  • Fact table rebuilt nightly: 80+ hours
  • Dimension tables: stable, low fragmentation
  • Queries are I/O-bound (large scans)

Problem: Rebuild takes longer than ETL!
  • Cannot rebuild during business hours
  • Must choose: Fast ETL or fresh indexes (can't have both)

Recommendation: Partition-based maintenance
  • Only rebuild partitions modified during ETL
  • Skip stable partitions (dimension tables)
  • Use CTAS (create-table-as-select) instead of rebuild
  • Can drop old partitions instantly

Statistics: Manual FULLSCAN after ETL
  • FULLSCAN is critical for correct query plans
  • Run after ETL completes (data distribution changed)
  • Async update insufficient (plan quality matters)
  • Cost: 30 minutes statistics scan >> queries running slow for 8 hours

The Hidden Costs

CPU & Disk I/O

-- Index rebuild on 500GB table:
-- • 4+ hours
-- • CPU: 100% on 4-core server (other queries starved)
-- • Disk I/O: Saturated (reads + writes)

-- During this time:
// • User queries slow down
// • Other maintenance blocked (backups, integrity checks)
// • If online rebuild: Tempdb grows to 3x index size (overhead)

Tempdb Pressure

-- Online rebuild uses tempdb:
// • Allocates 2-3x the index size
// • If tempdb is small or slow, rebuild crawls
// • Can cause "tempdb full" errors

-- Solution:
// • Pre-allocate tempdb (or use multiple tempdb files)
// • Schedule rebuilds when tempdb least congested

Backup Window Impact

-- After index rebuild, LSN (log sequence number) changes
// • Next backup cannot resume from prior backup
// • Full backup required (not incremental)
// • Backup storage costs increase

// Better: Schedule rebuilds after backups (not before)

Optimization Strategy by Workload

Workload Type Index Strategy Statistics Strategy Maintenance Window
OLTP Reorganize > 20%, ignore < 20% AUTO_UPDATE_STATISTICS (default) As-needed (no fixed schedule)
OLAP Rebuild partitions post-ETL FULLSCAN after ETL After ETL window
Mixed Rebuild critical indexes, reorganize others AUTO_UPDATE + manual critical stats Off-peak (weekends)
Always-On Online rebuild only (never block) AUTO_UPDATE_STATISTICS_ASYNC Continuous (spread over time)

Monitoring & Decision Framework

-- Script to identify real problems (not just fragmentation)
SELECT
  OBJECT_NAME(ips.object_id) AS table_name,
  i.name AS index_name,
  ips.avg_fragmentation_in_percent,
  ius.user_seeks + ius.user_scans AS scan_count,
  ius.user_updates AS update_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
INNER JOIN sys.indexes i
  ON ips.object_id = i.object_id AND ips.index_id = i.index_id
LEFT JOIN sys.dm_db_index_usage_stats ius
  ON ips.object_id = ius.object_id
  AND ips.index_id = ius.index_id
WHERE ips.avg_fragmentation_in_percent > 20
  AND (ius.user_seeks + ius.user_scans) > 1000  -- Frequently used
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- Rebuild only indexes that are:
// 1. Fragmented > 20%
// 2. Actually used (scans > 1000)
// 3. Large enough to matter (affects performance)

Best Practices Checklist

The Bottom Line

Index fragmentation and stale statistics ARE problems—but only for certain queries on certain indexes. The cost of maintenance (CPU, disk I/O, tempdb pressure, backup impact) must be weighed against the performance gain.

Default AUTO_UPDATE_STATISTICS is usually sufficient. Fragmentation < 10% is harmless. And blanket weekly rebuilds of all indexes is maintenance theater, not effective database administration.

Measure your actual workload. Identify which indexes matter. Apply maintenance strategically. Done right, you'll improve performance while reducing maintenance overhead.

← Back to Blog