Advanced Indexing in SQL Server

An index is a sorted B-tree structure (like a book index) that lets SQL Server find rows WITHOUT reading the whole table.

            [Root page]
           /     |     \
   [Intermediate pages]        <- navigation
   /    |     |     |    \
[Leaf][Leaf][Leaf][Leaf][Leaf] <- data rows (clustered) or keys + pointer (nonclustered)

This file covers:

  - Reading I/O statistics and execution plans
  - Index metadata (sys.indexes, sys.index_columns)
  - Fragmentation, REBUILD vs REORGANIZE, FILLFACTOR
  - Statistics (what the optimizer knows about your data)
  - Missing index / index usage DMVs (find useful & useless indexes)
  - Index on computed column, indexed view, data compression
Details of each index TYPE are in files 26 - 29.

• TIP: In SSMS press Ctrl+M (Include Actual Execution Plan) and look at the "Messages" tab for "logical reads" after each query.

The demo data on this site is deliberately small (300 orders and 50 customers) so the script runs instantly. With so few rows, SQL Server may choose a scan where a big table would use a seek, and the logical reads are tiny. To see the real performance difference, change TOP (300) to TOP (300000) and TOP (50) to TOP (50000) in the demo data script.

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Lesson table: copy of the big orders data

DROP VIEW  IF EXISTS dbo.vw_RegionSales;
DROP TABLE IF EXISTS dbo.IdxOrders;

SELECT * INTO dbo.IdxOrders FROM dbo.BigOrdersData;
ALTER TABLE dbo.IdxOrders ADD CONSTRAINT PK_IdxOrders PRIMARY KEY CLUSTERED (OrderID);
GO

(300 rows affected)

Part A: Measure Before You Tune

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

No index on CustomerID -> Clustered Index Scan (reads every page, ~3,900 reads)

SELECT OrderID, OrderDate, Amount FROM dbo.IdxOrders WHERE CustomerID = 12;

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 6 ms.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

Table 'IdxOrders'. Scan count 1, logical reads 8, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Add an index -> Index Seek + Key Lookup (a handful of reads)

CREATE NONCLUSTERED INDEX IX_IdxOrders_CustomerID ON dbo.IdxOrders (CustomerID);
SELECT OrderID, OrderDate, Amount FROM dbo.IdxOrders WHERE CustomerID = 12;

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'IdxOrders'. Scan count 1, logical reads 8, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 2 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

Table 'IdxOrders'. Scan count 1, logical reads 8, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Seek by primary key -> 3 reads (root -> intermediate -> leaf)

SELECT * FROM dbo.IdxOrders WHERE OrderID = 150;
GO

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

Table 'IdxOrders'. Scan count 0, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Part B: Index Metadata

All indexes on a table with their key and included columns

SELECT i.name AS IndexName, i.type_desc, i.is_unique, i.is_primary_key, i.fill_factor, i.has_filter, i.filter_definition,
       STRING_AGG(CASE WHEN ic.is_included_column = 0 THEN c.name END, ', ')
           WITHIN GROUP (ORDER BY ic.key_ordinal) AS KeyColumns,
       STRING_AGG(CASE WHEN ic.is_included_column = 1 THEN c.name END, ', ') AS IncludedColumns
FROM sys.indexes i
JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN sys.columns c        ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE i.object_id = OBJECT_ID('dbo.IdxOrders')
GROUP BY i.name, i.type_desc, i.is_unique, i.is_primary_key, i.fill_factor, i.has_filter, i.filter_definition;

Size of each index

SELECT i.name AS IndexName, i.type_desc,
       SUM(ps.used_page_count) AS UsedPages,
       SUM(ps.used_page_count) * 8 / 1024.0 AS UsedMB,
       SUM(ps.row_count) AS TotalRows
FROM sys.dm_db_partition_stats ps
JOIN sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE ps.object_id = OBJECT_ID('dbo.IdxOrders')
GROUP BY i.name, i.type_desc;

Depth of the B-tree (levels) - even 300k rows need only 3 levels

SELECT index_id, index_type_desc, index_depth, page_count, record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.IdxOrders'), NULL, NULL, 'DETAILED')
WHERE index_level = 0;
GO

Part C: Fragmentation, Rebuild, Reorganize, Fillfactor

Create fragmentation: the index on Notes gets random inserts / updates

CREATE NONCLUSTERED INDEX IX_IdxOrders_Notes ON dbo.IdxOrders (Notes);

UPDATE dbo.IdxOrders
SET Notes = CONCAT('Updated note ', REVERSE(CAST(OrderID AS VARCHAR(10))))     -- changes key order randomly
WHERE OrderID % 3 = 0;
GO

