Skip navigation

Clustered Indexes and Swiss Cheese

SQL Server 6.5 manages table space using 16KB extents. Because extents are the smallest unit of table space management, SQL Server can't deallocate an extent from a table until every row has been deleted from every page. If the average row size is 50 bytes, you can fit about 40 rows per 2KB page, or 320 rows per extent. If you delete 319 of those rows, SQL Server still considers the entire extent allocated to the table, so one 50-byte row consumes 16KB. One almost empty extent isn't that bad, but if you have 100 or 1000 empty extents, wasted space adds up quickly.

Clustered indexes help SQL Server manage space more effectively, minimizing the empty extent scenario. The following example shows how and why the clustered index is such an important space management tool.

To create our test environment, we cloned the pubs ..authors table until we ended up with 23,522 rows. We cloned the table by creating a new table called Authors2, initially an exact match of the authors table, and used the following command several times to populate the new table.


Authors2 now contains 23,522 rows spread across 1071 pages and is tightly compacted within the database. To obtain that information, we ran Database Consistency Checker (DBCC) SHOWCONTIG on the tables. (DBCC SHOWCONTIG lets us check how fragmented a table has become. For a full explanation of DBCC SHOWCONTIG's output, refer to the documentation in SQL Server Books Online--BOL.)

DBCC SHOWCONTIG scanning 'Authors2' table...



Table: 'Authors2' (572529073) Indid: 0 dbid:8

TABLE level scan performed.

- Pages Scanned...........................: 1071

- Avg. Page Density (full)..............: 97.94%

We know the data is tightly compacted on the page because the Avg. Page Density (full) is 97.94 percent. But watch what happens when we delete half (11,761) the rows (the even-numbered rows):

DELETE AuthorsTest2

WHERE PKCol % 2= 0

Running DBCC SHOWCONTIG after our DELETE shows that the 1071 pages are now 49.96 percent full. Why hasn't the size of the table decreased, even though we deleted half the data? SQL Server can't reclaim the space because the extents still have some data on them. Here's a condensed version of the DBCC SHOWCONTIG output after the deletion:

DBCC SHOWCONTIG scanning 'Authors2' table...


Table: 'Authors2' (572529073) Indid: 0 dbid:8

- Pages Scanned...........................: 1071

- Avg. Page Density (full)..............: 49.96%

Now watch what happens when we reinsert the 11,761 rows we just deleted:


SELECT * FROM Authors3

WHERE PKCol % 2= 0

(Authors3 is an identical copy of the original Authors2 table. We're using this data to easily repopulate the 11,761 rows we just deleted.)

DBCC SHOWCONTIG scanning 'Authors2' table...


- Pages Scanned...........................: 1606

- Avg. Page density (full)..............: 65.97%

Authors2 still has 23,522 rows, but we've added 535 pages to our original 1071 pages. We've increased the space required to store our original 23,522 rows by approximately 50 percent, without adding any new data. Something weird seems to be going on.

TABLE A: Space Utilization in Clustered Indexes
Step Description Pages Scanned Avg. Page Density

1 Start the test. Authors2 has 23522 rows in it. 1071 97.94 percent
2 Delete all of the even-numbered rows. 1071 49.96 percent
3 Reinsert all the even-numbered rows. 1071 97.94 percent
This growth occurred because the Authors2 table didn't have a clustered index on it. Without a clustered index, which forces SQL Server to insert the data in sorted order, SQL Server adds all the new rows to the end of the table. Now the growth makes sense. Deleting 11,761 rows without a clustered index freed up space in the middle of the table, but SQL Server didn't reuse this space when we reinserted the same 11,761 rows. SQL Server added all the new rows to the end of the table rather than restoring them in sorted order.

Rerunning the same tests with a clustered index on the Authors2 table has a dramatically different effect on space utilization within the table. We're starting this test with two identical tables. Authors2 and Authors3 have identical structures and data, except for the value of PKCol. Table A summarizes space utilization information as we delete and reinsert all the even-numbered PKCol rows:

In Step 2, Avg. Page Density (full) dropped to 49.96 percent, as we expected when we deleted half the rows. However, when we reinsert those rows, SQL Server still can't reclaim the empty space when the pages are half full. But the clustered index lets SQL Server reclaim the unused space when the new rows are reinserted in their sorted order. The clustered index lets us save 535 2KB pages, or 50 percent of the table's original size. The moral is to always place a clustered index on tables that are frequently subjected to INSERT, UPDATE, and DELETE commands. Otherwise, your tables will end up looking like Swiss cheese.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.