How SQL Server stores indexes on variable length columns

, 2011-01-10

In the last months I had done a lot of performance tuning workshops, 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 the

concrete storage details of an index defined on a variable length column, I have created

the following table and populated 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 on a VARCHAR(255) column,

which is enforced by a UNIQUE CLUSTERED INDEX by SQL Server. In the next step I have

retrieved 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

the trace-flag 3604 to get the DBCC PAGE output):

DBCC TRACEON (3604)

GO

-- Dump out the root index page

DBCC PAGE(VariableClusteredKeyStructure, 1, 458, 1)

GO

Each slot 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 which points

    to the offset 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 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.

-Klaus

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads