2

I have a monitoring tool that alerted me to high page splits, 50% of new allocations. After some Google Fu, I found the following query to look and see:

SELECT IOS.index_id, 
       O.NAME                       AS OBJECT_NAME, 
       I.NAME                       AS INDEX_NAME, 
       IOS.leaf_allocation_count    AS PAGE_SPLIT_FOR_INDEX, 
       IOS.nonleaf_allocation_count PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT 
FROM 
<db>.sys.Dm_db_index_operational_stats(Db_id(N'DB_NAME'), NULL, NULL, NULL) IOS
JOIN <db>.sys.indexes I 
  ON IOS.index_id = I.index_id 
     AND IOS.object_id = I.object_id 
JOIN <db>.sys.objects O 
  ON IOS.object_id = O.object_id 
WHERE  o.NAME NOT LIKE 'sys%' 
       AND i.NAME IS NOT NULL 
       AND IOS.leaf_allocation_count != 0 
       AND IOS.nonleaf_allocation_count != 0 
ORDER  BY IOS.leaf_allocation_count DESC 

The results showed that one index in particular was high in page splits/allocations caused by page splits. This table is empty.

Forgive my ignorance but how can an empty table cause page splits? There are no inserts, updates or deletes of the table.

SQL Server 9.0.5057

Any thoughts or direction would be appreciated.

  • 1
    This sounds like a better question for the vendor of the monitoring tool, no? – Erik Darling Jun 29 '17 at 15:43
  • Possibly. I tracked the splits and results of the above query and the numbers keep increasing which I probably should have included in my OP. But I guess my larger question is if it's possible for an empty table to continue to have page splits and if so, how? – jbkelly Jun 29 '17 at 15:48
  • How are you verifying that the table is empty? Do you mean it has 0 rows? If so, is it a HEAP? – Erik Darling Jun 29 '17 at 15:57
  • sp_spaceused 0 rows, 0 reserved, 0 data, 0 index_size, 0 unused. Has clustered index from before my time. Page_count is 0. – jbkelly Jun 29 '17 at 16:03
  • In that case I'd contact the vendor. It sounds like something might be amok with their query, or with the way they're evaluating high page splits. – Erik Darling Jun 29 '17 at 16:04
0

if DB_ID(N'DB_NAME') returns null you will get object_id's from all databases. Try running from the target database:

use db_name


SELECT IOS.index_id, 
       O.NAME                       AS OBJECT_NAME, 
       I.NAME                       AS INDEX_NAME, 
       IOS.leaf_allocation_count    AS PAGE_SPLIT_FOR_INDEX, 
       IOS.nonleaf_allocation_count PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT 
FROM 
sys.dm_db_index_operational_stats(Db_id(), NULL, NULL, NULL) IOS
JOIN sys.indexes I 
  ON IOS.index_id = I.index_id 
     AND IOS.object_id = I.object_id 
JOIN sys.objects O 
  ON IOS.object_id = O.object_id 
WHERE  o.NAME NOT LIKE 'sys%' 
       AND i.NAME IS NOT NULL 
       AND IOS.leaf_allocation_count != 0 
       AND IOS.nonleaf_allocation_count != 0 
ORDER  BY IOS.leaf_allocation_count DESC 

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.