Indexing Strategy, Statistics & Maintenance

Creating an index takes one line. Choosing the right indexes — and keeping them healthy as the IPL database grows season after season — is a continuous job. This topic is the "how to run it in real life" part of indexing.

OLTP vs OLAP — the workload decides the index

OLTP (Online Transaction Processing) workloads run many small INSERT / UPDATE statements and read single records — e.g. orders, banking, bookings, a fantasy-cricket app saving every team change.

OLAP (Online Analytical Processing) workloads run few, huge SELECTs that scan and aggregate millions of rows — e.g. data warehouses and BI reports on 15 seasons of ball-by-ball data.

Indexing strategy — 4 phases

Phases 1–3 decide which indexes exist; phase 4 runs forever.

Statistics

Statistics are small objects that describe the distribution of values in a column (a histogram of up to 200 steps, plus density). The optimizer uses them to estimate how many rows each step of a plan will return, and therefore to choose between a seek and a scan, a nested loop and a hash join, and so on.

If statistics are stale (the data changed a lot since they were built), the optimizer's estimates are wrong and it can pick a terrible plan. SQL Server auto-creates and auto-updates statistics, but on big tables the auto-update can lag behind.

-- Refresh all statistics on one table
UPDATE STATISTICS players;

-- Refresh one index's statistics, reading every row (most accurate)
UPDATE STATISTICS players IX_players_name WITH FULLSCAN;

-- Refresh statistics for the whole database
EXEC sp_updatestats;

-- Look at the histogram the optimizer uses
DBCC SHOW_STATISTICS ('players', 'IX_players_name');

Monitor index usage — find unused indexes

SELECT
    OBJECT_NAME(i.object_id) AS table_name,
    i.name                   AS index_name,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
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 OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1;

An index with 0 seeks / scans / lookups but many user_updates costs write time and gives nothing back → candidate to drop. (These counters reset when SQL Server restarts, so look over a long period.)

Missing and duplicate indexes

• Missing indexes — SQL Server records indexes it wished it had in sys.dm_db_missing_index_details (with _groups / _group_stats for impact). Treat them as suggestions, not orders — they often overlap.
• Duplicate indexes — two indexes with the same key columns in the same order (e.g. IX_team and IX_players_team both on (team)) double the write cost for no benefit. Keep one.

DROP INDEX IX_unused ON players;

Fragmentation

Fragmentation means the logical order of an index's pages no longer matches their physical order on disk, and/or pages are half empty — caused by page splits when rows are inserted into full pages or updated to a larger size. Fragmented indexes need more page reads.

-- How fragmented are the indexes on players?
SELECT
    i.name AS index_name,
    ps.avg_fragmentation_in_percent,
    ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('players'), NULL, NULL, 'LIMITED') AS ps
INNER JOIN sys.indexes AS i
    ON i.object_id = ps.object_id
   AND i.index_id  = ps.index_id;

-- Light fix: re-order leaf pages in place (online, can be stopped)
ALTER INDEX IX_players_name ON players REORGANIZE;

-- Full fix: rebuild the index from scratch (also refreshes its statistics)
ALTER INDEX ALL ON players REBUILD;

Example — see indexes and statistics (playground)

SQLite equivalents of SQL Server's catalog views and UPDATE STATISTICS: sqlite_master lists indexes and ANALYZE builds statistics.

CREATE INDEX IX_players_team ON players (team);

SELECT name AS index_name, tbl_name AS table_name, sql
FROM sqlite_master
WHERE type = 'index'
  AND tbl_name = 'players';

sqlite_autoindex_players_1 was created automatically by the PRIMARY KEY — just like SQL Server creates an index for every primary key and unique constraint.

Common mistakes

• ❌ Adding every index the "missing index" DMV suggests. ✅ Combine overlapping suggestions and test each one.
• ❌ Never updating statistics on a fast-growing table. ✅ Schedule UPDATE STATISTICS (e.g. weekly) — see tip #28.
• ❌ Rebuilding every index every night "just in case". ✅ Check fragmentation and page count first; tiny indexes don't matter.

Interview tip

Q: Your query was fast last month and is slow today, with no code change. What do you check? A: The execution plan — the plan may have changed because statistics are stale (data grew), so the optimizer misestimates rows. Update statistics, check for fragmentation, and check whether an index was dropped or has become unused.