"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:
It describes the data that may be stored in each of the three possible allocation unit types:
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.
