Category: File Management

Why Would a Delete Make My Database Grow?

Posted on by David Levy

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
GO

CREATE DATABASE RecordSizeTest
GO

USE RecordSizeTest
GO
IF EXISTS(SELECT * FROM sys.tables WHERE name = 'TestTable')
    DROP TABLE dbo.TestTable
GO

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())
)
GO

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

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
GO

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)
GO

DBCC PAGE(RecordSizeTest, 1, 145, 3)
GO

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)
GO

BEGIN TRANSACTION

DELETE  dbo.TestTable
WHERE   id = 1
GO

DBCC PAGE(RecordSizeTest, 1, 145, 3)
GO

ROLLBACK

DBCC PAGE(RecordSizeTest, 1, 145, 3)
GO

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.

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
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.

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

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 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)
<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.

Now that I have proven that I can delete a record in a database that uses Read Committed Snapshot Isolation and cause space usage to increase I now want to repeat the test after rebuilding the clustered index on the table with ONLINE=ON. Here is the next bit of code to run:

1
2
CREATE UNIQUE CLUSTERED INDEX IX_TestTable_id ON dbo.TestTable (id) WITH (ONLINE=ON, DROP_EXISTING=ON)
GO

Now that the index has been rebuilt it is time to figure out where it ended up. Time for DBCC IND:

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

Based on the results of DBCC IND we are looking for Page 156. It is noteable that DBCC IND returned 5 rows this time instead of 4. More pages generally means more data so lets dig into it.

1
2
3
4
5
6
7
8
9
10
11
PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber PartitionID          iam_chain_type       PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------
1       157         NULL   NULL        2105058535  1           1               72057594038910976    In-row data          10       NULL       0           0           0           0
1       156         1      157         2105058535  1           1               72057594038910976    In-row data          1        0          1           160         0           0
1       160         1      157         2105058535  1           1               72057594038910976    In-row data          1        0          1           161         1           156
1       161         1      157         2105058535  1           1               72057594038910976    In-row data          1        0          1           162         1           160
1       162         1      157         2105058535  1           1               72057594038910976    In-row data          1        0          0           0           1           161

(5 row(s) affected)

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

Here is the syntax of the next DBCC PAGE command to run:

1
2
3
4
5
DBCC TRACEON(3604)
GO

DBCC PAGE(RecordSizeTest, 1, 156, 3)
GO

The results below seem quite different. First off, m_freeCnt is 680 instead of 212. Adding version information should not increase free space so there must be less records here, m_slotCnt proves that. This page has 53 slots or rows while the earlier page held 58 rows of data. That proves that the extra row in DBCC IND is an extra row of data that was added after the rebuild of the index to add the versioning information. Looking at the record in slot 0, it now looks like it did before the rollback of the delete. The Record Attributes include VERSIONING_INFO and there is a Version Information section just below that. The Version Information includes a Transaction Timestamp from when the index was rebuilt but no Version Pointer because the row is unchanged.

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
PAGE: (1:156)


BUFFER:


BUF @0x0000000090FC6080

bpage = 0x0000000090182000           bhash = 0x0000000000000000           bpageno = (1:156)
bdbid = 6                            breferences = 0                      bUse1 = 34110
bstat = 0x6c0000b                    blog = 0x432159bb                    bnext = 0x0000000000000000

PAGE HEADER:


Page @0x0000000090182000

m_pageId = (1:156)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x2000
m_objId (AllocUnitId.idObj) = 29     m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594039828480                                
Metadata: PartitionId = 72057594038910976                                 Metadata: IndexId = 1
Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (1:160)
pminlen = 13                         m_slotCnt = 53                       m_freeCnt = 680
m_freeData = 7406                    m_reservedCnt = 0                    m_lsn = (34:385:19)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 0                      

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = 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 30

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VERSIONING_INFO
Record Size = 30                    
Memory Dump @0x0000000018A4A060

0000000000000000:   50000d00 01000000 d0530384 9d040000 †P.......ÐS.„....
0000000000000010:   00000000 00000000 47be0500 0000††††††........G¾....  

Version Information =
    Transaction Timestamp: 376391
    Version Pointer: Null


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 0x7e Length 38

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO
Record Size = 38                    
Memory Dump @0x0000000018A4A07E

