Home Dashboard Directory Help
Search

Include page split information in sys.dm_db_index_physical_stats by AaronBertrand


Status: 

Active


58
1
Sign in
to vote
Type: Suggestion
ID: 388403
Opened: 12/16/2008 11:29:58 AM
Access Restriction: Public
0
Workaround(s)
view

Description

It would be great to see page split information in this DMV, as we are relatively blind to this information currently without running extensive diagnostics.
Details
Sign in to post a comment.
Posted by AaronBertrand on 1/15/2012 at 6:15 PM
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
Posted by Microsoft on 1/15/2012 at 6:06 PM
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
Posted by Microsoft on 1/15/2012 at 6:06 PM
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
Posted by aaditya2000 on 6/22/2010 at 4:14 PM
Microsoft:

Any update on page split?

Thanks
Jay
Posted by Microsoft on 12/19/2008 at 2:59 PM
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
Posted by Jonathan Kehayias on 12/16/2008 at 11:45 AM
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.
Sign in to post a workaround.