Herramienta de monitoreo de deadlock de SQL Server

Los deadlocks en SQL Server se producen cuando dos o más transacciones, o recopilaciones de consultas SQL, se bloquean entre sí mediante una dependencia cíclica. Esto ocurre cuando las transacciones bloquean los recursos que otras transacciones también necesitan, lo que provoca un bloqueo de SQL Server en una tabla. La consulta en deadlock de SQL no se puede completar hasta que se libera el bloqueo, y tampoco pueden hacerlo las consultas SQL afectadas por el deadlock.

Cuando se recibe una notificación de deadlock de SQL Server, se detecta una transacción denominada “víctima” y otra denominada “sobreviviente”. La víctima se refiere a la sesión que se revierte, y SQL Server la selecciona automáticamente en función de la prioridad del deadlock y los costos de revertirla.

Entre las causas habituales de un deadlock se encuentran el diseño deficiente de la base de datos, la falta de índices, niveles incorrectos de aislamiento y consultas mal escritas. Cuando se produce un bloqueo de SQL Server en una tabla, las operaciones se demoran o, incluso, se detienen por completo. Eventualmente, los deadlock pueden detener por completo el procesamiento dentro del motor de base de datos.

La resolución de deadlocks de SQL Server puede resultar un proceso frustrante y lento, en especial si se trata de varias situaciones complejas de deadlock del servidor. Una situación típica de deadlock se desarrolla de la siguiente manera: un procedimiento almacenado ha realizado un bloqueo en el recurso A (como un objeto, página o fila) y espera a otro procedimiento antes de poder acceder al recurso B. Pero el segundo procedimiento realizó un bloqueo de B y también espera a que el primer procedimiento libere el bloqueo de A.

Para romper este ciclo de espiral descendente, resulta necesario identificar y abordar con rapidez el origen del deadlock.

Existen varias formas de solucionar los deadlocks de SQL Server en función de la situación. SQL Server puede realizar detecciones automáticas mediante un monitor de bloqueos. Como se mencionó antes, tan pronto como se detecta un deadlock, SQL Server elige de manera automática a la “víctima” y revierte esa transacción. A partir de aquí, depende de usted y de su equipo de administración de TI solucionar los deadlocks de SQL Server.

Estas son formas comunes de solucionar un deadlock de SQL Server:

• Índices de cobertura: Al eliminar por completo las búsquedas de marcadores, un índice de cobertura puede reducir las posibilidades de que surjan deadlocks por ese tema.
• Índices externos clave: Reduzca los deadlocks de SQL Server que se originan a partir de la organización en cascada de la integridad de referencia al crear índices que coincidan con las columnas clave externas. Sin un índice externo clave, las acciones de organización en cascada demorarán más y aumentarán las posibilidades de que ocurra un deadlock.
• Acceso por orden de objeto: Para crear deadlocks para realizar pruebas, acceda a los objetos de la base de datos en otro orden. Para evitar estos deadlocks, haga que las transacciones sean lo más breves posible y mantenga los objetos de acceso en el mismo orden lógico.
• Niveles de aislamiento de transacciones: El nivel de aislamiento de la transacción predeterminado en SQL Server, READ COMMITTED, puede generar deadlocks entre escritores y lectores de datos. Evite esto con COMMITTED SNAPSHOT o SNAPSHOT, en donde el lector no bloquea objetos y, en cambio, usa versiones de fila para aislamientos.
• Prioridad de deadlock: Si especifica la prioridad de una transacción como Alta, Normal (predeterminado) o Baja, puede permitir que las sesiones actuales sigan procesándose ante un deadlock de SQL Server.
• Manejo de los errores de las aplicaciones: Con la lógica TRY...CATCH, puede atrapar el número de deadlock. También podría establecer el número de reintentos permitidos a un determinado número, a fin de reducir las posibilidades de que ocurra un circuito infinito.

La mejor manera de evitar deadlocks en SQL Server es emplear la ayuda de una herramienta de monitoreo de deadlocks de SQL. Un monitor de deadlocks de SQL puede detectar y solucionar deadlocks de SQL Server al brindar perspectivas y capacidades que mejoran al monitorear el deadlock.

Existen maneras de detectar el deadlock en SQL Server sin usar un monitor de deadlocks de SQL. Estos métodos se llevan a cabo mediante SQL Server:

• Marcadores de seguimiento: Con los marcadores de seguimiento 1204 o 1222, SQL Server puede monitorear los deadlocks. Los marcadores de seguimiento deben ejecutarse en todo momento, por lo que puede ser difícil detectar proactivamente los deadlocks.
• Contador de rendimiento: Se diseñó para mostrar la cantidad de deadlocks presentes en SQL Server, pero no ofrece detalles acerca de las transacciones involucradas en la sesión.
• Generador de perfiles de SQL Server: También conocido como seguimiento del lado del servidor, puede captar los detalles de deadlock de SQL Server y mostrarlos en un gráfico XML. Este método también debe ejecutarse en todo momento, a fin de evitar que usted tenga que recopilar proactivamente las mediciones.
• Sesión de eventos extendida: SQL Server ejecuta un seguimiento de eventos extendido de manera predeterminada, pero este método consume tiempo, es propenso al error humano y requiere esfuerzo manual.

