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: )
  • There should only be one leaf level of any SQL Server index, AFAIK. If I am interpreting your question properly, you are describing a composite, clustered index with three levels: 0 (the leaf level), 1 (a non-leaf level) or 2 (another non-leaf level). Is problematic level 0 or l? Is PAD_INDEX turned on? Also, I would get the latest Service Pack on that RTM release. – darin strait Oct 30 '12 at 21:45
  • 4
    Why do you suspect a <10MB index is the source of your performance problem? How is this performance problem manifesting? – Mark Storey-Smith Oct 30 '12 at 23:21
  • [darin strait] - I am actually describing a composite index with three leaf levels (0) and two non leaf levels. One of the leaf levels has a avg_page_space_used_in_percent lower than suggested - 68.98%. PAD_INDEX is turned off. – AleksSarg Oct 31 '12 at 14:58

"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
    REPLICATE(CONVERT(nvarchar(max), N'X'), 8001),
    REPLICATE('Y', 4000),
    REPLICATE('Z', 6000)

FROM sys.dm_db_index_physical_stats
    ) AS ddips;


║ 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.

  • Thanks [SQL Kiwi]. I suspected that different allocation units may create additional leaf levels. I am going to experiment with removing LOB data types and see if that helps my performance. – AleksSarg Oct 31 '12 at 15:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.