leaf_allocation_count in dm_db_index_operational_stats not changing on insert bulk

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_alloca

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

StackExchange Database Administrators Q#219660, answer score: 2