Monitor Page splits using Extended Events session in SQL Server 2012

There are several ways to configure and start monitoring page splits on SQL Server. But now in 2012, we could make use of extended events to easily track down page splits.

If you do not want to go through the session wizard, I am sharing the script with which you could create the session in one go.

CREATE EVENT SESSION [SherPageSplit] ON SERVER

ADD EVENT sqlserver.page_split(ACTION(sqlserver.client_hostname,sqlserver.client_pid,sqlserver.context_info,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text,sqlserver.username))

ADD TARGET package0.event_file(SET filename=N'M:\Program Files\Microsoft SQL Server\MSSQL11.INST01\MSSQL\Log\SherPageSplit.xel',max_file_size=(100),max_rollover_files=(3))

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

GO

Below I have explained how to configure the extended events to start monitoring page splits through the Management Studio Wizard.

1. Connect to the target SQL Server Instance from SQL Server management studio.
2. Navigate to Management > Extended Events > Sessions
3. Right click “Sessions” and select “New Session Wizard”.
4. Click Next.
5. Provide a Session name and hit next.
6. Hit Next
7. Type page split select “page_split” click ‘>’ and hit Next.
8. Tick checkboxes for client_hostname, client_pid, context_info, database_id, database_name, nt_username, session_id, sql_text, username and any other counters that you are interested in. Hit Next.
9. Hit Next.
10. Tick “Save data to file…” checkbox and verify or change the path for the file. Hit Next.
11. Tick the checkboxes and hit Close button.