Максимальное число рабочих потоков sql

Пошаговые руководства, шпаргалки, полезные ссылки.

Инструменты пользователя

Инструменты сайта

Боковая панель

Опция SQL Server "Maximum worker threads"

Максимальное количество потоков (опция Maximum worker threads) в конфигурации по умолчанию в SQL Server 2016 установлено в . C таким значением сервер не создает больше потоков, чем рассчитано по формуле, описанной в документе Configure the max worker threads Server Configuration Option. В этом документе приводится таблица расчётных значений в зависимости от количества процессоров и битности сервера.

Например, в нашем случае используется x64 сервер с 12 ядрами. В этом случае расчёт будет выполняться по формуле 512 + ((12 logical CPU’s – 4) * 16) = 640

Увеличивая значения Maximum worker threads от рассчитываемого автоматически предела по вышеуказанной формуле, стоит помнить о том, что с увеличением этого значения повышаются накладные расходы на обслуживание экземпляра SLQ Server, выходящие за рамки параметра Maximum server memory. То есть с увеличением Maximum worker threads, нужно не забыть про перерасчёт показателя Maximum server memory.

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

К тому же если верить официальной документации, в некоторых ситуациях не увеличение, а напротив, – уменьшение Maximum worker threads от рассчитываемого автоматически значения, может приводить к улучшению производительности экземпляра SQL Server.

В общем случае лучше оставить это значение опции Maximum worker threads в конфигурации по умолчанию, то есть равным .

Проверено на следующих конфигурациях:

Версия ОС	Версия SQL Server
Microsoft Windows Server 2012 R2 Standard EN (6.3.9600)	Microsoft SQL Server 2016 SP2 CU4 (13.0.5233.0)

Автор первичной редакции:
Алексей Максимов
Время публикации: 13.02.2019 11:58

ОБЛАСТЬ ПРИМЕНЕНИЯ: SQL Server База данных SQL Azure Azure Synapse Analytics (хранилище данных SQL) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

В этом разделе описываются способы настройки параметра конфигурации сервера max worker threads в SQL Server SQL Server с помощью среды SQL Server Management Studio SQL Server Management Studio или Transact-SQL Transact-SQL . This topic describes how to configure the max worker threads server configuration option in SQL Server SQL Server by using SQL Server Management Studio SQL Server Management Studio or Transact-SQL Transact-SQL . Параметр max worker threads используется для установки количества рабочих потоков, доступных процессам SQL Server SQL Server . The max worker threads option configures the number of worker threads that are available to SQL Server SQL Server processes. SQL Server SQL Server используются собственные службы для потоков операционных систем, поэтому один или несколько потоков одновременно поддерживают все сети, которые поддерживает SQL Server SQL Server , еще один поток обрабатывает контрольные точки базы данных, а пул потоков обрабатывает запросы от всех пользователей. uses the native thread services of the operating systems so that one or more threads support each network that SQL Server SQL Server supports simultaneously, another thread handles database checkpoints, and a pool of threads handles all users. Значение по умолчанию для параметра max worker threads — 0. The default value for max worker threads is 0. Это позволяет SQL Server SQL Server автоматически настраивать количество рабочих потоков при запуске. This enables SQL Server SQL Server to automatically configure the number of worker threads at startup. Настройка по умолчанию является оптимальной для большинства систем. The default setting is best for most systems. Но иногда, в зависимости от конфигурации системы, установка параметра max worker threads в другое определенное значение может улучшить производительность. However, depending on your system configuration, setting max worker threads to a specific value sometimes improves performance.

В этом разделе In This Topic

Перед началом работы Before you begin:

Настройка параметра max worker threads с помощью различных средств. To configure the max worker threads option, using:

Перед началом Before You Begin

Ограничения Limitations and Restrictions

• Если реальное количество запросов меньше значения, заданного параметром max worker threads, каждый запрос обрабатывается одним потоком. When the actual number of query requests is less than the amount set in max worker threads, one thread handles each query request. Однако если реальное количество потоков превышает число, заданное параметром max worker threads, SQL Server SQL Server использует пул рабочих потоков, так что следующий доступный рабочий поток сможет обработать запрос. However, if the actual number of query request exceeds the amount set in max worker threads, SQL Server SQL Server pools the worker threads so that the next available worker thread can handle the request.

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

