Day 23: Indexing strategies

A. Lesson overview

Activity breakdown

Business scenario. Day 22’s baseline showed four queries reading a million rows to return a handful. Today you add the indexes that let SQL Server go straight to the rows — and learn to stop before every query has its own index.

B. Theory explained clearly

Part 1 — What an index looks like

A rowstore index is a B-tree of 8 KB pages:

                 [ root page ]                  ← one page
               /       |       \
   [intermediate]  [intermediate]  …            ← a few pages
     /    |    \
 [leaf][leaf][leaf] …                           ← most pages; sorted by the index key

A seek starts at the root and follows pointers down to the leaf page holding the key — 3 or 4 page reads even for millions of rows. A range seek then reads leaf pages in order until the range ends. A scan reads all the leaf pages.

Part 2 — Clustered, nonclustered, lookups and covering

When a nonclustered index finds the rows but lacks some columns the query needs, SQL Server performs a Key Lookup into the clustered index for each row. Ten lookups are cheap; fifty thousand are not. At some point (the tipping point), the optimiser prefers to scan the whole table instead.

A covering index contains every column the query uses, so no lookups are needed:

CREATE INDEX IX_PerfSalesOrder_CustomerID
    ON Perf.SalesOrder (CustomerID)                     -- key: used to seek and sort
    INCLUDE (OrderDate, OrderStatus);                   -- leaf only: just carried along

INCLUDE columns are stored only at the leaf level: they make the index cover more queries without widening the key.

Part 3 — Designing the key

• Equality first, then range. For WHERE CustomerID = @c AND OrderDate >= @d, the key (CustomerID, OrderDate) seeks to one customer and reads only their recent orders. (OrderDate, CustomerID) must read every order since @d, checking the customer on each.

• The leading column matters most. An index on (A, B) supports searches on A, or on A and B — not efficiently on B alone.

• Selectivity. An index on a column with few distinct values (OrderStatus) rarely helps — unless you want the rare value. That’s what filtered indexes are for:

  CREATE INDEX IX_PerfSalesOrder_Pending ON Perf.SalesOrder (OrderDate, OrderID)
      INCLUDE (CustomerID) WHERE OrderStatus = 'Pending';

  A filtered index is small and cheap to maintain. The query’s WHERE must clearly imply the filter — a literal 'Pending' works; a parameter may not (Day 24).

• Unique indexes enforce a rule and tell the optimiser at most one row matches.

• Sort order: an index in the order of ORDER BY removes the Sort operator.

Part 4 — Columnstore indexes

A columnstore stores each column separately, compressed, in row groups of up to about a million rows. Queries that aggregate a few columns over many rows read far less data, and run in batch mode (thousands of rows at a time). They are poor at single-row lookups. A nonclustered columnstore index can be added to a normal rowstore table for analytics:

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_PerfSalesOrderLine
    ON Perf.SalesOrderLine (OrderID, ProductID, Quantity, UnitPrice, DiscountPct);

STATISTICS IO reports columnstore reads as lob logical reads and segment reads/skipped.

Part 5 — The cost of indexes, and maintenance

Every index must be updated by every INSERT, by DELETE, and by UPDATEs of its columns; it uses disk and memory, and needs maintenance. Practical guidance:

• Index for your important, frequent queries; combine where one index can serve several.
• Review with sys.dm_db_index_usage_stats (seeks/scans/lookups vs updates since the last restart) and drop indexes nobody reads.
• The missing index DMVs and plan hints are per-query suggestions: they ignore existing indexes you could extend, never suggest filtered or columnstore indexes, and often suggest overlapping ones. Use them as clues.

Fragmentation. As rows are inserted out of key order, pages split and the logical order of pages diverges from the physical (fragmentation), and pages are left partly empty (page density). Check with sys.dm_db_index_physical_stats:

Microsoft’s current guidance is to base maintenance on measured benefit rather than fixed thresholds; on SSD storage, low page density usually matters more than fragmentation, and keeping statistics up to date usually matters more than either.

C. Dataset and starting state

