Why Would a Delete Make My Database Grow?

Introduction

A while back I had a developer come to me complaining that every time they ran a large delete statement on a certain database the delete would fail with a message claiming the database was full. My first instinct was that they were doing something wrong so I asked for the script so I could try it myself. To my surprise, running the delete actually did fill the database.

Troubleshooting

To figure out why a delete would cause a database to grow, I started with Profiler to see if anything was running as a side-effect of the deletes. The only thing that Profiler showed was the delete. Unable to explain what was happening, I threw the question to #SqlHelp on Twitter. Almost immediately, Paul Randal (Blog|Twitter) asked if I had Read Committed Snapshot Isolation (RCSI) turned on for that database. I confirmed that the database did in fact have RCSI turned on and Paul explained that what I was seeing was SQL Server adding the pointers to the version store to the data pages as they are marked deleted.

Moving Forward

Once I knew what the issue was my mind began to shift gears into how to prevent it from biting me in production. The obvious answer is to set the database size to be sufficiently high enough to allow extra space for large updates or deletes to add version information to the data pages. The problem with this approach is that eventually the reason for the free space will be forgotten and normal growth of the database will eventually eat up that free space.

While looking for options I remembered how I had a similar experience rebuilding indexes to move them to a new file group on new disk. The idea of the project was to move the database to new disk with minimal downtime. To accomplish the move I created a new file group and rebuilt all of the indexes onto it, starting with clustered indexes. Once the primary data file was down to just the system tables I shrunk it, took the whole database offline, moved the file and brought the database back online. The total downtime was about 15 seconds but all of the work took about a week. The work I was doing had to be minimally disruptive so I used the ONLINE flag along with DROP_EXISTING to recreate the indexes. I was surprised to find out at the end of that work to find out that my database had grown significantly in size. After a ton of research I discovered that the ONLINE flag was adding version information to each page, leading to the unexpected growth.

Could the version information for online index operations be the same as what is used by Read Committed Snapshot Isolation?

Could rebuilding all of the indexes in my database with help me to pre-size my database, avoiding later surprises in production?

How would I go about proving my theory?

The Proof

To prove my theory that rebuilding my indexes with would allow me to pre-size my database; I found a quiet corner of the development environment and created a test database.

Here is the script to create the database if you should choose to follow along at home:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

USE master

GO
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'RecordSizeTest')

DROP DATABASE RecordSizeTest

CREATE DATABASE RecordSizeTest

USE RecordSizeTest

GO
IF EXISTS(SELECT * FROM sys.tables WHERE name = 'TestTable')

DROP TABLE dbo.TestTable

CREATE TABLE dbo.TestTable
(

id int identity(1,1) NOT NULL,

varchar_value varchar(400) NOT NULL,

bit_value bit NOT NULL,

create_date smalldatetime NOT NULL DEFAULT(GETDATE())
)

CREATE UNIQUE CLUSTERED INDEX IX_TestTable_id ON dbo.TestTable (id)

INSERT dbo.TestTable (varchar_value, bit_value)

SELECT REPLICATE('TEST', COUNT(*)),

COUNT(*) % 2

FROM dbo.TestTable

GO 100

ALTER DATABASE RecordSizeTest SET READ_COMMITTED_SNAPSHOT ON

SELECT * FROM dbo.TestTable

Now that the database is created, the first step is to see what the database pages look like by default using DBCC IND and DBCC PAGE.

Note: The commands I am using are well-covered elsewhere so I am not going to spend any time describing them beyond just showing how I used them. It goes without saying that you should not run anything on your systems without first taking the time to understand what it does.

The first step is to figure out where the table ended up. Time for DBCC IND:

1 2	DBCC IND(RecordSizeTest, TestTable, 0) GO

Below are the results of the DBCC IND command. To keep things easy I am looking for the first page of the table. To find it I look for a page that has a PrevPageId of 0 and a PageType of 1. In this case the page I am looking for is 145.

