Cómo capturar información de bloqueos utilizando Extended Events

Supongamos que estamos monitorizando el estado de un servidor y nos encontramos habitualmente con tipos de espera por bloqueos.

Una opción que tenemos sería empezar a revisar vistas dinámicas. Sin embargo, si el problema aparece a menudo pero de forma aleatoria, es complicado estar revisando el servidor en el momento en el que aparecen.

Podemos capturar las queries involucradas de una manera sencilla con Extended Events. Para crear una sesión de Extended Events, empezaríamos con:  

CREATE EVENT SESSION [nombre] ON SERVER

ADD EVENT evento

ADD TARGET archivo

Ahora bien, ¿Cómo podemos saber qué eventos podemos capturar y cuales nos pueden interesar?

Para una lista de eventos completa, podemos ejecutar:

select * from sys.dm_xe_objects where object_type='event'

En nuestro caso , nos interesa la información de esperas: wait_info.

Para confirmar qué información nos dará wait_info, y si es la necesaria, podemos ejecutar:

SELECT * FROM sys.dm_xe_object_columns WHERE object_name = 'wait_info'

A continuación, la siguiente información que necesitamos es el paquete al que pertenece este evento, para ello, podemos tomar el object_package_guid de cualquiera de las querys anteriores, y ejecutamos:

select * from sys.dm_xe_packages where guid='BD97CC63-3F38-4922-AA93-607BD12E78B2'

 

Con estos dos datos, podemos crear la siguiente sesión:

CREATE EVENT SESSION [nombre] ON SERVER

ADD EVENT sqlos.wait_info

ADD TARGET archivo

En este caso, capturaríamos toda la información de esperas. Sin embargo, sólo queremos capturar la esperas por bloqueos y que duren más de tres segundos.

Para identificar el tipo de espera con su código asociado, podemos ejecutar:

select * from sys.dm_xe_map_values where name='wait_types'

 

Como nos interesan todos los tipos de bloqueos, incluiremos el siguiente filtro:

CREATE EVENT SESSION [nombre] ON SERVER

ADD EVENT sqlos.wait_info (Where duration>2000 and wait_type>1 and wait_type<22)

ADD TARGET archivo

Finalmente, si queremos incluir la información de la query en espera, podemos añadir:

CREATE EVENT SESSION [nombre] ON SERVER

ADD EVENT sqlos.wait_info (ACTION(sqlserver.sql_text),

Where duration>2000 and wait_type>1 and wait_type<22)

ADD TARGET archivo

Raquel Vicente de la Rosa

Ingeniero de Soporte de SQL Server