22

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.

  • Just a question - running a REORGANIZE on one of the indexes that grew, what happens? How many pages are removed? And if you Reorganize before deleting, what happens? I'm mostly thinking that the internal mechanisms might find it easier in some cases to allocate an entire new page and merge, but doesn't clean up the empty pages. I know that REORGANIZE ends up dropping significant amounts of pages, even on relatively unfragmented but larger indexes. – Laughing Vergil Feb 14 at 21:51
  • Good question @LaughingVergil When I have the answer, I'll come back here to report it. (But it may take a while). – Michael J Swart Feb 14 at 21:53
  • In our case, this increase was a temporary phenomenon. With enough patience, the ghost cleanup eventually job did its work and the sizes of the indexes decreased. – Michael J Swart Mar 22 at 21:02
28

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.

  • 3
    This is the explanation. It turns out that there are other circumstances that can lead to missing 14 version bytes. In my testing it seems that rebuilding an index offline will rebuild the rows without the version bytes. – Michael J Swart Feb 15 at 16:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.