(100 rows affected)

Check fragmentation

SELECT i.name AS IndexName,
       CAST(ps.avg_fragmentation_in_percent AS DECIMAL(5,2)) AS FragPct,
       ps.page_count,
       CAST(ps.avg_page_space_used_in_percent AS DECIMAL(5,2)) AS PageFullnessPct
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.IdxOrders'), NULL, NULL, 'SAMPLED') ps
JOIN sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id;

Common rule of thumb

< 5%  : do nothing
5-30% : ALTER INDEX ... REORGANIZE   (online, light, can be stopped any time)
> 30% : ALTER INDEX ... REBUILD      (recreates the index, also updates statistics)

ALTER INDEX IX_IdxOrders_Notes ON dbo.IdxOrders REORGANIZE;
ALTER INDEX IX_IdxOrders_Notes ON dbo.IdxOrders REBUILD WITH (FILLFACTOR = 90);   -- leave 10% free space per page

-- ONLINE = ON keeps the table usable during rebuild (Enterprise / Developer edition only):
-- ALTER INDEX ALL ON dbo.IdxOrders REBUILD WITH (ONLINE = ON);

Check again

SELECT i.name AS IndexName, i.fill_factor,
       CAST(ps.avg_fragmentation_in_percent AS DECIMAL(5,2)) AS FragPct, ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.IdxOrders'), NULL, NULL, 'SAMPLED') ps
JOIN sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id;

Script to generate maintenance commands for the whole database

SELECT OBJECT_NAME(ps.object_id) AS TableName, i.name AS IndexName,
       CAST(ps.avg_fragmentation_in_percent AS DECIMAL(5,2)) AS FragPct,
       CASE WHEN ps.avg_fragmentation_in_percent > 30
            THEN 'ALTER INDEX ' + QUOTENAME(i.name) + ' ON dbo.' + QUOTENAME(OBJECT_NAME(ps.object_id)) + ' REBUILD;'
            ELSE 'ALTER INDEX ' + QUOTENAME(i.name) + ' ON dbo.' + QUOTENAME(OBJECT_NAME(ps.object_id)) + ' REORGANIZE;'
       END AS MaintenanceCommand
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
JOIN sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE ps.avg_fragmentation_in_percent > 5 AND ps.page_count > 100 AND i.name IS NOT NULL;

DROP INDEX IX_IdxOrders_Notes ON dbo.IdxOrders;
GO

