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.
Best Answer
if DB_ID(N'DB_NAME') returns null you will get object_id's from all databases. Try running from the target database: