Database Maintenance: Indexes, Statistics, and Integrity

Keeping databases healthy

Index maintenance

Fragmentation levels and actions

Fragmentation %	Action	T-SQL	Impact
Under 5%	None	—	Negligible
5% – 30%	Reorganise	ALTER INDEX ... REORGANIZE	Online, minimal impact
Over 30%	Rebuild	ALTER INDEX ... REBUILD	Can be online (Enterprise) or offline

-- Check fragmentation levels
SELECT
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.page_count > 1000  -- skip tiny tables
ORDER BY ips.avg_fragmentation_in_percent DESC;

-- Reorganise (online, minimal lock)
ALTER INDEX IX_Orders_Date ON dbo.Orders REORGANIZE;

-- Rebuild (offline by default, online with Enterprise)
ALTER INDEX IX_Orders_Date ON dbo.Orders REBUILD WITH (ONLINE = ON);

-- Rebuild all indexes on a table
ALTER INDEX ALL ON dbo.Orders REBUILD;

Reorganise vs rebuild

Reorganise vs Rebuild

Aspect	Reorganise	Rebuild
When to use	5-30% fragmentation	Over 30% fragmentation
Online?	Always online	Offline by default (online = Enterprise)
Locking	Minimal (row-level)	Heavy (table lock if offline)
Statistics	NOT updated	Automatically updates statistics
Resumable	No	Yes (RESUMABLE = ON in SQL 2017+)
Space needed	Minimal	Needs extra space for new copy

ℹ️ Azure SQL Database and index maintenance

Azure SQL Database handles some index maintenance automatically, but not all:

• Automatic tuning can create and drop indexes based on workload patterns
• You should still monitor fragmentation and schedule maintenance for critical indexes
• All Azure SQL Database tiers support online index rebuild (no Enterprise requirement like on-prem)
• Use resumable index rebuilds for large indexes — if interrupted, resume without starting over

Statistics maintenance

Statistics tell the query optimizer how data is distributed in tables and indexes. Inaccurate statistics lead to bad execution plans.

Auto-update statistics

• Enabled by default on all platforms
• Triggers when ~20% of rows change (with modifications to this threshold for large tables)
• Happens asynchronously by default on Azure SQL Database

Manual statistics updates

-- Update statistics for a specific table
UPDATE STATISTICS dbo.Orders;

-- Update with full scan (most accurate, most expensive)
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

-- Update a specific statistics object
UPDATE STATISTICS dbo.Orders IX_Orders_Date;

-- Check when statistics were last updated
SELECT
    OBJECT_NAME(s.object_id) AS table_name,
    s.name AS stats_name,
    sp.last_updated,
    sp.rows,
    sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE sp.modification_counter > 0
ORDER BY sp.modification_counter DESC;

When to update manually

• After large data loads (bulk import, ETL)
• Before running critical reports
• When Query Store shows plan regressions correlating with stale stats
• For tables with skewed data distribution

Integrity checks (DBCC CHECKDB)

DBCC CHECKDB verifies the physical and logical integrity of every object in the database.

-- Full integrity check
DBCC CHECKDB ('NorthStarERP') WITH NO_INFOMSGS;

-- Check a specific table
DBCC CHECKTABLE ('dbo.Orders') WITH NO_INFOMSGS;

-- Check allocation consistency only (faster)
DBCC CHECKALLOC ('NorthStarERP') WITH NO_INFOMSGS;

-- Check catalog consistency
DBCC CHECKCATALOG ('NorthStarERP') WITH NO_INFOMSGS;

DBCC CHECKDB on Azure SQL

Platform	Support	Notes
SQL Database	DBCC CHECKDB runs but Microsoft handles integrity checks automatically	You can run it manually for verification
Managed Instance	Full DBCC support	Schedule via SQL Agent jobs
SQL on VMs	Full DBCC support	Must schedule yourself (Agent job or maintenance plan)

💡 What to do when CHECKDB finds errors

If DBCC CHECKDB reports corruption:

1. Don’t panic — check if the error is on a data page or index page
2. Index corruption: Rebuild the index (ALTER INDEX ... REBUILD)
3. Data page corruption: Restore the page from backup (RESTORE DATABASE ... PAGE)
4. Last resort: DBCC CHECKDB ... WITH REPAIR_ALLOW_DATA_LOSS — fixes corruption but may lose data
5. Always restore from backup if possible — repair is the last option

On Azure SQL DB, Microsoft monitors integrity. On MI and VMs, schedule CHECKDB weekly.

Automatic tuning

Azure SQL Database includes automatic tuning capabilities:

Feature	What It Does	Default
Force last good plan	Automatically forces a known-good plan when a regression is detected	ON
Create index	Creates indexes recommended by the workload analysis	OFF
Drop index	Drops unused indexes identified by workload analysis	OFF

-- Check automatic tuning status
SELECT name, desired_state_desc, actual_state_desc
FROM sys.database_automatic_tuning_options;

-- Enable automatic index management
ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (
    FORCE_LAST_GOOD_PLAN = ON,
    CREATE_INDEX = ON,
    DROP_INDEX = ON
);

Question

At what fragmentation level should you reorganise vs rebuild an index?

Click or press Enter to reveal answer

Answer

5-30%: Reorganise (online, minimal impact). Over 30%: Rebuild (heavier, but more thorough). Under 5%: Do nothing.

Click to flip back

Question

Does ALTER INDEX REORGANIZE update statistics?

Click or press Enter to reveal answer

Answer

No. Reorganise does NOT update statistics. Rebuild does update statistics automatically. After reorganising, you may need to manually UPDATE STATISTICS.

Click to flip back

Question

What does DBCC CHECKDB verify?

Click or press Enter to reveal answer

Answer

Physical and logical integrity of every object in the database — pages, indexes, allocation structures, and catalog metadata. Reports any corruption found.

Click to flip back

Knowledge Check

Kenji notices that a critical query's execution plan changed after a bulk data load, and performance dropped significantly. Statistics show modification_counter is very high. What should he do?

ARebuild all indexes on the tableBUPDATE STATISTICS on the affected table WITH FULLSCANCEnable automatic tuning to create new indexesDIncrease the database vCores

Check Answer

Knowledge Check

Tomas finds that an index on the Orders table has 45% fragmentation and 2 million pages. The table is in constant use by OLTP workloads. What should he do?

AALTER INDEX ... REORGANIZEBALTER INDEX ... REBUILD (offline)CALTER INDEX ... REBUILD WITH (ONLINE = ON, RESUMABLE = ON)DDrop and recreate the index

Check Answer

🎬 Video coming soon

Next up: Automatic Tuning and Performance Settings — configure server settings, Resource Governor, database-scoped configuration, and intelligent query processing.