I have a monitoring tool that alerted me to high page splits, 50% of new allocations. 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.
- Page_Split: 3215849
- PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT: 26576 at 8am
- Page_Split: 3211737
- PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT: 26542 at 12 (noon)
This table is empty.
sp_spaceused reports 0 rows, 0 reserved, 0 data, 0 index_size, 0 unused. The table has a clustered index from before my time. Page_count is 0. I do not have the same object name in different databases.
Forgive my ignorance but how can an empty table cause page splits? There are no inserts, updates or deletes on the table.
Is it possible for an empty table to continue to have page splits and if so, how?
SQL Server 9.0.5057