0000000000000000:   70000d00 02000000 d1530384 9d040000 †p.......ÑS.„....
0000000000000010:   01001800 54455354 00000000 00000000 †....TEST........
0000000000000020:   47be0500 0000††††††††††††††††††††††††G¾....          

Version Information =
    Transaction Timestamp: 376391
    Version Pointer: Null


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)  
<snip>
.
.
.
</snip>
Version Information =
    Transaction Timestamp: 376391
    Version Pointer: Null


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

id = 53                              

Slot 52 Column 2 Offset 0x14 Length 208 Length (physical) 208

varchar_value = TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTT
ESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTEST

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

bit_value = 0                        

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

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

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

KeyHashValue = (350070284e19)        


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

Now that the versioning information has been added it is time to re-run the delete test to see what effect a delete and subsequent rollback has. Here is the code for this test:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DBCC TRACEON(3604)
GO

BEGIN TRANSACTION

DELETE  dbo.TestTable
WHERE   id = 1
GO

DBCC PAGE(RecordSizeTest, 1, 156, 3)
GO

ROLLBACK

DBCC PAGE(RecordSizeTest, 1, 156, 3)
GO

The first set of DBCC PAGE results shows exactly what I expected. The record in slot 0 is marked as a GHOST_DATA_RECORD and the version pointer is now populated with a pointer to a location in TempDB. Note: You can run DBCC PAGE to look at that record in TempDB if the transaction is still open. It is beyond the scope of this post so I will not cover it here but definitely interesting to look at. What has not changed is that m_freeCnt is still 680. There was no change in record size or space usage.

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
150
151
152
153
154
155
156
157
158
PAGE: (1:156)


BUFFER:


BUF @0x0000000090FC6080

bpage = 0x0000000090182000           bhash = 0x0000000000000000           bpageno = (1:156)
bdbid = 6                            breferences = 1                      bUse1 = 35111
bstat = 0x6c0000b                    blog = 0x432159bb                    bnext = 0x0000000000000000

PAGE HEADER:


Page @0x0000000090182000

m_pageId = (1:156)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x2000
m_objId (AllocUnitId.idObj) = 29     m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594039828480                                
Metadata: PartitionId = 72057594038910976                                 Metadata: IndexId = 1
Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (1:160)
pminlen = 13                         m_slotCnt = 53                       m_freeCnt = 680
m_freeData = 7474                    m_reservedCnt = 0                    m_lsn = (34:435:35)
m_xactReserved = 0                   m_xdesId = (0:992)                   m_ghostRecCnt = 1
m_tornBits = 0                      

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x68 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED            

Slot 0 Offset 0x1d14 Length 30

Record Type = GHOST_DATA_RECORD      Record Attributes =  NULL_BITMAP VERSIONING_INFO
Record Size = 30                    
Memory Dump @0x0000000018A4BD14

0000000000000000:   5c000d00 01000000 d0530384 9d040000 †.......ÐS.„....
0000000000000010:   c8010000 01000100 a0bf0500 0000††††††È....... ¿....  

Version Information =
    Transaction Timestamp: 376736
    Version Pointer: (file 1 page 456 currentSlotId 1)


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 0x1cee Length 38

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO
Record Size = 38                    
Memory Dump @0x0000000018A4BCEE

0000000000000000:   70000d00 02000000 d1530384 9d040000 †p.......ÑS.„....
0000000000000010:   01001800 54455354 00000000 00000000 †....TEST........
0000000000000020:   47be0500 0000††††††††††††††††††††††††G¾....          

Version Information =
    Transaction Timestamp: 376391
    Version Pointer: Null


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)
<snip>
.
.
.
</snip>
KeyHashValue = (3400154ff2a1)        
Slot 52 Offset 0x1bfc Length 242

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO
Record Size = 242                    
Memory Dump @0x0000000018A4BBFC

0000000000000000:   70000d00 35000000 d0530384 9d040000 †p...5...ÐS.„....
0000000000000010:   0100e400 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 00000000 00000000 47be0500 †TEST........G¾..
00000000000000F0:   0000†††††††††††††††††††††††††††††††††..              

Version Information =
    Transaction Timestamp: 376391
    Version Pointer: Null


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

id = 53                              

Slot 52 Column 2 Offset 0x14 Length 208 Length (physical) 208

