I was doing some index maintenance on a SQL Server 2016 SP2 CU2 and I noticed something I couldn't explain.

I've a clustered index on a table of about 66 million rows and rows are added throughout the entire day, so new pages are added. I would expect that the leaf_allocation_count of sys.dm_db_index_operational_stats would increase but the value is always the same, it is never higher than 1.

This is the script I used to determine leaf_allocation_count:

select o.name,i.name,i.type_desc,ios.leaf_insert_count,leaf_delete_count,leaf_ghost_count,leaf_update_count,leaf_allocation_count , range_scan_count,
singleton_lookup_count,forwarded_fetch_count   
from sys.dm_db_index_operational_stats(my_dbid,my_objectid,null,null) ios  
inner join sys.objects o on o.object_id=ios.object_id  
inner join sys.indexes i on i.object_id=o.object_id and i.index_id=ios.index_id

This is the index:

CREATE UNIQUE CLUSTERED INDEX [IX_clustered] ON [dbo].[Orders]
(
    [ORDERNR] ASC,
    [TRANSACT] ASC,
    [TRANSID] ASC
)

This is the table definition:

CREATE TABLE [dbo].[ORDERS](
    [TRANSID] [nvarchar](10) NOT NULL,
    [ITEM] [nvarchar](10) NULL,
    [ORDERNR] [nvarchar](10) NULL,
    [SERIALNR] [nvarchar](10) NULL,
    [TRANSACT] [nvarchar](10) NULL,
    [DATE] [datetime] NULL,
    [TIME] [nvarchar](5) NULL,
    [STATE] [nvarchar](10) NULL,
    [SUPPLIER] [nvarchar](10) NULL,
    CONSTRAINT [idx_TRANSID] PRIMARY KEY NONCLUSTERED 
    (
        [TRANSID] ASC
    )
)

The insert query that is used most of the time:

insert bulk [dbo].[ORDERS] 
(
    [TRANSID] nvarchar(10),
    [ITEM] nvarchar(10),
    [ORDERNR] nvarchar(10),
    [SERIALNR] nvarchar(10),
    [TRANSACT] nvarchar(10),
    [DATE] datetime,
    [TIME] nvarchar(5),
    [STATE] nvarchar(10),
    [SUPPLIER] nvarchar(10)
)

So I wrote a small .Net application to do a 'insert bulk'. When I checked the dmv the leaf_allocation_count did not change. When I do a regular insert then the leaf_allocation_count increases.

Next I created an Extened Event (I found on sqlskills.com) to track the page splits:

IF EXISTS (SELECT 1 
            FROM sys.server_event_sessions 
            WHERE name = 'SQLskills_TrackPageSplits')
        DROP EVENT SESSION [SQLskills_TrackPageSplits] 
        ON SERVER

-- Create the Event Session to track LOP_DELETE_SPLIT transaction_log operations in the server
CREATE EVENT SESSION [SQLskills_TrackPageSplits]
ON    SERVER
ADD EVENT sqlserver.transaction_log(
    WHERE operation = 11  -- LOP_DELETE_SPLIT 
      AND database_id = 6 -- CHANGE THIS BASED ON TOP SPLITTING DATABASE!
)
ADD TARGET package0.histogram(
    SET filtering_event_name = 'sqlserver.transaction_log',
        source_type = 0, -- Event Column
        source = 'alloc_unit_id');
GO

-- Start the Event Session Again
ALTER EVENT SESSION [SQLskills_TrackPageSplits]
ON SERVER
STATE=START;
GO

I ran my .Net application again and looked at the result of the Extended Event:

SELECT
    o.name AS table_name,
    i.name AS index_name,
    tab.split_count,
    i.fill_factor
FROM (    SELECT 
            n.value('(value)[1]', 'bigint') AS alloc_unit_id,
            n.value('(@count)[1]', 'bigint') AS split_count
        FROM
        (SELECT CAST(target_data as XML) target_data
         FROM sys.dm_xe_sessions AS s 
         JOIN sys.dm_xe_session_targets t
             ON s.address = t.event_session_address
         WHERE s.name = 'SQLskills_TrackPageSplits'
          AND t.target_name = 'histogram' ) as tab
        CROSS APPLY target_data.nodes('HistogramTarget/Slot') as q(n)
) AS tab
JOIN sys.allocation_units AS au
    ON tab.alloc_unit_id = au.allocation_unit_id
JOIN sys.partitions AS p
    ON au.container_id = p.partition_id
JOIN sys.indexes AS i
    ON p.object_id = i.object_id
        AND p.index_id = i.index_id
JOIN sys.objects AS o
    ON p.object_id = o.object_id
WHERE o.is_ms_shipped = 0;

Above query returned one row with a split_count of 1. This corresponds with what I see in dm_db_index_operational_stats.

Can somebody explain why the leaf_allocation_count isn't increasing when executing a 'insert bulk'? Are full pages added so no splits are occurring?

This looks like a bug.

I have the same issue with a normal INSERT on 2016 SP2, but not on 2012. Interestingly, an index rebuild will allow leaf_allocation_count to be updated for a while afterwards.

Please note the documentation says leaf_allocation_count corresponds to a leaf-level page split, not the total number of leaf pages for the index. I built testing code that tracks page splits in addition to the operational stats dmv.

Setup:

USE TestDB
GO

DROP TABLE IF EXISTS leafmealone
GO

CREATE TABLE dbo.leafmealone (
ID INT,
bah UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED,
humbug CHAR(1000)
)
GO

DROP TABLE IF EXISTS #ouch
SELECT TOP 1 cntr_value INTO #ouch
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page Splits/sec'
GO

CREATE OR ALTER PROC #dothesplits AS
BEGIN
    DECLARE @t bigint = (SELECT TOP 1 cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Page Splits/sec')
    SELECT @t - cntr_value AS page_splits FROM #ouch
    UPDATE #ouch SET cntr_value = @t
END
GO

CREATE OR ALTER PROC #getinfo AS
BEGIN 
    SELECT o.name,i.type_desc,ios.leaf_insert_count,leaf_delete_count,leaf_ghost_count,leaf_update_count,leaf_allocation_count, ios.nonleaf_allocation_count
    from sys.dm_db_index_operational_stats(DB_ID(),OBJECT_ID('dbo.leafmealone'),null,null) ios  
    inner join sys.objects o on o.object_id=ios.object_id  
    inner join sys.indexes i on i.object_id=o.object_id and i.index_id=ios.index_id
    EXEC dbo.#dothesplits
END
GO

These don't push leaf_allocation_count above 1

INSERT dbo.leafmealone
SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY (SELECT 1)), NEWID(), 'grrrr'
FROM master..spt_values
GO

#getinfo
GO

UPDATE dbo.leafmealone
SET bah = NEWID()
WHERE ID%5 =0
GO

#getinfo
GO

After rebuilding, INSERT will show leaf allocations (splits) correctly, but UPDATE will not. In 2012, both will show an increasing count with each execution, corresponding to the number of page splits.

ALTER INDEX ALL ON dbo.leafmealone REBUILD
GO

#getinfo
GO

INSERT dbo.leafmealone
SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY (SELECT 1)), NEWID(), 'grrrr'
FROM master..spt_values
GO

#getinfo
GO
  • I did some testing with the update statement and in my test the leaf allocations changed correctly. Just the bulk insert behaves incorrectly. – Frederik Vanderhaegen Oct 11 at 6:20

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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