Website Technology Profiler

Discover What Any Website Is Built With – Instantly

Identify the technologies behind any website with one simple click you can quickly see what frameworks, platforms, plugins, analytics tools, and hosting providers a site uses.

Our site works as a powerful website technology profiler, scanning the page and generating a full breakdown of all detectable technologies. From CMS platforms and eCommerce systems to JavaScript libraries and server software helping you understand exactly how a website is built.

Perfect for developers, marketers, SEO professionals, and anyone curious about the tech stack behind their favorite websites.

Executive Summary for adventuresinsql.com

5403 Response Time (ms)

200 HTTP Status

14 Scripts

2 Images

10 Links

HTTP/1.1 Protocol

SEO & Content Analysis

Basic Information

Page Title

Adventures In SQL

Meta Description

David Levy\'s blog covering SQL Server topics.

HTML Language

en-US

Robots.txt Present

Sitemap Present

total_urls: 5

SEO Meta Tags

content-type: text/html; charset=UTF-8

Page Content

Adventures In SQL

IntroductionA 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.TroubleshootingTo 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 ForwardOnce 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 ProofTo 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:12345678910111213141516171819202122232425262728293031323334353637USE masterGOIFEXISTS(SELECT*FROMsys.databasesWHERE name ='RecordSizeTest')DROPDATABASE RecordSizeTestGOCREATEDATABASE RecordSizeTestGOUSE RecordSizeTestGOIFEXISTS(SELECT*FROMsys.tablesWHERE name ='TestTable')DROPTABLE dbo.TestTableGOCREATETABLE dbo.TestTable(id intidentity(1,1)NOTNULL,varchar_value varchar(400)NOTNULL,bit_value bitNOTNULL,create_date smalldatetimeNOTNULLDEFAULT(GETDATE()))GOCREATEUNIQUECLUSTEREDINDEX IX_TestTable_id ON dbo.TestTable(id)GOINSERT dbo.TestTable(varchar_value, bit_value)SELECT REPLICATE('TEST',COUNT(*)),COUNT(*)%2FROM dbo.TestTableGO100ALTERDATABASE RecordSizeTest SETREAD_COMMITTED_SNAPSHOTONGOSELECT*FROM dbo.TestTableNow 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:12DBCC IND(RecordSizeTest, TestTable,0)GOBelow 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.12345678910PageFID 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:12345DBCC TRACEON(3604)GODBCCPAGE(RecordSizeTest,1,145,3)GOBelow 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.123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149PAGE: (1:145)BUFFER:BUF @0x0000000090FC6300bpage = 0x000000009018C000 bhash = 0x0000000000000000 bpageno = (1:145)bdbid = 6 breferences = 0 bUse1 = 31047bstat = 0x6c00009 blog = 0x21432159 bnext = 0x0000000000000000PAGE HEADER:Page @0x000000009018C000m_pageId = (1:145) m_headerVersion = 1 m_type = 1m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x200m_objId (AllocUnitId.idObj) = 28 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594039762944 Metadata: PartitionId = 72057594038845440 Metadata: IndexId = 1Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (1:150)pminlen = 13 m_slotCnt = 58 m_freeCnt = 212m_freeData = 7864 m_reservedCnt = 0 m_lsn = (34:159:16)m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0m_tornBits = -436072588 Allocation StatusGAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGEDML (1:7) = NOT MIN_LOGGED Slot 0 Offset 0x60 Length 16Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 16Memory Dump @0x000000002309A0600000000000000000: 10000d00 01000000 00530384 9d040000 †.........S..... Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 1 Slot 0 Column 2 Offset 0x0 Length 0 Length (physical) 0varchar_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) 4create_date = 2010-05-28 14:11:00.000 Slot 0 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (010086470766) Slot 1 Offset 0x70 Length 24Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNSRecord Size = 24 Memory Dump @0x000000002309A0700000000000000000: 30000d00 02000000 01530384 9d040000 †0........S..... 0000000000000010: 01001800 54455354 †††††††††††††††††††....TEST Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 2 Slot 1 Column 2 Offset 0x14 Length 4 Length (physical) 4varchar_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) 4create_date = 2010-05-28 14:11:00.000 Slot 1 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (020068e8b274) Slot 2 Offset 0x88 Length 28Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNSRecord Size = 28 Memory Dump @0x000000002309A0880000000000000000: 30000d00 03000000 00530384 9d040000 †0........S..... 0000000000000010: 01001c00 54455354 54455354 ††††††††††....TESTTEST <snip>...</snipSlot 57 Offset 0x1dc0 Length 248Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNSRecord Size = 248 Memory Dump @0x000000002309BDC00000000000000000: 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) 4id = 58 Slot 57 Column 2 Offset 0x14 Length 228 Length (physical) 228varchar_value = TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTEST Slot 57 Column 3 Offset 0x8 Length 1 (Bit position 0)bit_value = 1 Slot 57 Column 4 Offset 0x9 Length 4 Length (physical) 4create_date = 2010-05-28 14:11:00.000 Slot 57 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (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:12345678910111213141516DBCC TRACEON(3604)GOBEGINTRANSACTIONDELETE dbo.TestTableWHERE id =1GODBCCPAGE(RecordSizeTest,1,145,3)GOROLLBACKDBCCPAGE(RecordSizeTest,1,145,3)GOBelow 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.123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132PAGE: (1:145)BUFFER:BUF @0x0000000090FC6300bpage = 0x000000009018C000 bhash = 0x0000000000000000 bpageno = (1:145)bdbid = 6 breferences = 1 bUse1 = 31887bstat = 0x6c0000b blog = 0x21432159 bnext = 0x0000000000000000PAGE HEADER:Page @0x000000009018C000m_pageId = (1:145) m_headerVersion = 1 m_type = 1m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x2000m_objId (AllocUnitId.idObj) = 28 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594039762944 Metadata: PartitionId = 72057594038845440 Metadata: IndexId = 1Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (1:150)pminlen = 13 m_slotCnt = 58 m_freeCnt = 198m_freeData = 7956 m_reservedCnt = 0 m_lsn = (34:324:10)m_xactReserved = 0 m_xdesId = (0:973) m_ghostRecCnt = 1m_tornBits = -436072588 Allocation StatusGAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x68 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGEDML (1:7) = NOT MIN_LOGGED Slot 0 Offset 0x1ef6 Length 30Record Type = GHOST_DATA_RECORD Record Attributes = NULL_BITMAP VERSIONING_INFORecord Size = 30 Memory Dump @0x000000002309BEF60000000000000000: 5c000d00 01000000 00530384 9d040000 †........S..... 0000000000000010: b0010000 01000000 3dbd0500 0000††††††°.......=½.... Version Information = Transaction Timestamp: 376125Version Pointer: (file 1 page 432 currentSlotId 0)Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 1 Slot 0 Column 2 Offset 0x0 Length 0 Length (physical) 0varchar_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) 4create_date = 2010-05-28 14:11:00.000 Slot 0 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (010086470766) Slot 1 Offset 0x1ede Length 24Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNSRecord Size = 24 Memory Dump @0x000000002309BEDE0000000000000000: 30000d00 02000000 01530384 9d040000 †0........S..... 0000000000000010: 01001800 54455354 †††††††††††††††††††....TEST Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 2 Slot 1 Column 2 Offset 0x14 Length 4 Length (physical) 4varchar_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) 4create_date = 2010-05-28 14:11:00.000 Slot 1 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (020068e8b274) Slot 2 Offset 0x88 Length 28Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNSRecord Size = 28 Memory Dump @0x000000002309A0880000000000000000: 30000d00 03000000 00530384 9d040000 †0........S..... 0000000000000010: 01001c00 54455354 54455354 ††††††††††....TESTTEST <snip>...</snip>Slot 57 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 58 Slot 57 Column 2 Offset 0x14 Length 228 Length (physical) 228varchar_value = TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTEST Slot 57 Column 3 Offset 0x8 Length 1 (Bit position 0)bit_value = 1 Slot 57 Column 4 Offset 0x9 Length 4 Length (physical) 4create_date = 2010-05-28 14:11:00.000 Slot 57 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (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.123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141PAGE: (1:145)BUFFER:BUF @0x0000000090FC6300bpage = 0x000000009018C000 bhash = 0x0000000000000000 bpageno = (1:145)bdbid = 6 breferences = 0 bUse1 = 32941bstat = 0x6c0000b blog = 0x21432159 bnext = 0x0000000000000000PAGE HEADER:Page @0x000000009018C000m_pageId = (1:145) m_headerVersion = 1 m_type = 1m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x6000m_objId (AllocUnitId.idObj) = 28 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594039762944 Metadata: PartitionId = 72057594038845440 Metadata: IndexId = 1Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (1:150)pminlen = 13 m_slotCnt = 58 m_freeCnt = 212m_freeData = 7972 m_reservedCnt = 0 m_lsn = (34:324:13)m_xactReserved = 0 m_xdesId = (0:973) m_ghostRecCnt = 0m_tornBits = -436072588 Allocation StatusGAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGEDML (1:7) = NOT MIN_LOGGED Slot 0 Offset 0x1f14 Length 16Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 16Memory Dump @0x000000002309BF140000000000000000: 10000d00 01000000 00530384 9d040000 †.........S..... Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 1 Slot 0 Column 2 Offset 0x0 Length 0 Length (physical) 0varchar_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) 4create_date = 2010-05-28 14:11:00.000 Slot 0 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (010086470766) Slot 1 Offset 0x1ede Length 24Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNSRecord Size = 24 Memory Dump @0x000000002309BEDE0000000000000000: 30000d00 02000000 01530384 9d040000 †0........S..... 0000000000000010: 01001800 54455354 †††††††††††††††††††....TEST Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 2 Slot 1 Column 2 Offset 0x14 Length 4 Length (physical) 4varchar_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) 4create_date = 2010-05-28 14:11:00.000 Slot 1 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (020068e8b274) <snip>...</snip>Slot 57 Offset 0x1dc0 Length 248Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNSRecord Size = 248 Memory Dump @0x000000002309BDC00000000000000000: 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) 4id = 58 Slot 57 Column 2 Offset 0x14 Length 228 Length (physical) 228varchar_value = TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTEST Slot 57 Column 3 Offset 0x8 Length 1 (Bit position 0)bit_value = 1 Slot 57 Column 4 Offset 0x9 Length 4 Length (physical) 4create_date = 2010-05-28 14:11:00.000 Slot 57 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (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:12CREATEUNIQUECLUSTEREDINDEX IX_TestTable_id ON dbo.TestTable(id)WITH(ONLINE=ON,DROP_EXISTING=ON)GONow that the index has been rebuilt it is time to figure out where it ended up. Time for DBCC IND:12DBCC IND(RecordSizeTest, TestTable,0)GOBased 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.1234567891011PageFID 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:12345DBCC TRACEON(3604)GODBCCPAGE(RecordSizeTest,1,156,3)GOThe 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.123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134PAGE: (1:156)BUFFER:BUF @0x0000000090FC6080bpage = 0x0000000090182000 bhash = 0x0000000000000000 bpageno = (1:156)bdbid = 6 breferences = 0 bUse1 = 34110bstat = 0x6c0000b blog = 0x432159bb bnext = 0x0000000000000000PAGE HEADER:Page @0x0000000090182000m_pageId = (1:156) m_headerVersion = 1 m_type = 1m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x2000m_objId (AllocUnitId.idObj) = 29 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594039828480 Metadata: PartitionId = 72057594038910976 Metadata: IndexId = 1Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (1:160)pminlen = 13 m_slotCnt = 53 m_freeCnt = 680m_freeData = 7406 m_reservedCnt = 0 m_lsn = (34:385:19)m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0m_tornBits = 0 Allocation StatusGAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGEDML (1:7) = NOT MIN_LOGGED Slot 0 Offset 0x60 Length 30Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VERSIONING_INFORecord Size = 30 Memory Dump @0x0000000018A4A0600000000000000000: 50000d00 01000000 d0530384 9d040000 †P.......ÐS..... 0000000000000010: 00000000 00000000 47be0500 0000††††††........G¾.... Version Information = Transaction Timestamp: 376391Version Pointer: NullSlot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 1 Slot 0 Column 2 Offset 0x0 Length 0 Length (physical) 0varchar_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) 4create_date = 2010-05-28 14:11:00.000 Slot 0 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (010086470766) Slot 1 Offset 0x7e Length 38Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFORecord Size = 38 Memory Dump @0x0000000018A4A07E0000000000000000: 70000d00 02000000 d1530384 9d040000 †p.......ÑS..... 0000000000000010: 01001800 54455354 00000000 00000000 †....TEST........ 0000000000000020: 47be0500 0000††††††††††††††††††††††††G¾.... Version Information = Transaction Timestamp: 376391Version Pointer: NullSlot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 2 Slot 1 Column 2 Offset 0x14 Length 4 Length (physical) 4varchar_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) 4create_date = 2010-05-28 14:11:00.000 Slot 1 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (020068e8b274) <snip>...</snip>Version Information = Transaction Timestamp: 376391Version Pointer: NullSlot 52 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 53 Slot 52 Column 2 Offset 0x14 Length 208 Length (physical) 208varchar_value = TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTSlot 52 Column 3 Offset 0x8 Length 1 (Bit position 0)bit_value = 0 Slot 52 Column 4 Offset 0x9 Length 4 Length (physical) 4create_date = 2010-05-28 14:11:00.000 Slot 52 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (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:12345678910111213141516DBCC TRACEON(3604)GOBEGINTRANSACTIONDELETE dbo.TestTableWHERE id =1GODBCCPAGE(RecordSizeTest,1,156,3)GOROLLBACKDBCCPAGE(RecordSizeTest,1,156,3)GOThe 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.123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158PAGE: (1:156)BUFFER:BUF @0x0000000090FC6080bpage = 0x0000000090182000 bhash = 0x0000000000000000 bpageno = (1:156)bdbid = 6 breferences = 1 bUse1 = 35111bstat = 0x6c0000b blog = 0x432159bb bnext = 0x0000000000000000PAGE HEADER:Page @0x0000000090182000m_pageId = (1:156) m_headerVersion = 1 m_type = 1m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x2000m_objId (AllocUnitId.idObj) = 29 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594039828480 Metadata: PartitionId = 72057594038910976 Metadata: IndexId = 1Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (1:160)pminlen = 13 m_slotCnt = 53 m_freeCnt = 680m_freeData = 7474 m_reservedCnt = 0 m_lsn = (34:435:35)m_xactReserved = 0 m_xdesId = (0:992) m_ghostRecCnt = 1m_tornBits = 0 Allocation StatusGAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED PFS (1:1) = 0x68 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGEDML (1:7) = NOT MIN_LOGGED Slot 0 Offset 0x1d14 Length 30Record Type = GHOST_DATA_RECORD Record Attributes = NULL_BITMAP VERSIONING_INFORecord Size = 30 Memory Dump @0x0000000018A4BD140000000000000000: 5c000d00 01000000 d0530384 9d040000 †.......ÐS..... 0000000000000010: c8010000 01000100 a0bf0500 0000††††††È....... ¿.... Version Information = Transaction Timestamp: 376736Version Pointer: (file 1 page 456 currentSlotId 1)Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 1 Slot 0 Column 2 Offset 0x0 Length 0 Length (physical) 0varchar_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) 4create_date = 2010-05-28 14:11:00.000 Slot 0 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (010086470766) Slot 1 Offset 0x1cee Length 38Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFORecord Size = 38 Memory Dump @0x0000000018A4BCEE0000000000000000: 70000d00 02000000 d1530384 9d040000 †p.......ÑS..... 0000000000000010: 01001800 54455354 00000000 00000000 †....TEST........ 0000000000000020: 47be0500 0000††††††††††††††††††††††††G¾.... Version Information = Transaction Timestamp: 376391Version Pointer: NullSlot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 2 Slot 1 Column 2 Offset 0x14 Length 4 Length (physical) 4varchar_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) 4create_date = 2010-05-28 14:11:00.000 Slot 1 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (020068e8b274) <snip>...</snip>KeyHashValue = (3400154ff2a1) Slot 52 Offset 0x1bfc Length 242Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFORecord Size = 242 Memory Dump @0x0000000018A4BBFC0000000000000000: 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: 376391Version Pointer: NullSlot 52 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 53 Slot 52 Column 2 Offset 0x14 Length 208 Length (physical) 208varchar_value = TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTSlot 52 Column 3 Offset 0x8 Length 1 (Bit position 0)bit_value = 0 Slot 52 Column 4 Offset 0x9 Length 4 Length (physical) 4create_date = 2010-05-28 14:11:00.000 Slot 52 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (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.123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190PAGE: (1:156)BUFFER:BUF @0x0000000090FC6080bpage = 0x0000000090182000 bhash = 0x0000000000000000 bpageno = (1:156)bdbid = 6 breferences = 3 bUse1 = 35512bstat = 0x6c0000b blog = 0x432159bb bnext = 0x0000000000000000PAGE HEADER:Page @0x0000000090182000m_pageId = (1:156) m_headerVersion = 1 m_type = 1m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x6000m_objId (AllocUnitId.idObj) = 29 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594039828480 Metadata: PartitionId = 72057594038910976 Metadata: IndexId = 1Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (1:160)pminlen = 13 m_slotCnt = 53 m_freeCnt = 680m_freeData = 7504 m_reservedCnt = 0 m_lsn = (34:435:38)m_xactReserved = 0 m_xdesId = (0:992) m_ghostRecCnt = 0m_tornBits = 0 Allocation StatusGAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGEDML (1:7) = NOT MIN_LOGGED Slot 0 Offset 0x1d32 Length 30Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VERSIONING_INFORecord Size = 30 Memory Dump @0x0000000018A4BD320000000000000000: 50000d00 01000000 d0530384 9d040000 †P.......ÐS..... 0000000000000010: 00000000 00000000 47be0500 0000††††††........G¾.... Version Information = Transaction Timestamp: 376391Version Pointer: NullSlot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 1 Slot 0 Column 2 Offset 0x0 Length 0 Length (physical) 0varchar_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) 4create_date = 2010-05-28 14:11:00.000 Slot 0 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (010086470766) Slot 1 Offset 0x1cee Length 38Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFORecord Size = 38 Memory Dump @0x0000000018A4BCEE0000000000000000: 70000d00 02000000 d1530384 9d040000 †p.......ÑS..... 0000000000000010: 01001800 54455354 00000000 00000000 †....TEST........ 0000000000000020: 47be0500 0000††††††††††††††††††††††††G¾.... Version Information = Transaction Timestamp: 376391Version Pointer: NullSlot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 2 Slot 1 Column 2 Offset 0x14 Length 4 Length (physical) 4varchar_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) 4create_date = 2010-05-28 14:11:00.000 Slot 1 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (020068e8b274) Slot 2 Offset 0xa4 Length 42Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFORecord Size = 42 Memory Dump @0x0000000018A4A0A40000000000000000: 70000d00 03000000 d0530384 9d040000 †p.......ÐS..... 0000000000000010: 01001c00 54455354 54455354 00000000 †....TESTTEST.... 0000000000000020: 00000000 47be0500 0000†††††††††††††††....G¾.... Version Information = Transaction Timestamp: 376391Version Pointer: NullSlot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 3 Slot 2 Column 2 Offset 0x14 Length 8 Length (physical) 8varchar_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) 4create_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 242Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFORecord Size = 242 Memory Dump @0x0000000018A4BBFC0000000000000000: 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: 376391Version Pointer: NullSlot 52 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 53 Slot 52 Column 2 Offset 0x14 Length 208 Length (physical) 208varchar_value = TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTSlot 52 Column 3 Offset 0x8 Length 1 (Bit position 0)bit_value = 0 Slot 52 Column 4 Offset 0x9 Length 4 Length (physical) 4create_date = 2010-05-28 14:11:00.000 Slot 52 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (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:123456789DBCC TRACEON(3604)GODELETE dbo.TestTableWHERE id =1GODBCCPAGE(RecordSizeTest,1,156,3)GOThe 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.123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134PAGE: (1:156)BUFFER:BUF @0x0000000090FC6080bpage = 0x0000000090182000 bhash = 0x0000000000000000 bpageno = (1:156)bdbid = 6 breferences = 1 bUse1 = 35886bstat = 0x6c0000b blog = 0x432159bb bnext = 0x0000000000000000PAGE HEADER:Page @0x0000000090182000m_pageId = (1:156) m_headerVersion = 1 m_type = 1m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x2000m_objId (AllocUnitId.idObj) = 29 m_indexId (AllocUnitId.idInd) = 256 Metadata: AllocUnitId = 72057594039828480 Metadata: PartitionId = 72057594038910976 Metadata: IndexId = 1Metadata: ObjectId = 2105058535 m_prevPage = (0:0) m_nextPage = (1:160)pminlen = 13 m_slotCnt = 53 m_freeCnt = 680m_freeData = 7504 m_reservedCnt = 0 m_lsn = (34:435:43)m_xactReserved = 0 m_xdesId = (0:993) m_ghostRecCnt = 1m_tornBits = 0 Allocation StatusGAM (1:2) = ALLOCATED SGAM (1:3) = ALLOCATED PFS (1:1) = 0x68 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGEDML (1:7) = NOT MIN_LOGGED Slot 0 Offset 0x1d32 Length 30Record Type = GHOST_DATA_RECORD Record Attributes = NULL_BITMAP VERSIONING_INFORecord Size = 30 Memory Dump @0x0000000018A4BD320000000000000000: 5c000d00 01000000 d0530384 9d040000 †.......ÐS..... 0000000000000010: d0010000 01000000 81c00500 0000††††††Ð........À.... Version Information = Transaction Timestamp: 376961Version Pointer: (file 1 page 464 currentSlotId 0)Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 1 Slot 0 Column 2 Offset 0x0 Length 0 Length (physical) 0varchar_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) 4create_date = 2010-05-28 14:11:00.000 Slot 0 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (010086470766) Slot 1 Offset 0x1cee Length 38Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFORecord Size = 38 Memory Dump @0x0000000018A4BCEE0000000000000000: 70000d00 02000000 d1530384 9d040000 †p.......ÑS..... 0000000000000010: 01001800 54455354 00000000 00000000 †....TEST........ 0000000000000020: 47be0500 0000††††††††††††††††††††††††G¾.... Version Information = Transaction Timestamp: 376391Version Pointer: NullSlot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 2 Slot 1 Column 2 Offset 0x14 Length 4 Length (physical) 4varchar_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) 4create_date = 2010-05-28 14:11:00.000 Slot 1 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (020068e8b274) <snip>...</snip>Version Information = Transaction Timestamp: 376391Version Pointer: NullSlot 52 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 53 Slot 52 Column 2 Offset 0x14 Length 208 Length (physical) 208varchar_value = TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTSlot 52 Column 3 Offset 0x8 Length 1 (Bit position 0)bit_value = 0 Slot 52 Column 4 Offset 0x9 Length 4 Length (physical) 4create_date = 2010-05-28 14:11:00.000 Slot 52 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (350070284e19) DBCC execution completed. If DBCC printed error messages, contact your system administrator.ConclusionI 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.;

