Blog

Can deleting rows make a table…bigger?

Last Updated February 15, 2019

Michael J. Swart posted an interesting question: he had a large table with 7.5 billion rows and 5 indexes. When he deleted 10 million rows, he noticed that the indexes were getting larger, not smaller.

Here’s one way that deletes can cause a table to grow:

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 each deleted row to support RCSI/SI/AG reads

To demonstrate the growth, I took the Stack Overflow database (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):

I then deleted about half of the rows:

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

BEGIN TRAN;

DELETE dbo.Posts WHERE Id % 2 = 0;

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:

(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!

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.

Things you might take away from this:

RCSI, SI, and AGs have cost overheads you might not initially consider
After enabling RCSI, SI, or an AG, rebuild your indexes – not that it’s going to make your life better, it’s just that you can control when the page splits, object growth, fragmentation, and logged activity happens instead of waiting around for it to hit when you’re in a rush to do something
Offline index rebuilds seems to have a related effect too – Swart noted in a comment that when he tested them, they were rebuilt without the 14-byte version stamp, meaning that subsequent updates and deletes incurred the page splits and object growth
I have really strange hobbies

[Video] Demoing SQL Server 2019’s new Accelerated Database Recovery

Last Updated October 15, 2019

Brent Ozar

SQL Server 2019, Videos

Wouldn’t it be nice if rollbacks just finished instantly? Wouldn’t you love for startup times to be near-zero even when SQL Server crashed just as someone in the middle of a transaction? How much would you pay for all this? (Well, I’m a little afraid to ask that, since we don’t know yet whether this is an Enterprise-only feature, but dang, I sure hope not.)

Here’s how Accelerated Database Recovery works in SQL Server 2019:

In the video, I’m using the Stack Overflow 2013 (50GB) database with this script:

/* Grow data & log files out to give us some room to work */
USE [master]
GO
ALTER DATABASE [StackOverflow2013] SET COMPATIBILITY_LEVEL = 150
GO
ALTER DATABASE [StackOverflow2013] 
  MODIFY FILE ( NAME = N'StackOverflow2013_1', SIZE = 15360000KB )
GO
ALTER DATABASE [StackOverflow2013] 
  MODIFY FILE ( NAME = N'StackOverflow2013_2', SIZE = 15360000KB )
GO
ALTER DATABASE [StackOverflow2013] 
  MODIFY FILE ( NAME = N'StackOverflow2013_3', SIZE = 15360000KB )
GO
ALTER DATABASE [StackOverflow2013] 
  MODIFY FILE ( NAME = N'StackOverflow2013_4', SIZE = 15360000KB )
GO
ALTER DATABASE [StackOverflow2013] 
  MODIFY FILE ( NAME = N'StackOverflow2013_log', SIZE = 10240000KB )
GO
/* Enable RCSI - not technically required, but more on that in another post. */
ALTER DATABASE [StackOverflow2013] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO
USE StackOverflow2013;
GO
/* Rebuild the table to get the RCSI version time stamps: */
ALTER INDEX ALL ON dbo.Users REBUILD;

/* Set up an index to make our transaction slower: */
CREATE INDEX IX_Reputation_LastAccessDate 
  ON dbo.Users(Reputation, LastAccessDate);

SELECT * FROM sys.databases;



/* Run a transaction that does a lot of work: */
BEGIN TRAN
UPDATE dbo.Users
  SET Reputation = 1000000,
      LastAccessDate = GETDATE();
GO
ROLLBACK

ALTER DATABASE StackOverflow2013 SET ACCELERATED_DATABASE_RECOVERY = ON;
SELECT * FROM sys.databases;

/* Run a transaction that does a lot of work: */
BEGIN TRAN
UPDATE dbo.Users
  SET Reputation = 1000000,
      LastAccessDate = GETDATE();
GO

/* How much space is being used? */
SELECT TOP 100 * FROM sys.dm_tran_persistent_version_store_stats
SELECT TOP 100 * FROM sys.dm_tran_top_version_generators
SELECT TOP 100 * FROM sys.dm_tran_version_store
SELECT TOP 100 * FROM sys.dm_tran_version_store_space_usage

/* Clustered index */
SELECT * FROM sys.dm_db_index_physical_stats(5, 149575571, 1, 0, 'DETAILED')

/* Nonclustered index */
SELECT * FROM sys.dm_db_index_physical_stats(5, 149575571, 2, 0, 'DETAILED')

EXEC sp_persistent_version_store;

ROLLBACK;
GO

EXEC sp_persistent_version_cleanup;

/* Grow data & log files out to give us some room to work */

USE [master]

ALTER DATABASE [StackOverflow2013] SET COMPATIBILITY_LEVEL = 150