Оптимизация нагрузки на CPU

блог о bi, №1 в рунете

В нашей практике был случай, когда неправильно заданный параметр привел к увеличению на 8% времени ожидания запросов, а правильная настройка увеличила производительность OLTP-запросов на 10%.
В данной статье будет рассмотрен параметр max worker threads.

Определение параметра

Данный параметр отвечает за размер пула рабочих потоков, который есть у каждого процессора. Каждый запрос к процессору создает один или несколько потоков, которые являются командами для процессора. Пока все потоки заняты, запрос стоит в ожидании. Изменив параметр max worker threads, можно увеличить размер пула рабочих потоков.

По умолчанию параметр max worker threads равен 0, то есть сервер самостоятельно выбирает размер пула потоков. Это значение вычисляется при запуске и зависит от количества логических процессоров, архитектуры системы, версии MS SQL Server. Значения параметра представлены в таблице 1.

Таблица 1. Размер пула потоков, заданный по умолчанию

<=4

256

512

288

576

352

704

480

960

736

1472

128

1248

2496

4480

256

2272

4544

8576

Изменить параметр можно через настройки сервера, как показано на рисунке 1.

Рисунок 1. Настройка параметра Maximum worker threads

Альтернативным вариантом изменения параметра может быть следующий скрипт:

USE master;
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max worker threads', 900;
GO
RECONFIGURE WITH OVERRIDE;
GO

Ошибки при выборе параметра

Значение, заданное по умолчанию, может показаться неподходящим, но изменение параметра без тщательного анализа производительности системы может привести к ухудшению ее работы.

Заниженное значение параметра приводит к потоковому голоду, когда потоки не успевают освободиться при появлении запросов. Запросы будут ожидать освобождения потока с типом ожидания THREADPOOL.

Завышенное значение параметра приводит к увеличению загрузки оперативной памяти и частым изменениям контекста потока, когда процессор прекращает выполнение одного потока и начинает выполнение другого. При этом постоянная выгрузка и загрузка состояний потоков уменьшают производительность. Поток заново ожидает предоставления процессорного времени с типом ожидания SOS_SCHEDULER_YIELD.

Проверить статистику ожиданий на предмет вышеперечисленных проблем можно следующим скриптом:

with [Waits] as (
	SELECT
		[wait_type],
		[wait_time_ms] / 1000.0 AS [WaitS],
		[waiting_tasks_count] AS [WaitCount],
		100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
		ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
	FROM sys.dm_os_wait_stats
	WHERE [waiting_tasks_count] > 0
		AND [wait_type] not in (
        N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',
        N'CHKPT', N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC', N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE', N'MEMORY_ALLOCATION_EXT',
        N'ONDEMAND_TASK_QUEUE',
        N'PREEMPTIVE_XE_GETTARGETSTATE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'QDS_SHUTDOWN_QUEUE', N'REDO_THREAD_PENDING_WORK',
        N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',
        N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_RECOVERY',
        N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT',
	 N'SOS_WORK_DISPATCHER')
)
SELECT
    MAX([W1].[wait_type]) AS [WaitType],
    MAX([W1].[WaitCount]) AS [WaitCount],
    CAST(MAX([W1].[WaitS]) AS DECIMAL(16,2)) AS [Wait_S],
    CAST((MAX([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
    CAST(MAX([W1].[Percentage]) AS DECIMAL(5,2)) AS [Percentage]
FROM [Waits] AS [W1]
GROUP BY [W1].[RowNum];

Данный скрипт считает долю конкретного ожидания от общего, при этом не учитывает ожидания, которые можно игнорировать. Те ожидания, которым стоит уделить внимание, перечислены сверху результата.

На одном из серверов наших клиентов значение параметра, установленное вручную, превышало в 2.5 раза рекомендованное, что привело к частым изменениям контекста потоков. Более 8% времени общего ожидания занимало ожидание с типом SOS_SCHEDULER_YIELD. После изменения параметра на значение по умолчанию, удалось добиться увеличения производительности OLTP-запросов на 10% и улучшить производительность системы в целом.

Оптимизация нагрузки на CPU

Определение параметра

Ошибки при выборе параметра

Рекомендации