Network & Infrastructure

DNS & Hosting

IP Address

168.62.48.183

Reverse DNS

Not detected

SSL/TLS Certificate

Issuer

CN=GeoTrust Global TLS RSA4096 SHA256 2022 CA1, O="DigiCert, Inc.", C=US

Protocol Tls13

Expires In 72 days

Technology Stack

Content Management Systems

WordPress WordPress (robots.txt)

JavaScript Frameworks

jQuery

Server Technologies

Generator: All in One SEO (AIOSEO) 4.9.3 PHP (inferred from WordPress)

Services & Integrations

Analytics & Tracking

Google Analytics GA4 Google Tag Manager

E-commerce Platforms

PrestaShop

CDN & Media Providers

Dynamic Analysis & Security

Dynamic JavaScript Analysis

Framework Pattern Analysis Error: The request was canceled due to the configured HttpClient.Timeout of 15 seconds elapsing. Google Tag Manager (Script Analysis) HTML Pattern Analysis Error: The request was canceled due to the configured HttpClient.Timeout of 15 seconds elapsing. Network Analysis Error: The request was canceled due to the configured HttpClient.Timeout of 15 seconds elapsing. Resource Analysis Error: The request was canceled due to the configured HttpClient.Timeout of 15 seconds elapsing. Server Technology: ASP.NET Server Technology: PHP/7.4.30 Web Server: Microsoft-IIS/10.0