Это расширенный параметр, и изменять его следует только опытным администраторам баз данных или сертифицированным по SQL Server SQL Server специалистам. This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server SQL Server professional. Если вы считаете, что есть проблема с производительностью, вероятно, причина не в доступности рабочих потоков. If you suspect that there is a performance problem, it is probably not the availability of worker threads. Скорее всего, их ожидание вызвано чем-то наподобие операций ввода-вывода. The cause is more likely something like I/O that is causing the worker threads to wait. Рекомендуется найти причину проблемы производительности, прежде чем изменять параметр max worker threads. It is best to find the root cause of a performance issue before you change the max worker threads setting.

Пул потоков помогает оптимизировать производительность при подключении к серверу большого числа пользователей. Thread pooling helps optimize performance when large numbers of clients are connected to the server. Обычно для каждого запроса в операционной системе создается отдельный поток. Usually, a separate operating system thread is created for each query request. Однако в случае сотен соединений с сервером, использование одного потока на каждый запрос приводит к потреблению большого числа системных ресурсов. However, with hundreds of connections to the server, using one thread per query request can consume large amounts of system resources. Параметр max worker threads позволяет SQL Server SQL Server создавать пул рабочих потоков, чтобы обслужить большое число запросов, что улучшает производительность. The max worker threads option enables SQL Server SQL Server to create a pool of worker threads to service a larger number of query requests, which improves performance.

В следующей таблице показано автоматически настраиваемое максимальное число рабочих потоков для различных сочетаний процессоров и версий SQL Server SQL Server . The following table shows the automatically configured number of max worker threads for various combinations of CPUs and versions of SQL Server SQL Server .

Число процессоров Number of CPUs	32-разрядный компьютер 32-bit computer	64-разрядный компьютер 64-bit computer
256 256	512 512
8 процессоров 8 processors	288 288	576 576
16 процессоров 16 processors	352 352	704 704
32 процессора 32 processors	480 480	960 960
64 процессора 64 processors	736 736	1472 1472
128 процессоров 128 processors	4224 4224	4480 4480
256 процессоров 256 processors	8320 8320	8576 8576

Используется следующая формула. Using the following formula:

Число процессоров Number of CPUs	32-разрядный компьютер 32-bit computer	64-разрядный компьютер 64-bit computer
256 256	512 512
> — 4 процессора и > 4 processors and	256 + ((число логических ЦП – 4) * 8) 256 + ((logical CPU’s – 4) * 8)	512 + ((число логических ЦП – 4) * 16) 512 + ((logical CPU’s – 4) * 16)
> — 64 процессора > 64 processors	256 + ((число логических ЦП – 4) * 32) 256 + ((logical CPU’s – 4) * 32)	512 + ((число логических ЦП – 4) * 32) 512 + ((logical CPU’s – 4) * 32)

SQL Server SQL Server больше нельзя устанавливать в 32-разрядной операционной системе. can no longer be installed on a 32-bit operating system. Значения для 32-разрядного компьютера приведены, чтобы помочь клиентам, работающим с SQL Server 2014 (12.x) SQL Server 2014 (12.x) и более ранних версий. 32-bit computer values are listed for the assistance of customers running SQL Server 2014 (12.x) SQL Server 2014 (12.x) and earlier. Для экземпляра SQL Server SQL Server , работающего на 32-разрядном компьютере, рекомендуется ограничить число рабочих потоков до 1024. We recommend 1,024 as the maximum number of worker threads for an instance of SQL Server SQL Server that is running on a 32-bit computer.

Рекомендации по использованию процессоров в количестве, превышающем 64, см. в разделе Рекомендации по использованию SQL Server на компьютерах, которые имеют более 64 процессоров. For recommendations on using more than 64 CPUs, refer to Best Practices for Running SQL Server on Computers That Have More Than 64 CPUs.

Если все рабочие потоки заняты выполнением длительных запросов, SQL Server SQL Server может не отвечать на другие запросы, пока один из потоков не завершит работу и не станет доступным. When all worker threads are active with long running queries, SQL Server SQL Server might appear unresponsive until a worker thread completes and becomes available. Хотя это и не ошибка, такое поведение иногда нежелательно. Although this is not a defect, it can sometimes be undesirable. Если процесс не отвечает и новые запросы не могут быть обработаны, подключитесь к SQL Server SQL Server через выделенное административное соединение (DAC) и уничтожьте процесс. If a process appears to be unresponsive and no new queries can be processed, then connect to SQL Server SQL Server using the dedicated administrator connection (DAC), and kill the process. Во избежание этого увеличьте максимальное число потоков управления. To prevent this, increase the number of max worker threads.

