Wide Covering Indexes: The Cost of INCLUDE Everything

Wide covering indexes save lookups by storing extra columns in every leaf page, and you pay for that copy on every write. The trade can be fair. It is just never free.

Why INCLUDE everything looks like a good idea

A junior DBA once asked me why a slow query still showed a key lookup. I said the index did not hold every column the query asked for. The next question was fair: “So why not add all of them?”

The lookup disappears and everyone is happy. Then the table grows, the nightly load slows down, and the storage team sends a polite email. An index is a second copy of your data. A wide index is a second copy of a lot of it. Let me show you the bill.

Build a narrow index and a wide one

Run this in any test database. The demo table holds 3,000 orders, and each row carries a 700-character Notes column. Both indexes search by CustomerId. The narrow one carries Amount. The wide one carries Amount and Notes. The demo needs SQL Server 2022 or later for GENERATE_SERIES.

DROP TABLE IF EXISTS dbo.OrderNotes;

CREATE TABLE dbo.OrderNotes
(
    Id         int           NOT NULL PRIMARY KEY CLUSTERED,
    CustomerId int           NOT NULL,
    Amount     decimal(18,2) NOT NULL,
    Notes      varchar(800)  NOT NULL
);

INSERT dbo.OrderNotes (Id, CustomerId, Amount, Notes)
SELECT value, value % 20, 10.00, REPLICATE('x', 700)
FROM GENERATE_SERIES(1, 3000);

CREATE INDEX IX_OrderNotes_Narrow ON dbo.OrderNotes (CustomerId) INCLUDE (Amount);
CREATE INDEX IX_OrderNotes_Wide   ON dbo.OrderNotes (CustomerId) INCLUDE (Amount, Notes);

SELECT i.name, SUM(p.used_page_count) AS UsedPages
FROM sys.indexes AS i
JOIN sys.dm_db_partition_stats AS p
  ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE i.object_id = OBJECT_ID(N'dbo.OrderNotes')
GROUP BY i.name
ORDER BY i.name;

Read the first result. The narrow index uses 11 pages. The wide index uses 275. That is about 25 times more space for the same search key. The clustered primary key also shows 275, because it holds the whole row, Notes included. In other words, the wide index is nearly a second copy of the table.

I made every Notes value long on purpose, so the gap is easy to see. Your table has a mix of short and long values. Check their real length before you guess at the cost.

See what the wide copy costs on a read

Now read the same customer through each index. I force the choice with a hint so the comparison is fair. Look at the logical reads in the Messages tab.

SET STATISTICS IO ON;

SELECT SUM(Amount) AS TotalAmount
FROM dbo.OrderNotes WITH (INDEX (IX_OrderNotes_Narrow))
WHERE CustomerId = 5;

SELECT SUM(Amount) AS TotalAmount
FROM dbo.OrderNotes WITH (INDEX (IX_OrderNotes_Wide))
WHERE CustomerId = 5;

SET STATISTICS IO OFF;

Both queries return 1500.00. The narrow index answers with 2 logical reads. The wide index needs 16. Same answer, eight times the pages, because the wide index packs far fewer rows into each page.

Here is the part people miss. A wide index removes a lookup for one query, but it also makes every scan of that index read fatter pages. Those pages sit in memory too, so your cache holds fewer useful ones.

Watch one update touch the wide index

Now change only the Notes column of one row. The narrow index does not store Notes, so it has nothing to do. The wide index has to rewrite its copy. The usage counters below show which structures were touched.

UPDATE dbo.OrderNotes
SET Notes = REPLICATE('y', 700)
WHERE Id = 1;

SELECT i.name, COALESCE(u.user_updates, 0) AS UpdateOperations
FROM sys.indexes AS i
LEFT JOIN sys.dm_db_index_usage_stats AS u
  ON u.database_id = DB_ID()
 AND u.object_id = i.object_id
 AND u.index_id = i.index_id
WHERE i.object_id = OBJECT_ID(N'dbo.OrderNotes')
ORDER BY i.name;

Read the second result. The narrow index shows 0, so the update never touched it. The wide index shows 1. The clustered index shows 2: one for the original load and one for this update. Only the wide index had to change, because only it stores a copy of Notes.

One row is a small test. Now picture a job that edits Notes on a million rows. Each change is written to the table and again to the wide index, and both writes go to the transaction log. That is where the nightly load slows down.

How I decide what to include

Start with the queries that run all day, not the one that runs once a quarter. Include the few columns those queries need, and stop there.

Long text columns are the most expensive thing to copy. A key lookup for a handful of rows is often cheaper than carrying a wide value for every row in the table. SELECT * is a trap too: it asks for everything, so the covering list grows every time someone adds a column.

Measure writes as well as reads. Inserts and deletes touch every index, not only updates. Usage counters reset when the server restarts, and a monthly report can vanish from a short window. So do not drop an index because one quiet week called it unused.

Last, clean up the demo table.

DROP TABLE IF EXISTS dbo.OrderNotes;

Next time someone says “just include everything,” ask what the copy will cost on a busy night.

A covering index is not a free shortcut, it is another copy you agree to maintain.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.