Indexes: Internals and Management in Visual Basic .NET

Encode UPC-A Supplement 5 in Visual Basic .NET Indexes: Internals and Management

6

DBCC PAGE (IndexInternals, 1, 8544, 3); GO RESULT FileId -----1 1 1 1 1 1 1 1 ... 1 1 1 (abbreviated): PageId Row -------- -----8544 0 8544 1 8544 2 8544 3 8544 4 8544 5 8544 6 8544 7 8544 8544 8544 536 537 538

Level -----0 0 0 0 0 0 0 0 0 0 0

EmployeeID (key) ---------------1 2 3 4 5 6 7 8 537 538 539

HEAP RID ------------------0xF500000001000000 0xF500000001000100 0xF500000001000200 0xF500000001000300 0xF500000001000400 0xF500000001000500 0xF500000001000600 0xF500000001000700 0xD211000001001000 0xD211000001001100 0xD211000001001200

KeyHashValue ---------------(010086470766) (020068e8b274) (03000d8f0ecc) (0400b4b7d951) (0500d1d065e9) (06003f7fd0fb) (07005a186c43) (08000c080f1b) (190098ec2ef0) (1a0076439be2) (1b001324275a)

From the output of DBCC PAGE, you can see that the leaf-level page of a nonclustered index on a heap has the index key column value (in this case, the EmployeeID), plus the actual data row s RID. The nal value displayed is called KeyHashValue, which is not actually stored in the index row. It is a xed-length string derived using a hash formula on all the key columns. This value is used to represent the row in certain other tools. One such tool that is discussed in 10 is the sys.dm_tran_locks DMV that shows the locks that are being held. When a lock is held on an index row, the list of locks displays KeyHashValue to indicate which key (or index row) is locked. The RID can be converted to the FileID:PageID:SlotNumber format by using the following function:

CREATE FUNCTION convert_RIDs (@rid BINARY(8)) RETURNS VARCHAR(30) AS BEGIN RETURN ( CONVERT (VARCHAR(5), CONVERT(INT, SUBSTRING(@rid, 6, 1) + SUBSTRING(@rid, 5, 1)) ) + ':' + CONVERT(VARCHAR(10), CONVERT(INT, SUBSTRING(@rid, 4, 1) + SUBSTRING(@rid, 3, 1) + SUBSTRING(@rid, 2, 1) + SUBSTRING(@rid, 1, 1)) ) + ':' + CONVERT(VARCHAR(5), CONVERT(INT, SUBSTRING(@rid, 8, 1) + SUBSTRING(@rid, 7, 1)) ) ) END; GO

With this function, you can nd out the speci c page number on which a row resides. For example, a row with an EmployeeID of 6 has a hexadecimal RID of 0xF500000001000500:

SELECT dbo.convert_RIDs (0xF500000001000500); GO RESULT: 1:245:5

Using the function, this converts to 1:245:5, which is comprised of FileID 1, PageID 245, and SlotNumber 5. To view this speci c page, we can use DBCC PAGE and then review the data on slot 5 (to see if this is in fact the row with EmployeeID of 6):

DBCC PAGE (IndexInternals, 1, 245, 3); GO Slot 5 Column 1 Offset 0x4 Length 4 Length (physical) 4 EmployeeID = 6 Slot 5 Column 2 Offset 0x8 Length 60 Length (physical) 60 LastName = Anderson Slot 5 Column 3 Offset 0x44 Length 58 Length (physical) 58 FirstName = Dreaxjktgvnhye Slot 5 Column 4 Offset 0x7e Length 2 Length (physical) 2 MiddleInitial = Slot 5 Column 5 Offset 0x80 Length 11 Length (physical) 11 SSN = 250-07-9751 Slot 5 Column 6 Offset 0x8b Length 258 Length (physical) 258 OtherColumns = Junk ...

In this case, you have seen the structure of a nonclustered index row in the leaf level of the nonclustered index, as well as how a bookmark lookup is performed using the heap s RID from the nonclustered index to the heap. In terms of navigation, imagine the following query:

SELECT e.* FROM dbo.EmployeeHeap AS e WHERE e.EmployeeID = 27682;

Because this table is a heap, only nonclustered indexes can be used to navigate this data ef ciently. And, in this case, we have a nonclustered index on EmployeeID. The rst step is to go to the root page (as shown in the DBCC IND output earlier, the root page is page 8608 of FileID 1):

6

RESULT: FileId ------1 1 ... 1 1 1 1 1 1 ... 1 1 PageId -----8608 8608 8608 8608 8608 8608 8608 8608 8608 8608 Row ------0 1 49 50 51 52 53 54 147 148 Level ----1 1 1 1 1 1 1 1 1 1 ChildFileId ----------1 1 1 1 1 1 1 1 1 1 ChildPageId ----------8544 8545 8593 8594 8595 8596 8597 8598 8755 8756