Index Size Estimate: Sizing an Index Before You Create It

An index size estimate is a guess with arithmetic, so write the assumptions next to the number. Then build the real index on a copy and compare. The gap between the two teaches you more than the estimate does.

Why guess before you build

Picture a change window. You start an index build at 1 AM, then go to bed. At 2 AM the drive fills up, the build rolls back, and your phone lights up. A quick size estimate beforehand would have told you to ask for more disk.

The estimate does not need to be perfect. It needs to be honest. So let me do one on a small table, then check it against a real index.

Count the rows once

The demo table holds 3,000 rows. The first query counts them from the heap or clustered index only (index_id 0 or 1). Those two represent the base table. The second query lists the columns with their declared size in bytes.

DROP TABLE IF EXISTS dbo.SizeDemo;

CREATE TABLE dbo.SizeDemo
(
    Id         int PRIMARY KEY CLUSTERED,
    CustomerId int,
    OrderDate  date,
    Amount     decimal(18,2)
);

INSERT dbo.SizeDemo (Id, CustomerId, OrderDate, Amount)
SELECT value, value % 20, '20250101', 10.00
FROM GENERATE_SERIES(1, 3000);

SELECT SUM(row_count) AS EstimatedRows
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID(N'dbo.SizeDemo') AND index_id IN (0, 1);

SELECT name, max_length, is_nullable
FROM sys.columns
WHERE object_id = OBJECT_ID(N'dbo.SizeDemo')
ORDER BY column_id;

EstimatedRows is 3000. The widths are 4, 4, 3 and 9 bytes. Remember that max_length is what the column is declared to hold. For fixed-width columns like these it is the real width. For varchar columns it is only a ceiling, so measure real lengths on production data.

Put your assumptions in the arithmetic

Now the math. I assume each leaf row is 40 bytes and each page is 90 percent full. The 40 is a round guess that leaves room for row overhead. Using variables keeps the guesses visible, so anyone reading the result can argue with them.

DECLARE @Rows decimal(18,2) = 3000,
        @AssumedBytes decimal(18,2) = 40,
        @AssumedOccupancy decimal(5,2) = 0.90;

SELECT @Rows AS AssumedRows,
       @AssumedBytes AS AssumedLeafBytes,
       @AssumedOccupancy AS AssumedOccupancy,
       CONVERT(decimal(18,4), @Rows * @AssumedBytes / 1048576.0 / @AssumedOccupancy) AS ApproximateLeafMiB;

The answer is 0.1272 MiB. A page holds 8 KB, so there are 128 pages in a MiB, and the estimate is about 16 pages. This is not a full B-tree formula. It ignores the upper levels of the tree, compression and variable-length data. Treat it as a first number you will correct.

Build it on a copy and measure

Now create the index you really plan to use. I include Amount and set the fill factor to 90 to match my assumption. Then I read used and reserved pages for every index on the table.

CREATE INDEX IX_SizeDemo
ON dbo.SizeDemo (CustomerId, OrderDate)
INCLUDE (Amount)
WITH (FILLFACTOR = 90);

SELECT i.name, SUM(p.used_page_count) AS UsedPages, SUM(p.reserved_page_count) AS ReservedPages
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.SizeDemo')
GROUP BY i.name
ORDER BY i.name;

The new index uses 13 pages and reserves 33. My estimate was about 16 pages, so it came out a little high. That is a good miss, because a high guess is safe. Reserved is larger than used because space is handed out in groups of 8 pages.

Here is a surprise. The table itself also uses 13 pages. This index carries every column of a four-column table, so it is nearly a second copy of the table. Wide indexes cost real space.

One more check. Add up the row counts of every index and you count each row several times.

SELECT SUM(row_count) AS RowsCountedPerIndex
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID(N'dbo.SizeDemo');

It says 6000 for a table with 3000 rows. That is why the first query looked only at index_id 0 and 1.

Plan for the build, not just the result

The finished index is only part of the bill. While it builds, SQL Server may also need room in tempdb and in the transaction log. Check free space on the data drive, the log drive and tempdb before the window opens.

Test on data that looks like production. A small sample full of short values will make your estimate too low. Add growth for the next year. Keep the estimate and the measured result together, so the next person can start from your numbers. Then clean up the demo.

DROP TABLE IF EXISTS dbo.SizeDemo;

Next time someone asks for disk, hand them an estimate with its assumptions attached.

An index estimate is not a reservation, it is a guess you should check.

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