¿Alguna vez te has encontrado en una situación en la que un usuario ejecutó una consulta en tu SQL Server y este se bloqueó o dejó el servidor inutilizable debido a que la CPU se disparó al 100%? En tales casos, es crucial identificar la causa del bloqueo o el error que ocurrió justo antes de que el uso de la CPU se disparara. Aquí es donde entra en juego el concepto de una caja negra de SQL Server.

Una caja negra de SQL Server es similar a un registrador de datos de vuelo. Registra todas las consultas que se pasan a tu SQL Server, junto con otra información útil como errores. Esta información puede ser invaluable para determinar la causa raíz de los bloqueos del servidor o identificar el error específico que ocurrió.

La caja negra captura la siguiente información:

• Errores y advertencias (Atención y Excepción)
• Ejecución de procedimientos almacenados (RPC: Inicio)
• Ejecución de T-SQL (SQL: Inicio de lote)

Para cada uno de estos eventos, la caja negra captura los siguientes detalles:

• Consulta o error que se ejecutó
• Fecha y hora de ejecución
• Usuario que ejecutó la consulta o el procedimiento almacenado
• Base de datos en la que ocurrió el evento
• Servidor o estación de trabajo que envió la consulta o causó el error
• Nombre de la aplicación que realizó la consulta

La caja negra escribe esta información en un archivo en fragmentos de 128K. Cuando inicias la caja negra, el archivo inicialmente parece tener 0K hasta que acumula suficientes datos para escribir 128K de información. Esta arquitectura asegura que el proceso de la caja negra sea altamente eficiente y minimiza la utilización de la CPU. Como resultado, puedes ejecutarlo durante períodos prolongados sin preocuparte de que afecte el rendimiento de tu servidor.

Si detienes SQL Server, la caja negra escribirá cualquier dato que tenga en caché en el archivo de traza. Por defecto, el archivo se escribe en el directorio de datos (\Program Files\Microsoft SQL Server\MSSQL\data) y se llama “blackbox.trc”. A medida que el archivo crece hasta 5MB, se crea un nuevo archivo. Además, se crea un nuevo archivo si inicias y detienes SQL Server, sobrescribiendo el archivo antiguo. Es importante eliminar periódicamente los archivos de traza antiguos que puedan haberse creado para liberar espacio en disco.

Ahora que comprendes qué es una caja negra, veamos cómo puedes iniciar una. Para iniciar una caja negra, debes utilizar el procedimiento almacenado del sistema sp_trace_create. Estableces el tipo de traza en el estado especial de traza del lado del servidor 8 para crear un archivo de traza de caja negra.

Aquí tienes un ejemplo de un procedimiento almacenado llamado “startblackbox” que se puede utilizar para iniciar la caja negra bajo demanda:

USE master
GO

CREATE PROC startblackbox
AS
DECLARE @TraceID int
EXEC sp_trace_create @TraceID output, 8
EXEC sp_trace_setstatus @traceID, 1
RETURN
GO

-- Parte opcional de la sintaxis para hacer que la caja negra se inicie al iniciar SQL Server
EXEC sp_procoption startblackbox, 'startup', 'on'
GO

Una vez que tengas el procedimiento almacenado, puedes probarlo en un servidor de desarrollo. La salida de la caja negra se puede utilizar de varias formas. Por ejemplo, puedes utilizar la salida como entrada para el Asistente para la Optimización de Índices para optimizar el rendimiento de tu servidor. El propósito principal de esta traza es proporcionar información valiosa al soporte técnico de Microsoft o para fines de depuración.

Comprender y utilizar la caja negra de SQL Server puede ayudar enormemente en la solución de problemas y la resolución de problemas de rendimiento. Al capturar y analizar las consultas y errores, puedes obtener información sobre las causas raíz de los bloqueos del servidor y optimizar tu base de datos para obtener un mejor rendimiento.