【indexing-7】How SQL Server stores indexes on variable length columns

In the last months I had done a lot of performance tuning workshops(n. 车间; 工作坊;), and there is almost the same question: How SQL Server stores indexes on variable length columns? Therefore I thought this would be a great topic for a weblog posting. To examine( vt. 检查，调查; ) the concrete( adj.具体的，有形的，实在的，实际的;) storage details of an index defined on a variable length column, I have created the following table and populated(vt. 居住于; 生活于; 移民于; 落户于;) it with 80.000 records:

-- Create a new table
CREATE TABLE Customers
(
    CustomerName VARCHAR(255) NOT NULL PRIMARY KEY,
    Filler CHAR(138) NOT NULL
)
GO

-- Insert 80.000 records
DECLARE @i INT = 1
WHILE (@i <= 80000)
BEGIN
    INSERT INTO Customers VALUES
    (
        ‘CustomerName’ + CAST(@i AS VARCHAR),
        ‘Filler’ + CAST(@i AS VARCHAR)
    )
    
    SET @i += 1
END
GO

As you can see I have created the PRIMARY KEY constraint( n. 约束; 限制; 强制;) on a VARCHAR(255) column, which is enforced(n. 强迫的;) by a UNIQUE CLUSTERED INDEX by SQL Server. In the next step I have retrieved(vt. 取回; 恢复; [计] 检索; 重新得到;) some physical information about the created clustered index by using the DMF sys.dm_db_index_physical_stats:

-- Retrieve physical information about the clustered index
SELECT * FROM sys.dm_db_index_physical_stats
(
    DB_ID(‘VariableClusteredKeyStructure’),
    OBJECT_ID(‘Customers’),
    NULL,
    NULL,
    ‘DETAILED’
)
GO

When you look into the output, you can see that the column min_record_size_in_bytes show you a value of 7 and the column max_record_size_in_bytes shown you the value 28 in the index page. This leads us to the conclusion that the clustered keys are stored as variable length columns inside an index record. So let’s examine an index record on an index page. I’ve used the DBCC IND command to retrieve all pages for our clustered index and stored that output in a little helper table.

 

-- Create a helper table
CREATE TABLE HelperTable
(
  PageFID TINYINT, 
  PagePID INT,   
  IAMFID TINYINT, 
  IAMPID INT, 
  ObjectID INT,
  IndexID TINYINT,
  PartitionNumber TINYINT,
  PartitionID BIGINT,
  iam_chain_type VARCHAR(30),    
  PageType TINYINT, 
  IndexLevel TINYINT,
  NextPageFID TINYINT,
  NextPagePID INT,
  PrevPageFID INT,
  PrevPagePID INT, 
  PRIMARY KEY (PageFID, PagePID)
)
GO

-- Write everything in a table for further analysis
INSERT INTO HelperTable EXEC(‘DBCC IND(VariableClusteredKeyStructure, Customers, 1)’)
GO

-- Retrieve the root index page (1 page)
SELECT * FROM HelperTable
WHERE IndexLevel = 2
GO

In my case SQL Server stored the index root page on the page id 458, which I have dumped out through the DBCC PAGE command (after enabling( adj. 有可能，或有条件完成的;) the trace-flag 3604 to get the DBCC PAGE output):

 [3604]微软的跟踪标记

DBCC TRACEON (3604)
GO
--Dump out the root index page
DBCC PAGE(VariableClusteredKeyStructure, 1, 458, 1)
GO

Each slot(vt. 跟踪，跟着足迹追赶;) in the output represents one index record, like:

26 95020000 0100 0100 1b00 43757374 6f6d6572 4e616d65 31333533

Let’s examine those hex values.

• 26: The first byte represents status bits.
• 95020000: The next 4 bytes are the child-page-id to which this index record points
• 0100: The next 2 bytes are the child-file-id to which this index record points
• 0100: The next 2 bytes are the number of variable length columns
• 1b00: For each variable length column SQL Server stores a 2 byte entry(n. 进入，入场; 入口处，门口; ) which points to the offset( vt. 抵消; 补偿; ) at which the variable length column ends – it’s the same as when you store variable length columns on a regular data page. In this case we have one variable length column, therefore SQL Server has to store one 2 byte offset – byte offset 27 in this case. This means that the next bytes until the byte offset 27 are part of our variable length column – the clustered key.
• 43757374 6f6d6572 4e616d65 31333533: The hex value for our clustered key – the column CustomerName

As you can see from this explanation SQL Server stores variable length index columns in the same format as regular variable length columns on data pages. But you have to be aware that there is a slight( adj.微小的; 细小的; ) overhead(额外开销;额外消耗) with this, because you need 2 additional bytes for storing the count of variable length columns and for each variable length column you need 2 bytes in the variable column offset array. Keep this in mind when you are designing your indexes and when you calculate how many index records fits onto one index page. You can download the whole script from here and play around with it.

DBCC TRACEON 跟踪标记说明
DBCC常用跟踪标记
260：输出有关扩展存储过程动态链接库（DLL）的版本信息
1204：返回参与死锁的锁的类型以及当前受影响的命令
2528：通过DBCC CHECKDB、DBCC CHECKFILEGROUP和DBCC CHECKTABLE禁用对象的并行检查
3205：禁用磁带驱动程序的硬件压缩
3604：将跟踪结果输出的屏幕
3605：将跟踪结果输出的SQL SERVER2000的错误日志文件
8602：停止索引提示功能
8722：停止最优化提示功能
8755：停止锁提示功能