Starting state. Day 22’s end state: the Perf data with clustered primary keys only, and your five Day 22 rows in Lab.PerfBaseline. If you skipped Day 22, run script 05 and create Lab.PerfBaseline from Day 22 section C.

Validation. Expected: 1000000, 0, 5.

SELECT  (SELECT COUNT(*) FROM Perf.SalesOrder)                         AS Orders,
        (SELECT COUNT(*) FROM sys.indexes
         WHERE object_id IN (OBJECT_ID('Perf.SalesOrder'), OBJECT_ID('Perf.SalesOrderLine'), OBJECT_ID('Perf.Customer'))
           AND index_id > 1)                                          AS NonclusteredIndexes,
        (SELECT COUNT(*) FROM Lab.PerfBaseline WHERE LessonDay = 22)  AS BaselineRows;

(NonclusteredIndexes counts only indexes, not the statistics object from Day 22.)

D. Worked examples

Keep Include Actual Execution Plan (Ctrl + M) and SET STATISTICS IO ON for all examples.

Worked example 1 — A nonclustered index and the key lookup

SET STATISTICS IO ON;

CREATE INDEX IX_PerfSalesOrder_CustomerID ON Perf.SalesOrder (CustomerID);

SELECT OrderID, OrderDate, OrderStatus FROM Perf.SalesOrder WHERE CustomerID = 12345 ORDER BY OrderID;  -- Q1
SELECT OrderID, OrderDate, OrderStatus FROM Perf.SalesOrder WHERE CustomerID = 1     ORDER BY OrderID;

Expected result: Q1 returns 10 rows (93371 … 993362). Its plan: Index Seek on the new index → Nested Loops → Key Lookup (10 executions) — logical reads fall from thousands to a few dozen. Customer 1 returns 50,000 rows; its plan typically ignores the new index and scans the clustered index, because 50,000 key lookups would cost more than a scan. That’s the tipping point.

The rows are already in OrderID order within one CustomerID (the clustered key is part of every nonclustered index row), so no Sort is needed.

Worked example 2 — Make it covering

CREATE INDEX IX_PerfSalesOrder_CustomerID ON Perf.SalesOrder (CustomerID)
    INCLUDE (OrderDate, OrderStatus)
    WITH (DROP_EXISTING = ON);

SELECT OrderID, OrderDate, OrderStatus FROM Perf.SalesOrder WHERE CustomerID = 12345 ORDER BY OrderID;
SELECT OrderID, OrderDate, OrderStatus FROM Perf.SalesOrder WHERE CustomerID = 1     ORDER BY OrderID;

Expected result: both plans are now a single Index Seek — no lookups. Q1’s reads drop again (to a handful); customer 1’s query reads only the part of the index holding its 50,000 entries, a fraction of the table. DROP_EXISTING = ON replaces the index in one step. OrderID doesn’t need including: as the clustered key it is already in every row of the index.

Worked example 3 — A range seek and a filtered index

CREATE INDEX IX_PerfSalesOrder_OrderDate ON Perf.SalesOrder (OrderDate) INCLUDE (ShippingCost);

SELECT COUNT(*) AS Orders, SUM(ShippingCost) AS Shipping                 -- Q2
FROM   Perf.SalesOrder WHERE OrderDate >= '2025-12-01' AND OrderDate < '2026-01-01';

CREATE INDEX IX_PerfSalesOrder_Pending ON Perf.SalesOrder (OrderDate, OrderID)
    INCLUDE (CustomerID)
    WHERE OrderStatus = 'Pending';

SELECT OrderID, CustomerID, OrderDate FROM Perf.SalesOrder                -- Q4
WHERE  OrderStatus = 'Pending' ORDER BY OrderDate, OrderID;

SELECT i.name, SUM(ps.row_count) AS IndexRows, SUM(ps.used_page_count) AS UsedPages
FROM   sys.indexes AS i
JOIN   sys.dm_db_partition_stats AS ps ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE  i.object_id = OBJECT_ID('Perf.SalesOrder')
GROUP BY i.name ORDER BY IndexRows DESC, i.name;

