Combinar SQL Server Profiler y Windows Performance Log para alertar o encontrar problemas de performance

SQL Profiler puede ayudarte de manera significativa cuando estás haciendo troubleshooting de performance en una base de datos SQL Server. Puedes combinar el Profiler con el funcionamiento de los Performance Logs (perfmon) . Tú puedes poner trazas de profiler (archivos TRC) en combinación con los contadores de performance (archivos BLG) para encontrar los puntos débiles en el performance de la instancia o base de datos.

En el ejemplo siguiente, demostraremos cómo encontrar un deadlock combinando ambas herramientas:

Configurar un contador de Performance :

Abrir el snap-in de Performance Logs and Alerts (herramientas administrativas de Windows)

Expandir Performance Logs and Alerts, botón derecho en Counter Logs y click en New Log Settings.

 Escoger un nombre para el registro (por ejemplo DeadlockLogging)

En el tab de General dar click en Add Counters

Configurar las siguientes caracteristicas:

 o Performance object: SQLServer:Locks

o Seleccionar counters from list: Number of Deadlocks/sec

o Seleccionar Instances from list: _Total , Click en Close

En el tab general, cambiar el intervalo a 1 segundo

En el tab de Log Files, seleccionar Text File (coma delimitada) para el Log File Type

En el tab de Schedule, seleccionar ambos métodos Start y Stop a Manualmente

Click OK (si un mensaje de alerta sale para crear el directorio, dar la opción sí)

Seleccionar el contador que se acaba de crear e iniciarlo, botón derecho y Start

Configurar el SQL Profiler:

Abrir el Profiler de SQL Server (All Programs\Microsoft SQL Server 2005\Performance Tools\SQL Server Profiler)

Crear una nueva traza (File \ New Trace...) y conectarse al servidor

En el cuadro de usar template, seleccionar el template TSQL_Replay

Activar la casilla/opción de Save to File y especificar la ubicación del archivo

Click en Run

 

Crear o provocar un deadlock :

Abrir la consola de SQL Server Management Studio

Abrir un nuevo query con la opción New Query y ejecutar las siguientes instrucciones:

USE tempdb;
GO
CREATE TABLE Product (
ProdID INT,
ProdName VARCHAR(50)
)
GO
INSERT INTO Product VALUES (1, 'Book')
GO
CREATE TABLE ProdOrder (
ProdOrderID INT,
ProdID INT,
OrderCode VARCHAR(10)
)
GO
INSERT INTO ProdOrder VALUES (1, 1, 'BKO1')
GO

Introducir el siguiente query en una nueva ventana pero no correrlo todavia:

USE tempdb;
GO
BEGIN TRAN
UPDATE Product
SET ProdName = 'DVD'
WHERE ProdID = 1
WAITFOR DELAY '00:00:10'
UPDATE ProdOrder
SET OrderCode = 'DVD01'
WHERE ProdOrderID = 1

Abrir una nueva ventana de query y teclear las siguientes instrucciones:
USE tempdb;
GO

BEGIN TRAN
UPDATE ProdOrder
SET OrderCode = 'BK01'
WHERE ProdOrderID = 1
WAITFOR DELAY '00:00:10'
UPDATE Product
SET ProdName = 'BOOK'
WHERE ProdID = 1

Iniciar ambos queries

Despues de algunos segundos de ejecución en los queries, el error siguiente será mostrado: : "Msg 1205, Level 13, State 45, Line 8 Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

Buscar el deadlock en el Profiler :

Abrir la consola de Performance Log and Alerts y detener el contador previamente creado e inicializado "DeadlockLogging"

Cambiarse inmediatamente al SQL Profiler y detener la traza

Cerrar el SQL Profiler y abrir el archivo de traza que se creó. Desde el menú de File, seleccionar Import Performance Data y seleccionar el archivo de log y contador creado por Performance Log and Alerts.

En el cuadro de dialogo de Performance Counter Limit asegurse que todos los contadores están seleccionados y dar click en OK.

Ahora puedes colocarte a través del timebar y ver qué sentencia o instrucción provoco el problema de performance / deadlock.