Tracking PageSplits using Extended Events
In my experience working with significantly large sized databases, PageSplits can be the real culprits for performance problems if Fill Factor is not correctly determined. Having an index on a table (Clustered or Non-Clustered) is pretty useful in pulling the data out from tables but can cause serious problems (beyond the no. of Inserts or updates to happen for every index) if the Fill Factor is not properly managed. So, what is the problem that a wrong fill factor can cause and what way it can impact the performance? Well, when we have an index on a table, consider the Clustered index for a moment, the data should be maintained in a sorted order which means that the logical scan should read the data in the sorted order defined in the index. Imagine a column having a clustered index in a table is having values 1, 2, 4, 5,6,7 (3 is missing). Assume that the page in which these values exists has no more free space to accommodate any new value. At this time, if the application inserts the value 3, as per the indexed (sorted) order, the value 3 should be inserted between 2 & 4. Due to the lack of free space in the page, SQL Server performs an operation called PageSplit which is nothing but allocate a new page to the table and move the some portion of the data to it and insert 3 next to 2. To manage all this, SQL Server has to do extra work for the insert which can increase the wait (both latch and lock) time there by causes blocking etc.
Various Perfmon counters are available to track these PageSplit operations but doesn't give a detailed info. Using the below Extended events session script, you can extract more bit information about the PageSplit operations.
ADD EVENT sqlserver.page_split(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.is_system,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.sql_text)
WHERE ([sqlserver].[database_id]>(4)))
ADD TARGET package0.event_file(SET filename=N'C:\1\PageSplit.xel',max_file_size=(5120),max_rollover_files=(3))
WITH (STARTUP_STATE=OFF)
