What is a slot array?

  • Article

What is a slot array? Though it sounds like a line of gambling machines at the nearest casino, the slot array in SQL Server serves a crucial role in record management on a page. A database page (and all other page types) in SQL Server is 8KB (8192 bytes). A database page has three basic components to it:

  • The Page Header – this contains the page ID, the ID of the object it belongs to, the ghosted record count, number of slots in the array, Torn Page info, and much more meta data for managing the page. This consumes 96 bytes of the 8KB page size.
  • The Page Body – where the records are stored. This is 8KB minus the page header and whatever space the slot array takes.
  • The Slot Array – an array stored at the end of the page to manage the location of the records on the page.

The slot array is an array of offsets into the page where the records are physically stored. Records are not physically stored on the page in order they are logically sorted in by the index. Maintaining this order with update and delete operations would be cost prohibitive. Instead. the job of the slot array is to store the offsets where the records start in the logical order as dictated by the index – if one exists. So reading the slot array in order and going to the offsets to obtain each record on the page will return the records in the correct order.

To see the slots on a page, use the undocumented DBCC PAGE command. Most of the parameters for the detail parameter (4th parameter) return the slot information (0 does not). The command below will return the slot information on page 189 for the AdventureWorksLT database on my system:

DBCC PAGE(9, 1, 189, 1)
GO

Slot 3, Offset 0x117, Length 61, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 61

Memory Dump @0x000000000CDBC117

0000000000000000: 10003a00 64180100 18b00100 04008903 †..:.d....°....‰.
0000000000000010: 00005c55 21000000 00000000 00000000 †..\U!...........
0000000000000020: 0000c946 72378344 ed48a5b9 e56f0053 †..ÉFr7ƒDíH¥¹åo.S
0000000000000030: 64e00000 0000f994 00000800 00††††††††dà....ù”..... 

You can get the sample databases here:

https://msftdbprodsamples.codeplex.com/

- Jay