Настройка максимального количества рабочих потоков (параметр конфигурации сервера)

Статья
03/21/2023

Применимо к:SQL Server

В этой статье описывается настройка параметра конфигурации сервера max worker threads в SQL Server с помощью SQL Server Management Studio или Transact-SQL. Параметр max worker threads задает количество рабочих потоков, доступных SQL Server для обработки запросов входа, выхода и аналогичных запросов приложений.

SQL Server использует собственные службы потоков операционных систем, чтобы обеспечить соблюдение следующих условий.

Один или несколько потоков одновременно поддерживают все сети, поддерживаемые SQL Server.
Один поток обрабатывает контрольные точки базы данных.
Пул потоков обрабатывает запросы от всех пользователей.

Значение по умолчанию для параметра max worker threads — 0. Это позволяет SQL Server автоматически настраивать количество рабочих потоков при запуске. Настройка по умолчанию является оптимальной для большинства систем. Но иногда, в зависимости от конфигурации системы, установка параметра max worker threads в другое определенное значение может улучшить производительность.

ограничения

Если реальное количество запросов превышает число, заданное параметром max worker threads, то SQL Server создает пул рабочих потоков, чтобы передавать запросы на обработку освободившимся рабочим потокам. Рабочий поток назначается только активным запросам и освобождается после обслуживания запроса. Это происходит в случае, даже если сеанс пользователя или подключение, в которых был создан запрос, остаются открытыми.
Параметр конфигурации сервера max worker threads не ограничивает все потоки, которые могут быть порождены внутри подсистемы. Системные потоки, требуемые для LazyWriter, контрольной точки, модуля записи журнала, Service Broker, диспетчера блокировок или других задач, порождаются независимо от этого ограничения. Группы доступности используют некоторые рабочие потоки из максимального предела рабочих потоков , но также используют системные потоки (см. раздел Использование потоков группами доступности). Если количество настроенных потоков превышено, следующий запрос предоставляет сведения о системных задачах, создающих дополнительные потоки.
```
SELECT s.session_id,
    r.command,
    r.STATUS,
    r.wait_type,
    r.scheduler_id,
    w.worker_address,
    w.is_preemptive,
    w.STATE,
    t.task_state,
    t.session_id,
    t.exec_context_id,
    t.request_id
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r
    ON s.session_id = r.session_id
INNER JOIN sys.dm_os_tasks AS t
    ON r.task_address = t.task_address
INNER JOIN sys.dm_os_workers AS w
    ON t.worker_address = w.worker_address
WHERE s.is_user_process = 0;
```

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

Это расширенный параметр, и изменять его следует только опытным администраторам баз данных или сертифицированным по SQL Server специалистам. Если вы считаете, что есть проблема с производительностью, вероятно, причина не в доступности рабочих потоков. Причина, скорее всего, связана с действиями, которые занимают рабочие потоки и не освобождают их. В число примеров входят длительные запросы или узкие места в системе (операции ввода-вывода, блокировка, ожидания кратковременной блокировки, ожидания сетевых операций). Рекомендуется найти причину проблемы производительности, прежде чем изменять параметр max worker threads. Дополнительные сведения об оценке производительности см. в статье Наблюдение и настройка производительности.
Пул потоков помогает оптимизировать производительность при подключении к серверу большого числа клиентов. Обычно для каждого запроса в операционной системе создается отдельный поток. Однако в случае сотен соединений с сервером, использование одного потока на каждый запрос приводит к потреблению большого числа системных ресурсов. Параметр max worker threads позволяет SQL Server создавать пул рабочих потоков, чтобы обслужить большое число запросов, что улучшает производительность.

В следующей таблице показано автоматически настроенное число максимальных рабочих потоков (если задано значение 0) на основе различных сочетаний логических ЦП, архитектуры компьютера и версий SQL Server с помощью формулы: Максимальное число рабочих ролей по умолчанию + ((логические ЦП - 4) * Рабочие роли на ЦП)).

Число логических ЦП	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

До SQL Server 2016 (13.x) с пакетом обновления 1 (SP1) рабочие роли на ЦП зависят только от архитектуры (32-разрядная или 64-разрядная):

Число логических ЦП	32-разрядный компьютер ¹	64-разрядный компьютер
< = 4	256	512
> 4	256 + ((логические ЦП - 4) * 8)	512 ² + ((логические ЦП - 4) * 16)

Начиная с SQL Server 2016 (13.x); SP2 и SQL Server 2017 (14.x);, Число рабочих потоков на ЦП зависит от архитектуры и количества процессоров (от 4 до 64 или больше 64):

Число логических ЦП	32-разрядный компьютер ¹	64-разрядный компьютер
< = 4	256	512
> 4 и < = 64	256 + ((логические ЦП - 4) * 8)	512 ² + ((логические ЦП - 4) * 16)
> 64	256 + ((логические ЦП - 4) * 32)	512 ² + ((логические ЦП - 4) * 32)

¹ Начиная с SQL Server 2016 г. (13.x) SQL Server больше нельзя установить в 32-разрядной операционной системе. Значения для 32-разрядного компьютера приведены, чтобы помочь клиентам, работающим с SQL Server 2014 (12.x) и более ранних версий. Для экземпляра SQL Server, работающего на 32-разрядном компьютере, рекомендуется ограничить число рабочих потоков до 1024.

² Начиная с SQL Server 2017 г. (14.x) значение по умолчанию Max Workers делится на 2 для компьютеров с менее чем 2 ГБ памяти.

Совет

Дополнительные сведения об использовании более 64 логических ЦП см. в статье Рекомендации по выполнению SQL Server на компьютерах с более чем 64 ЦП.

Если все рабочие потоки заняты выполнением длительных запросов, SQL Server может не отвечать на другие запросы, пока один из потоков не завершит работу и не станет доступным. Хотя это не дефект, иногда это может быть нежелательным. Если процесс не отвечает и новые запросы не могут быть обработаны, подключитесь к SQL Server через выделенное административное соединение (DAC) и уничтожьте процесс. Во избежание этого увеличьте максимальное число потоков управления.

Разрешения

Разрешения на выполнение хранимой процедуры sp_configure без параметров или только с первым параметром по умолчанию предоставляются всем пользователям. Для выполнения sp_configure с обоими параметрами для изменения параметра конфигурации или выполнения инструкции RECONFIGURE пользователю должно быть предоставлено ALTER SETTINGS разрешение на уровне сервера. Разрешение ALTER SETTINGS неявным образом предоставлено предопределенным ролям сервера sysadmin и serveradmin.

С помощью SQL Server Management Studio (SSMS)

В обозревателе объектов щелкните правой кнопкой мыши сервер и выберите пункт Свойства.
Выберите узел Процессоры .
В поле Максимальное число рабочих потоков введите или выберите значение от 128 до 65 535.

Совет

Используйте параметр max worker threads для установки количества рабочих потоков, доступных процессам SQL Server . Значение по умолчанию параметра max worker threads является оптимальным для большинства систем.
Но в зависимости от конфигурации системы установка параметра max worker threads в меньшее значение может улучшить производительность. Дополнительные сведения см. в разделе Рекомендации этой статьи.

Использование Transact-SQL

Установите соединение с компонентом Компонент Database Engine.
На стандартной панели выберите пункт Создать запрос.
Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить. В этом примере описывается использование процедуры sp_configure для задания значения параметра max worker threads равным 900.

EXEC sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

EXEC sp_configure 'max worker threads', 900;
GO

RECONFIGURE;
GO

Изменения вступят в силу сразу после выполнения RECONFIGURE без необходимости перезапуска Компонент Database Engine.