Sql-server – Why Do My Nonclustered Indexes Use More Space When I Delete Rows

nonclustered-indexsql serversql server 2014

I have a large table with 7.5 billion rows and 5 indexes.
When I delete roughly 10 million rows, I notice that the nonclustered indexes seem to increase the number of pages they're stored on.

I wrote a query against dm_db_partition_stats to report the difference (after – before) in pages:

dm_db_partition_stats deltas

Index 1 is the clustered index, Index 2 is the primary key. The others are nonclustered and non-unique.

Why are the pages increasing on those non-clustered indexes?
I expected the numbers to at worst stay the same.
I do see performance counters report an increase in page-splits during the delete.

When deleting, does the ghost record have to move to another page?
Does this have to do with "uniqueifiers"?

We are in the middle of rolling out RCSI, but right now, RCSI is off.

It is a primary node in an availability group. I know that snapshot is used somehow on secondaries. I'd be surprised if that was relevant. I plan to dig into this (looking dbcc page output) to learn more. Here's hoping someone has seen something similar.

Best Answer

One possible scenario that very much amuses me:

  • The rows were originally written when the database didn't have Read Committed Snapshot (RCSI), Snapshot Isolation (SI), or Availability Groups (AGs) enabled
  • RCSI or SI was enabled, or the database was added into an Availability Group
  • During the deletions, a 14-byte timestamp was added to the deleted rows to support RCSI/SI/AG reads

Since this server is a primary in an AG, it's affected just like the secondaries are. The version info is added on the primary - the data pages are the exact same on both the primaries and secondaries. The secondaries leverage the version store to do their reads while the rows are being updated by the AG, but the secondaries don't write their own versions of the timestamp to the page. They just inherit the versions from the primary's work.

To demonstrate the growth, I took the Stack Overflow database export (which doesn't have RCSI enabled) and created a bunch of indexes on the Posts table. I checked index sizes with sp_BlitzIndex @Mode = 2 (copy/pasted into a spreadsheet, and cleaned up a little to maximize info density):

sp_BlitzIndex before

I then deleted about half of the rows:

BEGIN TRAN;
DELETE dbo.Posts WHERE Id % 2 = 0;
GO

Amusingly, while the deletes were happening, the data file was growing to accommodate the timestamps, too! The SSMS Disk Usage Report shows the growth events - here's just the top to illustrate:

Growth events

(Gotta love a demo where deletes make the database grow.) While the delete was running, I ran sp_BlitzIndex again. Note that the clustered index has less rows, but its size has already grown by about 1.5GB. The nonclustered indexes on AcceptedAnswerId have grown dramatically - they're indexes on a small value that's mostly null, so their index sizes have nearly doubled!

sp_BlitzIndex during deletion

I don't have to wait for the deletion to finish to prove that out, so I'll stop the demo there. Point being: when you do big deletions on a table that was implemented before RCSI, SI, or AGs were enabled, the indexes (including the clustered) can actually grow to accommodate the addition of the version store timestamp.

Related Question