Параметр конфигурации сервера Максимальное количество рабочих потоков не ограничивает все потоки, которые могут быть порождены в системе. The max worker threads server configuration option does not limit all threads that may be spanwed in the system. Потоки, требуемые для групп доступности, Service Broker, диспетчера блокировок или других задач порождаются независимо от этого ограничения. Threads required for tasks such as Availibility Groups, Service Broker, Lock Manager, or others are spawned outside this limit. Если число настроенных потоков превышается, то следующий запрос будет содержать сведения о системных задачах, породивших дополнительные потоки. If the number of threads configured is being exceeded, the following query will provide information about the system tasks that have spawned the additional threads.

безопасность Security

Permissions Permissions

Разрешения на выполнение хранимой процедуры sp_configure без параметров или только с первым параметром по умолчанию предоставляются всем пользователям. Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. Для выполнения процедуры sp_configure с обоими параметрами для изменения параметра конфигурации или запуска инструкции RECONFIGURE необходимо иметь разрешение ALTER SETTINGS на уровне сервера. To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. Разрешение ALTER SETTINGS неявным образом предоставлено предопределенным ролям сервера sysadmin и serveradmin. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

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

Настройка параметра max worker threads To configure the max worker threads option

В обозревателе объектов щелкните правой кнопкой мыши сервер и выберите пункт Свойства. In Object Explorer, right-click a server and select Properties.

Щелкните узел Процессоры . Click the Processors node.

В поле Максимальное число рабочих потоков введите или выберите значение от 128 до 32 767. In the Max worker threads box, type or select a value from 128 through 32,767.

Используйте параметр max worker threads для установки количества рабочих потоков, доступных процессам SQL Server SQL Server . Use the max worker threads option to configure the number of worker threads available to SQL Server SQL Server processes. Значение по умолчанию параметра max worker threads является оптимальным для большинства систем. The default setting for max worker threads is best for most systems. Но в зависимости от конфигурации системы установка параметра max worker threads в меньшее значение может улучшить производительность. However, depending on your system configuration, setting max worker threads to a smaller value sometimes improves performance. Дополнительные сведения см. в разделе Рекомендации на этой странице. See Recommendations in this page for more information.

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

Настройка параметра max worker threads To configure the max worker threads option

Установите соединение с компонентом Компонент Database Engine Database Engine . Connect to the Компонент Database Engine Database Engine .

На панели «Стандартная» нажмите Создать запрос. From the Standard bar, click New Query.

Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. Copy and paste the following example into the query window and click Execute. В этом примере описывается использование процедуры sp_configure для задания значения параметра max worker threads равным 900 . This example shows how to use sp_configure to configure the max worker threads option to 900 .

Дальнейшие действия. После настройки параметра "Максимальное количество рабочих потоков" Follow Up: After you configure the max worker threads option

Изменения вступят в силу сразу после выполнения RECONFIGURE без необходимости перезапуска Компонент Database Engine Database Engine . The change will take effect immediately after executing RECONFIGURE, without requiring the Компонент Database Engine Database Engine to restart.

В целом настройка MS SQL Server для работы с 1С предприятия не сильно отличается от его обычной настройки, но все же есть некоторые нюансы выявленные опытным путем.

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

Содержание:

Установка MS SQL Server

Не будем рассматривать все шаги установки и затронем только те моменты, которые требуют особого внимания.

Выбор и настройка компонентов

Для работы MS SQL Server c 1С Предприятие достаточно выбрать следующий набор компонент:

• Службы компонента Dtabase Engine
• Средства связи клиентских средств
• Средства управления — основные
• Средства управления — полный набор (полный набор нам будет необходим для создания плана обслуживания)

Важно! Каталог общих компонентов лучше указывать на отдельном диске (отдельно от операционной системы). Это повысит скорость работы и отказоустойчивость.

Конфигурация сервера

Для запуска служб Агент SQL Server и SQL Database Engine указываем учетную запись. Можно создать отдельнную учетную запись с правами администратора, либо указать учетную запись Администратор. Однако стоит помнить — если Вы решите когда-нибудь сменить пароль для учетной записи, которую здесь указали, то и служба перестанет запускаться. Поэтому используйте учетную запись в которой не планируете менять пароль.

