Monitor using extended event session

Capture blocking queries

Extended events are light weight version of SQL profiler which will not impact any performance of the ongoing transactions.

Below query will capture blocking queries if any query is blocked for more than 60 seconds, which can be configurable [refer highlighted part below]. There is a file path which we have to make sure that it is accessible by sql server account.

1. Set Blocked Process Threshold to a duration beyond which if any query execution time goes then it will be captured. We have set it to 60 seconds. That means if any query is locked on resource beyond 60 seconds extended event can capture.
2. Set up extended event:

   Use Master
   Go
   IF EXISTS (Select 1 from Sys.server_event_sessions Where name = 'CaptureBlockingSession')
   Begin
        DROP EVENT SESSION [CaptureBlockingSession] ON SERVER
   END
   GO
   CREATE EVENT SESSION [CaptureBlockingSession] ON SERVER
   ADD EVENT sqlserver.blocked_process_report
   ADD TARGET package0.event_file(SET
   filename=N'C:\Code\Blocked_Process_report.xel',max_rollover_files=(0))
   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

3. When we are done with step 2 we should see our extended event created as shown below. Right-click on the session and click “start session”. Doing so we are ready capture queries getting locked beyond our set threshold period.
4. Need to wait till we get something like shown as below under “view target data”.