Оптимизация SQL Server по CPI вместе с ИИ

1.1. Общая статистика CPU из Ring Buffer (последние 10 минут)

Показывает историю загрузки CPU как для SQL Server, так и для всей системы. Хорошо для быстрой оценки текущей ситуации.

WITH ScheduleMonitorResults AS (
    SELECT 
        DATEADD(ms, (SELECT [ms_ticks] - [timestamp] FROM sys.dm_os_sys_info), GETDATE()) AS EventDateTime,
        CAST(record AS XML) AS record
    FROM sys.dm_os_ring_buffers
    WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'
      AND [timestamp] > (SELECT [ms_ticks] - 10*60000 FROM sys.dm_os_sys_info)
)
SELECT 
    CONVERT(varchar, EventDateTime, 126) AS EventTime,
    SysHealth.value('ProcessUtilization[1]','int') AS [CPU_SQL_%],
    100 - SysHealth.value('SystemIdle[1]','int') AS [CPU_All_%]
FROM ScheduleMonitorResults
CROSS APPLY record.nodes('/Record/SchedulerMonitorEvent/SystemHealth') T(SysHealth)
ORDER BY EventDateTime DESC;

1.2. Ожидания (Wait Stats) — топ-15

Важно: на «свежем» сервере статистика репрезентативна. Если сервер работает давно — значения накопленные. Сбрасывать только осознанно и не в рабочее время:

DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);

SELECT TOP 15
    wait_type,
    wait_time_ms / 1000.0 AS wait_sec,
    waiting_tasks_count,
    wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_ms,
    signal_wait_time_ms / 1000.0 AS signal_wait_sec
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','LOGMGR_QUEUE',
    'CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT',
    'BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT','CLR_AUTO_EVENT',
    'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT','XE_DISPATCHER_WAIT',
    'XE_DISPATCHER_JOIN','SQLTRACE_INCREMENTAL_FLUSH_SLEEP','ONDEMAND_TASK_QUEUE',
    'BROKER_EVENTHANDLER','SLEEP_BPOOL_FLUSH','BROKER_RECEIVE_WAITFOR',
    'DIRTY_PAGE_POLL','HADR_FILESTREAM_IOMGR_IOCOMPLETION','SP_SERVER_DIAGNOSTICS_SLEEP',
    'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP','QDS_ASYNC_QUEUE','QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP'
)
ORDER BY wait_time_ms DESC;

Интерпретация ожиданий

1.3. Кто прямо сейчас ест CPU (активные запросы)

Запускать в момент всплеска нагрузки. Показывает выполняющиеся сейчас запросы и уже потраченный ими CPU.

SELECT TOP 10
    r.session_id,
    r.start_time,
    r.status,
    r.command,
    r.cpu_time AS cpu_ms,
    r.total_elapsed_time AS elapsed_ms,
    r.wait_type,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS db_name,
    SUBSTRING(t.text, (r.statement_start_offset/2)+1,
        ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
          ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1) AS query_text,
    qp.query_plan
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE r.session_id > 50
ORDER BY r.cpu_time DESC;

1.4. Топ-запросов по накопленному CPU (исторический анализ)

Анализирует кэш планов. Показывает, какие запросы потребляли больше всего CPU с момента последнего перезапуска SQL Server или очистки кэша.

SELECT TOP 10
    qs.total_worker_time/1000 AS total_cpu_ms,
    qs.execution_count,
    qs.total_worker_time/NULLIF(qs.execution_count,0)/1000 AS avg_cpu_ms,
    qs.total_elapsed_time/1000 AS total_elapsed_ms,
    qs.total_logical_reads,
    qs.total_physical_reads,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC;

Если запрос вернул пусто — нет прав VIEW SERVER STATE или кэш планов очищен.

1.5. Память SQL Server (внутренняя)

-- Внешняя картина
SELECT 
    total_physical_memory_kb/1024 AS total_phys_mb,
    available_physical_memory_kb/1024 AS avail_phys_mb,
    system_memory_state_desc
FROM sys.dm_os_sys_memory;

-- Что SQL держит
SELECT 
    physical_memory_in_use_kb/1024 AS sql_phys_mb,
    virtual_address_space_committed_kb/1024 AS sql_vm_committed_mb,
    page_fault_count
FROM sys.dm_os_process_memory;

-- Разбивка по клеркам
SELECT TOP 10 
    type,
    SUM(pages_kb) AS pages_kb,
    SUM(virtual_memory_committed_kb) AS vm_committed_kb
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY pages_kb DESC;

1.6. Настройки SQL Server

SELECT name, value, value_in_use, description
FROM sys.configurations
WHERE name IN (
    'max server memory (MB)',
    'min server memory (MB)',
    'cost threshold for parallelism',
    'max degree of parallelism',
    'optimize for ad hoc workloads',
    'query governor cost limit'
)
ORDER BY name;

-- Флаги трассировки
DBCC TRACESTATUS(-1);

1.7. Активные задания SQL Agent

Помогает понять, не совпадают ли всплески CPU с расписанием заданий (сбор статистики, индексация, бэкапы).