Настройке компонента Databse Engine

Указываем смешанный режим и задаем пароль для sa — системной учетной записи SQL Server.

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

Далее все настройки можно оставить по-умолчанию.

Настраиваем брэндмауэр для работы mssql и 1С Серверa

Создаем правила разрешающее входящие подключения на порт 1433 для MS SQL и 1541-1560 для 1С Сервера

Создаем правило для программы. Путь до программы будет выглядеть примерно так
C:Program FilesMicrosoft SQL ServerMSSQL13. MSSQLBinnsqlservr.exe

Настройка свойств сервера Ms SQL для работы с 1С

Запускаем Microsoft SQL Server Management Studio и подключаемся к серверу.

Открываем окно свойств сервера и переходим к пункту Память. Выставляем максимально допустимое значение выделения памяти под нужды SQL сервера. Если этого не сделать он скушает всю свободную память, потому-что по-умолчанию стоит значение 2147483647 МБ. Допустимое значение памяти можно рассчитать по формуле (использовал опыт Алексея Новоселова с Infostat.ru):
[Общее количество оперативной памяти сервера] – [4ГБ под систему(2ГБ если Win2003)] – [1,5 ГБ * количество процессов rphost (если SQL и 1С на одном сервере вращаются.)] Например если у нас на сервере всего 36 ГБ оперативной памяти, стоит Windows 2008 и запущено 8 процессов rphost то рассчет идет так: 36 — 4 — 1.5*8 = 20 ГБ ставим ограничение для SQL.

Переходим к пункту Процессор. Максимальное число рабочих потоков так же лучше установить вручную и задать значение 2048 так как при значении 0 число потоков может не превышать 255. Включаем параметр Поддерживать приоритет SQL.

Конечно эти советы по настройке свойств сервера не панацея и не во всех условиях они будут одинаково хороши, но для большинства случаев думаю вполне подойдет.

Настройка рабочей базы 1С Предприятия

Открываем свойства настраиваемой базы данных.

Теперь самое главное определится с моделью восстановления базы данных. Они настраиваются в нукте параметры. Рассмотрим две основные модели восстановления.

1. Простая. Ее нужно использовать в том случае, когда вы планируете делать бэкап раз в день и для вас не имеет значения возможность восстановления с точностью до определенного момента. Это может быть 1С Бухгалтерия или ЗУП где нет большого количества ежедневных транзакций. Делаете один бэкап каждую ночь и спите спокойно. Никаких сложностей.

2. Полная. Такую модель лучше всего использовать для бэкапа баз с большим количеством внутридневных транзакций, например продажи в 1С Розница. При такой модели у вас будут сохранятся все транзакции в журналах и будет возможность восстановления базы до любого момента времени. Но в этом случае придется повозится с настройками журналов транзакций.

Когда мы определились с моделью восстановления можно перейти к пункту [Файлы]

Для файла [Данные строк] размер авторасширения выставляем 200МБ. По-умолчанию выставлен 1МБ и это мало. Если у вас много транзакций по 1С, тогда SQL будет вынужден постоянно выполнять авторасширение и это будет тормозить его работу.

Настройку типа файла [Журнал] можно пропустить если используется простая модель восстановления.
Если используется полная то необходимо скорректировать настройки.Авторасширение установим 50МБ. Стоит обратить внимание на ограничение авторасширения и его лучше изменить т.к. значение по-умолчанию больше 2Тб. При большом количестве транзакций, например розничные продажи в 1С Розница, журнал транзакций будет расти очень быстро и вскоре у вас закончится свободное место на накопителе. Поэтому ограничение лучше установить на 10ГБ. Но это всего-лишь рекомендация, потому-что все индивидуально и зависит от количества транзакций.

При установке ограничения стоит помнить, что при достижении крайнего значения вас ждет ошибка: «журнал транзакций для базы данных заполнен» и 1С не будет запускаться. Чтобы журнал транзакций своевременно очищался необходимо настроить его бэкап в плане обслуживания базы данных. О том как создать план обслуживания базы данных читайте здесь.

Но очистка журнала транзакций не уменьшает размер самого файла, а только освобождает в нем свободное место для новых записей путем удаления неактивных завершенных транзакций.

Если же журнал переполнился, то его придется почистить вручную чтобы база заработала. Как это сделать читайте в этой статье.