{"id":503,"date":"2011-12-08T21:17:00","date_gmt":"2011-12-08T21:17:00","guid":{"rendered":"\/blogs\/jonathan\/post\/Tracking-Problematic-Pages-Splits-in-SQL-Server-2012-Extended-Events-e28093-No-Really-This-Time!.aspx"},"modified":"2017-04-13T14:41:27","modified_gmt":"2017-04-13T18:41:27","slug":"tracking-problematic-pages-splits-in-sql-server-2012-extended-events-no-really-this-time","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/tracking-problematic-pages-splits-in-sql-server-2012-extended-events-no-really-this-time\/","title":{"rendered":"Tracking Problematic Pages Splits in SQL Server 2012 Extended Events \u2013 No Really This Time!"},"content":{"rendered":"

Just over a year ago I blogged about the enhancements that were made to the sqlserver.page_split Event in SQL Server 2012<\/a> to make it easier to identify what the splitting object was and the type of split that was being performed.  Sadly what I discovered writing that post was that even with the extra information about the split type, the event didn\u2019t give you enough information to really focus on the problematic splits that lead to fragmentation and page density issues in the database.  I didn\u2019t do a whole lot with this again until recently when a question was posted by Ami Levin (Blog<\/a> | Twitter<\/a>) on the MVP email list that commented that the page_split event was broken in SQL Server 2012 based on a presentation he\u2019d seen by Guy Glantser (Blog <\/a>| Twitter<\/a>). <\/p>\n

Let me start off by saying, the event isn\u2019t broken, it tracks page splits, but it doesn\u2019t differentiate between an end page split that occurs for an ever increasing index, versus a mid-page split for a random index that leads to fragmentation and page density issues in the database.  Both of these are technically splits inside the storage engine, even if we as DBA\u2019s don\u2019t really care about the end-page split for a increasing key value like an IDENTITY column in the database.  I had Ami pass my information along to the presenter and we traded a few emails on the subject of tracking splits with the specific focus on trying to pull out the mid-page, fragmenting splits.  While going through things for the third time, it dawned on me that this is incredibly simple, based one of the demo\u2019s that was sent to me.  Just over a year ago, I also blogged about tracking transaction log activity in SQL Server 2012<\/a> using the sqlserver.transaction_log event, which can be used to track mid-page splits in a database.<\/p>\n

Last year when I wrote about the sqlserver.transaction_log event, there were 10 columns output by the event in CTP1, but as of RC0, the events output has changed and only 9 columns are output by the event.<\/p>\n

\n
SELECT<\/span> \r\n    oc.name, \r\n    oc.type_name, \r\n    oc.description\r\nFROM<\/span> sys.dm_xe_packages AS<\/span> p\r\nINNER<\/span> JOIN<\/span> sys.dm_xe_objects AS<\/span> o\r\n    ON<\/span> p.guid = o.package_guid\r\nINNER<\/span> JOIN<\/span> sys.dm_xe_object_columns AS<\/span> oc\r\n    ON<\/span> oc.object_name = o.name\r\n        AND<\/span> oc.object_package_guid = o.package_guid\r\nWHERE<\/span> o.name = 'transaction_log'<\/span>\r\n  AND<\/span> oc.column_type = 'data'<\/span>;<\/pre>\n