Connect Item: Enhancements to XEvents page_split Event

One of my "things to blog" was how to use Extended Events in SQL Server 2008 to monitor page splits inside SQL Server.  This was a hot topic amongst MVP's recently, and I thought I would have time to get to it, but I didn't, and another MVP did.  Rather than post the same kind of thing over again, I'll send you to Eladio Rincon's blog post:

Using XEvents (Extended Events) in SQL Server 2008 to detect which queries are causing Page Splits

As I have said before, I think that Extended Events are one of the best kept secrets at Microsoft, and I am glad to see that others feel the same way.  However, like any product in its infancy, there is room to grow.  The page_split event is an extremely powerful event to capture, if only it could return the necessary information to make it really useful.  I submitted a connect feedback item:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=388482

which is targeted at getting the page_split event enhanced.  Now, I can't personally take credit for the idea behind this.  Paul Randal, Adam Machanic, and Greg Linwood all contributed to this information in the MVP groups, I came along after the fact, but it was suggested by a Developer at Microsoft that I put the connect item in to get votes on it, since connect items with votes get attention.

How can this help you out?  Glad you asked.  The connect item is seeking to add additional columns to the events default payload, which is very inexpensive to do if the information is already available at the point in which the event is being fired.  Currently the only columns returned by the event are file_id and page_id.  Both pretty useful, but not to the level that the event could be.  Missing is database_id, which requires a synchronous action to be added to the event payload to collect.  If the allocation_id were added to the event, then we could do a lookup in sys.partitions and easily get not only the object_id, but also the index_id that is being split.  Using these, we can create aggregations across time to identify which indexes in our database(s) are causing the most splits.  Now lets add to this the split_point (begin, middle, end), and split_level (leaf or internal) and we now have a really good idea of what is happening inside our allocation structures, and we can begin to find problems that we might not have other wise found.