Обсудить
бизнес-задачи

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

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

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

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

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

Количество логических процессоров

32-битный сервер с SQL Server до 2014 (12.x)

64-битный сервер с SQL Server до 2016 (13.x) SP1

64-битный сервер с SQL Server 2016 (13.x) SP2 и SQL Server 2017 (14.x)

<=4

256

512

512

8

288

576

576

16

352

704

704

32

480

960

960

64

736

1472

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% и улучшить производительность системы в целом.

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

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