Expected result: Q2: 21216, 71716.28 via an Index Seek (range) on IX_PerfSalesOrder_OrderDate. Q4: 11402 rows via an Index Scan of the filtered index — and no Sort, because the index is already in OrderDate, OrderID order. The size query: PK_PerfSalesOrder, IX_PerfSalesOrder_CustomerID and IX_PerfSalesOrder_OrderDate each hold 1,000,000 rows; IX_PerfSalesOrder_Pending holds 11,402 — and uses far fewer pages. Note your page counts.

Worked example 4 — A columnstore for the analytical query

SET STATISTICS IO, TIME ON;

CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_PerfSalesOrderLine
    ON Perf.SalesOrderLine (OrderID, ProductID, Quantity, UnitPrice, DiscountPct);

SELECT TOP (5) p.ProductCode,                                              -- Q5
       CAST(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS decimal(14,2)) AS NetSales
FROM   Perf.SalesOrder AS o
JOIN   Perf.SalesOrderLine AS l ON l.OrderID = o.OrderID
JOIN   Perf.Product        AS p ON p.ProductID = l.ProductID
WHERE  o.OrderStatus <> 'Cancelled' AND o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01'
GROUP BY p.ProductCode
ORDER BY NetSales DESC, p.ProductCode;

SET STATISTICS IO, TIME OFF;

Expected result: the same five rows as Day 22 (NAV-004 22195143.60 …). The plan now reads Perf.SalesOrderLine through a Columnstore Index Scan; operator properties show Actual Execution Mode: Batch. STATISTICS IO reports lob logical reads and segment information for SalesOrderLine. Compare CPU and elapsed time with your Day 22 baseline — on most machines the difference is large, but measure rather than assume. The SalesOrder side may still be a scan: IX_PerfSalesOrder_OrderDate doesn’t include OrderStatus, so using it for a whole year would need many key lookups.

E. Guided practical lab — Index Q3 and compare with the baseline

Step 1 — A unique index for the email search

1. Do: Emails are unique per customer; say so with a unique index.
2. Run:

   USE RetailLab;
   GO
   SET STATISTICS IO ON;
   CREATE UNIQUE INDEX UX_PerfCustomer_Email ON Perf.Customer (Email) INCLUDE (CustomerName, Region);
   
   SELECT CustomerID, CustomerName, Region FROM Perf.Customer WHERE Email = 'customer12345@example.com';  -- Q3

3. Observe: One row (12345, Customer 12345, North); plan: one Index Seek; a handful of logical reads.
4. Why it matters: The unique index enforces a business rule and lets the optimiser know at most one row can match.
5. Confirm: SELECT is_unique FROM sys.indexes WHERE name = 'UX_PerfCustomer_Email'; → 1.

Step 2 — Re-measure Q1–Q5

1. Do: Run each baseline query (Day 22 lab, step 2) twice; record the second run.
2. Run: the five queries from Day 22, then insert your numbers:

   INSERT INTO Lab.PerfBaseline (QueryName, LessonDay, LogicalReads, CpuMs, ElapsedMs, PlanShape)
   VALUES ('Q1 customer orders',  23, 0, 0, 0, N'Index Seek (covering)'),
          ('Q2 month of orders',  23, 0, 0, 0, N'Index Seek (range)'),
          ('Q3 customer by email',23, 0, 0, 0, N'Index Seek (unique)'),
          ('Q4 pending orders',   23, 0, 0, 0, N'Index Scan (filtered), no Sort'),
          ('Q5 top products 2025',23, 0, 0, 0, N'Columnstore Index Scan, batch mode');

3. Observe: Your own numbers.
4. Why it matters: An improvement is a claim; the baseline turns it into evidence.
5. Confirm: 5 rows with LessonDay = 23.

Step 3 — Compare before and after

