Sql-server – Index with multiple leaf levels

clustered-indexdatabase-internalsindexsql server

I have a three-column (int, smallint, smallint) composite clustered index with three leaf levels. My question is how and when does SQL Server create multiple leaf levels (index_level 0) for the same index.

I am experiencing performance issues and I can't get avg_page_space_used_in_percent higher than 70% (leaf page count 1200, fill factor 80).

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (X64) 
    Apr 22 2011 19:23:43 
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

Best Answer

"My question is how and when does SQL Server create multiple leaf levels (index_level 0) for the same index."

The 2008 R2 documentation for sys.dm_db_index_physical_stats includes a link to Table and Index Organization, which shows the following diagram:

Allocation Units Diagram

It describes the data that may be stored in each of the three possible allocation unit types:

Allocation Unit Type Descriptions

Your clustered index does contain three leaf levels, one per allocation unit type. For example:

CREATE TABLE dbo.Example
(
    example_id  integer PRIMARY KEY,
    lob_data    nvarchar(max) NULL,
    padding     varchar(8000) NULL,
    overflow    varchar(8000) NULL
);

INSERT dbo.Example
    (
    example_id,
    lob_data,
    padding,
    overflow
    )
VALUES
    (
    1,
    REPLICATE(CONVERT(nvarchar(max), N'X'), 8001),
    REPLICATE('Y', 4000),
    REPLICATE('Z', 6000)
    );

SELECT
    ddips.index_id,
    ddips.index_type_desc,
    ddips.alloc_unit_type_desc,
    ddips.index_level,
    ddips.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
    (
        DB_ID(), 
        OBJECT_ID('dbo.Example'), 
        1, 
        1, 
        'DETAILED'
    ) AS ddips;

Output:

╔══════════╦═════════════════╦══════════════════════╦═════════════╦════════════════════════════════╗
║ index_id ║ index_type_desc ║ alloc_unit_type_desc ║ index_level ║ avg_page_space_used_in_percent ║
╠══════════╬═════════════════╬══════════════════════╬═════════════╬════════════════════════════════╣
║        1 ║ CLUSTERED INDEX ║ IN_ROW_DATA          ║           0 ║ 50.3953545836422               ║
║        1 ║ CLUSTERED INDEX ║ ROW_OVERFLOW_DATA    ║           0 ║ 74.3019520632567               ║
║        1 ║ CLUSTERED INDEX ║ LOB_DATA             ║           0 ║ 99.0239683716333               ║
╚══════════╩═════════════════╩══════════════════════╩═════════════╩════════════════════════════════╝

Your table contains large object (LOB) columns (MAX or old-style text, ntext or image types) and variable-length column definitions which allow individual rows to exceed the 8060 byte INROW limit.

For rows that exceed 8060 bytes, ROW_OVERFLOW_DATA allocation units will be created. This is often problematic for performance, since row data access requires following an off-page pointer to retrieve the overflowed data.

I would certainly look at the design of the table before worrying too much about how full the pages are on average. Whether you should be concerned about page fullness depends on which allocation unit it refers to.

Related Question