Part D: Statistics (the optimizer's knowledge about data distribution)

Statistics objects on the table (indexes have stats; _WA_Sys_ = auto-created column stats)

SELECT s.name AS StatName, s.auto_created, s.user_created,
       sp.last_updated, sp.rows, sp.rows_sampled, sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE s.object_id = OBJECT_ID('dbo.IdxOrders');

Look inside: header, density, histogram (max 200 steps)

DBCC SHOW_STATISTICS ('dbo.IdxOrders', IX_IdxOrders_CustomerID);

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Create stats manually and update them

CREATE STATISTICS ST_IdxOrders_Region ON dbo.IdxOrders (Region) WITH FULLSCAN;
UPDATE STATISTICS dbo.IdxOrders WITH FULLSCAN;     -- all stats on the table
-- EXEC sp_updatestats;                            -- all tables in the database

Estimated vs actual rows: compare them in the actual execution plan.

Big differences = stale or missing statistics = bad plans.

SELECT Region, COUNT(*) AS Orders FROM dbo.IdxOrders WHERE Region = 'East' GROUP BY Region;
GO

Part E: Missing Index and Index Usage DMVs

Run some "workload" queries that would like an index

SELECT COUNT(*) FROM dbo.IdxOrders WHERE ProductID = 5 AND OrderDate >= '2025-01-01';
SELECT SUM(Amount) FROM dbo.IdxOrders WHERE Region = 'North' AND Status = 'Pending';
SELECT TOP (10) OrderID, Amount FROM dbo.IdxOrders WHERE CouponCode = 'SAVE50';
GO

What SQL Server suggests (since last restart). Treat as Hints, not orders!

SELECT TOP (10)
       DB_NAME(d.database_id) AS DatabaseName,
       d.statement            AS TableName,
       d.equality_columns, d.inequality_columns, d.included_columns,
       s.user_seeks, CAST(s.avg_user_impact AS DECIMAL(5,2)) AS AvgImpactPct,
       'CREATE INDEX IX_Suggested ON ' + d.statement + ' (' +
           ISNULL(d.equality_columns, '') +
           CASE WHEN d.equality_columns IS NOT NULL AND d.inequality_columns IS NOT NULL THEN ', ' ELSE '' END +
           ISNULL(d.inequality_columns, '') + ')' +
           ISNULL(' INCLUDE (' + d.included_columns + ')', '') AS SuggestedIndex
FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g       ON g.index_handle = d.index_handle
JOIN sys.dm_db_missing_index_group_stats s  ON s.group_handle = g.index_group_handle
WHERE d.database_id = DB_ID()
ORDER BY s.user_seeks * s.avg_user_impact DESC;

How each index is Used (seeks / scans / lookups vs updates = cost)

SELECT OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName,
       ISNULL(us.user_seeks, 0)   AS Seeks,
       ISNULL(us.user_scans, 0)   AS Scans,
       ISNULL(us.user_lookups, 0) AS Lookups,
       ISNULL(us.user_updates, 0) AS Updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats us
       ON us.object_id = i.object_id AND us.index_id = i.index_id AND us.database_id = DB_ID()
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1 AND i.name IS NOT NULL
ORDER BY TableName, IndexName;
-- Index with many Updates but 0 Seeks/Scans/Lookups = only costs, consider dropping it.

Duplicate indexes (same key columns) - another common waste

CREATE INDEX IX_Dup1 ON dbo.IdxOrders (CustomerID);   -- same as IX_IdxOrders_CustomerID
WITH IndexKeys AS
(
    SELECT i.object_id, i.name,
           STRING_AGG(c.name, ',') WITHIN GROUP (ORDER BY ic.key_ordinal) AS KeyCols
    FROM sys.indexes i
    JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
    JOIN sys.columns c        ON c.object_id = ic.object_id AND c.column_id = ic.column_id
    WHERE i.object_id = OBJECT_ID('dbo.IdxOrders')
    GROUP BY i.object_id, i.name
)
SELECT a.name AS Index1, b.name AS Index2, a.KeyCols
FROM IndexKeys a JOIN IndexKeys b ON a.object_id = b.object_id AND a.KeyCols = b.KeyCols AND a.name < b.name;
DROP INDEX IX_Dup1 ON dbo.IdxOrders;
GO

Part F: Special Indexes

Index on a Computed column (makes a "function on column" searchable)

ALTER TABLE dbo.IdxOrders ADD OrderYear AS YEAR(OrderDate) PERSISTED;
GO
CREATE INDEX IX_IdxOrders_OrderYear ON dbo.IdxOrders (OrderYear);

SET STATISTICS IO ON;
SELECT COUNT(*) FROM dbo.IdxOrders WHERE OrderYear = 2024;   -- seek on the computed column index
SET STATISTICS IO OFF;
GO

Table 'IdxOrders'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Indexed View (materialized pre-aggregation). Requires Schemabinding and COUNT_BIG(*).

SUM over a NULLable column is not allowed in an indexed view -> wrap it in ISNULL().

CREATE VIEW dbo.vw_RegionSales
WITH SCHEMABINDING
AS
SELECT Region, Status, COUNT_BIG(*) AS Orders, SUM(ISNULL(Amount, 0)) AS Revenue
FROM dbo.IdxOrders
GROUP BY Region, Status;
GO
CREATE UNIQUE CLUSTERED INDEX CIX_vw_RegionSales ON dbo.vw_RegionSales (Region, Status);
GO

SET STATISTICS IO ON;
SELECT Region, Status, Orders, Revenue FROM dbo.vw_RegionSales WITH (NOEXPAND);   -- reads a few pages instead of 300k rows
SET STATISTICS IO OFF;
GO

Table 'vw_RegionSales'. Scan count 1, logical reads 2, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Data Compression (smaller indexes = fewer reads)

EXEC sp_estimate_data_compression_savings 'dbo', 'IdxOrders', NULL, NULL, 'PAGE';
ALTER INDEX PK_IdxOrders ON dbo.IdxOrders REBUILD WITH (DATA_COMPRESSION = PAGE);
EXEC sp_spaceused 'dbo.IdxOrders';
GO

Index Design Checklist

• Index columns used in WHERE, JOIN, ORDER BY, GROUP BY.
• Index foreign key columns.
• Prefer selective columns (many distinct values) as the leading key.
• Every index slows INSERT/UPDATE/DELETE and uses space - don't over-index.
• Write SARGable predicates (no functions on indexed columns).
• Keep statistics up to date; check fragmentation regularly.

PRACTICE QUESTIONS 1. Create the index suggested by E2 for the ProductID + OrderDate query and compare logical reads. 2. Find all indexes in SqlLearningDb that have never been used since restart. 3. Rebuild IX_IdxOrders_CustomerID with FILLFACTOR = 70 and compare page_count before/after. 4. Create an indexed view of monthly revenue per ProductID and query it with NOEXPAND.