1. Do: Put the days side by side.
2. Run:

   SELECT  b.QueryName,
           b.LogicalReads AS ReadsDay22, a.LogicalReads AS ReadsDay23,
           CAST(100.0 * (b.LogicalReads - a.LogicalReads) / NULLIF(b.LogicalReads, 0) AS decimal(5,1)) AS ReadReductionPct,
           b.ElapsedMs AS MsDay22, a.ElapsedMs AS MsDay23
   FROM    Lab.PerfBaseline AS b
   JOIN    Lab.PerfBaseline AS a ON a.QueryName = b.QueryName AND a.LessonDay = 23
   WHERE   b.LessonDay = 22
   ORDER BY b.QueryName;

3. Observe: Large reductions for Q1–Q4. For Q5, lob logical reads aren’t directly comparable with row-store logical reads — compare its elapsed and CPU time instead.
4. Why it matters: Different measures fit different access methods.
5. Confirm: 5 rows.

Step 4 — What does it cost?

1. Do: Total the space used by each table’s indexes.
2. Run:

   SELECT  OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName, i.type_desc,
           SUM(ps.used_page_count) * 8 / 1024 AS UsedMB
   FROM    sys.indexes AS i
   JOIN    sys.dm_db_partition_stats AS ps ON ps.object_id = i.object_id AND ps.index_id = i.index_id
   WHERE   i.object_id IN (OBJECT_ID('Perf.SalesOrder'), OBJECT_ID('Perf.SalesOrderLine'), OBJECT_ID('Perf.Customer'))
   GROUP BY i.object_id, i.name, i.type_desc
   ORDER BY TableName, UsedMB DESC;

3. Observe: The clustered indexes are the largest; the filtered index is tiny; note how the columnstore’s size compares with the line table’s clustered index (it’s compressed).
4. Why it matters: Each index is a copy of some data that must be kept in step on every write.
5. Confirm: 8 rows: 3 primary keys + 5 nonclustered indexes (IX_…CustomerID, IX_…OrderDate, IX_…Pending, UX_…Email, NCCI_…).

F. Independent practice

Task 1 — Key order

Create IX_PerfSalesOrder_Customer_Date on (CustomerID, OrderDate) and query customer 1’s orders in 2025 (COUNT(*)), then customer 12345’s orders in 2024. Compare reads with an (OrderDate, CustomerID) index — create it, force each with WITH (INDEX(…)) for the comparison, then drop the second index.

Success criteria: customer 1, 2025: 12486; customer 12345, 2024: 5. The (CustomerID, OrderDate) version seeks directly to the customer’s date range and reads far fewer pages than the (OrderDate, CustomerID) version.

Task 2 — Does anyone use these indexes?

Query sys.dm_db_index_usage_stats for the Perf.SalesOrder indexes: seeks, scans, lookups and updates. Then look at sys.dm_db_missing_index_details for Perf tables.

Success criteria: each index you queried today shows seeks or scans > 0 and updates = 0 (no data has changed). IX_PerfSalesOrder_Customer_Date from task 1 makes IX_PerfSalesOrder_CustomerID largely redundant for searching — identify this and decide which to keep. Missing-index suggestions (if any) are from queries that ran before today’s indexes existed.

Task 3 — The write cost

In a transaction you roll back, mark pending orders dated before 2025-12-20 as Delivered, with STATISTICS IO on. Note which indexes the update had to maintain.

Success criteria: (3356 rows affected). The plan’s update operator lists the clustered index and IX_PerfSalesOrder_Pending (rows leave the filtered index) and IX_PerfSalesOrder_CustomerID (it includes OrderStatus). After ROLLBACK, 11402 orders are still pending.

Task 4 — Fragmentation and maintenance

Check fragmentation and page density for the Perf.SalesOrder indexes with sys.dm_db_index_physical_stats ('SAMPLED' mode). Rebuild IX_PerfSalesOrder_CustomerID and check STATS_DATE for its statistics before and after.

Success criteria: fragmentation is low for all indexes (they were built on static data); the rebuild changes STATS_DATE to now. Explain why you would not schedule a nightly rebuild of these indexes.

Stretch challenge — An indexed view (Standard edition rules)