varchar_value = TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTT
ESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTEST

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

bit_value = 0                        

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

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

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

KeyHashValue = (350070284e19)        


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

The second DBCC PAGE result shows that the record in slot 0 is no longer a GHOST_DATA_RECORD and the version pointer has reverted to Null. The m_freeCnt is still 680.

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
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
PAGE: (1:156)


BUFFER:


BUF @0x0000000090FC6080

bpage = 0x0000000090182000           bhash = 0x0000000000000000           bpageno = (1:156)
bdbid = 6                            breferences = 3                      bUse1 = 35512
bstat = 0x6c0000b                    blog = 0x432159bb                    bnext = 0x0000000000000000

PAGE HEADER:


Page @0x0000000090182000

m_pageId = (1:156)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x6000
m_objId (AllocUnitId.idObj) = 29     m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594039828480                                
Metadata: PartitionId = 72057594038910976                                 Metadata: IndexId = 1
Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (1:160)
pminlen = 13                         m_slotCnt = 53                       m_freeCnt = 680
m_freeData = 7504                    m_reservedCnt = 0                    m_lsn = (34:435:38)
m_xactReserved = 0                   m_xdesId = (0:992)                   m_ghostRecCnt = 0
m_tornBits = 0                      

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED            

Slot 0 Offset 0x1d32 Length 30

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VERSIONING_INFO
Record Size = 30                    
Memory Dump @0x0000000018A4BD32

0000000000000000:   50000d00 01000000 d0530384 9d040000 †P.......ÐS.„....
0000000000000010:   00000000 00000000 47be0500 0000††††††........G¾....  

Version Information =
    Transaction Timestamp: 376391
    Version Pointer: Null


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 0x1cee Length 38

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO
Record Size = 38                    
Memory Dump @0x0000000018A4BCEE

0000000000000000:   70000d00 02000000 d1530384 9d040000 †p.......ÑS.„....
0000000000000010:   01001800 54455354 00000000 00000000 †....TEST........
0000000000000020:   47be0500 0000††††††††††††††††††††††††G¾....          

Version Information =
    Transaction Timestamp: 376391
    Version Pointer: Null


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 0xa4 Length 42

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO
Record Size = 42                    
Memory Dump @0x0000000018A4A0A4

0000000000000000:   70000d00 03000000 d0530384 9d040000 †p.......ÐS.„....
0000000000000010:   01001c00 54455354 54455354 00000000 †....TESTTEST....
0000000000000020:   00000000 47be0500 0000†††††††††††††††....G¾....      

Version Information =
    Transaction Timestamp: 376391
    Version Pointer: Null


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

id = 3                              

Slot 2 Column 2 Offset 0x14 Length 8 Length (physical) 8

varchar_value = TESTTEST            

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

bit_value = 0                        

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

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

Slot 2 Offset 0x0 Length 0 Length (physical) 0
<snip>
.
.
.
</snip>
KeyHashValue = (3400154ff2a1)        
Slot 52 Offset 0x1bfc Length 242

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO
Record Size = 242                    
Memory Dump @0x0000000018A4BBFC

0000000000000000:   70000d00 35000000 d0530384 9d040000 †p...5...ÐS.„....
0000000000000010:   0100e400 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 00000000 00000000 47be0500 †TEST........G¾..
00000000000000F0:   0000†††††††††††††††††††††††††††††††††..              

Version Information =
    Transaction Timestamp: 376391
    Version Pointer: Null


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

id = 53                              

Slot 52 Column 2 Offset 0x14 Length 208 Length (physical) 208

varchar_value = TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTT
ESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTEST

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

bit_value = 0                        

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

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

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

KeyHashValue = (350070284e19)        


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

I am feeling pretty good about my results so far but I want to do a final test to rule out any effect from the use of transactions in my testing. Here is the final test:

1
2
3
4
5
6
7
8
9
DBCC TRACEON(3604)
GO

DELETE  dbo.TestTable
WHERE   id = 1
GO

DBCC PAGE(RecordSizeTest, 1, 156, 3)
GO

The DBCC PAGE results from this test confirm that transactions have not impacted the test cases above. The results below do depend on how the test is run though. I ran both the delete and DBCC PAGE as a single command and was able to see the ghost record in slot 0 and m_freeCnt was still 680. If I had run the statements individually I would most likely have seen that the ghost cleanup process had removed the record in slot 0 with the row that had been in slot 1 now showing in slot 0. The m_freeCnt would also have been updated to be 712, reflecting the removal of the record.

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
PAGE: (1:156)


