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.

SQL Server 2008 Extended Events - Reference List

One of the best kept secrets of SQL Server 2008 has to be the new Extended Events architecture that was added to the troubleshooting toolset. The Extended Events Engine is the foundation on which Extended Events provide detailed information from inside the database engine. I wrote good coverage of Jerome Halmans session at PASS on them for Universal Thread at:

http://www.utcoverage.com/PASS/2008/

However, that only slightly begins to cover the subject. I spent over a month figuring out Extended Events while working on the Extended Events Manager application that won the SQL Heroes contest. Back then the information on Extended Events was very limited. There were numerous errors in the Books Online, and only Bob Beauchemin had blogged about it. (BTW, I owe a great debt of gratitude to Bob for his willingness to test my app, and provide feedback early on. He also provided me some excellent recommendations along the way.)

However as time goes on, things change, and good information is fairly readily available on the subject today. First Paul Randal, recently published an article in Technet Magazine that you can find through his blog post. Then coming in the February/March 2009, the topic will be covered in the Microsoft® SQL Server® 2008 Internals (Pro - Developer) which is available for pre-order now. (Watch for a quick review from me when it actually ships. I plan to dedicate my time to reading this.)

Some web references on XEvents (Extended Events) are:

http://blogs.msdn.com/sqlqueryprocessing/archive/2006/11/12/using-etw-for-sql-server-2005.aspx
Introducing SQL Server Extended Events
MSDN Webcast: SQL Server 2008 Advanced Troubleshooting with Extended Events (Level 200)
Debugging slow response times in SQL Server 2008

SQL Server 2008 Extended Events Manager Build 1.0.1.89

I moved the project from the http://code.msdn.microsoft.com/ExtendedEventManager site to http://www.codeplex.com/ExtendedEventManager/ since this was the release point defined in the SQL Heros contest and I had originally planned to enter this into that contest when I realized that it was running through Sept 1, 2008.

Included in this release is an updated Documentation file, and fixes to all bugs that have been found to date.  This should be a very stable release, and pending any major problems will be the last release until I have the StandAlone library completed and coded with Unit Tests for use with PowerShell and or inside other applications.

This has definately been a long learning experience building.

SQL 2008 Extended Events Manager Build 1.0.1.38

I uploaded a newer version of the app I have been working on tonight.  It now has the ability to create/edit/drop Event Sessions by script.

Extended Events Manager - Build 1.0.0.12 (Released)

For a few weeks I have been focused on Extended Events and how they work in SQL 2008.  One of things missing for the Extended Events is UI support.  In fact there isn't even a SMO namespace for Extended Events as of the RTM of SQL 2008.  This makes using Extended Events fairly complex, and requires that you have a firm understanding of the architecture and Metadata for the packages available.

To simplify this, I have written a C#.NET winforms application that can be used to help manage/explore extended events in SQL 2008.  I posted the source as well as compiled binaries for the first release on the MSDN code gallery as an opensource project.  I am sure that the code is not in keeping with design patterns or best practices, but I am not a C# developer, I am a DBA.  I welcome any comments or advice on this:

http://code.msdn.microsoft.com/ExtendedEventManager/

Suggestion for Extended Events

As you can probably tell, I have been completely focused on Extended Events and learning/documenting how to use them for the last few weeks.  One thing I noticed is that the new Audit functionality in SQL 2008 actually runs on the Extended Events Engine.  This morning a friend chatted me by IM and asked about sp_rename and how the Audit catches it.  It is actually picked up by the Audit as an ALTER of the object.  However, they also needed to know the HostName that originated the request, which is not available in the Audit.  This is because it is also not available as an Action in Extended Events.  This would be a valuable piece of information to have and I have filed this as a suggestion with the SQL Server connect site:

SQL 2008 Extended Events Addition

There is however, a workaround that I provided my friend.  You can create a DDL LOGON trigger that uses the session_id to get the HostName if it exists from the sys.dm_exec_sessions DMV and store it to an Audit table.  Then you can correlate the Audit Event Time, and session_id with the Audit tables Session_id and event time for the Logon to know what HostName was logged onto the Session_ID.  Kind of a hack work around but it will get the job done.

Extended Events and Errors in the BOL

I have been working since the RTM release of SQL Server 2008 with the Extended Events, which are a powerful tool to assist Administrators in troubleshooting problems.  That is, if you can actually get an understanding of how to use them from the little information that actually exists currently (not to worry, I am doing a lot of heavy documentation as I go, and will publish it in the coming weeks).  To compound the issue, the Books Online haven't been maintained from CTP to CTP or even through the RC0 to RTM, and contain numerous errors in them.  I filed a few connect items already:

Errors in the BOL for SQL 2008 DMV sys.dm_xe_map_values

Errors in the BOL for SQL 2008 DMV sys.dm_xe_objects

I goofed on the title for the second one, but I can't figure out how to update it at this point.  I will keep this post updated with other bugs I file as I find them.