Create a schema-bound view Perf.vMonthlyNetSales (month start, net sales, COUNT_BIG(*)) over non-cancelled orders and their lines, with a unique clustered index. Query 2025 with WITH (NOEXPAND).

Success criteria: 12 rows; January 2025 20540623.05; December 2025 20535631.18; total for 2025 241814855.83. The plan reads the view’s index, not the base tables.

G. Solutions and explanations

CREATE INDEX IX_PerfSalesOrder_Customer_Date ON Perf.SalesOrder (CustomerID, OrderDate);
CREATE INDEX IX_PerfSalesOrder_Date_Customer ON Perf.SalesOrder (OrderDate, CustomerID);

SET STATISTICS IO ON;
SELECT COUNT(*) FROM Perf.SalesOrder WITH (INDEX(IX_PerfSalesOrder_Customer_Date))
WHERE  CustomerID = 1 AND OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01';
SELECT COUNT(*) FROM Perf.SalesOrder WITH (INDEX(IX_PerfSalesOrder_Date_Customer))
WHERE  CustomerID = 1 AND OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01';

SELECT COUNT(*) FROM Perf.SalesOrder WITH (INDEX(IX_PerfSalesOrder_Customer_Date))
WHERE  CustomerID = 12345 AND OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01';
SELECT COUNT(*) FROM Perf.SalesOrder WITH (INDEX(IX_PerfSalesOrder_Date_Customer))
WHERE  CustomerID = 12345 AND OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01';
SET STATISTICS IO OFF;

DROP INDEX IX_PerfSalesOrder_Date_Customer ON Perf.SalesOrder;

SELECT  i.name AS IndexName, us.user_seeks, us.user_scans, us.user_lookups, us.user_updates,
        us.last_user_seek, us.last_user_scan
FROM    sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS us
       ON us.object_id = i.object_id AND us.index_id = i.index_id AND us.database_id = DB_ID()
WHERE   i.object_id = OBJECT_ID('Perf.SalesOrder')
ORDER BY i.index_id;

SELECT  d.statement AS TableName, d.equality_columns, d.inequality_columns, d.included_columns,
        s.user_seeks, CAST(s.avg_user_impact AS decimal(5,1)) AS AvgImpactPct
FROM    sys.dm_db_missing_index_details     AS d
JOIN    sys.dm_db_missing_index_groups      AS g ON g.index_handle = d.index_handle
JOIN    sys.dm_db_missing_index_group_stats AS s ON s.group_handle = g.index_group_handle
WHERE   d.database_id = DB_ID() AND d.statement LIKE N'%Perf%';

SET STATISTICS IO ON;
BEGIN TRANSACTION;
    UPDATE Perf.SalesOrder
    SET    OrderStatus = 'Delivered'
    WHERE  OrderStatus = 'Pending' AND OrderDate < '2025-12-20';
ROLLBACK TRANSACTION;
SET STATISTICS IO OFF;

SELECT COUNT(*) AS PendingOrders FROM Perf.SalesOrder WHERE OrderStatus = 'Pending';   -- 11402

SELECT  i.name AS IndexName, ps.index_level, ps.page_count,
        CAST(ps.avg_fragmentation_in_percent  AS decimal(5,1)) AS FragPct,
        CAST(ps.avg_page_space_used_in_percent AS decimal(5,1)) AS PageFullPct
FROM    sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('Perf.SalesOrder'), NULL, NULL, 'SAMPLED') AS ps
JOIN    sys.indexes AS i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE   ps.index_level = 0
ORDER BY i.index_id;

SELECT name, STATS_DATE(object_id, stats_id) AS LastUpdated
FROM   sys.stats WHERE object_id = OBJECT_ID('Perf.SalesOrder') AND name = 'IX_PerfSalesOrder_CustomerID';

ALTER INDEX IX_PerfSalesOrder_CustomerID ON Perf.SalesOrder REBUILD;

SELECT name, STATS_DATE(object_id, stats_id) AS LastUpdated
FROM   sys.stats WHERE object_id = OBJECT_ID('Perf.SalesOrder') AND name = 'IX_PerfSalesOrder_CustomerID';

