Include page split information in sys.dm_db_index_physical_stats by aaronbertrand

Count alone is not sufficient. Did those happen in the last hour, last 36 hours, last 6 months? Also, it would be quite useful to differentiate between a "bad" page split (DML operation that causes a portion of a page to be moved to a new page) and a less harmful split (new page added to an increasing index such as identity column or dateline column). Jonathan has come up with an alternative approach for this but it seems this could be provided by the DMVs with much less hassle for the end user.

http://www.sqlskills.com/blogs/jonathan/post/Tracking-Problematic-Pages-Splits-in-SQL-Server-2012-Extended-Events-e28093-No-Really-This-Time!.aspx

The DMV sys.dm_db_index_operational_stats provides page split information as follows

"leaf_allocation_count
bigint
Cumulative count of leaf-level page allocations in the index or heap.

For an index, a page allocation corresponds to a page split.

nonleaf_allocation_count
bigint
Cumulative count of page allocations caused by page splits above the leaf level. "


0 = Heap

Please advise us why this won't suffice.

Thanks
Sunil

The DMV sys.dm_db_index_operational_stats provides page split information as follows

"leaf_allocation_count
bigint
Cumulative count of leaf-level page allocations in the index or heap.

For an index, a page allocation corresponds to a page split.

nonleaf_allocation_count
bigint
Cumulative count of page allocations caused by page splits above the leaf level. "


0 = Heap

Please advise us why this won't suffice.

Thanks
Sunil

Microsoft:

Any update on page split?

Thanks
Jay

Hi,

Thank you for sending your feedback to us. We will consider this enhancement in a future release of SQL Server and we will update this connect-item when we have more information on your request.

Again, thank you for you feedback!
- Tobias Ternstrom

This would be incredibly helpful in determining when fill factors have been set properly for indexes. Currently it is a guess and check kind of process where we notice that a index is consistently heavily fragmented, and change the fill factor during a rebuild operation to what seems reasonable, and then go back later and evaluate our success or failure.