About Snapshots and Ghosts

A few weeks ago Kalen Delaney presented a 3-day seminar in Israel. Unfortunately, I wasn’t able to attend the seminar, but some of our consultants attended and returned from the seminar with some interesting insights.

The first day was about concurrency control. Among other things, Kalen spoke about the snapshot isolation level and how row versioning works when this isolation level is used.

One question was remained unanswered. Suppose you have an index on column “Col1”, and you update this column while a snapshot transaction is open. The index key is, of course, updated and moved to the new position in the index, and it also now has an additional 14-bytes pointer to the versioning info in tempdb. As long as queries in the snapshot transaction access the table, they will retrieve the old value from the version store. Let’s assume you updated the column from 2 to 5. Now, what happens if a query in the snapshot transaction tries to retrieves the rows with Col1 = 2? The information about the previous value (2) is stored in tempdb, and the pointer is stored in the index under the new value (5). So if a query performs an index seek in order to retrieve only the rows with a value of 2, how would SQL Server know about the old value stored in tempdb?

Let’s look at an example. First, here is the setup:

Transact-SQL

USE master; GO -- Create the SnapshotTest database IF DB_ID (N'SnapshotTest') IS NOT NULL BEGIN ALTER DATABASE SnapshotTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE SnapshotTest; END; GO CREATE DATABASE SnapshotTest; GO ALTER DATABASE SnapshotTest SET ALLOW_SNAPSHOT_ISOLATION ON; GO -- Create the "dbo.Test" table USE SnapshotTest; GO CREATE TABLE dbo.Test ( Col1 INT NOT NULL , Col2 CHAR(5) NOT NULL ); GO -- Populate the "dbo.Test" table with 10 consecutive rows INSERT INTO dbo.Test WITH (TABLOCK) ( Col1 , Col2 ) SELECT TOP (10) Col1 = ROW_NUMBER () OVER (ORDER BY (SELECT NULL) ASC) , Col2 = N'XXXXX' FROM sys.all_columns; GO SELECT Col1 , Col2 FROM dbo.Test ORDER BY Col1 ASC; GO -- Create a non-clustered index on the "Col1" column CREATE NONCLUSTERED INDEX ix_Test_nc_nu_Col1 ON dbo.Test (Col1 ASC); GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

USE

master;

GO

-- Create the SnapshotTest database

IF

DB_ID (N'SnapshotTest') IS NOT NULL

BEGIN

ALTER DATABASE

SnapshotTest

SET

SINGLE_USER

WITH

ROLLBACK IMMEDIATE;

DROP DATABASE

SnapshotTest;

END;

GO

CREATE DATABASE

SnapshotTest;

GO

ALTER DATABASE

SnapshotTest

SET

ALLOW_SNAPSHOT_ISOLATION ON;

GO

-- Create the "dbo.Test" table

USE

SnapshotTest;

GO

CREATE TABLE

dbo.Test

(

Col1 INT NOT NULL ,

Col2 CHAR(5) NOT NULL

);

GO

-- Populate the "dbo.Test" table with 10 consecutive rows

INSERT INTO

dbo.Test WITH (TABLOCK)

(

Col1 ,

Col2

)

SELECT TOP (10)

Col1 = ROW_NUMBER () OVER (ORDER BY (SELECT NULL) ASC) ,

Col2 = N'XXXXX'

FROM

sys.all_columns;

GO

SELECT

Col1 ,

Col2

FROM

dbo.Test

ORDER BY

Col1 ASC;

GO

-- Create a non-clustered index on the "Col1" column

CREATE NONCLUSTERED INDEX

ix_Test_nc_nu_Col1

ON

dbo.Test (Col1 ASC);

GO

Let’s look at the index page before we update the table. First, we use DBCC IND in order to retrieve the index pages:

Transact-SQL

-- Look at the index pages DBCC IND (N'SnapshotTest' , N'dbo.Test' , 2); GO

1

2

3

4

-- Look at the index pages

DBCC IND (N'SnapshotTest' , N'dbo.Test' , 2);

GO

Here are the results (I left only the interesting columns):PageFIDPagePIDPageTypeIndexLevel19010NULL18920

As we can see, there is a single index page (89) that contains the index data. Page #90 is the IAM page, so it’s not relevant for our test. Let’s look at the contents of page #89:

Transact-SQL

DBCC TRACEON (3604); GO DBCC PAGE (N'SnapshotTest' , 1 , 89 , 1); GO

1

2

3

4

5

DBCC TRACEON (3604);

GO

DBCC PAGE (N'SnapshotTest' , 1 , 89 , 1);

GO

Here are the results (only a partial view): Slot 0, Offset 0x60, Length 13, DumpStyle BYTE Record Type = INDEX_RECORD Record Attributes = Record Size = 13 Memory Dump @0x000000000FAFA060 0000000000000000: 06010000 004f0000 00010000 00††††††††…..O……. Slot 1, Offset 0x6d, Length 13, DumpStyle BYTE Record Type = INDEX_RECORD Record Attributes = Record Size = 13 Memory Dump @0x000000000FAFA06D 0000000000000000: 06020000 004f0000 00010001 00††††††††…..O……. Slot 2, Offset 0x7a, Length 13, DumpStyle BYTE Record Type = INDEX_RECORD Record Attributes = Record Size = 13 Memory Dump @0x000000000FAFA07A 0000000000000000: 06030000 004f0000 00010002 00††††††††…..O……. Slot 3, Offset 0x87, Length 13, DumpStyle BYTE Record Type = INDEX_RECORD Record Attributes = Record Size = 13 Memory Dump @0x000000000FAFA087 0000000000000000: 06040000 004f0000 00010003 00††††††††…..O……. Slot 4, Offset 0x94, Length 13, DumpStyle BYTE Record Type = INDEX_RECORD Record Attributes = Record Size = 13 Memory Dump @0x000000000FAFA094 0000000000000000: 06050000 004f0000 00010004 00††††††††…..O……. Slot 5, Offset 0xa1, Length 13, DumpStyle BYTE Record Type = INDEX_RECORD Record Attributes = Record Size = 13 Memory Dump @0x000000000FAFA0A1 0000000000000000: 06060000 004f0000 00010005 00††††††††…..O…….

