SQL Server 2012 Archives

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

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)

Continue reading “Monitor Page splits using Extended Events session in SQL Server 2012”

Tag: SQL Server 2012

SQL Server 2012 Daily Health Check

Monitor Page splits using Extended Events session in SQL Server 2012