SELECT 
    j.name AS job_name,
    ja.start_execution_date,
    ja.stop_execution_date,
    DATEDIFF(SECOND, ja.start_execution_date, GETDATE()) AS running_sec,
    ja.last_executed_step_id
FROM msdb.dbo.sysjobactivity ja
JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
WHERE ja.session_id = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity)
  AND ja.start_execution_date IS NOT NULL
  AND ja.stop_execution_date IS NULL;

1.8. Блокировки и deadlocks

-- Текущие блокировки
SELECT 
    blocking.session_id AS blocker,
    blocked.session_id AS blocked,
    blocking.wait_type,
    blocking.wait_time/1000 AS wait_sec,
    blocking_text.text AS blocker_sql,
    blocked_text.text AS blocked_sql
FROM sys.dm_exec_requests blocked
JOIN sys.dm_exec_requests blocking ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) blocking_text
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_text
WHERE blocked.blocking_session_id > 0;

-- Открытые транзакции
SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    t.transaction_begin_time,
    DATEDIFF(SECOND, t.transaction_begin_time, GETDATE()) AS open_sec,
    t.transaction_type,
    t.transaction_state
FROM sys.dm_tran_active_transactions t
JOIN sys.dm_tran_session_transactions st ON t.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions s ON st.session_id = s.session_id
WHERE s.session_id > 50
ORDER BY t.transaction_begin_time;

1.9. Кольцевой буфер: ошибки и OOM

-- Ошибки, связанные с памятью
SELECT 
    DATEADD(ms, -1 * (SELECT ms_ticks FROM sys.dm_os_sys_info) + timestamp, GETDATE()) AS event_time,
    CAST(record AS XML).value('(//Error/@value)[1]', 'int') AS error_num,
    CAST(record AS XML).value('(//Error/@value)[2]', 'int') AS error_num2,
    CAST(record AS XML).value('(//Error/@value)[3]', 'int') AS error_num3
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_EXCEPTION'
ORDER BY timestamp DESC;

-- OOM события
SELECT 
    DATEADD(ms, -1 * (SELECT ms_ticks FROM sys.dm_os_sys_info) + timestamp, GETDATE()) AS event_time,
    CAST(record AS XML).value('(//Record/@type)[1]', 'varchar(100)') AS event_type,
    CAST(record AS XML).value('(//Record/@id)[1]', 'int') AS event_id,
    CAST(record AS XML).value('(//Record/@value)[1]', 'bigint') AS event_value,
    CAST(record AS XML).value('(//Record/@data)[1]', 'varchar(max)') AS event_data
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
ORDER BY timestamp DESC;

1.10. Query Store — где включён

SELECT 
    name,
    is_query_store_on,
    desired_state_desc,
    actual_state_desc,
    current_storage_size_mb,
    max_storage_size_mb
FROM sys.database_query_store_options dqso
JOIN sys.databases d ON dqso.database_id = d.database_id
WHERE d.database_id > 4;

1.11. Extended Events — сессия для ловли тяжёлых запросов

Позволяет логировать запросы с CPU > 5000 мс. Удобно, когда всплески кратковременные и их сложно поймать вручную.

-- Удалить старую сессию, если есть
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = 'HighCPU')
    DROP EVENT SESSION [HighCPU] ON SERVER;

-- Создать сессию
CREATE EVENT SESSION [HighCPU] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (sqlserver.sql_text, sqlserver.database_name, sqlserver.username, sqlserver.client_app_name)
    WHERE cpu_time > 5000
),
ADD EVENT sqlserver.rpc_completed (
    ACTION (sqlserver.sql_text, sqlserver.database_name, sqlserver.username, sqlserver.client_app_name)
    WHERE cpu_time > 5000
)
ADD TARGET package0.ring_buffer (SET max_memory = 4096)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS, STARTUP_STATE = OFF);
GO

-- Запустить
ALTER EVENT SESSION [HighCPU] ON SERVER STATE = START;
GO

-- Прочитать данные
SELECT 
    DATEADD(ms, -1 * (SELECT ms_ticks FROM sys.dm_os_sys_info), GETDATE()) AS session_start,
    xed.event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
    xed.event_data.value('(event/@timestamp)[1]', 'varchar(50)') AS event_time,
    xed.event_data.value('(event/action[@name="sql_text"]/value)[1]', 'varchar(max)') AS sql_text,
    xed.event_data.value('(event/action[@name="database_name"]/value)[1]', 'varchar(128)') AS db_name,
    xed.event_data.value('(event/action[@name="username"]/value)[1]', 'varchar(128)') AS username,
    xed.event_data.value('(event/data[@name="cpu_time"]/value)[1]', 'bigint') AS cpu_time,
    xed.event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS duration
FROM (
    SELECT CAST(target_data AS XML) AS target_data
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE s.name = 'HighCPU' AND st.target_name = 'ring_buffer'
) AS data
CROSS APPLY target_data.nodes('//RingBufferTarget/event') AS xed(event_data)
ORDER BY event_time DESC;

Остановить сессию после сбора: ALTER EVENT SESSION [HighCPU] ON SERVER STATE = STOP;