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 NextClick Next.
  5. Session NameProvide a Session name and hit next.
  6. Hit NextHit Next
  7. Select Page Split EventType page split select “page_split” click ‘>’ and hit Next.
  8. Select CountersTick 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 NextHit Next.
  10. Save Output to FileTick “Save data to file…” checkbox and verify or change the path for the file. Hit Next.
  11. FinishTick the checkboxes and hit Close button.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.