BUFFER:


BUF @0x0000000090FC6080

bpage = 0x0000000090182000           bhash = 0x0000000000000000           bpageno = (1:156)
bdbid = 6                            breferences = 1                      bUse1 = 35886
bstat = 0x6c0000b                    blog = 0x432159bb                    bnext = 0x0000000000000000

PAGE HEADER:


Page @0x0000000090182000

m_pageId = (1:156)                   m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x2000
m_objId (AllocUnitId.idObj) = 29     m_indexId (AllocUnitId.idInd) = 256  
Metadata: AllocUnitId = 72057594039828480                                
Metadata: PartitionId = 72057594038910976                                 Metadata: IndexId = 1
Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (1:160)
pminlen = 13                         m_slotCnt = 53                       m_freeCnt = 680
m_freeData = 7504                    m_reservedCnt = 0                    m_lsn = (34:435:43)
m_xactReserved = 0                   m_xdesId = (0:993)                   m_ghostRecCnt = 1
m_tornBits = 0                      

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED              
PFS (1:1) = 0x68 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED            

Slot 0 Offset 0x1d32 Length 30

Record Type = GHOST_DATA_RECORD      Record Attributes =  NULL_BITMAP VERSIONING_INFO
Record Size = 30                    
Memory Dump @0x0000000018A4BD32

0000000000000000:   5c000d00 01000000 d0530384 9d040000 †.......ÐS.„....
0000000000000010:   d0010000 01000000 81c00500 0000††††††Ð........À....  

Version Information =
    Transaction Timestamp: 376961
    Version Pointer: (file 1 page 464 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 0x1cee Length 38

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFO
Record Size = 38                    
Memory Dump @0x0000000018A4BCEE

0000000000000000:   70000d00 02000000 d1530384 9d040000 †p.......ÑS.„....
0000000000000010:   01001800 54455354 00000000 00000000 †....TEST........
0000000000000020:   47be0500 0000††††††††††††††††††††††††G¾....          

Version Information =
    Transaction Timestamp: 376391
    Version Pointer: Null


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)  
<snip>
.
.
.
</snip>
Version Information =
    Transaction Timestamp: 376391
    Version Pointer: Null


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

id = 53                              

Slot 52 Column 2 Offset 0x14 Length 208 Length (physical) 208

varchar_value = TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTT
ESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTEST

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

bit_value = 0                        

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

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

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

KeyHashValue = (350070284e19)        


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

Conclusion

I started out with a theory that rebuilding the indexes in a database with ONLINE=ON will prevent unexpected space usage due to large update or delete operations in a database that has Read Committed Snapshot Isolation enabled. I feel that through my tests I have shown that adding the versioning information at the time of reindexing is a viable alternative to keeping extra free space in a database. It is definitely a great alternative to dealing with production issues caused by a full database. Like anything else this solution is not perfect, but if I know there is a big delete coming, I will definitely make sure the indexes on the impacted tables are rebuilt with ONLINE=ON or that I have included extra space via FILL_FACTOR. Most importantly, I know to expect growth from large update or delete operations and can manage accordingly in the way the database is set up and the number of records impacted by each pass over the data.

Thank you for sticking with me to the end of this long post. I hope it was as informative to read as it was to write.

Script to Create A TempDB File Per Processor

Posted on by David Levy

Introduction

Trying to keep the streak alive so here is my 5th blog post in 5 days. This one is another script and by now you are probably wondering if I am either really incredibly lazy or just hate screwing up. The answer to both is an unqualifed YES.

The Need

The best practice from Microsoft is to have 1 TempDB file per processor on a dedicated disk. The files should be set large enough so that they do not need to grow. You can read more here. I should warn you that there is some debate on this recommendation and many people run .25 TempDB files per processor or even just 1 big file on less busy servers.

Update: There is not a lot of good information on this subject outside of the best practices recommendations and I am not trying to fill that void here. I am not advocating a certain number of files for TempDB in this post, that would probably occupy an entire series of blog posts and require intimate knowledge of the internals of the storage engine.

