Como resumen de todo, sin querer extenderme demasiado en palabrerías, la DMV registra las causas por las que las conexiones esperan a recursos del sistema operativo como disco, escribir en el log, acceder al procesador, que se liberen bloqueos transaccionales (locks), que se liberen bloqueos sobre recursos (latches), en definitiva, una lista muy bien documentada en la url anterior de msdn2. Recuerda que esta información se registra desde la última vez que se inició la instancia, o desde que un administrador solicitara limpiarlo (DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR);
La DMV
Los valores que buscamos los implementa la siguiente consulta:
SELECT
wait_type
, waiting_tasks_count
, wait_time_ms
, signal_wait_time_ms
FROM
sys.dm_os_wait_stats
Además, de todos los tipos que se reportan, muchos de ellos, son internos del sistema, y otros no tienen mucho sentido como el LAZYWRITER_SLEEP, que es el proceso que espera a escribir operaciones a ficheros de datos (proceso lazywriter). El tema es que cada DBA gestiona su propia lista de valores a excluir, y yo en mi caso pues también tengo mi propia lista (que es suma de cosechas de otros).
La tabla variable
Como he comentado anteriormente, el objetivo es analizar las esperas en el sistema durante un periodo de tiempo… nosotros en Solid tenemos unos trabajos que están capturando esa información durante todo el día a intervalos regulares, pero si estás es un periodo de esos de estrés en el que suenan todos los teléfonos, y no sabes qué demonios está pasando, pues te interesará analizarlo en el momento actual para un periodo reducido… para ello, se capturan dos instantáneas de las esperas, y se hacen cálculos… sencillo, no?
Esperas en Periodo = Esperas Actuales – Esperas Inicio
La tabla variable que utilizaremos de apoyo será la siguiente:
DECLARE @t TABLE (
id int identity
, wait_type nvarchar(60)
, waiting_tasks_count bigint
, wait_time_ms bigint
, signal_wait_time_ms bigint)
Ahí insertaremos, las dos capturas (Esperas Actuales, y Esperas Inicio), y luego haremos sumas y restas… vamos… “contar habas” como diría ese amigo mi jienense J
Los cálculos finales
Como tenemos CTEs en SQL Server 2005, pues vamos a utilizarlas como método divide y vencerás. Primero obtento el delta del periodo (vamos T2-T1):
SELECT
T1.wait_type
, calculos
FROM @T t1
JOIN @T t2
ON T1.wait_type = T2.wait_type
AND T1.id < T2.id
GROUP BY T1.wait_type
Luego calculo los totales:
SELECT
totales
FROM detalle
Y luego calculo las habas (vamos los porcenajes):
SELECT
detalle.*
, porcentajes
FROM detalle
, suma
Si todo este pseudocódigo lo pongo en CTEs, hasta yo puedo comprenderlo J
Encapsularlo en un procedimiento almacenado y retoques finales
Ahora sólo queda meterlo en un procedimiento almacenado (si, muerte a los ad-hoc queries), y adornarlo con argumentos de entrada que representan el tiempo de espera…
IF EXISTS (SELECT * FROM SYS.PROCEDURES WHERE name = ‘sproc_get_waitstats_percentage’)
DROP PROC dbo.sproc_get_waitstats_percentage
GO
CREATE PROC dbo.sproc_get_waitstats_percentage
@hours tinyint = null,
@minutes tinyint = null,
@seconds tinyint = null
AS
SET NOCOUNT ON
IF @hours IS NULL
SET @hours = 0
IF @minutes IS NULL
SET @minutes = 0
IF @seconds IS NULL
SET @seconds = 0
—
— Validaciones iniciales
—
IF @hours < 0 OR @hours > 24
BEGIN
RAISERROR (‘Hours range is not valid.’, 16, 1 )
RETURN
END
IF @minutes < 0 OR @minutes > 60
BEGIN
RAISERROR (‘Minutes range is not valid.’, 16, 1 )
RETURN
END
IF @seconds < 0 OR @seconds > 60
BEGIN
RAISERROR (‘Seconds range is not valid.’, 16, 1 )
RETURN
END
IF @hours = 0 and @minutes = 0 and @seconds = 0
BEGIN
RAISERROR (‘The measure time must be greater than zero.’, 16, 1 )
RETURN
END
—
— Definición de variable table
—
DECLARE @t TABLE (
id int identity
, wait_type nvarchar(60)
, waiting_tasks_count bigint
, wait_time_ms bigint
, signal_wait_time_ms bigint)
—
— Inserción de captura inicial
—
INSERT @t
( wait_type
, waiting_tasks_count
, wait_time_ms
, signal_wait_time_ms )
SELECT
wait_type
, waiting_tasks_count
, wait_time_ms
, signal_wait_time_ms
FROM
sys.dm_os_wait_stats
—
— A esperar n tiempo
—
DECLARE @s CHAR(8)
SET @s =
RIGHT (’00’ + CAST (@hours as VARCHAR(2)), 2) + ‘:’
+ RIGHT (’00’ + CAST (@minutes as VARCHAR(2)), 2) + ‘:’
+ RIGHT (’00’ + CAST (@seconds as VARCHAR(2)), 2)
WAITFOR DELAY @s
—
— Inserción de segunda captura
—
INSERT @t
( wait_type
, waiting_tasks_count
, wait_time_ms
, signal_wait_time_ms )
SELECT
wait_type
, waiting_tasks_count
, wait_time_ms
, signal_wait_time_ms
FROM
sys.dm_os_wait_stats
—
— calculos finales
—
;WITH detalle AS (
SELECT * FROM (
— TOTALES POR TIPO DE ESPERA
SELECT
T1.wait_type
, AVG(T2.waiting_tasks_count – T1.waiting_tasks_count)
waiting_tasks_count
, AVG(T2.wait_time_ms – T1.wait_time_ms)
wait_time_ms
, AVG(T2.signal_wait_time_ms – T1.signal_wait_time_ms)
signal_wait_time_ms
FROM @T t1
JOIN @T t2
ON T1.wait_type = T2.wait_type
AND T1.id < T2.id
GROUP BY T1.wait_type
) v
WHERE
wait_time_ms <> 0
),
suma AS (
— TOTALES GENERALES (PARA CALCULO DE PORCENTAJES)
SELECT
SUM(waiting_tasks_count) waiting_tasks_count
, SUM(wait_time_ms) wait_time_ms
, SUM(signal_wait_time_ms) signal_wait_time_ms
FROM detalle
)
— CALCULOS FINALES
SELECT
detalle.*
, detalle.wait_time_ms * 1.00 / detalle.waiting_tasks_count as wait_per_request
, CASE WHEN suma.waiting_tasks_count = 0
THEN 0
ELSE detalle.waiting_tasks_count * 1.00 / suma.waiting_tasks_count
END as porcen_waiting_tasks_count
, CASE WHEN suma.wait_time_ms = 0
THEN 0
ELSE detalle.wait_time_ms * 1.00 / suma.wait_time_ms
END as porcen_wait_time_ms
, CASE WHEN suma.signal_wait_time_ms = 0
THEN 0
ELSE detalle.signal_wait_time_ms * 1.00 / suma.signal_wait_time_ms
END as porcen_signal_wait_time_ms
FROM detalle
, suma
Siguiente paso
Bueno, ya sólo queda interpretar los resultados, tendrán mayor o menor complejidad. Con la experiencia tendrás más soltura a la hora de interpretar los resultados JJ
Nota1: la lista de recursos es muy grande (algo así como cerca de 200 en SQL Server 2005); sin embargo, se suelen ignorar algunos de ellos porque o bien representan esperas internas, o engloban varias cosas al mismo tiempo. Aquí cada DBA tiene sus propias preferencias. Personalmente, antes de despreciar nada, le doy un vistazo a la representación absoluta, y luego voy eliminando cosillas…
Nota2: quizás te parece demasiado trivial, pero mira qué forma más interesante de convertir tres valores digitales a fecha:
DECLARE @s CHAR(8)
SET @s =
RIGHT (’00’ + CAST (@hours as VARCHAR(2)), 2) + ‘:’
+ RIGHT (’00’ + CAST (@minutes as VARCHAR(2)), 2) + ‘:’
+ RIGHT (’00’ + CAST (@seconds as VARCHAR(2)), 2)
Tendríamos solucionado el problema del formateo cuando se envía una hora, y un minuto. En definitiva convierte los valores digitales de 1 posición en 2 posiciones usando la función RIGHT.
Bueno, a disfrutar, que el tren está llegando a Alicante…
