Veremos hoy una secuencia de comandos muy simple y efectiva para el programador.

Es bastante fácil averiguar cuántos recuentos de tareas de CPU están esperando en el disco IO con la ayuda de sys.dm_os_schedulers.

SERVIDOR SQL

– Medir la presión de la CPU

– Detectar la presión de la CPU y SQL SERVER

– Consultar la presión de la CPU.

CPU Scheduler Waiting On Disk

SELECT  COUNT(*) Schedulers,
        AVG(work_queue_count) AS [Promedio de colas de trabajo],		-- Avg Work Queue Count
        AVG(pending_disk_io_count) AS [Promedio de DiskIO pendiente],	        -- Avg Pending DiskIO Count
        SUM(work_queue_count) AS [SUMA de la cola de trabajo],			-- SUM Work Queue Count
        SUM(pending_disk_io_count) AS [SUMA Promedio DiskIO pendiente]	        -- SUM Pending DiskIO Count
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE scheduler_id < 255;

Es muy posible que su SQL Server esté instalado en la máquina donde también están instaladas otras aplicaciones de Windows y están consumiendo su CPU. Podemos averiguar fácilmente qué procesos están usando su CPU simplemente ejecutando la siguiente consulta.

DECLARE @ms_ticks_now BIGINT
SELECT @ms_ticks_now = ms_ticks
FROM sys.dm_os_sys_info;
SELECT TOP 60 record_id
    ,dateadd(ms, - 1 * (@ms_ticks_now - [timestamp]), GetDate()) AS EventTime
    ,[SQLProcess (%)]
    ,SystemIdle
    ,100 - SystemIdle - [SQLProcess (%)] AS [OtherProcess (%)]
FROM (
    SELECT record.value('(./Record/@id)[1]', 'int') AS record_id
        ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle
        ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcess (%)]
        ,TIMESTAMP
    FROM (
        SELECT TIMESTAMP
            ,convert(XML, record) AS record
        FROM sys.dm_os_ring_buffers
        WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE '%<SystemHealth>%'
        ) AS x
    ) AS y
ORDER BY record_id DESC

Esta consulta le dará una idea clara de si su servidor SQL está utilizando CPU o cualquier otro proceso. Si su SQL Server está utilizando la CPU, debe continuar con la investigación en SQL Server; de lo contrario, debe comenzar a buscar procesos que consumen su CPU en la aplicación de Windows.

Actualmente, este script le brinda detalles sobre los últimos 60 minutos. Puede configurarlo aún más para su intervalo deseado.

Consultas actuales usando CPU

Una vez que esté seguro de que su SQL Server está utilizando la CPU máxima, puede ejecutar el siguiente script para identificar qué consulta está utilizando la CPU máxima. Tenga en cuenta que esta consulta solo analizará las consultas que se están ejecutando actualmente.

SELECT
    r.session_id
    ,st.TEXT AS batch_text
    ,SUBSTRING(st.TEXT, statement_start_offset / 2 + 1, (
            (
                CASE
                    WHEN r.statement_end_offset = - 1
                        THEN (LEN(CONVERT(NVARCHAR(max), st.TEXT)) * 2)
                    ELSE r.statement_end_offset
                    END
                ) - r.statement_start_offset
            ) / 2 + 1) AS statement_text
    ,qp.query_plan AS 'XML Plan'
    ,r.cpu_time, r.total_elapsed_time
    ,r.logical_reads, r.writes, r.dop
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
ORDER BY cpu_time DESC

La consulta anterior actualmente está ordenada por tiempo de CPU, puede cambiar el orden si lo prefiere y si desea recuperar detalles adicionales sobre la consulta, puede encontrar fácilmente más detalles en DMV dm_exec_requests, que tiene el alias r en la consulta anterior.

Historial de Consultas que usan CPU

A menudo, los clientes me contratan para ayudarlos a investigar la historia de SQL Server. Quieren saber por qué en el pasado su SQL Server funcionaba muy alto. En ese caso, puede ejecutar el siguiente script para identificar qué consulta estaba utilizando la mayor cantidad de CPU en el pasado.

SELECT TOP(10) qs.execution_count AS [Execution Count],
    (qs.total_logical_reads)/1000.0 AS [Total Logical Reads in ms],
    (qs.total_logical_reads/qs.execution_count)/1000.0 AS [Avg Logical Reads in ms],
    (qs.total_worker_time)/1000.0 AS [Total Worker Time in ms],
    (qs.total_worker_time/qs.execution_count)/1000.0 AS [Avg Worker Time in ms],
    (qs.total_elapsed_time)/1000.0 AS [Total Elapsed Time in ms],
    (qs.total_elapsed_time/qs.execution_count)/1000.0 AS [Avg Elapsed Time in ms],
    qs.creation_time AS [Creation Time]
    ,t.text AS [Complete Query Text], qp.query_plan AS [Query Plan]
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
WHERE t.dbid = DB_ID()
ORDER BY (qs.total_logical_reads/qs.execution_count) DESC;

La consulta anterior recuperará datos de su caché de memoria. Esto significa que si borra su caché o si su servidor se reinicia, los datos se borrarán. No debe depender al 100% de esto para su investigación. Sin embargo, desde mi experiencia, he encontrado que el resultado es bastante confiable.

Una vez que identifique qué consulta está tomando la CPU máxima, puede considerar ajustar esa consulta en función de lo que encuentre ofensivo en esa consulta. Debe centrarse en el siguiente aspecto, junto con los operadores del plan de ejecución cuando esté analizando el ejercicio de ajuste de consultas.