This post is a good start at dispelling some of the myths around how many files you should have for TempDB. Most noticeably, it only recommends multiple files when you are seeing latch contention in TempDB.

I plan to update this post directly as new information is discovered to get the best information out there so please either check back or subscribe to the feed to stay informed.

April 12, 2010 – Paul Randal has just blogged “A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core”. Please make sure to read that post before going down the path of 1 TempDB file per processor.

For me, on my big servers, I like to run with 1 TempDB file per core / processor. Unfortunately being big servers, these machines have a lot of cores. The last server I built had 32 cores. Creating individual files via the GUI would have taken 30 minutes to an hour assuming I did not get something wrong and have to redo it.

The Script

To satisfy my need to be lazy and protect me from myself I have written a script to add a TempDB file per processor. The script is easy enough to use. Step 1 is to figure out how much space you have in total on your TempDB data drive. I specifically say data because logs should be on another drive. Step 2 is to divide the size of your drive by the number of cores / processors in your server and make that the size of your current single TempDB file. To give an example: Your new server has 32 cores and a 66 GB TempDB drive. 32 neatly goes into 64 2 times so you would set the size of your TempDB file to 2 GB, with maybe a slightly higher maximum size if you prefer to leave autogrow on. Next you would simply double check your math and settings then run the script and 31 exact copies of that file would be created. The whole thing should take less than 5 minutes.

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
USE [master]
GO
DECLARE @cpu_count      int,
        @file_count     int,
        @logical_name   sysname,
        @file_name      nvarchar(520),
        @physical_name  nvarchar(520),
        @size           int,
        @max_size       int,
        @growth         int,
        @alter_command  nvarchar(max)

SELECT  @physical_name = physical_name,
        @size = size / 128,
        @max_size = max_size / 128,
        @growth = growth / 128
FROM    tempdb.sys.database_files
WHERE   name = 'tempdev'

SELECT  @file_count = COUNT(*)
FROM    tempdb.sys.database_files
WHERE   type_desc = 'ROWS'

SELECT  @cpu_count = cpu_count
FROM    sys.dm_os_sys_info

