Ms sql 2008r2 ограничить количество процессоров
This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.
Параметры
Answered by:
Перед началом
Соответствие процессоров
Связывает процессоры с определенными потоками, чтобы устранить чрезмерную нагрузку на процессоры и уменьшить количество переходов потоков между процессорами. Дополнительные сведения см. в разделе Параметр конфигурации сервера "affinity mask".
Повысить приоритет SQL Server
Указывает следует ли SQL Server выставить более высокий приоритет планирования Microsoft Windows по сравнению с другими процессами на том же компьютере. Дополнительные сведения см. в статье Настройка параметра конфигурации сервера priority boost.
Этот параметр недоступен в SSMS 18.x и более поздних версиях.
Основные понятия ресурсов
Приведенные ниже три понятия лежат в основе понимания работы регулятора ресурсов.
Пулы ресурсов. Пул ресурсов представляет физические ресурсы сервера. Пул можно считать виртуальным экземпляром SQL Server внутри экземпляра SQL Server . При установке SQL Server создается два пула ресурсов (внутренний и по умолчанию). Кроме того, регулятор ресурсов поддерживает определяемые пользователями пулы ресурсов. Дополнительные сведения см. в разделе Resource Governor Resource Pool.
Группы рабочей нагрузки. Группа рабочей нагрузки выступает в качестве контейнера для запросов сеансов, имеющих подобные критерии классификации. Рабочая нагрузка обеспечивает статистический мониторинг сеансов и определяет политики для сеансов. Каждая группа рабочей нагрузки существует в пуле ресурсов. При установке SQL Server создается две группы рабочей нагрузки (внутренняя и по умолчанию), которые сопоставляются с соответствующими пулами ресурсов. Кроме того, регулятор ресурсов поддерживает определяемые пользователями группы рабочей нагрузки. Дополнительные сведения см. в разделе Resource Governor Workload Group.
Классификация. Процесс классификации назначает входящие сеансы группе рабочей нагрузки с учетом характеристик сеанса. Логику классификации можно адаптировать путем написания определяемой пользователем функции, называемой функцией-классификатором. Кроме того, регулятор ресурсов поддерживает определяемую пользователем функцию-классификатор для реализации правил классификации. Дополнительные сведения см. в разделе Resource Governor Classifier Function.
Регулятор ресурсов никак не управляет выделенным административным соединением. Нет необходимости классифицировать запросы выделенного административного соединения, которые выполняются во внутренней группе рабочей нагрузки и пуле ресурсов.
В контексте регулятора ресурсов можно обрабатывать описанные выше понятия как компоненты. На следующей иллюстрации показаны эти компоненты и отношения между ними в среде компонента Database Engine. С точки зрения обработки упрощенный поток выглядит следующим образом.
Имеется входящее соединение с сеансом (сеанс 1 из n).
Сеанс классифицирован (классификация).
Рабочая нагрузка сеанса направляется в группу рабочей нагрузки, например в группу 4.
Группа рабочей нагрузки использует пул ресурсов, с которым она связана, например Пул 2.
Пул ресурсов предоставляет или ограничивает доступ к ресурсам, которые требуются приложению, например Приложению 3.
В этом разделе описывается настройка параметра конфигурации сервера max degree of parallelism (MAXDOP) в SQL Server с помощью среды SQL Server Management Studio или Transact-SQL. Если экземпляр SQL Server работает на компьютере с более чем одним микропроцессором или ЦП, Компонент Database Engine определяет, можно ли использовать параллелизм. Уровень параллелизма ограничивает максимальное число процессоров, которые задействуются для выполнения одной инструкции для каждого выполнения параллельных планов. Для ограничения количества процессоров в плане параллельного выполнения может быть использован параметр max degree of parallelism . Дополнительные сведения об ограничениях максимальной степени параллелизма (MAXDOP) см. в разделе Рекомендации на этой странице. SQL Server учитывает планы параллельного выполнения для запросов, операций с индексами на языке DDL, параллельной вставки, изменения столбца в режиме "в сети", параллельного сбора статистики и заполнения статических курсоров и курсоров, управляемых набором ключей.
SQL Server 2019 (15.x) содержит автоматические рекомендации по настройке параметра конфигурации сервера MAXDOP в процессе установки на основе количества доступных процессоров. Пользовательский интерфейс программы установки позволяет либо принять рекомендуемые параметры, либо задать свое значение. Дополнительные сведения см. в разделе Конфигурация ядра СУБД — страница MaxDOP.
В Базе данных и Управляемом экземпляре SQL Azure параметр MAXDOP по умолчанию для каждой новой отдельной базы данных, базы данных эластичного пула и управляемого экземпляра имеет значение 8. В Базе данных SQL Azure значение MAXDOP в конфигурации для каждой отдельной базы имеет значение 8. В Управляемом экземпляре SQL Azure параметр максимальной степени параллелизма (MAXDOP) в конфигурации сервера имеет значение 8.
Дополнительные сведения об MAXDOP в База данных SQL Azure см. в статье Настройка максимальной степени параллелизма (MAXDOP) в Базе данных SQL Azure.
Автоматически устанавливать маску схожести ввода-вывода для всех процессоров
Позволяет SQL Server устанавливать сопоставление ввода-вывода.
Текущие значения
Просмотр текущих значений для параметров на этой панели. Эти значения доступны только для чтения.
Answered by:
Использовать волокна Windows (использование упрощенных пулов)
Вы можете использовать легковесные потоки (волокна) Windows вместо обычных потоков для службы SQL Server. Такая возможность доступна только в Windows 2003 Server Edition. Дополнительные сведения см. в разделе Параметр конфигурации сервера «использование упрощенных пулов».
Этот параметр недоступен в SSMS 18.x и более поздних версиях.
Автоматически устанавливать маску соответствия для всех процессоров
Позволяет SQL Server устанавливать сопоставление процессоров.
All replies
From Management Studio, connect to the SQL Server then right click over it and choose Propierties.
Uncheck "Automatically set . . ." and then select the amount of logical CPUs you want to use
Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you
Thanks for your reply. It is really helpful. Is there any T-SQL query which tells me how many CPU is used by SQL and how many CPU used by Windows? I found following query but i guess it shows just number of physical CPU used by SQL but not by both windows and SQL.
SELECT cpu_count / hyperthread_ratio AS physical_cpu_sockets
FROM sys.dm_os_sys_info
Following query will confirm how many CPUs SQL Server is using -
select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers where status='VISIBLE ONLINE'
Following query lists how many CPUs a particular instance of SQL Server can see -
select cpu_count from sys.dm_os_sys_info
You should also understand parallelism and its configuration options.These settings should be set properly after analyzing the work load.
Anup | Database Consultant
Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
In addition to the above, please do concentrate on the below links before doing on cpu configuration.
The below links are about NUMA.
Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)
The questions are already answered by the experts but just to summarize,
1. First of all we need to clarify if we can use all the 8 physical processors. As per the article shared by Satish : - http://msdn.microsoft.com/en-us/library/ms143760(v=sql.100).aspx , if we have enterprise/developer edition then only we can make use of all the "physical" processors which the OS can see.
Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
|Team Blog:- Team Blog
По памяти SQL обрезали чтобы все не жрало а вот по процикам есть такая возможность? объясняю : обработка расчета себестоимости занимает 3 - 4 часа занимает ресурсы SQL сервера на 100% чтобы не мешать народу работать подняли SQL на терминале и пробные просчеты проводим там а финишный уже в рабочей БД. вот тока днем иногда хочется посчитать но в момент расчета сервер занят только этим. Как SQL ограничить чтобы хоть капельку оставляло остальным в идеале по ядрам пусть берет 6 из 8ми и лопатит.
(10) а подробнее нельзя? интересна физика. попробовать смогу тока завтра. вчерашний расчет обработают.
нашел. у меня SQL 2008 но не думаю что смысл параметра отличается
Если SQL Server 2005 работает на многопроцессорном компьютере, он определяет оптимальную степень параллелизма, то есть количество процессоров, задействованных для выполнения одной инструкции для каждого из планов параллельного выполнения. Для ограничения количества процессоров в плане параллельного выполнения может быть использован параметр max degree of parallelism. Установленное по умолчанию значение 0 использует все доступные процессоры. Чтобы отключить формирование параллельных планов, присвойте параметру max degree of parallelism значение 1. Значение выше 1 (до 64) позволяет ограничить максимальное количество процессоров, используемых при выполнении одного запроса. Если указано значение, превышающее число доступных процессоров, используется фактическое число доступных процессоров. Если у компьютера только один процессор, то значение параметра max degree of parallelism учитываться не будет.
(12) ограничить максимальное количество процессоров, используемых при выполнении ОДНОГО ЗАПРОСА
когда 0, тяжелый запрос раскидывается по всем доступным процам, и все остальные курят и ждут пока он выполнится
больше чем уверен, что сабж можно ускорить раз в 10!
обычно основное время занимает запись, если обрабработка долго работает без записи объектов в базу - то на 99% хреновая это обработка (или база слишком нормализована)
(16) вот и вопрос почему =1 а не например = 3 ядер все равно много а вот насколько замедлится выполнение при сокращении с неограниченно (в данном случае 8) до одного.
(17) не думаю что имеет смысл обсуждать оптимизацию конкретного расчета в этой ветке. да и писал не я а франчи. разгребать и оптимизировать если надо у меня сейчас нет ни знаний ни времени. там очень развернутый просчет. в не сезон считает работы компании расчет занимает 30-40 минут. сейчас растут объемы производства.
блин много поначитался про этот max degree of parallelism
такое впечатление что лагерь разбит на две части в какое значение его взводить.
1. в какой момент изменения вступят в силу , в момент сохранения параметра или нужно пере запускать сервис?
2. все таки при работе 7-8 десятков пользователей в УПП какое значение выгоднее? (причем на SQL еще живет бухия с 15 пользователями и архивная 8.1 УПП)
ищи у себя статистику по этим видам блокировок.
а вообще если регулярно не делать обновление статистики - то лучше ставить параллелизм = 1, ведь именно на основании статистики он и строит планы запроса, и если статистика старая он строится далеко не оптимально, тогда и возникают блокировки
(3)Это не всегда хорошо для производительности, да он и сам больше выделенных процессоров не должен параллелить
SQL Server Resource Governor — это компонент, предназначенный для управления рабочей нагрузкой SQL Server и использованием системных ресурсов. Resource Governor позволяет задать ограничения на объем ресурсов ЦП, памяти и количество физических операций ввода-вывода, которые могут использоваться для входящих запросов приложений.
Хотя База данных SQL Azure использует Resource Governor (помимо других методик) для управления ресурсами, пользовательская конфигурация настраиваемых пулов ресурсов и групп рабочих нагрузок в Базе данных SQL Azure не поддерживается. В Azure Synapse Analytics используется другая реализация аналогичного поведения Resource Governor с функцией классификации рабочих нагрузок.
Настроенные значения
Отображает настроенные значения для параметров на этой панели. В случае изменения этих значений выберите пункт Текущие значения и посмотрите, вступили ли в силу внесенные изменения. В противном случае первым должен быть перезапущен экземпляр SQL Server.
Преимущества регулятора ресурсов
Регулятор ресурсов позволяет управлять рабочими нагрузками и ресурсами SQL Server путем задания лимитов на потребление ресурсов входящими запросами. В контексте регулятора ресурсов рабочая нагрузка представляет собой набор запросов приблизительно одинакового размера, которые могут и должны обрабатываться как единая сущность. Это не жесткое требование, но чем однообразнее выглядит шаблон использования ресурсов рабочей нагрузки, тем больше преимуществ можно извлечь из регулятора ресурсов. Ограничения на ресурсы можно перенастроить в режиме реального времени, при этом воздействие на выполняемую рабочую нагрузку остается минимальным.
В среде, имеющей несколько различных рабочих нагрузок на одном и том же сервере, регулятор ресурсов позволяет различать эти рабочие нагрузки и распределять общие ресурсы по мере запросов в зависимости от установленных ограничений. Эти ресурсы — ЦП, память и физические операции ввода-вывода.
С помощью регулятора ресурсов можно выполнить следующее.
Обеспечить облуживание многих клиентов и изоляцию ресурсов на одиночных экземплярах SQL Server, обслуживающих несколько клиентских рабочих нагрузок. То есть можно разделить доступные ресурсы сервера среди рабочих нагрузок, минимизировав проблемы, которые могут возникнуть при конкуренции рабочих нагрузок за ресурсы.
Предоставить прогнозируемую производительность и поддержку соглашений об уровне обслуживания для клиентов рабочих нагрузок в среде с несколькими рабочими нагрузками и несколькими пользователями.
Изолировать и ограничивать неконтролируемые запросы и регулировать ресурсы ввода-вывода для таких операций, как DBCC CHECKDB, способных занять всю производительность подсистемы ввода-вывода и негативно повлиять на другие рабочие нагрузки.
Добавить детализированное отслеживание ресурсов для возврата платежей за использование ресурсов и предоставлять прогнозируемое выставление счетов потребителям ресурсов сервера.
Answers
Following query will confirm how many CPUs SQL Server is using -
select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers where status='VISIBLE ONLINE'
Following query lists how many CPUs a particular instance of SQL Server can see -
select cpu_count from sys.dm_os_sys_info
You should also understand parallelism and its configuration options.These settings should be set properly after analyzing the work load.
Anup | Database Consultant
Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
What is your goal here? Are you suggesting that you want to ensure there is at least one physical CPU for use by the OS only?
Unless you have a specific proven need to do this I would suggest leaving the default configuration in place. SQL Server will not consume all CPU resource and starve the OS. A cooperative model is used I believe ensuring that the OS can always access CPU resource because SQL Server will concede to it.
Question
I have a windows 2008 server. Now I need to install SQL server 2008 on this box. The box has 8 physical and 32 logical CPU. While installing SQL Server is there anyway I can configure that SQL Server will take maximum 7 physical and 28 logical CPU and widows will use rest of the CPU? Or will SQL server use all the physical and logical CPU available on the server?
Использование Transact-SQL
Настройка параметра максимальной степени параллелизма
Установите соединение с компонентом Компонент Database Engine.
На панели «Стандартная» нажмите Создать запрос.
Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В этом примере описывается использование процедуры sp_configure для задания значения параметра max degree of parallelism равным 16 .
Используйте эту страницу, чтобы просмотреть или изменить параметры процессоров. Настройки соответствия процессоров доступны только в случае, если в системе установлено более одного процессора.
Привязка ввода-вывода
Связывает операции дискового ввода-вывода Microsoft SQL Server с определенным подмножеством ЦП. Дополнительные сведения см. в разделе Параметр конфигурации сервера "affinity Input-Output mask".
Рекомендации
Начиная с SQL Server 2016 (13.x); по умолчанию система Компонент Database Engine автоматически создает узлы архитектуры Soft-NUMA, если во время запуска обнаруживает более восьми физических ядер на один сокет или узел NUMA. Компонент Database Engine помещает логические процессоры одного и того же физического ядра в разных узлах программной архитектуры NUMA. Рекомендации, приведенные в следующей таблице, нацелены на сохранение рабочих потоков параллельного запроса на одном узле программной архитектуры NUMA. Это улучшит производительность запросов и распределение рабочих потоков между узлами NUMA для рабочей нагрузки. Дополнительные сведения см. в разделе Программная архитектура NUMA.
Начиная с SQL Server 2016 (13.x); при настройке значения параметра max degree of parallelism в конфигурации сервера следуйте приведенным ниже рекомендациям.
Конфигурация сервера | Количество процессоров | Руководство |
---|---|---|
Сервер с одним узлом NUMA | Не более 8 логических процессоров | Значение параметра MAXDOP не должно превышать количество логических процессоров |
Сервер с одним узлом NUMA | Больше 8 логических процессоров | Значение параметра MAXDOP должно быть равно 8 |
Сервер с несколькими узлами NUMA | Не более 16 логических процессоров на узел NUMA | Значение параметра MAXDOP не должно превышать количество логических процессоров на каждый узел NUMA |
Сервер с несколькими узлами NUMA | Больше 16 логических процессоров на каждый узел NUMA | Значение MAXDOP должно быть равно половине количества логических процессоров на узел NUMA со значением MAX, равным 16 |
Узлы NUMA в приведенной выше таблице — это узлы программной архитектуры NUMA, автоматически создаваемые в SQL Server 2016 (13.x); и более поздних версий, или аппаратной архитектуры NUMA, если узлы программной архитектуры NUMA отключены.
Эти же правила используются в том случае, если значение max degree of parallelism задано для групп рабочей нагрузки регулятора ресурсов. Дополнительные сведения см. в разделе CREATE WORKLOAD GROUP (Transact-SQL).
В версиях с SQL Server 2008 по SQL Server 2014 (12.x) при настройке значения параметра max degree of parallelism в конфигурации сервера следуйте приведенным ниже рекомендациям.
Конфигурация сервера | Количество процессоров | Руководство |
---|---|---|
Сервер с одним узлом NUMA | Не более 8 логических процессоров | Значение параметра MAXDOP не должно превышать количество логических процессоров |
Сервер с одним узлом NUMA | Больше 8 логических процессоров | Значение параметра MAXDOP должно быть равно 8 |
Сервер с несколькими узлами NUMA | Не более 8 логических процессоров на узел NUMA | Значение параметра MAXDOP не должно превышать количество логических процессоров на каждый узел NUMA |
Сервер с несколькими узлами NUMA | Больше 8 логических процессоров на узел NUMA | Значение параметра MAXDOP должно быть равно 8 |
Использование среды SQL Server Management Studio
Настройка параметра максимальной степени параллелизма
В обозревателе объектов щелкните правой кнопкой мыши сервер и выберите пункт Свойства.
Щелкните узел Дополнительно .
В поле Максимальная степень параллелизма укажите максимальное число процессоров, которое может быть использовано в плане параллельного выполнения.
Замечания
Это расширенный параметр, и изменять его следует только опытным администраторам баз данных или сертифицированным по SQL Server специалистам.
Если параметр affinity mask имеет значение, отличное от значения по умолчанию, он может ограничивать число процессоров, доступных для SQL Server в симметричных многопроцессорных системах (SMP).
Установка значения 0 в качестве максимальной степени параллелизма (MAXDOP) позволяет SQL Server использовать все доступные процессоры (до 64). Однако в большинстве случаев использовать это значение не рекомендуется. Дополнительные сведения о рекомендуемых значениях максимальной степени параллелизма см. в разделе Рекомендации на этой странице.
Чтобы отключить создание параллельных планов, присвойте параметру max degree of parallelism значение 1. Задайте значение для параметра в диапазоне от 1 до 32 767, чтобы указать максимальное количество процессорных ядер, которые могут использоваться при выполнении одного запроса. Если указано значение, превышающее количество доступных процессоров, используется действительное количество доступных процессоров. Если у компьютера только один процессор, то значение параметра max degree of parallelism учитываться не будет.
Ограничение максимальной степени параллелизма задается для каждой задачи. Оно не задается для каждого запроса. Это означает, что при выполнении параллельных запросов один запрос может порождать несколько задач вплоть до ограничения MAXDOP и каждая задача будет использовать одну рабочую роль и один планировщик. Дополнительные сведения см. в разделе Планирование параллельных задач статьи Руководство по архитектуре потоков и задач.
Параметр конфигурации сервера max degree of parallelism можно переопределить:
- на уровне запроса с помощью указания запросаMAXDOP;
- на уровне базы данных с помощью конфигурации области баз данныхMAXDOP;
- на уровне рабочей нагрузки с помощью параметра конфигурации группы рабочей нагрузки Resource GovernorMAX_DOP.
Операции по созданию и перестройке индексов, а также по удалению кластеризованного индекса могут оказаться достаточно ресурсоемкими. Значение параметра max degree of parallelism для операций с индексами можно переопределить, указав в инструкции параметр индекса MAXDOP. Значение MAXDOP применяется к инструкции во время выполнения и в метаданных индекса не хранится. Дополнительные сведения см. в статье Настройка параллельных операций с индексами.
Помимо запросов и операций с индексами, этот параметр также управляет степенью параллелизма при выполнении инструкций DBCC CHECKTABLE, DBCC CHECKDB и DBCC CHECKFILEGROUP. Планы параллельного выполнения для этих инструкций можно отключить с помощью флага трассировки 2528. Дополнительные сведения см. в разделе Флаги трассировки (Transact-SQL).
Ограничения регулятора ресурсов
В данном выпуске регулятора ресурсов имеются следующие ограничения.
Управление ресурсами ограничено компонентом Компонент SQL Server Database Engine. Использование Resource Governor для Службы Analysis Services, Службы Integration Services и Службы Reporting Services невозможно.
Наблюдение за рабочей нагрузкой и управление рабочими нагрузками между экземплярами SQL Server не предусмотрены.
Управление рабочими нагрузками OLTP с помощью регулятора ресурсов возможно, но данные типы запросов, как правило, непродолжительные, не всегда находятся в ЦП настолько долго, чтобы к ним можно было применить средства управления пропускной способностью. Это может привести к искажению возвращаемых статистических данных по загрузке ЦП.
Возможность администрирования физического ввода-вывода относится только к операциям пользователя, но не к задачам системы. К системным задачам относятся операции записи в журнал транзакций и операции отложенного ввода-вывода модуля отложенной записи. Resource Governor работает главным образом с пользовательскими операциями чтения, так как большинство операций записи обычно выполняются системными задачами.
Нельзя задать пороговые значения ввода-вывода на внутреннем пуле ресурсов.
Максимальное число потоков исполнителя.
Значение 0 позволяет SQL Server динамически устанавливать количество рабочих потоков. Эта настройка является наиболее подходящей для большинства систем. Однако в зависимости от конфигурации системы, присвоение этому параметру определенного значения иногда улучшает производительность. Дополнительные сведения см. в статье Настройка параметра конфигурации сервера max worker threads.
безопасность
Permissions
Разрешения на выполнение хранимой процедуры sp_configure без параметров или только с первым параметром по умолчанию предоставляются всем пользователям. Для выполнения процедуры sp_configure с обоими параметрами для изменения параметра конфигурации или запуска инструкции RECONFIGURE необходимо иметь разрешение ALTER SETTINGS на уровне сервера. Разрешение ALTER SETTINGS неявным образом предоставлено предопределенным ролям сервера sysadmin и serveradmin .
Читайте также: