Los motivos, por los que nuestras consultas o procesos sufren tiempos de espera, pueden ser muy diferentes.
Uno de los motivos de espera, puede ocurrir cuando un trabajo solicita el acceso a un recurso (Resource waits) que no está disponible, bien porque el recurso este siendo utilizado por otro trabajo, o bien por que tenga algún tipo de problema para acceder al recurso. Claros ejemplos de recursos en espera, pueden ser: locks, latches, red y esperas de I/O en disco.
Otro motivo, pueden ser la Cola de espera (Queue waits) , ya que se produce cuando un(os) trabajo(s) está(n) inactivo(s) y esperando que se le asigne un recuro para continuar. Son más normales encontrarlas en el sistema con trabajos en background. Estas tareas se ponen en colas, a la espera de que los recursos que necesitan no estén siendo utilizados , momento en el que se les asignarán dichos recursos para que se puedan iniciar los trabajos. Periódicamente el sistema revisa las colas de espera, por si hay nuevos trabajos, para tenerlos en cuenta, en el orden de asignación de recursos.
Un tercer motivo, se puede producir cuando un trabajo de SQL Server está esperando la finalización de algún elemento externo, como por ejemplo, una llamada a procedimiento almacenado extendido o una consulta en un servidor vinculado.
Los tiempos de espera durante la ejecución de los procesos, pueden indicar cuellos de botella o puntos de parada en las consultas. Del mismo modo, los tiempos altos de espera, pueden ser un indicador de cuellos de botella o un indicador de elevadas interacciones en la consulta dentro de la instancia del servidor. Por ejemplo: lock waits indican que los datos están usándose por otras consultas, page IO latch waits indican tiempos lentos en espera de I/O (entrada y salida a disco)…
Para poder consultar estos datos, y poder conocer en que dedica mayor tiempo nuestros servidores de base de datos, se utilizan las vistas de administración dinámica (DMV, Dynamic Management Views) de SQL Server y las funciones de administración dinámica (DMF, Dynamic Management Functions) correspondientes. Se trata de vistas y funciones basadas en el sistema que proporcionan información sobre el estado del servidor con la que es posible diagnosticar problemas y posteriormente ajustar el rendimiento de la base de datos.
Hay un DMV específica, sys.dm_os_wait_stats, que se pueden consultar rápidamente para determinar donde se nos van los tiempos en nuestras bases de datos. Los datos que consulta esta DMV, se acumulan desde la última vez que se restablecieron las estadísticas o se inició el servidor (la instancia). Se puede restablecer los datos ejecutando el siguiente comando:
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO
Para ejecutar el anterior DBCC, se requiere el permiso ALTER SERVER STATE en el servidor.
La consulta más sencilla para obtener los resultados para cada wait_type, sería:
SELECT * FROM sys.dm_os_wait_stats;
Una consulta general de este tipo en realidad no nos dice mucho. Paso a describir los campo que nos devuelve la consulta:
Wait_type - El tipo de esperar que se han producido, , desde la última vez que se reiniciaron los servicios (instancia) de SQL o se restablecieron a cero sus valores.
Waiting_tasks_count - El número acumulado de las tareas que se han registrado por casa wait_type.
Wait_time_ms - La cantidad total de tiempo dedicado, por cada wait_type del mismo tipo.
Max_wait_time_ms - Máximo tiempo de espera de cada wait_tipe.
Signal_wait_time_ms - La cantidad de tiempo, en milisegundos, desde que se ha puesto en libertad un recurso, hasta se asignó ese recurso a un trabajo. Un tiempo alto de espera es indicativo de problemas en CPU en el que el hilo todavía tenía que esperar para la asignación de CPU, incluso después de que el recurso que se esperaba fue liberado.
Son muchos los autores, y articulos en Internet, que han depurado la consulta a la vista dinámica: sys.dm_os_wait_stats, para obtener datos más precisos. Paso a comentaros las que considero interesantes.
wait_type irrelevantes
La causa acumulada de todas las esperas se puede examinar mediante:
SELECT TOP 10
[Wait type] = wait_type,
[Wait time (s)] = wait_time_ms / 1000,
[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0
/ SUM(wait_time_ms) OVER())
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
ORDER BY wait_time_ms DESC;
El resultado que se obtiene, enumera los tipos de espera ordenados según el tiempo de espera total
Es posible que nos aparezcan wait_tipe, que podamos descartar, (en el anterior ejemplo descartamos todas las que contienen '%SLEEP%' ), ya que su existencia no reflejan síntoma de problema, como por ejemplo:
'CLR_SEMAPHORE'.- Tiene lugar cuando una tarea está realizando actualmente la ejecución de CLR y espera un semáforo.
'LAZYWRITER_SLEEP'.- Tiene lugar cuando se suspenden tareas de escritura diferida. Ésta es una medida del tiempo invertido por las tareas en segundo plano que esperan.
'RESOURCE_QUEUE'.- Tiene lugar durante la sincronización de diferentes colas internas de recursos.
'SLEEP_TASK'.- Tiene lugar cuando una tarea se mantiene inactiva mientras espera que se produzca un evento genérico.
'SLEEP_SYSTEMTASK'.- Tiene lugar durante el inicio de una tarea en segundo plano mientras se espera que tempdb finalice el inicio.
'SQLTRACE_BUFFER_FLUSH'.- Tiene lugar cuando una tarea está esperando a que una tarea en segundo plano vuelque los búferes de seguimiento al disco cada cuatro segundos.
'WAITFOR'.- Tiene lugar como resultado de una instrucción WAITFOR de Transact-SQL. La duración de la espera viene determinada por los parámetros de la instrucción. Se trata de una espera iniciada por el usuario.
'CLR_AUTO_EVENT'.- Tiene lugar cuando una tarea está realizando actualmente la ejecución de Common Language Runtime (CLR) y espera que se inicie un evento automático determinado. Son habituales las esperas largas y no indican ningún problema.
'CLR_MANUAL_EVENT'.- Tiene lugar cuando una tarea está realizando actualmente la ejecución de CLR y espera que se inicie un evento manual específico.
Por lo que podemos depurar la consulta dejándola de esta forma:
SELECT
wait_type ,
wait_time_ms / 1000. AS wait_time_s ,
100. * wait_time_ms / SUM ( wait_time_ms ) OVER () AS pct ,
ROW_NUMBER () OVER ( ORDER BY wait_time_ms DESC ) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type
NOT IN
( 'CLR_SEMAPHORE' , 'LAZYWRITER_SLEEP' , 'RESOURCE_QUEUE' ,
'SLEEP_TASK' , 'SLEEP_SYSTEMTASK' , 'SQLTRACE_BUFFER_FLUSH' , 'WAITFOR' ,
'CLR_AUTO_EVENT' , 'CLR_MANUAL_EVENT' )
Otra consulta, interesante, en la cual son tratados los wait_type, para agruparlos según su concepto, nos ayudara a deducir mejor, en qué dedica mas tiempo nuestro servidor:
select
case
when ws.wait_type like N'LCK_M_%' then N'Lock'
when ws.wait_type like N'LATCH_%' then N'Latch'
when ws.wait_type like N'PAGELATCH_%' then N'Buffer Latch'
when ws.wait_type like N'PAGEIOLATCH_%' then N'Buffer IO'
when ws.wait_type like N'RESOURCE_SEMAPHORE_%' then N'Compilation'
when ws.wait_type = N'SOS_SCHEDULER_YIELD' then N'Scheduler Yield'
when ws.wait_type in (N'LOGMGR', N'LOGBUFFER', N'LOGMGR_RESERVE_APPEND', N'LOGMGR_FLUSH', N'WRITELOG') then N'Logging'
when ws.wait_type in (N'ASYNC_NETWORK_IO', N'NET_WAITFOR_PACKET') then N'Network IO'
when ws.wait_type in (N'CXPACKET', N'EXCHANGE') then N'Parallelism'
when ws.wait_type in (N'RESOURCE_SEMAPHORE', N'CMEMTHREAD', N'SOS_RESERVEDMEMBLOCKLIST') then N'Memory'
when ws.wait_type like N'CLR_%' or ws.wait_type like N'SQLCLR%' then N'CLR'
when ws.wait_type like N'DBMIRROR%' or ws.wait_type = N'MIRROR_SEND_MESSAGE' then N'Mirroring'
when ws.wait_type like N'XACT%' or ws.wait_type like N'DTC_%' or ws.wait_type like N'TRAN_MARKLATCH_%' or ws.wait_type like N'MSQL_XACT_%' or ws.wait_type = N'TRANSACTION_MUTEX' then N'Transaction'
when ws.wait_type like N'SLEEP_%' or ws.wait_type in(N'LAZYWRITER_SLEEP', N'SQLTRACE_BUFFER_FLUSH', N'WAITFOR', N'WAIT_FOR_RESULTS') then N'Sleep'
else N'Other'
end as category,
ws.wait_type,
ws.waiting_tasks_count,
case when ws.waiting_tasks_count = 0 then 0 else ws.wait_time_ms / ws.waiting_tasks_count end as average_wait_time_ms,
ws.wait_time_ms as total_wait_time_ms,
convert(decimal(12,2), ws.wait_time_ms * 100.0 / sum(ws.wait_time_ms) over()) as wait_time_proportion,
ws.wait_time_ms - signal_wait_time_ms as total_wait_ex_signal_time_ms,
ws.max_wait_time_ms,
ws.signal_wait_time_ms as total_signal_wait_time_ms
-- @tstamp as tstamp
from
sys.dm_os_wait_stats ws
where
ws.waiting_tasks_count > 0 -- Restrict results to requests that have actually occured.
order by
ws.wait_time_ms desc
Instantánea de wait_type
Caso 1.
Gracias a la idea de Glenn Berry, a partir de una CTE, obtenemos una instantánea de la espera en forma de porcentaje, filtrando tipos irrelevante esperas, para luego sacar una lista de esperas, de sólo a aquellas que superan un % (en este ejemplo: 95%) de las esperas en la instancia de SQL Server:
WITH Waits AS
(
SELECT
wait_type ,
wait_time_ms / 1000. AS wait_time_s ,
100. * wait_time_ms / SUM ( wait_time_ms ) OVER () AS pct ,
ROW_NUMBER () OVER ( ORDER BY wait_time_ms DESC ) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type
NOT IN
( 'CLR_SEMAPHORE' , 'LAZYWRITER_SLEEP' , 'RESOURCE_QUEUE' ,
'SLEEP_TASK' , 'SLEEP_SYSTEMTASK' , 'SQLTRACE_BUFFER_FLUSH' , 'WAITFOR' ,
'CLR_AUTO_EVENT' , 'CLR_MANUAL_EVENT' )
)
SELECT W1.wait_type ,
CAST ( W1.wait_time_s AS DECIMAL ( 12 , 2 )) AS wait_time_s ,
CAST ( W1.pct AS DECIMAL ( 12 , 2 )) AS pct ,
CAST ( SUM ( W2.pct ) AS DECIMAL ( 12 , 2 )) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.rn <= W1.rn
GROUP BY W1.rn ,
W1.wait_type ,
W1.wait_time_s ,
W1.pct
HAVING SUM ( W2.pct ) - W1.pct < 95 ;
Caso 2.
Los valores wait_type, que estamos obteniendo se acumulan con el tiempo y se resetean una vez se reinicie el servicio de SQL Server. De todos modos, estas consultas no se suele lanzar en un momento puntual, sino periódicamente y si puedes, calcular valores delta entre una captura y otra. De ese modo sabrás más concretamente cómo se comporta tu servidor a lo largo del día y no solo en un momento concreto. Por esta razón, podemos almacenar los valores en una tabla estática, dentro de una base de datos dedicada a tareas administrativas, ubicada por ejemplo en cada instancia. A continuación, mostramos como podemos almacenar los valores en una tabla estática:
USE BBDD ;
CREATE TABLE dbo.dm_os_wait_stats
(
[wait_type] [nvarchar] ( 60 ) NOT NULL,
[waiting_tasks_count] [bigint] NOT NULL,
[wait_time_ms] [bigint] NOT NULL,
[max_wait_time_ms] [bigint] NOT NULL,
[signal_wait_time_ms] [bigint] NOT NULL,
[capture_time] [datetime] NOT NULL,
[increment_id] [int] NOT NULL
);
ALTER TABLE dbo.dm_os_wait_stats
ADD DEFAULT ( GETDATE ()) FOR [capture_time] ;
DECLARE @DT DATETIME ;
SET @DT = GETDATE () ;
DECLARE @increment_id INT ;
SELECT @increment_id = MAX ( increment_id ) + 1 FROM dbo.dm_os_wait_stats;
SELECT @increment_id = ISNULL ( @increment_id, 1 )
INSERT INTO dbo.dm_os_wait_stats
( [wait_type] , [waiting_tasks_count] , [wait_time_ms] , [max_wait_time_ms] ,
[signal_wait_time_ms] , [capture_time] , [increment_id] )
SELECT [wait_type] , [waiting_tasks_count] , [wait_time_ms] , [max_wait_time_ms] ,
[signal_wait_time_ms] , @DT , @increment_id
FROM sys.dm_os_wait_stats ;
Puede ejecutar una consulta que sólo cuente la actividad reciente - no la actividad en días, meses, semanas, etc:
DECLARE @max_increment_id INT
SELECT @max_increment_id = MAX ( increment_id )
FROM dbo.dm_os_wait_stats
A continuación, puede ejecutar una consulta a la anterior tabla, para obtener solamente las esperas más recientes en forma de porcentaje, filtrando tipos irrelevante esperas, para luego sacar una lista de esperas, de sólo a aquellas que superan un % (en este ejemplo: 95%)
USE BBDD ;
DECLARE @max_increment_id INT
SELECT @max_increment_id = MAX ( increment_id )
FROM dbo.dm_os_wait_stats;
WITH Waits AS
(
SELECT DOWS1.wait_type ,
(( DOWS1.wait_time_ms - DOWS2.wait_time_ms )/ 1000 ) AS [wait_time_s] ,
100. * ( DOWS1.wait_time_ms - DOWS2.wait_time_ms ) / SUM ( DOWS1.wait_time_ms - DOWS2.wait_time_ms ) OVER () AS pct ,
ROW_NUMBER () OVER ( ORDER BY ( DOWS1.wait_time_ms - DOWS2.wait_time_ms ) DESC ) AS rn
FROM
(
SELECT wait_type , waiting_tasks_count , wait_time_ms , max_wait_time_ms ,
signal_wait_time_ms , capture_time , increment_id
FROM dbo.dm_os_wait_stats
WHERE increment_id = @max_increment_id
) AS DOWS1
INNER JOIN
(
SELECT wait_type , waiting_tasks_count , wait_time_ms , max_wait_time_ms ,
signal_wait_time_ms , capture_time , increment_id
FROM dbo.dm_os_wait_stats
WHERE increment_id = ( @max_increment_id - 1 )
) AS DOWS2 ON DOWS1.wait_type = DOWS2.wait_type
WHERE ( DOWS1.wait_time_ms - DOWS2.wait_time_ms ) > 0
)
SELECT W1.wait_type ,
CAST ( W1.wait_time_s AS DECIMAL ( 12 , 2 )) AS wait_time_s ,
CAST ( W1.pct AS DECIMAL ( 12 , 2 )) AS pct ,
CAST ( SUM ( W2.pct ) AS DECIMAL ( 12 , 2 )) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2 ON W2.rn <= W1.rn
GROUP BY W1.rn ,
W1.wait_type ,
W1.wait_time_s ,
W1.pct
HAVING SUM ( W2.pct ) - W1.pct < 95 ;
Tipos de wait_type
Los tipos de esperas, pueden ser muchos, aquí os dejo un link donde podréis encontrar a que hace referencia cada tipo de espera:
http://msdn.microsoft.com/en-us/library/ms179984.aspx
Una búsqueda rápida por Internet, ayudará a conocer, que indican vuestros TOP wait_tipe…
A modo de ejemplo, os paso a comentar un famoso wait_tipe: CXPACKET (hace referencia a consultas en paralelo pueden experimentar un interbloqueo no detectado)
CXPacket -> Son bloqueos de corta o larga duración. Se suelen producir con mayor fecuencia cuando las consultas paralelizan (distintos hilos de ejcución en paralelo por cada consulta o por consultas diferentes, que necesitan acceder a los mismos objetos de la base de datos (indices, registros, tablas...), dichos hilos de ejecución no tienen por que tener la misma cantidad de carga. Un hilo puede tener mucho más que hacer que los demás, y así en momentos puntuales toda la consulta está bloqueada, mientras que el hilo de larga duración completa).
Puede darse la espera CXPacket, con consultas muy grandes o incluso con repeticiones consecutivas de consultas, en las bbdd. Siendo la configuración del paralelismo la de pordefecto (“max degree of parallelism” = 0) en la instancia, estaríamos ante el Maximo nivel de paralelismo concurrente. Sería recomendable modificar el grado máximo de paralelismo en 1 o quizás en un número que no supere el número de procesadores físicos, o si fuera posible establecer MAXDOP para las consultas más destacadas, (Pruebas en servidores de testeo, con las mismas características que los servidores de producción, pueden ayudar a obtener el valor que mas se ajuste a nuestras necesidades).
Por defecto MAXDOP es cero, o sea, que SQL Server decide la cantidad de procesadores a utilizar, puede ser 1 entonces se deshabilita el paralelismo o un número fijo que determine la cantidad máxima de CPUs a utilizar. Un heurístico utilizado es que en sistemas OLTP el valor de MAXDOP debe ser 8 aunque el servidor tenga más procesadores ya que el costo de dividir la consulta en más de 8 partes puede comprometer el beneficio del paralelismo.
Por ejemplo. establecer grado máximo de paralelismo en 1 para suprimir el paralelismo de consulta:
EXEC dbo.sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC dbo.sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
Referencias:
Microsoft
www.mssqltips.com
Glenn Berry
Apuntes y recopilaciones por Norman M. Pardell
Puedes consultarme, si deseas cualquier aclaración, pregunta o sugerencia en: Contacto, contestaré tan pronto como me sea posible.