WHILE @file_count < @cpu_count -- Add * 0.25 here to add 1 file for every 4 cpus, * .5 for every 2 etc.
 BEGIN
    SELECT  @logical_name = 'tempdev' + CAST(@file_count AS nvarchar)
    SELECT  @file_name = REPLACE(@physical_name, 'tempdb.mdf', @logical_name + '.ndf')
    SELECT  @alter_command = 'ALTER DATABASE [tempdb] ADD FILE ( NAME =N''' + @logical_name + ''', FILENAME =N''' +  @file_name + ''', SIZE = ' + CAST(@size AS nvarchar) + 'MB, MAXSIZE = ' + CAST(@max_size AS nvarchar) + 'MB, FILEGROWTH = ' + CAST(@growth AS nvarchar) + 'MB )'
    PRINT   @alter_command
    EXEC    sp_executesql @alter_command
    SELECT  @file_count = @file_count + 1
 END

Conclusion

I hope you find this script helpful. Please let me know if you run into any issues with it or if it makes your life easier. Please keep in mind that scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.

A Busy/Accidental DBA’s Guide to Managing VLFs

Posted on by David Levy

Introduction

Properly managing VLFs can make or break the performance of your databases. There is a ton of information out there on the proper management of VLFs, but nothing I have found that tries to boil it down to the most important parts. So here it is, my attempt at A Busy/Accidental DBA’s Guide to Managing VLFs.

What are VLFs?

When SQL Server allocates new space in a log file it does it using Virtual Log Files (VLFs), meaning every growth of a transaction log file is made of 4 or more VLFs.  Think of VLFs as small files within the file that are easier for SQL Server to manage than one large file. (There really is a lot more to it than that but rather than lift from BOL I will refer you to this page for a more detailed explanation.)

Why Manage VLFs?

Having too many or in some cases not enough VLFs can cause sluggish database performance. I have also heard cases of database recovery taking far longer than expected when a log file contains too many VLFs.

How Many VLFs Should I have?

To quote someone much wiser: “It depends”. I use 50 VLFs as my rule of thumb because it is much easier to have a simple rule and it is a safe number in most cases. I do suggest reading this article: Transaction Log VLFs – too many or too few? before committing to a number of your own, especially if you are working with VLDBs.

How do I Manage VLFs?

Managing VLFs is a 2 step process. Step 1 is figuring out how many VLFs you have in each of your transaction logs. Step 2 is deciding on what number of VLFs is acceptable to you and shrinking and growing the log files to get them back under your threshold.  I have included scripts below that will help you identify and remediate high VLF counts. They probably could be wrapped up into a single script but I prefer to have control of what is running when so I can monitor for any issues the maintenance might cause.

Many people also add a step 3 where they increase the auto-growth increment of their database. I tend to avoid raising the auto-growth unless the database is new. The log should only grow very rarely on a mature database; constantly having to address VLFs in a particular database’s log could be a sign of a larger problem like auto-shrink being turned on.

What if I Just Shrink the Log and Let it Grow Back?

There is a misconception that shrinking a log and increasing the auto-growth is enough to remediate high VLF counts. While shrinking a log file may lower VLF counts temporarily, they will come right back when the log file grows back. This article: Transaction Log VLFs – too many or too few? lays out how many VLFs will be added based on the auto-growth increment.  Rephrased from the article:

  •       If the file growth is up to 64MB the new portion of the log file will contain 4 VLFs
  •       If the file growth is more than 64MB and up to 1GB the new portion of the log file will contain 8 VLFs
  •       If the file growth larger than 1GB = 16VLFs

Based on that, if an 80GB log with 100 VLFs was shrunk to remove VLFs then allowed to auto-grow back to 80GB with a larger auto-growth increment, say 4GB, the log would contain 20*16 = 320 VLFs.

How Many VLFs are in My Databases?

This script will return the VLF count for each database on the server it is run on. I am not sure of the origins of the script but I can say it works for me.  If you know or are the original author of this script please let me know so I can give proper credit or replace the script with a link to a more current version.

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
DECLARE @query varchar(1000),
@dbname varchar(1000),
@count int

SET NOCOUNT ON

DECLARE csr CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT name
FROM master.dbo.sysdatabases

CREATE TABLE ##loginfo
(
dbname varchar(100),
num_of_rows int)

OPEN csr

FETCH NEXT FROM csr INTO @dbname

WHILE (@@fetch_status &lt;&gt; -1)
BEGIN

CREATE TABLE #log_info
(
fileid tinyint,
file_size bigint,
start_offset bigint,
FSeqNo int,
[status] tinyint,
parity tinyint,
create_lsn numeric(25,0)
)

SET @query = 'DBCC loginfo (' + '''' + @dbname + ''') '

INSERT INTO #log_info
EXEC (@query)

SET @count = @@rowcount

DROP TABLE #log_info

INSERT ##loginfo
VALUES(@dbname, @count)

FETCH NEXT FROM csr INTO @dbname

END

CLOSE csr
DEALLOCATE csr

SELECT dbname,
num_of_rows
FROM ##loginfo
WHERE num_of_rows &gt;= 50 --My rule of thumb is 50 VLFs. Your mileage may vary.
ORDER BY dbname

DROP TABLE ##loginfo

Updated August 27, 2013. Here is a version that works on SQL 2012:

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
DECLARE @query varchar(1000),
 @dbname varchar(1000),
 @count int

SET NOCOUNT ON

DECLARE csr CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT name
FROM sys.databases

CREATE TABLE ##loginfo
(
 dbname varchar(100),
 num_of_rows int)

OPEN csr

FETCH NEXT FROM csr INTO @dbname

WHILE (@@fetch_status <> -1)
BEGIN

CREATE TABLE #log_info
(
 RecoveryUnitId tinyint,
 fileid tinyint,
 file_size bigint,
 start_offset bigint,
 FSeqNo int,
[status] tinyint,
 parity tinyint,
 create_lsn numeric(25,0)
)

SET @query = 'DBCC loginfo (' + '''' + @dbname + ''') '

INSERT INTO #log_info
EXEC (@query)

SET @count = @@rowcount

DROP TABLE #log_info

INSERT ##loginfo
VALUES(@dbname, @count)

FETCH NEXT FROM csr INTO @dbname

END

CLOSE csr
DEALLOCATE csr

SELECT dbname,
 num_of_rows
FROM ##loginfo
WHERE num_of_rows >= 50 --My rule of thumb is 50 VLFs. Your mileage may vary.
ORDER BY dbname