1
2
3
4
5
6
7
8
9
10

PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID

------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------

1 146 NULL NULL 2105058535 1 1 72057594038845440 In-row data 10 NULL 0 0 0 0

1 145 1 146 2105058535 1 1 72057594038845440 In-row data 1 0 1 150 0 0

1 150 1 146 2105058535 1 1 72057594038845440 In-row data 1 0 1 153 1 145

1 153 1 146 2105058535 1 1 72057594038845440 In-row data 1 0 0 0 1 150

(4 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Next I want to get a look at that page so I run the following command:

1
2
3
4
5

DBCC TRACEON(3604)

DBCC PAGE(RecordSizeTest, 1, 145, 3)

Below are the DBCC PAGE results. The 2 things to really notice are that free space on the page, m_freeCnt is currently 212 bytes and that the values for Record Attributes are NULL_BITMAP and VARIABLE_COLUMNS. While we are looking at this page I am also making sure that the record in slot 0 has an id of 1 for the next step in the test.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149

PAGE: (1:145)

BUFFER:

BUF @0x0000000090FC6300

bpage = 0x000000009018C000 bhash = 0x0000000000000000 bpageno = (1:145)

bdbid = 6 breferences = 0 bUse1 = 31047

bstat = 0x6c00009 blog = 0x21432159 bnext = 0x0000000000000000

PAGE HEADER:

Page @0x000000009018C000

m_pageId = (1:145) m_headerVersion = 1 m_type = 1

m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x200

m_objId (AllocUnitId.idObj) = 28 m_indexId (AllocUnitId.idInd) = 256

Metadata: AllocUnitId = 72057594039762944

Metadata: PartitionId = 72057594038845440 Metadata: IndexId = 1

Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (1:150)

pminlen = 13 m_slotCnt = 58 m_freeCnt = 212

m_freeData = 7864 m_reservedCnt = 0 m_lsn = (34:159:16)

m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0

m_tornBits = -436072588

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED

PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 16

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 16

Memory Dump @0x000000002309A060

0000000000000000: 10000d00 01000000 00530384 9d040000 †.........S.?....

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 1

Slot 0 Column 2 Offset 0x0 Length 0 Length (physical) 0

varchar_value =

Slot 0 Column 3 Offset 0x8 Length 1 (Bit position 0)

bit_value = 0

Slot 0 Column 4 Offset 0x9 Length 4 Length (physical) 4

create_date = 2010-05-28 14:11:00.000

Slot 0 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (010086470766)

Slot 1 Offset 0x70 Length 24

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Record Size = 24

Memory Dump @0x000000002309A070

0000000000000000: 30000d00 02000000 01530384 9d040000 †0........S.?....

0000000000000010: 01001800 54455354 †††††††††††††††††††....TEST

Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 2

Slot 1 Column 2 Offset 0x14 Length 4 Length (physical) 4

varchar_value = TEST

Slot 1 Column 3 Offset 0x8 Length 1 (Bit position 0)

bit_value = 1

Slot 1 Column 4 Offset 0x9 Length 4 Length (physical) 4

create_date = 2010-05-28 14:11:00.000

Slot 1 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (020068e8b274)

Slot 2 Offset 0x88 Length 28

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Record Size = 28

Memory Dump @0x000000002309A088

0000000000000000: 30000d00 03000000 00530384 9d040000 †0........S.?....

0000000000000010: 01001c00 54455354 54455354 ††††††††††....TESTTEST

<snip>

</snip

Slot 57 Offset 0x1dc0 Length 248

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Record Size = 248

Memory Dump @0x000000002309BDC0

0000000000000000: 30000d00 3a000000 01530384 9d040000 †0...:....S.?....

0000000000000010: 0100f800 54455354 54455354 54455354 †..ø.TESTTESTTEST

0000000000000020: 54455354 54455354 54455354 54455354 †TESTTESTTESTTEST

0000000000000030: 54455354 54455354 54455354 54455354 †TESTTESTTESTTEST

0000000000000040: 54455354 54455354 54455354 54455354 †TESTTESTTESTTEST

0000000000000050: 54455354 54455354 54455354 54455354 †TESTTESTTESTTEST

0000000000000060: 54455354 54455354 54455354 54455354 †TESTTESTTESTTEST

0000000000000070: 54455354 54455354 54455354 54455354 †TESTTESTTESTTEST

0000000000000080: 54455354 54455354 54455354 54455354 †TESTTESTTESTTEST

0000000000000090: 54455354 54455354 54455354 54455354 †TESTTESTTESTTEST

00000000000000A0: 54455354 54455354 54455354 54455354 †TESTTESTTESTTEST

00000000000000B0: 54455354 54455354 54455354 54455354 †TESTTESTTESTTEST

00000000000000C0: 54455354 54455354 54455354 54455354 †TESTTESTTESTTEST

00000000000000D0: 54455354 54455354 54455354 54455354 †TESTTESTTESTTEST

00000000000000E0: 54455354 54455354 54455354 54455354 †TESTTESTTESTTEST

00000000000000F0: 54455354 54455354 †††††††††††††††††††TESTTEST

Slot 57 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 58

Slot 57 Column 2 Offset 0x14 Length 228 Length (physical) 228

varchar_value = TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTT

ESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTE

STTESTTESTTESTTEST

Slot 57 Column 3 Offset 0x8 Length 1 (Bit position 0)

bit_value = 1

Slot 57 Column 4 Offset 0x9 Length 4 Length (physical) 4

create_date = 2010-05-28 14:11:00.000

Slot 57 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (3a0026382d41)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Next I want to delete the first row from the table to see what effect that has. I picked the first row because I already know what page it is on so I can quickly see the impact, if any, of deleting it. I chose to do this in a transaction to also see what effect a rollback might have. Here is the next bit of code in the test:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

DBCC TRACEON(3604)

BEGIN TRANSACTION

DELETE dbo.TestTable
WHERE id = 1

DBCC PAGE(RecordSizeTest, 1, 145, 3)

ROLLBACK

DBCC PAGE(RecordSizeTest, 1, 145, 3)

Below are the latest DBCC PAGE results from before the rollback. Right away it is clear that the row in Slot 0 has been deleted because it’s Record Type is now GHOST_DATA_RECORD. It is also noteable that even though the row has been marked deleted the m_freeCnt on the page has gone down to 198. Sticking with our theory, the reduction in free space should be caused by the addition of version information and, sure enough, the Record Attributes now include VERSIONING_INFO and a new Version Information section is visible with a Transaction Timestamp and a Version Pointer to a location in TempDB. We have now proven that we know how to make a page grow on demand by running a delete.

PAGE: (1:145)

BUFFER:

BUF @0x0000000090FC6300

bpage = 0x000000009018C000 bhash = 0x0000000000000000 bpageno = (1:145)

bdbid = 6 breferences = 1 bUse1 = 31887

bstat = 0x6c0000b blog = 0x21432159 bnext = 0x0000000000000000

PAGE HEADER:

Page @0x000000009018C000

m_pageId = (1:145) m_headerVersion = 1 m_type = 1

m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x2000

m_objId (AllocUnitId.idObj) = 28 m_indexId (AllocUnitId.idInd) = 256

Metadata: AllocUnitId = 72057594039762944

Metadata: PartitionId = 72057594038845440 Metadata: IndexId = 1

Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (1:150)

pminlen = 13 m_slotCnt = 58 m_freeCnt = 198

m_freeData = 7956 m_reservedCnt = 0 m_lsn = (34:324:10)

m_xactReserved = 0 m_xdesId = (0:973) m_ghostRecCnt = 1

m_tornBits = -436072588

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED

PFS (1:1) = 0x68 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x1ef6 Length 30

Record Type = GHOST_DATA_RECORD Record Attributes = NULL_BITMAP VERSIONING_INFO

Record Size = 30

Memory Dump @0x000000002309BEF6

0000000000000000: 5c000d00 01000000 00530384 9d040000 †\........S.?....

0000000000000010: b0010000 01000000 3dbd0500 0000††††††°.......=½....

Version Information =

Transaction Timestamp: 376125

Version Pointer: (file 1 page 432 currentSlotId 0)

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 1

Slot 0 Column 2 Offset 0x0 Length 0 Length (physical) 0

varchar_value =

Slot 0 Column 3 Offset 0x8 Length 1 (Bit position 0)

bit_value = 0

Slot 0 Column 4 Offset 0x9 Length 4 Length (physical) 4

create_date = 2010-05-28 14:11:00.000

Slot 0 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (010086470766)

Slot 1 Offset 0x1ede Length 24

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Record Size = 24

Memory Dump @0x000000002309BEDE

0000000000000000: 30000d00 02000000 01530384 9d040000 †0........S.?....

0000000000000010: 01001800 54455354 †††††††††††††††††††....TEST

Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 2

Slot 1 Column 2 Offset 0x14 Length 4 Length (physical) 4

varchar_value = TEST

Slot 1 Column 3 Offset 0x8 Length 1 (Bit position 0)

bit_value = 1

Slot 1 Column 4 Offset 0x9 Length 4 Length (physical) 4

create_date = 2010-05-28 14:11:00.000

Slot 1 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (020068e8b274)

Slot 2 Offset 0x88 Length 28

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Record Size = 28

Memory Dump @0x000000002309A088

0000000000000000: 30000d00 03000000 00530384 9d040000 †0........S.?....

0000000000000010: 01001c00 54455354 54455354 ††††††††††....TESTTEST

<snip>

</snip>

Slot 57 Column 1 Offset 0x4 Length 4 Length (physical) 4

id = 58

Slot 57 Column 2 Offset 0x14 Length 228 Length (physical) 228

varchar_value = TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTT

ESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTE

STTESTTESTTESTTEST

Slot 57 Column 3 Offset 0x8 Length 1 (Bit position 0)

bit_value = 1

Slot 57 Column 4 Offset 0x9 Length 4 Length (physical) 4

create_date = 2010-05-28 14:11:00.000

Slot 57 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (3a0026382d41)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Next are the DBCC PAGE results from after the rollback. The m_freeCnt has gone back to 212, the row in slot 0 no longer shows as deleted and the versioning information has been removed. The fact that the versioning information goes away as part of the rollback is interesting. It means that no matter how many times I try to do a delete that fills the database I will always start from the same point. It makes perfect sense in terms of ACID but until I saw it for myself I was not sure how it would work.

PAGE: (1:145)

BUFFER:

BUF @0x0000000090FC6300

bpage = 0x000000009018C000 bhash = 0x0000000000000000 bpageno = (1:145)

bdbid = 6 breferences = 0 bUse1 = 32941

bstat = 0x6c0000b blog = 0x21432159 bnext = 0x0000000000000000

PAGE HEADER:

Page @0x000000009018C000

m_pageId = (1:145) m_headerVersion = 1 m_type = 1

m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x6000

m_objId (AllocUnitId.idObj) = 28 m_indexId (AllocUnitId.idInd) = 256

Metadata: AllocUnitId = 72057594039762944

Metadata: PartitionId = 72057594038845440 Metadata: IndexId = 1

Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (1:150)

pminlen = 13 m_slotCnt = 58 m_freeCnt = 212

m_freeData = 7972 m_reservedCnt = 0 m_lsn = (34:324:13)

m_xactReserved = 0 m_xdesId = (0:973) m_ghostRecCnt = 0

m_tornBits = -436072588

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED

PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED

ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x1f14 Length 16

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 16

Memory Dump @0x000000002309BF14