SQLUNINTERRUPTED

In the previous posts in this series I had discussed about Regular Data Record structures and the special case of Row Forwarding. In this post I will talk about two other special cases and how the record structures are changed or impacted with they are present.

• Row Versioning
• Ghost Records/Ghost Cleanup

Row Versioning

Row Versioning was first introduced in SQL Server 2005. Several SQL Server features (Online Index Rebuild, CheckDB, etc.) and the 2 new row versioning based Isolations Levels (Snapshot Isolation and Read Committed Isolation level) in SQL use Row Versioning. There is a plethora of content on MSDN and other blogs about how the row versioning based Isolations work, it is for this very reason I wont be talking about these features in this post. Instead, what I would concentrate is how row versioning based isolation levels change the record structure in SQL Server.

Row Versioning is applicable to all types of records (data, Index, text) in SQL. When any of the row versioning based Isolation level is enabled on the database, for any update on an existing record in the table, SQL Server creates a last committed copy of the record and put this copy in the version store in the Tempdb database. Any other operation that attempt to read this record (i.e. before the update transaction has committed) would read the record from the version store.

The original record, which remains in the database is modified to append a 14 Byte Versioning Tag. This 14 byte is used to present the Time Stamp of when the record was created and a pointer to the pervious version of the record in the Version Store.

Example

--- create a table for testing for the versioning, insert two records in the table. 

Create Table VersionedRecords (Col1 int Identity(1,1), Col2 varchar(100), Col3 Datetime, Col4 char(50))

go

insert into VersionedRecords(col2, col3, Col4) values (Replicate ('Sourabh', 10), getdate(), Replicate('AAA', 10))

go

insert into VersionedRecords(col2, col3, Col4) values (Replicate ('Agarwal', 14), getdate(), Replicate('BBB', 10))

go

-- Now lets check the Record sizes for this Table.

DBCC IND ('DatabaseName', 'VersionedRecords',-1)

DBCC PAGE(10, 1, 281, 3)

the Record Sizes are as below….

The sizes for the records 143 and 171 bytes respectively, which is in accordance with what I discussed in the part 1 of this blog series.

Now lets enable the Snapshot Isolation on the database.

-- Enable Snapshot Isolation in the DataBase.

Alter Database DatabaseName Set ALLOW_SNAPSHOT_ISOLATION  ON

Once the Database option was enabled, I ran an Update Statement on the Table using another connection.

-- Run the Update transaction so that the Versioned Records are created

begin transaction 

update VersionedRecords set Col2 = Replicate('Sourabh', 14) where Col1 = 1

now if we dump the data page again, we see that the record size for the 1st record changes, while the one for 2nd record remains the same.

we can see that there is a increase of 42 bytes in the record (28 extra bytes to store the new value + 14 bytes for the versioning tag). details of the versioning information (14 byte) will be discussed in another blog at a later time.

Side Pointers

1. Existing records in a table are not immediately modified when Row Versioning is enabled on the data, they are only done when a subsequent update operation happens on the record.
2. All new records added to the table (after changing the database setting) would have the Versioning bytes appended to them.
3. Versioning store is located in the tempdb, and can cause of I/O activity in the TempDB. This can lead to performance issues on the server, if Tempdb storage is not carefully planned.
4. Multiple updates can be done on the same record which can lead to a chain of versioning records. Any subsequent read operation has to traverse this chain to get the correct version of the record. This can be time consuming and hence can lead to performance issues.
5. Version store cleanup is a background process and there can be scenarios where the rate of cleanup is less than the rate of generation of version records. This can lead to increased Tempdb usage.

Ghost Records/Ghost Cleanup

In SQL Server when a record is deleted from the table, the physical storage for the record is not immediately destroyed or deleted, instead the record is marked for deletion and the actual deletion happens at a much later point in time. These records which are marked for Deletion are called Ghost Records. This process significantly increases the performance of the delete operations. How? I will leave it up to you to figure that out.

In the 2 byte record header, the combination of bits 1-3 is used to represent whether the record is ghost record or not.

Bits 1 through 3 Taken as a three-bit value

5 indicates a ghost index record,

6 indicates a ghost data record, and

7 indicates a ghost version record.

The actual deletion of the records is performed by the Ghost Cleanup Task, which runs at an interval 10 seconds (SQL 2008 and above) and 5 seconds (SQL 2005 and below). The deleted records are added to the delete queue of the ghost cleanup task, during a table scan.

Another important thing to remember is that the records are not really deleted, just the space is marked as not being used.