DROP TABLE ##loginfo

How Do I Lower a Database’s VLF Count?

Once armed with a list of databases that have high VLF counts, the next step is to shrink the logs to as small as possible then grow them back to the original size, ideally in a single growth. This is best done during off-peak times. I wrote the following script to perform those exact steps given the appropriate USE statement. You may have to run it multiple times to get to a low enough VLF count.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
/*USE <db_name>*/ --Set db name before running using drop-down above or this USE statement

DECLARE @file_name sysname,
@file_size int,
@file_growth int,
@shrink_command nvarchar(max),
@alter_command nvarchar(max)

SELECT @file_name = name,
@file_size = (size / 128)
FROM sys.database_files
WHERE type_desc = 'log'

SELECT @shrink_command = 'DBCC SHRINKFILE (N''' + @file_name + ''' , 0, TRUNCATEONLY)'
PRINT @shrink_command
EXEC sp_executesql @shrink_command

SELECT @shrink_command = 'DBCC SHRINKFILE (N''' + @file_name + ''' , 0)'
PRINT @shrink_command
EXEC sp_executesql @shrink_command

SELECT @alter_command = 'ALTER DATABASE [' + db_name() + '] MODIFY FILE (NAME = N''' + @file_name + ''', SIZE = ' + CAST(@file_size AS nvarchar) + 'MB)'
PRINT @alter_command
EXEC sp_executesql @alter_command

In Closing

This has by no means a comprehensive lesson in VLFs or transaction log management, but hopefully enough to get the job done. If you are looking for a more in-depth look at VLFs and transaction logs in general I suggest reading the following articles: Understanding Logging and Recovery in SQL Server, Transaction Log VLFs – too many or too few? and 8 Steps to better Transaction Log throughput.

How to Shrink TempDB in SQL 2005

Posted on by David Levy

Introduction

From time to time you find yourself needing to shrink some space out of TempDB. Shrinking database files is never my first choice but sometimes it is the best I have. Many people think that you cannot shrink TempDB in SQL 2005, but I am going to show you how.

Why would I need to shrink TempDB?

Yesterday afternoon my pager started going crazy because an Ad-Hoc query that needed some tuning filled TempDB on a server. Luckily, the user only impacted their own query so it was easy to quickly identify them and work with the right people to get the query rewritten.

Once the immediate problem was resolved there had to be some cleanup. On this server, TempDB has 32 files (1 per processor) all on the same disk. The full database condition caused all kinds of alerts in our monitoring tools, from drive space alerts to too few growths remaining. There were 3 possible solutions to quiet the alerts:

1. Reboot – There is never a good time to reboot a production server

2. Turn off the Alerts – Not really an option. My preference would be for increasing the sensitivity

3. Shrink TempDB – Not a great option, but the best of the 3

Shrinking TempDB

Once we had decided that we would go ahead and shrink the files in TempDB it seemed like the hard part was done, but after running the following command:

USE [tempdb]

GO

DBCC SHRINKFILE (N’tempdev’ , 5000)

GO

I got back the following:

DBCC SHRINKFILE: Page 1:878039 could not be moved because it is a work file page.

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages

—— ———– ———– ———– ———– ————–

2 1 878040 640000 4672 4672

 

(1 row(s) affected)

 

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

 

“Page could not be moved because it is a work file page.”…grrr. This is a new thing in SQL 2005 caused by the caching that is done in TempDB. I am not going to try to explain here how objects are cached in TempDB, but Kalen Delaney’s Inside Sql Server Series is a great place to learn about it if you are interested (http://www.insidesqlserver.com/books.html). What is important is that the cached objects are tied to a query plan and that by freeing the procedure cache you can make those objects go away, allowing you to shrink your files.

Trying again:

DBCC FREEPROCCACHE

GO

USE [tempdb]

GO

DBCC SHRINKFILE (N’tempdev’ , 5000)

GO

This time it worked:

 

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

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages

—— ———– ———– ———– ———– ————–

2 1 640000 640000 264 264

 

(1 row(s) affected)

 

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

I think I got lucky that the shrink worked on the first try. There will certainly be times when you have to try freeing the procedure cache and shrinking multiple times to get a file to shrink, but eventually it will get the job done.