A pesar de que estos métodos pueden ofrecer algo de alivio, no son tan rápidos, sencillos ni eficientes como usar una herramienta de monitoreo de deadlocks para evitar los que ocurren en SQL Server. Una herramienta de detección de deadlocks de SQL, como SolarWinds SQL Sentry, le permite reconocer cuándo ocurre un deadlock, comprender sus causas raíz y ver las acciones que se pueden tomar para reducir las posibilidades de que este se repita.

Las capacidades de Deadlock Analysis de SQL Sentry brindan métricas detalladas para que pueda resolver los deadlocks del servidor y reducir el tiempo que dedique a restablecer el desempeño máximo de la base de datos. Las métricas incluyen las siguientes:

• Servidor SQL en el que se produjo el deadlock.
• Hora en que se produjo el deadlock de la base de datos.
• SPID víctima: ID del proceso de sesión de la víctima que interviene en el deadlock.
• Host víctima: estación de trabajo perteneciente al subproceso víctima.
• Aplicación víctima: nombre de la aplicación perteneciente al subproceso víctima.
• Base de datos víctima.
• Datos de texto de la víctima.
• XML de deadlock: XML de deadlock capturado.

El área de detalles de bloqueos desglosa el deadlock de la base de datos por tipos de bloqueos específicos, incluidos los responsables y los servidores de espera que intervienen en cada bloqueo. Los detalles incluyen los siguientes:

• SPID: identificador del proceso de sesión del responsable/esperador asociado.
• Plan: abre una sesión del Explorador de planes para ver el plan de consulta asociado.
• Host
• Aplicación
• Monitoreo
• Uso del registro: la cantidad de espacio de registro utilizado por el proceso.
• Prioridad de deadlock: cero (0) o normal es la prioridad definida de forma predeterminada.
• Tiempo de espera: tiempo en milisegundos que se usa para esperar el recurso.
• Hora de inicio de la transacción
• Hora de inicio del último lote
• Hora de finalización del último lote
• Modo/tipo de bloqueo de recursos
• Estado de la tarea
• Nivel actual de aislamiento de la transacción
• Nombre de usuario de la sesión

SolarWinds^® SQL Sentry está diseñada para captar y examinar deadlocks con rapidez y por completo en SQL Server. Sus capacidades de monitoreo de deadlocks de SQL pueden ayudarlo a detectarlos y solucionarlos antes de que causen estragos en sus operaciones de base de datos y a obtener perspectivas factibles acerca de cómo evitar deadlocks similares en el futuro.

SQL Sentry se creó a fin de usar una sesión modificada de eventos extendidos para captar detalles de monitoreo. SQL Sentry está diseñada para mostrar los deadlocks que se detectan en la parte inferior de la pestaña Tendencias, en donde puede encontrar más información crítica sobre deadlocks de SQL Server, como la siguiente:

• Hora en que ocurrió el deadlock
• Cantidad de sesiones involucradas en el deadlock
• Objetos involucrados
• Nombre de la máquina y nombre de usuario

Otra de las métricas de SQL Sentry se llama Impacto para la víctima y está diseñada para mostrar la cantidad de tiempo durante la que una transacción de deadlock se ejecutó y consumió recursos. Dado que el tiempo de transacción implica recursos perdidos, el tiempo de transacción de una víctima de una consulta de deadlock de SQL es un buen indicador del impacto que el deadlock provocó en los servidores SQL y en los usuarios finales.

Hay otras páginas en la pestaña Tendencias, en las que puede acceder a más acciones e información sobre deadlocks de SQL Server. Por ejemplo, SQL Sentry le permite configurar sesiones no predeterminadas en la página Propiedades avanzadas. En la página Detalles de deadlocks de SQL Sentry se muestra un resumen de los deadlocks, con marcas de tiempo, objetos, programas y cantidad de sesiones de la víctima y del sobreviviente. Aquí también puede encontrar datos sobre recursos en deadlock, incluido el nombre del índice, el ID de base de datos, el ID de bloqueo.

SQL Sentry le permite exportar métricas y detalles clave que se pueden usar en conjunto con SQL Server Management Studio (SSMS). SSMS es un software de Microsoft, que cuenta con la capacidad de migrar con SQL Sentry y otras bases de datos. Ya que admite la integración con SSMS, SQL Sentry puede mejorar la comunicación y optimizar la solución de los deadlocks. También puede enviar los detalles del deadlock como un correo electrónico mediante SQL Sentry e incluir el archivo exportado como adjunto.

Aproveche al máximo la capacidad de SQL Sentry de monitorear varios aspectos de las operaciones de SQL Server a fin de obtener una amplia visión de los elementos que afectan el desempeño de la base de datos.