We can see the first 6 slots in the index page containing the values 1 through 6. I marked the values in red. Each slot is an “INDEX_RECORD”, and the size of each record is 13 bytes. So far so good…

Now, let’s begin our experiment. In a new connection, we are going to start a snapshot transaction and retrieve the data from the “dbo.Test” table:

Transact-SQL

USE SnapshotTest; GO SET TRANSACTION ISOLATION LEVEL SNAPSHOT; GO BEGIN TRANSACTION ReadData; GO SELECT Col1 , Col2 FROM dbo.Test ORDER BY Col1 ASC; GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

USE

SnapshotTest;

GO

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

GO

BEGIN TRANSACTION

ReadData;

GO

SELECT

Col1 ,

Col2

FROM

dbo.Test

ORDER BY

Col1 ASC;

GO

And now, in the first connection, let’s update Col1 from 2 to 5:

Transact-SQL

-- Update the "dbo.Test" table UPDATE dbo.Test SET Col1 = 5 WHERE Col1 = 2; GO

1

2

3

4

5

6

7

8

9

-- Update the "dbo.Test" table

UPDATE

dbo.Test

SET

Col1 = 5

WHERE

Col1 = 2;

GO

If we retrieve the data again inside the snapshot transaction (in the second connection), we will still see the previous value (2), as expected:

Transact-SQL

SELECT Col1 , Col2 FROM dbo.Test ORDER BY Col1 ASC; GO

1

2

3

4

5

6

7

8

SELECT

Col1 ,

Col2

FROM

dbo.Test

ORDER BY

Col1 ASC;

GO

Col1Col21XXXXX2XXXXX3XXXXX4XXXXX5XXXXX6XXXXX7XXXXX8XXXXX9XXXXX10XXXXX

If we try to retrieve only the rows with Col1 = 2, it also works:

Transact-SQL

SELECT Col1 , Col2 FROM dbo.Test WHERE Col1 = 2; GO

1

2

3

4

5

6

7

8

SELECT

Col1 ,

Col2

FROM

dbo.Test

WHERE

Col1 = 2;

GO

Col1Col22XXXXX

How does it work?

Let’s look again at the contents of the index page:

Slot 0, Offset 0x60, Length 13, DumpStyle BYTE Record Type = INDEX_RECORD Record Attributes = Record Size = 13 Memory Dump @0x00000000111EA060 0000000000000000: 06010000 004f0000 00010000 00††††††††…..O……. Slot 1, Offset 0xe2, Length 27, DumpStyle BYTE Record Type = GHOST_INDEX_RECORD Record Attributes = VERSIONING_INFO Record Size = 27 Memory Dump @0x00000000111EA0E2 0000000000000000: 4a020000 004f0000 00010001 00602d00 †J….O…….`-. 0000000000000010: 00010001 009e0100 000000†††††††††††††……….. Slot 2, Offset 0x7a, Length 13, DumpStyle BYTE Record Type = INDEX_RECORD Record Attributes = Record Size = 13 Memory Dump @0x00000000111EA07A 0000000000000000: 06030000 004f0000 00010002 00††††††††…..O……. Slot 3, Offset 0x87, Length 13, DumpStyle BYTE Record Type = INDEX_RECORD Record Attributes = Record Size = 13 Memory Dump @0x00000000111EA087 0000000000000000: 06040000 004f0000 00010003 00††††††††…..O……. Slot 4, Offset 0xfd, Length 27, DumpStyle BYTE Record Type = INDEX_RECORD Record Attributes = VERSIONING_INFO Record Size = 27 Memory Dump @0x00000000111EA0FD 0000000000000000: 46050000 004f0000 00010001 00000000 †F….O………. 0000000000000010: 00000000 009e0100 000000†††††††††††††……….. Slot 5, Offset 0x94, Length 13, DumpStyle BYTE Record Type = INDEX_RECORD Record Attributes = Record Size = 13 Memory Dump @0x00000000111EA094 0000000000000000: 06050000 004f0000 00010004 00††††††††…..O……. Slot 6, Offset 0xa1, Length 13, DumpStyle BYTE Record Type = INDEX_RECORD Record Attributes = Record Size = 13 Memory Dump @0x00000000111EA0A1 0000000000000000: 06060000 004f0000 00010005 00††††††††…..O…….

First, we see that the value “2” has been changed to “5” and moved to its new position. We can also see that it now has the “VERSIONING_INFO” attribute, and it contains the additional 14 bytes for the pointer to the version store (27 bytes instead of 13). This is how SQL Server accesses the old value (2) when the snapshot transaction retrieves all the rows.

But we can also see that there is a ghost index record where the old record was, with the value “2”. The ghost record always appears when an index record is deleted, but it is usually cleaned up by the ghost cleanup task. In our case, the ghost record won’t be removed, because it also has the “VERSIONING_INFO” attribute, and there is an open snapshot transaction that requires that data. It points to the same entry in the version store. This record is used in order to retrieve the old value (2) when we try to retrieve only the rows with Col1 = 2. As soon as we commit or rollback the snapshot transaction, the ghost record will be cleaned up.

#rowversioning #isolationlevels #indexes