CREATE VIEW Perf.vMonthlyNetSales
WITH SCHEMABINDING
AS
SELECT  DATEFROMPARTS(YEAR(o.OrderDate), MONTH(o.OrderDate), 1)     AS MonthStart,
        SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct))         AS NetSales,
        COUNT_BIG(*)                                                AS LineCount
FROM    Perf.SalesOrder     AS o
JOIN    Perf.SalesOrderLine AS l ON l.OrderID = o.OrderID
WHERE   o.OrderStatus <> 'Cancelled'
GROUP BY DATEFROMPARTS(YEAR(o.OrderDate), MONTH(o.OrderDate), 1);
GO
CREATE UNIQUE CLUSTERED INDEX UCX_vMonthlyNetSales ON Perf.vMonthlyNetSales (MonthStart);
GO

SELECT MonthStart, CAST(NetSales AS decimal(14,2)) AS NetSales
FROM   Perf.vMonthlyNetSales WITH (NOEXPAND)
WHERE  MonthStart >= '2025-01-01' AND MonthStart < '2026-01-01'
ORDER BY MonthStart;

SELECT CAST(SUM(NetSales) AS decimal(14,2)) AS NetSales2025
FROM   Perf.vMonthlyNetSales WITH (NOEXPAND)
WHERE  MonthStart >= '2025-01-01' AND MonthStart < '2026-01-01';

H. Knowledge check

1. Understanding. What does the leaf level of a nonclustered index contain, and how does SQL Server get from it to the rest of the row?

2. Query interpretation. With only IX(CustomerID), the plan for customer 12345 uses a seek and key lookups, but the plan for customer 1 scans the table. Why?

3. Understanding. Why is (CustomerID, OrderDate) a better key than (OrderDate, CustomerID) for WHERE CustomerID = @c AND OrderDate >= @d?

4. Debugging. You created a filtered index WHERE OrderStatus = 'Pending', but a procedure with WHERE OrderStatus = @Status never uses it. Why?

5. Understanding. Give two reasons not to create every index the missing-index DMVs suggest.

I. Troubleshooting and cleanup

Cleanup. Keep today’s indexes — Day 24 relies on IX_PerfSalesOrder_CustomerID, IX_PerfSalesOrder_OrderDate and the others as they are. You may drop the practice and stretch objects:

DROP INDEX IF EXISTS IX_PerfSalesOrder_Customer_Date ON Perf.SalesOrder;
DROP INDEX IF EXISTS IX_PerfSalesOrder_Date_Customer ON Perf.SalesOrder;
DROP VIEW  IF EXISTS Perf.vMonthlyNetSales;

To return Perf to its Day 22 state at any time, re-run script 05.

J. Completion checklist, recap and references

Completion checklist

• You saw a key lookup for 10 rows and a scan for 50,000 (tipping point), then a covering seek for both.
• Q2 used a range seek; Q4 used the filtered index (11,402 rows) without a Sort.
• Q5 ran in batch mode on the columnstore.
• Lab.PerfBaseline has Day 23 numbers and you compared them with Day 22.
• Task 1 returned 12486 and 5; task 3 affected 3356 rows and was rolled back.

Recap

• Clustered index = the table in key order; nonclustered index = key + included columns + clustered key.
• Key lookups are fine for few rows; covering indexes remove them.
• Equality columns first, then range; filtered indexes for rare values; unique indexes state rules.
• Columnstore for large aggregations (batch mode); rowstore for seeks.
• Every index costs writes, space and maintenance; review usage; treat missing-index hints as clues.
• Maintain on evidence; statistics usually matter most.

Next: Day 24 — Query Optimisation: sargability, implicit conversions, parameter sniffing and hints, using these indexes.

References (Microsoft Learn)

• SQL Server and Azure SQL index architecture and design guide
• CREATE INDEX (Transact-SQL)
• Create filtered indexes
• Columnstore indexes: overview
• Optimize index maintenance to improve query performance and reduce resource consumption
• sys.dm_db_index_usage_stats
• sys.dm_db_index_physical_stats
• Create indexed views