Review the main accumulated waits

The server feels slow at different times, but a snapshot of active requests does not show a stable pattern. We need to know in which waiting families the time has been concentrated since the beginning of the measurement.

What we need to distinguish

A worker is the engine resource that runs a task. The instance is the complete SQL Server service; its cumulative counters combine all activity since the last startup or statistics reset.

Cumulative waits describe time already completed by workers in the entire instance. They do not show what is waiting right now and mix up loads, schedules and system tasks. That is why it is advisable to read them as an initial profile and not as a finished diagnosis.

Query

/*
    Antes de ejecutar:
        Revisa los permisos y los límites antes de ejecutar la consulta.
        Confirma el alcance, la base activa y el costo posible de la consulta.
        Captura evidencia en varios momentos antes de atribuir una causa.

    Este script es de observación. No aplica correcciones ni modifica datos.
*/

SET NOCOUNT ON;
GO
-- Consulta
SELECT TOP (30)
    si.sqlserver_start_time AS InicioInstancia,
    ws.wait_type AS TipoEspera,
    ws.waiting_tasks_count AS CantidadEsperas,
    ws.wait_time_ms AS EsperaTotalMs,
    ws.signal_wait_time_ms AS EsperaSenalMs,
    ws.wait_time_ms - ws.signal_wait_time_ms AS EsperaRecursoMs,
    CONVERT
    (
        DECIMAL(18, 2),
        1.0 * ws.wait_time_ms / NULLIF(ws.waiting_tasks_count, 0)
    ) AS EsperaPromedioMs,
    CONVERT
    (
        DECIMAL(6, 2),
        100.0 * ws.wait_time_ms
        / NULLIF(SUM(ws.wait_time_ms) OVER (), 0)
    ) AS PorcentajeDelTiempo
FROM sys.dm_os_wait_stats AS ws
CROSS JOIN sys.dm_os_sys_info AS si
WHERE ws.waiting_tasks_count > 0
ORDER BY
    ws.wait_time_ms DESC,
    ws.waiting_tasks_count DESC;
GO

Parameters or permissions

The query needs VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE from SQL Server 2022. InstanceStart helps recognize the maximum window, although the counters may also have been reset independently. Waiting is not excluded through a fixed list. Some queue or internal task waits typically grow when the server is healthy, and a copied list can become stale between releases. First identify the family and confirm its meaning in the installed version.

How to read the result

WaitTotalMs includes WaitSignalMs. The difference, WaitResourceMs, approximates the time before the worker was ready to use CPU. QuantityWaits and WaitAverageMs allow us to distinguish many small pauses from a few long pauses. The percentage is calculated on all rows with at least one wait, although only the first thirty are shown. A rate with a high total may simply reflect frequent loading or a long observation window.

What this does establish

It allows us to recognize which types of waits accumulated more time within the available window and decide which family deserves a more specific measurement.

What this still does not establish

It does not identify the responsible query, it does not confirm a current bottleneck, and it does not demonstrate that the wait with the highest total is abnormal. It also does not allow you to compare two servers without considering duration, load and configuration.

Next reasonable check

Captures the counters at the start and end of a representative interval and compares their differences. Match the dominant family to active request queues before proposing a change.

Search SQL Server