Server Headers

ASP.NET

Microsoft-IIS/10.0

PHP/7.4.30

Resource Analysis

External Resource Hosts

adventuresinsql.com

cdn-cookieyes.com

gmpg.org

www.googletagmanager.com

Cookies Observed

UI Frameworks & Libraries

Angular Material (Class Names) Bootstrap (Class Names) D3.js Ionic (Class Names)

Analysis Errors

Analysis Warnings & Errors

The following issues occurred during analysis:

Reverse DNS failed: No such host is known.

Raw JSON Data

{
  "PageContent": "<h3>Adventures In SQL</h3>IntroductionA 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.TroubleshootingTo 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 ForwardOnce 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 ProofTo 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:12345678910111213141516171819202122232425262728293031323334353637USE masterGOIFEXISTS(SELECT*FROMsys.databasesWHERE name ='RecordSizeTest')DROPDATABASE RecordSizeTestGOCREATEDATABASE RecordSizeTestGOUSE RecordSizeTestGOIFEXISTS(SELECT*FROMsys.tablesWHERE name ='TestTable')DROPTABLE dbo.TestTableGOCREATETABLE dbo.TestTable(id              intidentity(1,1)NOTNULL,varchar_value   varchar(400)NOTNULL,bit_value       bitNOTNULL,create_date     smalldatetimeNOTNULLDEFAULT(GETDATE()))GOCREATEUNIQUECLUSTEREDINDEX IX_TestTable_id ON dbo.TestTable(id)GOINSERT  dbo.TestTable(varchar_value, bit_value)SELECT  REPLICATE('TEST',COUNT(*)),COUNT(*)%2FROM    dbo.TestTableGO100ALTERDATABASE RecordSizeTest SETREAD_COMMITTED_SNAPSHOTONGOSELECT*FROM dbo.TestTableNow 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:12DBCC IND(RecordSizeTest, TestTable,0)GOBelow 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.12345678910PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber PartitionID          iam_chain_type       PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID\r\n------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------\r\n1       146         NULL   NULL        2105058535  1           1               72057594038845440    In-row data          10       NULL       0           0           0           0\r\n1       145         1      146         2105058535  1           1               72057594038845440    In-row data          1        0          1           150         0           0\r\n1       150         1      146         2105058535  1           1               72057594038845440    In-row data          1        0          1           153         1           145\r\n1       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:12345DBCC TRACEON(3604)GODBCCPAGE(RecordSizeTest,1,145,3)GOBelow 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.123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149PAGE: (1:145)BUFFER:BUF @0x0000000090FC6300bpage = 0x000000009018C000           bhash = 0x0000000000000000           bpageno = (1:145)bdbid = 6                            breferences = 0                      bUse1 = 31047bstat = 0x6c00009                    blog = 0x21432159                    bnext = 0x0000000000000000PAGE HEADER:Page @0x000000009018C000m_pageId = (1:145)                   m_headerVersion = 1                  m_type = 1m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x200m_objId (AllocUnitId.idObj) = 28     m_indexId (AllocUnitId.idInd) = 256  Metadata: AllocUnitId = 72057594039762944                                 Metadata: PartitionId = 72057594038845440                                 Metadata: IndexId = 1Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (1:150)pminlen = 13                         m_slotCnt = 58                       m_freeCnt = 212m_freeData = 7864                    m_reservedCnt = 0                    m_lsn = (34:159:16)m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0m_tornBits = -436072588              Allocation StatusGAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED           PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = CHANGEDML (1:7) = NOT MIN_LOGGED            Slot 0 Offset 0x60 Length 16Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 16Memory Dump @0x000000002309A0600000000000000000:   10000d00 01000000 00530384 9d040000 †.........S.„.... Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 1                               Slot 0 Column 2 Offset 0x0 Length 0 Length (physical) 0varchar_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) 4create_date = 2010-05-28 14:11:00.000                                     Slot 0 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (010086470766)        Slot 1 Offset 0x70 Length 24Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNSRecord Size = 24                     Memory Dump @0x000000002309A0700000000000000000:   30000d00 02000000 01530384 9d040000 †0........S.„.... 0000000000000010:   01001800 54455354 †††††††††††††††††††....TEST         Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 2                               Slot 1 Column 2 Offset 0x14 Length 4 Length (physical) 4varchar_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) 4create_date = 2010-05-28 14:11:00.000                                     Slot 1 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (020068e8b274)        Slot 2 Offset 0x88 Length 28Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNSRecord Size = 28                     Memory Dump @0x000000002309A0880000000000000000:   30000d00 03000000 00530384 9d040000 †0........S.„.... 0000000000000010:   01001c00 54455354 54455354 ††††††††††....TESTTEST     &lt;snip&gt;...&lt;/snipSlot 57 Offset 0x1dc0 Length 248Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNSRecord Size = 248                    Memory Dump @0x000000002309BDC00000000000000000:   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) 4id = 58                              Slot 57 Column 2 Offset 0x14 Length 228 Length (physical) 228varchar_value = TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTEST                   Slot 57 Column 3 Offset 0x8 Length 1 (Bit position 0)bit_value = 1                        Slot 57 Column 4 Offset 0x9 Length 4 Length (physical) 4create_date = 2010-05-28 14:11:00.000                                     Slot 57 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (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:12345678910111213141516DBCC TRACEON(3604)GOBEGINTRANSACTIONDELETE  dbo.TestTableWHERE   id =1GODBCCPAGE(RecordSizeTest,1,145,3)GOROLLBACKDBCCPAGE(RecordSizeTest,1,145,3)GOBelow 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.123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132PAGE: (1:145)BUFFER:BUF @0x0000000090FC6300bpage = 0x000000009018C000           bhash = 0x0000000000000000           bpageno = (1:145)bdbid = 6                            breferences = 1                      bUse1 = 31887bstat = 0x6c0000b                    blog = 0x21432159                    bnext = 0x0000000000000000PAGE HEADER:Page @0x000000009018C000m_pageId = (1:145)                   m_headerVersion = 1                  m_type = 1m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x2000m_objId (AllocUnitId.idObj) = 28     m_indexId (AllocUnitId.idInd) = 256  Metadata: AllocUnitId = 72057594039762944                                 Metadata: PartitionId = 72057594038845440                                 Metadata: IndexId = 1Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (1:150)pminlen = 13                         m_slotCnt = 58                       m_freeCnt = 198m_freeData = 7956                    m_reservedCnt = 0                    m_lsn = (34:324:10)m_xactReserved = 0                   m_xdesId = (0:973)                   m_ghostRecCnt = 1m_tornBits = -436072588              Allocation StatusGAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED           PFS (1:1) = 0x68 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = CHANGEDML (1:7) = NOT MIN_LOGGED            Slot 0 Offset 0x1ef6 Length 30Record Type = GHOST_DATA_RECORD      Record Attributes =  NULL_BITMAP VERSIONING_INFORecord Size = 30                     Memory Dump @0x000000002309BEF60000000000000000:   5c000d00 01000000 00530384 9d040000 †........S.„.... 0000000000000010:   b0010000 01000000 3dbd0500 0000††††††°.......=½....   Version Information = Transaction Timestamp: 376125Version Pointer: (file 1 page 432 currentSlotId 0)Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 1                               Slot 0 Column 2 Offset 0x0 Length 0 Length (physical) 0varchar_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) 4create_date = 2010-05-28 14:11:00.000                                     Slot 0 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (010086470766)        Slot 1 Offset 0x1ede Length 24Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNSRecord Size = 24                     Memory Dump @0x000000002309BEDE0000000000000000:   30000d00 02000000 01530384 9d040000 †0........S.„.... 0000000000000010:   01001800 54455354 †††††††††††††††††††....TEST         Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 2                               Slot 1 Column 2 Offset 0x14 Length 4 Length (physical) 4varchar_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) 4create_date = 2010-05-28 14:11:00.000                                     Slot 1 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (020068e8b274)        Slot 2 Offset 0x88 Length 28Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNSRecord Size = 28                     Memory Dump @0x000000002309A0880000000000000000:   30000d00 03000000 00530384 9d040000 †0........S.„.... 0000000000000010:   01001c00 54455354 54455354 ††††††††††....TESTTEST     &lt;snip&gt;...&lt;/snip&gt;Slot 57 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 58                              Slot 57 Column 2 Offset 0x14 Length 228 Length (physical) 228varchar_value = TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTEST                   Slot 57 Column 3 Offset 0x8 Length 1 (Bit position 0)bit_value = 1                        Slot 57 Column 4 Offset 0x9 Length 4 Length (physical) 4create_date = 2010-05-28 14:11:00.000                                     Slot 57 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (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.123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141PAGE: (1:145)BUFFER:BUF @0x0000000090FC6300bpage = 0x000000009018C000           bhash = 0x0000000000000000           bpageno = (1:145)bdbid = 6                            breferences = 0                      bUse1 = 32941bstat = 0x6c0000b                    blog = 0x21432159                    bnext = 0x0000000000000000PAGE HEADER:Page @0x000000009018C000m_pageId = (1:145)                   m_headerVersion = 1                  m_type = 1m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x6000m_objId (AllocUnitId.idObj) = 28     m_indexId (AllocUnitId.idInd) = 256  Metadata: AllocUnitId = 72057594039762944                                 Metadata: PartitionId = 72057594038845440                                 Metadata: IndexId = 1Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (1:150)pminlen = 13                         m_slotCnt = 58                       m_freeCnt = 212m_freeData = 7972                    m_reservedCnt = 0                    m_lsn = (34:324:13)m_xactReserved = 0                   m_xdesId = (0:973)                   m_ghostRecCnt = 0m_tornBits = -436072588              Allocation StatusGAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED           PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = CHANGEDML (1:7) = NOT MIN_LOGGED            Slot 0 Offset 0x1f14 Length 16Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP     Record Size = 16Memory Dump @0x000000002309BF140000000000000000:   10000d00 01000000 00530384 9d040000 †.........S.„.... Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 1                               Slot 0 Column 2 Offset 0x0 Length 0 Length (physical) 0varchar_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) 4create_date = 2010-05-28 14:11:00.000                                     Slot 0 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (010086470766)        Slot 1 Offset 0x1ede Length 24Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNSRecord Size = 24                     Memory Dump @0x000000002309BEDE0000000000000000:   30000d00 02000000 01530384 9d040000 †0........S.„.... 0000000000000010:   01001800 54455354 †††††††††††††††††††....TEST         Slot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 2                               Slot 1 Column 2 Offset 0x14 Length 4 Length (physical) 4varchar_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) 4create_date = 2010-05-28 14:11:00.000                                     Slot 1 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (020068e8b274) &lt;snip&gt;...&lt;/snip&gt;Slot 57 Offset 0x1dc0 Length 248Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNSRecord Size = 248                    Memory Dump @0x000000002309BDC00000000000000000:   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) 4id = 58                              Slot 57 Column 2 Offset 0x14 Length 228 Length (physical) 228varchar_value = TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTEST                   Slot 57 Column 3 Offset 0x8 Length 1 (Bit position 0)bit_value = 1                        Slot 57 Column 4 Offset 0x9 Length 4 Length (physical) 4create_date = 2010-05-28 14:11:00.000                                     Slot 57 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (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:12CREATEUNIQUECLUSTEREDINDEX IX_TestTable_id ON dbo.TestTable(id)WITH(ONLINE=ON,DROP_EXISTING=ON)GONow that the index has been rebuilt it is time to figure out where it ended up. Time for DBCC IND:12DBCC IND(RecordSizeTest, TestTable,0)GOBased 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.1234567891011PageFID PagePID     IAMFID IAMPID      ObjectID    IndexID     PartitionNumber PartitionID          iam_chain_type       PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID\r\n------- ----------- ------ ----------- ----------- ----------- --------------- -------------------- -------------------- -------- ---------- ----------- ----------- ----------- -----------\r\n1       157         NULL   NULL        2105058535  1           1               72057594038910976    In-row data          10       NULL       0           0           0           0\r\n1       156         1      157         2105058535  1           1               72057594038910976    In-row data          1        0          1           160         0           0\r\n1       160         1      157         2105058535  1           1               72057594038910976    In-row data          1        0          1           161         1           156\r\n1       161         1      157         2105058535  1           1               72057594038910976    In-row data          1        0          1           162         1           160\r\n1       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:12345DBCC TRACEON(3604)GODBCCPAGE(RecordSizeTest,1,156,3)GOThe 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.123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134PAGE: (1:156)BUFFER:BUF @0x0000000090FC6080bpage = 0x0000000090182000           bhash = 0x0000000000000000           bpageno = (1:156)bdbid = 6                            breferences = 0                      bUse1 = 34110bstat = 0x6c0000b                    blog = 0x432159bb                    bnext = 0x0000000000000000PAGE HEADER:Page @0x0000000090182000m_pageId = (1:156)                   m_headerVersion = 1                  m_type = 1m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x2000m_objId (AllocUnitId.idObj) = 29     m_indexId (AllocUnitId.idInd) = 256  Metadata: AllocUnitId = 72057594039828480                                 Metadata: PartitionId = 72057594038910976                                 Metadata: IndexId = 1Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (1:160)pminlen = 13                         m_slotCnt = 53                       m_freeCnt = 680m_freeData = 7406                    m_reservedCnt = 0                    m_lsn = (34:385:19)m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0m_tornBits = 0                       Allocation StatusGAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED               PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = CHANGEDML (1:7) = NOT MIN_LOGGED            Slot 0 Offset 0x60 Length 30Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VERSIONING_INFORecord Size = 30                     Memory Dump @0x0000000018A4A0600000000000000000:   50000d00 01000000 d0530384 9d040000 †P.......ÐS.„.... 0000000000000010:   00000000 00000000 47be0500 0000††††††........G¾....   Version Information = Transaction Timestamp: 376391Version Pointer: NullSlot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 1                               Slot 0 Column 2 Offset 0x0 Length 0 Length (physical) 0varchar_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) 4create_date = 2010-05-28 14:11:00.000                                     Slot 0 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (010086470766)        Slot 1 Offset 0x7e Length 38Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFORecord Size = 38                     Memory Dump @0x0000000018A4A07E0000000000000000:   70000d00 02000000 d1530384 9d040000 †p.......ÑS.„.... 0000000000000010:   01001800 54455354 00000000 00000000 †....TEST........ 0000000000000020:   47be0500 0000††††††††††††††††††††††††G¾....           Version Information = Transaction Timestamp: 376391Version Pointer: NullSlot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 2                               Slot 1 Column 2 Offset 0x14 Length 4 Length (physical) 4varchar_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) 4create_date = 2010-05-28 14:11:00.000                                     Slot 1 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (020068e8b274)  &lt;snip&gt;...&lt;/snip&gt;Version Information = Transaction Timestamp: 376391Version Pointer: NullSlot 52 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 53                              Slot 52 Column 2 Offset 0x14 Length 208 Length (physical) 208varchar_value = TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTSlot 52 Column 3 Offset 0x8 Length 1 (Bit position 0)bit_value = 0                        Slot 52 Column 4 Offset 0x9 Length 4 Length (physical) 4create_date = 2010-05-28 14:11:00.000                                     Slot 52 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (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:12345678910111213141516DBCC TRACEON(3604)GOBEGINTRANSACTIONDELETE  dbo.TestTableWHERE   id =1GODBCCPAGE(RecordSizeTest,1,156,3)GOROLLBACKDBCCPAGE(RecordSizeTest,1,156,3)GOThe 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.123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158PAGE: (1:156)BUFFER:BUF @0x0000000090FC6080bpage = 0x0000000090182000           bhash = 0x0000000000000000           bpageno = (1:156)bdbid = 6                            breferences = 1                      bUse1 = 35111bstat = 0x6c0000b                    blog = 0x432159bb                    bnext = 0x0000000000000000PAGE HEADER:Page @0x0000000090182000m_pageId = (1:156)                   m_headerVersion = 1                  m_type = 1m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x2000m_objId (AllocUnitId.idObj) = 29     m_indexId (AllocUnitId.idInd) = 256  Metadata: AllocUnitId = 72057594039828480                                 Metadata: PartitionId = 72057594038910976                                 Metadata: IndexId = 1Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (1:160)pminlen = 13                         m_slotCnt = 53                       m_freeCnt = 680m_freeData = 7474                    m_reservedCnt = 0                    m_lsn = (34:435:35)m_xactReserved = 0                   m_xdesId = (0:992)                   m_ghostRecCnt = 1m_tornBits = 0                       Allocation StatusGAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED               PFS (1:1) = 0x68 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = CHANGEDML (1:7) = NOT MIN_LOGGED            Slot 0 Offset 0x1d14 Length 30Record Type = GHOST_DATA_RECORD      Record Attributes =  NULL_BITMAP VERSIONING_INFORecord Size = 30                     Memory Dump @0x0000000018A4BD140000000000000000:   5c000d00 01000000 d0530384 9d040000 †.......ÐS.„.... 0000000000000010:   c8010000 01000100 a0bf0500 0000††††††È....... ¿....   Version Information = Transaction Timestamp: 376736Version Pointer: (file 1 page 456 currentSlotId 1)Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 1                               Slot 0 Column 2 Offset 0x0 Length 0 Length (physical) 0varchar_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) 4create_date = 2010-05-28 14:11:00.000                                     Slot 0 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (010086470766)        Slot 1 Offset 0x1cee Length 38Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFORecord Size = 38                     Memory Dump @0x0000000018A4BCEE0000000000000000:   70000d00 02000000 d1530384 9d040000 †p.......ÑS.„.... 0000000000000010:   01001800 54455354 00000000 00000000 †....TEST........ 0000000000000020:   47be0500 0000††††††††††††††††††††††††G¾....           Version Information = Transaction Timestamp: 376391Version Pointer: NullSlot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 2                               Slot 1 Column 2 Offset 0x14 Length 4 Length (physical) 4varchar_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) 4create_date = 2010-05-28 14:11:00.000                                     Slot 1 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (020068e8b274) &lt;snip&gt;...&lt;/snip&gt;KeyHashValue = (3400154ff2a1)        Slot 52 Offset 0x1bfc Length 242Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFORecord Size = 242                    Memory Dump @0x0000000018A4BBFC0000000000000000:   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: 376391Version Pointer: NullSlot 52 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 53                              Slot 52 Column 2 Offset 0x14 Length 208 Length (physical) 208varchar_value = TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTSlot 52 Column 3 Offset 0x8 Length 1 (Bit position 0)bit_value = 0                        Slot 52 Column 4 Offset 0x9 Length 4 Length (physical) 4create_date = 2010-05-28 14:11:00.000                                     Slot 52 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (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.123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190PAGE: (1:156)BUFFER:BUF @0x0000000090FC6080bpage = 0x0000000090182000           bhash = 0x0000000000000000           bpageno = (1:156)bdbid = 6                            breferences = 3                      bUse1 = 35512bstat = 0x6c0000b                    blog = 0x432159bb                    bnext = 0x0000000000000000PAGE HEADER:Page @0x0000000090182000m_pageId = (1:156)                   m_headerVersion = 1                  m_type = 1m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x6000m_objId (AllocUnitId.idObj) = 29     m_indexId (AllocUnitId.idInd) = 256  Metadata: AllocUnitId = 72057594039828480                                 Metadata: PartitionId = 72057594038910976                                 Metadata: IndexId = 1Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (1:160)pminlen = 13                         m_slotCnt = 53                       m_freeCnt = 680m_freeData = 7504                    m_reservedCnt = 0                    m_lsn = (34:435:38)m_xactReserved = 0                   m_xdesId = (0:992)                   m_ghostRecCnt = 0m_tornBits = 0                       Allocation StatusGAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED               PFS (1:1) = 0x60 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = CHANGEDML (1:7) = NOT MIN_LOGGED            Slot 0 Offset 0x1d32 Length 30Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VERSIONING_INFORecord Size = 30                     Memory Dump @0x0000000018A4BD320000000000000000:   50000d00 01000000 d0530384 9d040000 †P.......ÐS.„.... 0000000000000010:   00000000 00000000 47be0500 0000††††††........G¾....   Version Information = Transaction Timestamp: 376391Version Pointer: NullSlot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 1                               Slot 0 Column 2 Offset 0x0 Length 0 Length (physical) 0varchar_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) 4create_date = 2010-05-28 14:11:00.000                                     Slot 0 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (010086470766)        Slot 1 Offset 0x1cee Length 38Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFORecord Size = 38                     Memory Dump @0x0000000018A4BCEE0000000000000000:   70000d00 02000000 d1530384 9d040000 †p.......ÑS.„.... 0000000000000010:   01001800 54455354 00000000 00000000 †....TEST........ 0000000000000020:   47be0500 0000††††††††††††††††††††††††G¾....           Version Information = Transaction Timestamp: 376391Version Pointer: NullSlot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 2                               Slot 1 Column 2 Offset 0x14 Length 4 Length (physical) 4varchar_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) 4create_date = 2010-05-28 14:11:00.000                                     Slot 1 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (020068e8b274)        Slot 2 Offset 0xa4 Length 42Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFORecord Size = 42                     Memory Dump @0x0000000018A4A0A40000000000000000:   70000d00 03000000 d0530384 9d040000 †p.......ÐS.„.... 0000000000000010:   01001c00 54455354 54455354 00000000 †....TESTTEST.... 0000000000000020:   00000000 47be0500 0000†††††††††††††††....G¾....       Version Information = Transaction Timestamp: 376391Version Pointer: NullSlot 2 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 3                               Slot 2 Column 2 Offset 0x14 Length 8 Length (physical) 8varchar_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) 4create_date = 2010-05-28 14:11:00.000                                     Slot 2 Offset 0x0 Length 0 Length (physical) 0&lt;snip&gt;...&lt;/snip&gt;KeyHashValue = (3400154ff2a1)        Slot 52 Offset 0x1bfc Length 242Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFORecord Size = 242                    Memory Dump @0x0000000018A4BBFC0000000000000000:   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: 376391Version Pointer: NullSlot 52 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 53                              Slot 52 Column 2 Offset 0x14 Length 208 Length (physical) 208varchar_value = TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTSlot 52 Column 3 Offset 0x8 Length 1 (Bit position 0)bit_value = 0                        Slot 52 Column 4 Offset 0x9 Length 4 Length (physical) 4create_date = 2010-05-28 14:11:00.000                                     Slot 52 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (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:123456789DBCC TRACEON(3604)GODELETE  dbo.TestTableWHERE   id =1GODBCCPAGE(RecordSizeTest,1,156,3)GOThe 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.123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134PAGE: (1:156)BUFFER:BUF @0x0000000090FC6080bpage = 0x0000000090182000           bhash = 0x0000000000000000           bpageno = (1:156)bdbid = 6                            breferences = 1                      bUse1 = 35886bstat = 0x6c0000b                    blog = 0x432159bb                    bnext = 0x0000000000000000PAGE HEADER:Page @0x0000000090182000m_pageId = (1:156)                   m_headerVersion = 1                  m_type = 1m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x2000m_objId (AllocUnitId.idObj) = 29     m_indexId (AllocUnitId.idInd) = 256  Metadata: AllocUnitId = 72057594039828480                                 Metadata: PartitionId = 72057594038910976                                 Metadata: IndexId = 1Metadata: ObjectId = 2105058535      m_prevPage = (0:0)                   m_nextPage = (1:160)pminlen = 13                         m_slotCnt = 53                       m_freeCnt = 680m_freeData = 7504                    m_reservedCnt = 0                    m_lsn = (34:435:43)m_xactReserved = 0                   m_xdesId = (0:993)                   m_ghostRecCnt = 1m_tornBits = 0                       Allocation StatusGAM (1:2) = ALLOCATED                SGAM (1:3) = ALLOCATED               PFS (1:1) = 0x68 MIXED_EXT ALLOCATED   0_PCT_FULL                         DIFF (1:6) = CHANGEDML (1:7) = NOT MIN_LOGGED            Slot 0 Offset 0x1d32 Length 30Record Type = GHOST_DATA_RECORD      Record Attributes =  NULL_BITMAP VERSIONING_INFORecord Size = 30                     Memory Dump @0x0000000018A4BD320000000000000000:   5c000d00 01000000 d0530384 9d040000 †.......ÐS.„.... 0000000000000010:   d0010000 01000000 81c00500 0000††††††Ð........À....   Version Information = Transaction Timestamp: 376961Version Pointer: (file 1 page 464 currentSlotId 0)Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 1                               Slot 0 Column 2 Offset 0x0 Length 0 Length (physical) 0varchar_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) 4create_date = 2010-05-28 14:11:00.000                                     Slot 0 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (010086470766)        Slot 1 Offset 0x1cee Length 38Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS VERSIONING_INFORecord Size = 38                     Memory Dump @0x0000000018A4BCEE0000000000000000:   70000d00 02000000 d1530384 9d040000 †p.......ÑS.„.... 0000000000000010:   01001800 54455354 00000000 00000000 †....TEST........ 0000000000000020:   47be0500 0000††††††††††††††††††††††††G¾....           Version Information = Transaction Timestamp: 376391Version Pointer: NullSlot 1 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 2                               Slot 1 Column 2 Offset 0x14 Length 4 Length (physical) 4varchar_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) 4create_date = 2010-05-28 14:11:00.000                                     Slot 1 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (020068e8b274)  &lt;snip&gt;...&lt;/snip&gt;Version Information = Transaction Timestamp: 376391Version Pointer: NullSlot 52 Column 1 Offset 0x4 Length 4 Length (physical) 4id = 53                              Slot 52 Column 2 Offset 0x14 Length 208 Length (physical) 208varchar_value = TESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTTESTSlot 52 Column 3 Offset 0x8 Length 1 (Bit position 0)bit_value = 0                        Slot 52 Column 4 Offset 0x9 Length 4 Length (physical) 4create_date = 2010-05-28 14:11:00.000                                     Slot 52 Offset 0x0 Length 0 Length (physical) 0KeyHashValue = (350070284e19)        DBCC execution completed. If DBCC printed error messages, contact your system administrator.ConclusionI 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.",
  "Url": "https://adventuresinsql.com",
  "IpAddress": "168.62.48.183",
  "ReverseDns": null,
  "Asn": null,
  "Http2": false,
  "Http3": false,
  "ResponseTimeMs": 5403,
  "StatusCode": 200,
  "ServerHeaders": [
    "Microsoft-IIS/10.0",
    "PHP/7.4.30",
    "ASP.NET"
  ],
  "Ssl": {
    "Issuer": "CN=GeoTrust Global TLS RSA4096 SHA256 2022 CA1, O=\"DigiCert, Inc.\", C=US",
    "Subject": "CN=adventuresinsql.com",
    "NotBefore": "2025-11-12T16:00:00-08:00",
    "NotAfter": "2026-04-14T16:59:59-07:00",
    "SubjectAltNames": [
      "DNS Name=adventuresinsql.com"
    ],
    "Hsts": false,
    "Ciphers": [],
    "Protocol": "Tls13",
    "DaysUntilExpiry": 72
  },
  "Cdns": [],
  "HostingHints": [],
  "Cms": [
    "WordPress",
    "WordPress (robots.txt)"
  ],
  "Frameworks": [
    "jQuery"
  ],
  "JsLibraries": [],
  "Analytics": [
    "Google Analytics GA4",
    "Google Tag Manager"
  ],
  "AdsAndMarketing": [],
  "PaymentGateways": [],
  "EcommerceHints": [
    "PrestaShop"
  ],
  "SocialSignals": [],
  "MediaProviders": [],
  "SeoTags": [
    "content-type: text/html; charset=UTF-8"
  ],
  "HtmlLanguage": "en-US",
  "HasRobots": true,
  "HasSitemap": true,
  "SecurityHeaders": [],
  "CookiesObserved": [
    "ARRAffinity=524e4e9fdfdb12a7e1efb670c96842f7aefd05989bc8fb52fa130004333c1d77;Path=/;HttpOnly;Secure;Domain=adventuresinsql.com",
    "ARRAffinitySameSite=524e4e9fdfdb12a7e1efb670c96842f7aefd05989bc8fb52fa130004333c1d77;Path=/;HttpOnly;SameSite=None;Secure;Domain=adventuresinsql.com"
  ],
  "XhrApiHints": [],
  "SitemapCounts": {
    "total_urls": 5
  },
  "ChatWidgets": [],
  "ABTesting": [],
  "PrivacyConsent": [],
  "EmailMarketing": [],
  "WebFonts": [],
  "DynamicJs": [
    "Server Technology: PHP/7.4.30",
    "Server Technology: ASP.NET",
    "Web Server: Microsoft-IIS/10.0",
    "Google Tag Manager (Script Analysis)",
    "Resource Analysis Error: The request was canceled due to the configured HttpClient.Timeout of 15 seconds elapsing.",
    "Network Analysis Error: The request was canceled due to the configured HttpClient.Timeout of 15 seconds elapsing.",
    "Framework Pattern Analysis Error: The request was canceled due to the configured HttpClient.Timeout of 15 seconds elapsing.",
    "HTML Pattern Analysis Error: The request was canceled due to the configured HttpClient.Timeout of 15 seconds elapsing."
  ],
  "ResourceHosts": [
    "cdn-cookieyes.com",
    "www.googletagmanager.com",
    "adventuresinsql.com",
    "gmpg.org"
  ],
  "CssFrameworks": [],
  "BuildTools": [],
  "ServerTech": [
    "Generator: All in One SEO (AIOSEO) 4.9.3",
    "PHP (inferred from WordPress)"
  ],
  "DatabaseHints": [],
  "PageTitle": "Adventures In SQL",
  "MetaDescription": "David Levy\\&#039;s blog covering SQL Server topics.",
  "ImageCount": 2,
  "ScriptCount": 14,
  "LinkCount": 10,
  "Errors": [
    "Reverse DNS failed: No such host is known."
  ],
  "UiFrameworks": [
    "D3.js",
    "Angular Material (Class Names)",
    "Bootstrap (Class Names)",
    "Ionic (Class Names)"
  ]
}

Analysis Complete

Analyzed adventuresinsql.com with 5 technologies detected across 5 categories

Analysis completed in 5403 ms • 2026-03-23 10:59:08 UTC

Website Technology Profiler

Executive Summary for adventuresinsql.com

SEO & Content Analysis

Basic Information

SEO Meta Tags

Page Content

Adventures In SQL

Network & Infrastructure

DNS & Hosting

SSL/TLS Certificate

Technology Stack

Content Management Systems

JavaScript Frameworks

Server Technologies

Services & Integrations

Analytics & Tracking

E-commerce Platforms

CDN & Media Providers

Dynamic Analysis & Security

Dynamic JavaScript Analysis

Server Headers

Resource Analysis

External Resource Hosts

Cookies Observed

UI Frameworks & Libraries

Analysis Errors

Analysis Warnings & Errors

The following issues occurred during analysis:

Raw JSON Data

Analysis Complete

Latest search queries

Older search queries