Конфигурация сервера: максимальное число рабочих потоков

Область применения: 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, диспетчера блокировок или других задач, порождаются независимо от этого ограничения. Группы доступности используют некоторые рабочие потоки изнутри max worker thread limit , но также используют системные потоки (см. раздел "Использование потоков по группам доступности"). Если число настроенных потоков превышается, следующий запрос содержит сведения о системных задачах, которые породили дополнительные потоки.

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 параметр позволяет SQL Server создавать пул рабочих потоков для обслуживания большего количества запросов, что повышает производительность.

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

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

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

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

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

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

Разрешения

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

Использование SQL Server Management Studio (SSMS)

1. В обозревателе объектов щелкните правой кнопкой мыши сервер и выберите пункт Свойства.

2. Выберите узел процессоров.

3. В поле Максимальное число рабочих потоков введите или выберите значение от 128 до 65 535.

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

1. Соединитесь с ядром СУБД .

2. На стандартной панели выберите пункт Создать запрос.

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

   USE master;
   GO
   
   EXECUTE sp_configure 'show advanced options', 1;
   GO
   
   RECONFIGURE;
   GO
   
   EXECUTE sp_configure 'max worker threads', 900;
   GO
   
   RECONFIGURE;
   GO
   
   EXECUTE sp_configure 'show advanced options', 0;
   GO
   
   RECONFIGURE;
   GO
   

Изменение вступит в силу сразу после выполнения RECONFIGURE, не требуя перезапуска ядро СУБД.

Связанный контент

• Параметры конфигурации сервера
• RECONFIGURE (Transact-SQL)
• sp_configure (Transact-SQL)
• Диагностическое подключение для администраторов баз данных