¿Alguna vez te han llamado para decirte que un job o algún otro proceso falla constantemente y el error que reciben menciona que la causa fue un deadlock?
Si eres DBA o estás encargado de alguna base de datos seguro que te ha pasado. Desgraciadamente en ese momento, justo después del error no hay mucho que podamos hacer dado que no tenemos la información referente al deadlock, como qué tablas y queries fueron los causantes. (Spoiler alert: en ocasiones sí es posible, ya veremos en qué casos.)
En este post vamos a crear una rutina para capturar deadlocks de manera rápida y sencilla. Podríamos incluso incluir esta rutina como uno más de nuestros jobs de mantenimiento y tener métricos sobre nuestros deadlocks.
Esta solución se basa en Extended Events, si no estás muy familiarizado con ellos, no te preocupes, seguir los pasos de este post debe ser suficiente.
Primero vamos a crear la tabla dónde guardaremos la información de los deadlocks. Por cierto puedes crear tu tabla en cualquier base de datos, incluso muchas personas optan por usar “master” aunque no lo recomiendo. En lo personal yo creo una base de datos llamada DBA donde tengo mis tablas para almacenar métricos y los stored procedures para obtenerlos.
CREATE TABLE [dbo].[DeadlockGraphInfo]( [CreationDate] [datetime2](3) NULL, [DeadlockInfo] [xml] NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO CREATE CLUSTERED INDEX [CIX_CreationDate] ON [dbo].[DeadlockGraphInfo] ( [CreationDate] DESC ) ON [PRIMARY] GO
Una vez creada la tabla vamos a crear el stored procedure que ejecutaremos en nuestro job y que extraerá la información de los deadlocks de nuestra sesión de Extended Events y la guardará en la tabla que creamos en el paso anterior.
CREATE PROCEDURE [dbo].[CaptureDeadlockInfo]
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM [dbo].[DeadlockGraphInfo]
WHERE CreationDate < DATEADD(MONTH, -3, GETDATE());
SELECT CONVERT(xml, event_data) AS EventData
INTO #XMLData
FROM sys.fn_xe_file_target_read_file(N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\Deadlocks_XE_Session*.xel', NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report'
INSERT INTO [dbo].[DeadlockGraphInfo]
(CreationDate, DeadlockInfo)
SELECT CreationDate, DeadlockInfo
FROM (
SELECT
DATEADD(HH, -5, EventData.value('(event/@timestamp)[1]', 'datetime2(3)')) AS CreationDate,
EventData.query('event/data/value/deadlock') as DeadlockInfo
FROM
#XMLData
) AS A
WHERE
CreationDate > COALESCE((SELECT MAX(CreationDate) FROM DeadlockGraphInfo_Job),'1900-01-01');
IF OBJECT_ID('tempdb..#XMLData') IS NOT NULL
DROP TABLE #XMLData;
END
GO
Nótese la ruta que estoy agregando en el primer párametro de la función sys.fn_xe_file_target_read_file. Aquí debes poner la ruta donde quieras almacenar los archivo que generará la sesión de extended events. Yo uso la ruta default que es donde SQL Server guarda sus logs. Hay varias maneras de verificar cuál es la ruta exacta en tu instancia, para mi la más sencilla es simplemente leer del log con el siguiente query:
EXEC xp_ReadErrorLog 0, 1, N'Logging SQL Server messages' GO
Ahora creamos la sesión de extended events.
CREATE EVENT SESSION [Deadlocks_XE_Session] ON SERVER ADD EVENT sqlserver.xml_deadlock_report ADD TARGET package0.event_file(SET filename=N'Deadlocks_XE_Session',max_file_size=(512),max_rollover_files=(10)) GO ALTER EVENT SESSION [Deadlocks_XE_Session] ON SERVER WITH (STARTUP_STATE=ON) GO ALTER EVENT SESSION [Deadlocks_XE_Session] ON SERVER STATE = START;
El primer comando es para crear la sesión, si quieres cambiar el nombre del archivo recuerda también cambiarlo en el stored procedure.
El segundo comando es para que la sesión se active al momento en que el servicio de SQL arranque en caso de que sea reiniciado.
El tercer comando es para iniciar la sesión que acabamos de crear en ese momento.
Por último creamos un job que ejecute nuestro stored procedure varias veces por día, en los servidores que yo administro lo tengo programado para que se ejecute cada hora, de cualquier modo si necesito ver la información actualizada no es necesario esperar, puedo simplemente ejecutar el stored procedure o el job y una vez completados tendré la información más actual en la tabla.
Para probar el proceso simplemente ejecuta el stored procedure, si tu sistemas no está infestado de deadlocks seguro puede pasar mucho tiempo antes de que se genere alguno, así que vamos a generar uno nosotros para verificar que el proceso funciona.
Después de generar un deadlock manualmente y ejecutar el stored procedure podemos verificar la información en la tabla y debemos ver la información generada en formato xml.
Y aquí está el por qué me encanta este método, podemos simplemente dar click en la información xml y se nos desplegará la información detallada sobre el deadlock en una nueva ventana.
Pero esto no es todo, si guardamos esa información como archivo .xdl y lo abrimos con SQL Server Management Studio podemos ver la gráfica del deadlock. Yo personalmente prefiero la información en XML pero con esta solución tenemos las dos opciones:
De esta manera no sólo podrás hacer troubleshooting de manera más eficiente, también podrás llevar métricos de cuántos deadlocks ocurren al día, por hora, qué momento del día es donde más deadlocks ocurren, crear un job que te informe si ocurrió un deadlock y te mande la información por correo, etc.
¿Recuerdas que al principio del post mencioné que en ocasiones sí se podía saber la información de los deadlocks sin crear todo este proceso? En la sección de extended events hay una sesión que corre por default, esta sesión es llamada system_health y entre las mil cosas que almacena esta la misma información sobre deadlocks que yo estoy capturando con la sesión que creamos en este post.
¿Por qué entonces no usar esa sesión siempre? En algunas instancias esta sesión es detenida, y en otras ocasiones cuando el servidor está realmente ocupado no siempre logra cachar todos los eventos. Es por esta razón que prefiero crear mi sesión aparte que únicamente guarda deadlocks y hasta ahora no me ha pasado que en servidores donde hay muchos deadlocks y mucho procesamiento se pierda de cachar alguno como la sesión system_health que muy frecuentemente no era capaz de guardar todos los deadlocks que ocurrían. Además de lo ya mencionado anteriormente, al tener una sesión aparte podemos asegurarnos de configurarla a nuestro modo en el caso que en nuestra empresa no permitan cambiar o no aprueben encender la sesión de system_health.
Espero este tip les haya servido y nos vemos pronto en un nuevo post.
