Ms sql настройка процессоров

Обновлено: 07.07.2024

Настройка производительности базы данных — не самая приятная задача для специалистов по базам данных, но пренебрежение ею теперь обходится дороже, чем раньше. И разработчикам, и администраторам приходится заниматься этим регулярно. Например, один из моих клиентов недавно попросил выяснить причину высокой загрузки процессора в среде SQL Server, размещенной на экземпляре AWS EC2.

Для тех, кто не знаком с данной технологией, поясню, что экземпляр EC2 — это выделенная виртуальная машина, размещенная в «облаке» AWS компании Amazon. Существуют экземпляры EC2 с различными наборами вычислительных ресурсов, и плата взимается в зависимости от предоставляемых ресурсов процессора и оперативной памяти. Большинство поставщиков «облачных» служб используют схожие структуры затрат, где вычислительные ресурсы и память — факторы, определяющие плату за ресурсы в их среде.

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

динамические административные представления;
статистика по времени;
статистика клиента SQL Server Management Studio.

Рассмотрим каждый из вариантов, чтобы вы могли выбрать наиболее подходящий для своей задачи настройки. Сделаем это на основе тестового случая, подготовленного в демонстрационной среде. Я составил очень простую хранимую процедуру в экземпляре тестовой базы данных SQL Server с именем Wide World Importers (листинг 1).

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

Вариант первый: динамические административные представления

Существует два динамических административных представления (DMV), полезных при оценке показателей процессора и продолжительности запросов SQL Server: sys. dm_exec_query_stats и sys. dm_exec_procedure_stats. Первое предоставляет сведения о нерегламентированных запросах, второе — о выполненных хранимых процедурах. В остальном эти представления очень похожи по структуре и результатам. Каждое из них возвращает информацию о выполнении с минимальным/максимальным/средним и последним значениями всех показателей, в том числе ввода-вывода (чтение и запись), процессора и продолжительности. Поскольку в качестве примера используется хранимая процедура, запрашиваются результаты из dm_exec_procedue_stats. Показанный в листинге 2 запрос предоставляет информацию о последнем исполнении хранимой процедуры.

Значения last_worker_time и last_elapsed_time были получены в микросекундах, но я преобразовал их в миллисекунды для удобства сравнения с двумя другими вариантами, рассматриваемыми в статье. Миллисекунды — более широко распространенная единица измерения для времени выполнения и загрузки центрального процессора. Результаты выглядят таким образом, как показано на экране 1.

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

Кроме того, эта информация уже занесена в кэш, и пользователю нет необходимости выполнять запрос каждый раз, когда нужно получить информацию (при условии, что рассматриваемая хранимая процедура была выполнена хотя бы один раз). Изменить запрос, чтобы охватить информацию, относящуюся к среднему рабочему времени (времени загрузки процессора) и среднему затраченному времени (количеству времени, которое состоит не только из времени загрузки процессора, но и времени ожидания процессора через механизм планирования), просто. Достаточно выполнить деление на число выполнений.

Это единственный из трех вариантов, который будет хорошим диагностическим средством для начального отбора процедур для настройки: можно строить запросы к sys. dm_exec_procedure_stats и выполнять их в рабочей среде, не запуская самих процедур (в рабочей или тестовой среде). Вы сможете увидеть кэшированное поведение, так как метаданные, поступающие в эти DMV, сохраняются в кэше.

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

Отображение статистики по времени включается и выключается в SQL Server Management Studio (SSMS) на уровне сеансов с помощью следующей команды:

У статистики по времени есть ряд недостатков. Так, результаты рассчитываются только во время выполнения. Кроме того, вам необходимо вручную отмечать и рассчитывать итеративное влияние настроек после каждого изменения. Однако последний вариант очень удобен тем, что не предполагает ручных расчетов успехов или неудач процесса настройки. Он эффективнее, чем статистика по времени.

Вариант третий: просмотр статистики клиента

Это самый неясный вариант из трех, что удивительно, поскольку кнопка для включения соответствующей функции находится под рукой у каждого пользователя SQL Server Management Studio (экран 3).

Данная функция также доступна в панели меню в SSMS в разделе Query\Include Client Statistics («Запрос/Включить статистику клиента») или включается с помощью комбинации клавиш Shift+Alt+S.

Любой из трех вариантов предоставляет дополнительную вкладку при каждом выполнении запроса в окне запроса в SSMS. Каждое окно запроса — отдельный сеанс, и статистика клиента своя для каждого выполнения любого программного кода в конкретном окне запроса. Как выглядит статистика для окна запроса, соответствующего запросу, выполненному в каждом из приведенных выше сценариев, показано на экране 4.

В приведенном на экране 4 примере следует обратить внимание на несколько особенностей. Желтым цветом выделены те же метрики времени загрузки процессора (рабочее время) и времени выполнения, которые предоставляются в других вариантах. Существует некоторое сходство с использованием динамических административных представлений. Статистика клиента не ограничивается только временем загрузки процессора и продолжительностью. Благодаря этому она пригодна для настройки других параметров кроме загрузки процессора.

Может возникнуть вопрос, почему производительность на испытании Trial 2 настолько отличается, и это проблема, о которой упоминалось ранее: данная статистика относится к любому программному коду, выполняемому в текущем окне запроса. Испытания Trial 1 и Trial 3 связаны с вызовом EXEC для pOption1, но Trial 2 был результатом кода, запущенного для очистки пула буфера между выполнениями. Помните об этих особенностях при итерациях настройки. При итеративной настройке очень помогают стрелки «вверх» и «вниз», указывающие на улучшения после изменения и повторного исполнения программного кода.

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

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

Параметры

Соответствие процессоров

Связывает процессоры с определенными потоками, чтобы устранить чрезмерную нагрузку на процессоры и уменьшить количество переходов потоков между процессорами. Дополнительные сведения см. в разделе Параметр конфигурации сервера "affinity mask".

Привязка ввода-вывода

Связывает операции дискового ввода-вывода Microsoft SQL Server с определенным подмножеством ЦП. Дополнительные сведения см. в разделе Параметр конфигурации сервера "affinity Input-Output mask".

Автоматически устанавливать маску соответствия для всех процессоров

Позволяет SQL Server устанавливать сопоставление процессоров.

Автоматически устанавливать маску схожести ввода-вывода для всех процессоров

Позволяет SQL Server устанавливать сопоставление ввода-вывода.

Максимальное число потоков исполнителя.

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

Повысить приоритет SQL Server

Указывает следует ли SQL Server выставить более высокий приоритет планирования Microsoft Windows по сравнению с другими процессами на том же компьютере. Дополнительные сведения см. в статье Настройка параметра конфигурации сервера priority boost.

Этот параметр недоступен в SSMS 18.x и более поздних версиях.

Использовать волокна Windows (использование упрощенных пулов)

Вы можете использовать легковесные потоки (волокна) Windows вместо обычных потоков для службы SQL Server. Такая возможность доступна только в Windows 2003 Server Edition. Дополнительные сведения см. в разделе Параметр конфигурации сервера «использование упрощенных пулов».

Этот параметр недоступен в SSMS 18.x и более поздних версиях.

Настроенные значения

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

Текущие значения

Просмотр текущих значений для параметров на этой панели. Эти значения доступны только для чтения.

На производительность сервера существенно могут настройки, управляющие поведением мощностей процессора, дисков, доступа к памяти, размещения памяти и т.п.

В BIOS сервера отключаем все настройки по экономии электропитания процессора.

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

В некоторых случаях (особенно для HP!) надо зайти в BIOS сервера, и ВЫКЛЮЧИТЬ там пункты, в названии которых есть EIST и C1E.
Взамен надо там же найти пункты, связанные с процессором, в названии которых есть Turbo Boost, включить Intel SpeedStep и ВКЛЮЧИТЬ их.
Если в биосе есть общее указание режима энергосбережения — включить его в режим максимальной производительности (он ещё может называться «агрессивный»)

Обратите внимание, что такие настройки популярны, но бывают исключения, когда вендоры реализуют обозначенные выше настройки и механизмы работы иначе, и тогда может потребоваться не выключать, а включать какие-то пункты, связанные с EIST, SpeedStep и Turbo Boost.

Не забываем и также и про настройки схемы в операционной системе.

В конечном итоге надо не ориентироваться на названия этих пунктов, а на итоговые максимальные частоты процессоров. Можно контролировать их утилитой CPU-Z. Приведём пример:

Но не спешим радоваться, на снимке всего лишь моментально зафиксированная частота одного из ядер. А как обстоит ситуация на остальных ядрах? В новых версиях CPU-Z появилась возможность наблюдать множитель и частоту по всем имеющимся ядрам (меню Tools – Clocks)

Заходим туда, и видим, что не на всех ядрах множитель максимальный, некоторые ядра «сачкуют»!

Продолжаем изыскания с настройками до тех пор, пока не увидим, что при отсутствии максимальной загрузки процессоров, частота всех ядер максимальна для данного процессора:

Вот теперь уже можно со спокойной совестью запускать тест TPC и смотреть там улучшение результата.

Сервера с архитектурой Intel Sandy Bridge и более новые умеют динамически менять частоты процессора.

Для управления под линуксом отправляем к документации редхат .

Убедитесь что после настройки схемы энергоснабжения процессор работает на нужной максимальной частоте, заявленной производителем. Для этого посмотрите с помощью утилиты cpu-z на core speed.

Использование виртуальной среды означает что может быть 4 места где надо проверить влияние настроек на частоты процессора (биос физического сервер, схему электроснабжения хостовой ОС, биос виртуального сервера, схему электроснабжения виртуальной ОС ).

На серверах 1С и MS SQL Server использование антивирусов (даже сам факт инсталяции без включения) будет приводить к снижению производительности в виде периодических массовых замедлений и подвисаний интерфейса.

Оптимизация работы 1C. Настройка сервера MS SQL.

Включите возможность мгновенной инициализации файлов (Database instant file initialization)

Это позволяет ускорить работу таких операций как:

Создание базы данных
Добавление файлов, журналов или данных в существующую базу данных
Увеличение размера существующего файла (включая операции автоувеличения)
Восстановление базы данных или файловой группы

Включите параметр «Блокировка страниц в памяти» (Lock pages in memory)

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

В меню Пуск выберите команду Выполнить. В поле Открыть введите gpedit.msc.
В консоли Редактор локальных групповых политик разверните узел Конфигурация компьютера, затем узел Конфигурация Windows.
Разверните узлы Настройки безопасности и Локальные политики.
Выберите папку Назначение прав пользователя.
Политики будут показаны на панели подробностей.
На этой панели дважды кликните параметр Блокировка страниц в памяти.
В диалоговом окне Параметр локальной безопасности — блокировка страниц в памяти выберите «Добавить» пользователя или группу.
В диалоговом окне Выбор: пользователи, учетные записи служб или группы добавьте ту учетную запись, под которой у вас запускается служба MS SQL Server.
Чтобы изменения вступили в силу, перезагрузите сервер или зайдите под тем пользователем, под которым у вас запускается MS SQL Server.

Отключите механизм DFSS для дисков.

Механизм Dynamic Fair Share Scheduling отвечает за балансировку и распределение аппаратных ресурсов между пользователями. Иногда его работа может негативно сказываться на производительности 1С. Чтобы отключить его только для дисков, нужно:

Найти в реестре ветку HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\TSFairShare\Disk
Установить значение параметра EnableFairShare в 0

Отключите сжатие данных для каталогов, в которых лежат файлы базы.

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

Открыть свойства каталога
На закладке Общие нажать кнопку Другие
Снять флаг «Сжимать» содержимое для экономии места на диске.

Установите параметр «Максимальная степень параллелизма» (Max degree of parallelism) в значение 1.

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

Запустить Management Studio и подключиться к нужному серверу
Открыть свойства сервера и выбрать закладку Дополнительно
Установить значение параметра равное единице.

Ограничьте максимальный объем памяти сервера MS SQL Server.

Необходимо ограничить максимальный объем памяти, потребляемый MS SQL Server, особенно это критично, если роли сервера 1С и сервера СУБД совмещены. Максимальный объем памяти, рекомендуемый для MS SQL Server, можно рассчитать по следующей формуле:
Память для MS SQL Server = Память всего – Память для ОС – Память для сервера 1С
Например, на сервере установлено 64 ГБ оперативной памяти, необходимо понять, сколько памяти выделить серверу СУБД, чтобы хватило серверу 1С.
Для нормальной работы ОС в большинстве случаев более чем достаточно 4 ГБ, обычно – 2-3 ГБ.
Чтобы определить, сколько памяти требуется серверу 1С, необходимо посмотреть, сколько памяти занимают процессы кластера серверов в разгар рабочего дня. Этими процессами являются ragent, rmngr и rphost, подробно данные процессы рассматриваются в разделе, который посвящен кластеру серверов. Снимать данные нужно именно в период пиковой рабочей активности, когда в базе работает максимальное количество пользователей. Получив эти данные, необходимо прибавить к ним 1 ГБ – на случай запуска в 1С «тяжелых» операций.
Чтобы установить максимальный объем памяти, используемый MS SQL Server, необходимо:

Запустить Management Studio и подключиться к нужному серверу
Открыть свойства сервера и выбрать закладку Память
Указать значение параметра Максимальный размер памяти сервера.

Включите флаг «Поддерживать» приоритет SQL Server (Boost SQL Server priority).

Данный флаг позволяет повысить приоритет процесса MS SQL Server над другими процессами.
Имеет смысл включать флаг только в том случае, если на компьютере с сервером СУБД не установлен сервер 1С.
Для установки флага необходимо:

Запустить Management Studio и подключиться к нужному серверу
Открыть свойства сервера и выбрать закладку Процессоры
Включить флаг «Поддерживать приоритет SQL Server (Boost SQL Server priority)» и нажать Ок.

Установите размер авто увеличения файлов базы данных.

Автоувеличение позволяет указать величину, на которую будет увеличен размер файла базы данных, когда он будет заполнен. Если поставить слишком маленький размер авторасширения, тогда файл будет слишком часто расширяться, на что будет уходить время. Рекомендуется установить значение от 512 МБ до 5 ГБ.
Для установки размера авторасширения необходимо:

Запустить Management Studio и подключиться к нужному серверу
Открыть свойства нужной базы и выбрать закладку Файлы
Напротив каждого файла в колонке Автоувеличение поставить необходимое значение

Данная настройка будет действовать только для выбранной базы. Если вы хотите, чтобы такая настройка действовала для всех баз, нужно выполнить эти же действия для служебной базы model. После этого все вновь созданные базы будет иметь те же настройки, что и база model.

Разнесите файлы данных mdf и файлы логов ldf на разные физические диски.

В этом случае работа с файлами может идти не последовательно, а практически параллельно, что повышает скорость работы дисковых операций. Лучше всего для этих целей подходят диски Супер.
Для переноса файлов необходимо:

Запустить Management Studio и подключиться к нужному серверу
Открыть свойства нужной базы и выбрать закладку Файлы
Запомнить имена и расположение файлов
Отсоединить базу, выбрав через контекстное меню Задачи – Отсоединить
Поставить флаг Удалить соединения и нажать Ок
Открыть Проводник и переместить файл данных и файл журнала на нужные носители
В Management Studio открыть контекстное меню сервера и выбрать пункт Присоединить базу
Нажать кнопку Добавить и указать файл mdf с нового диска
В нижнем окне сведения о базе данных в строке с файлом лога нужно указать новый путь к файлу журнала транзакций и нажать Ок.

Вынесите файлы базы TempDB на отдельный диск.

Служебная база данных TempDB используется всеми базами сервера для хранения, промежуточных расчетов, временных таблиц, версий строк при использовании RCSI и многих других вещей. Обычно обращений к этой базе очень много, и если она будет лежать на медленных дисках, это может замедлить работу системы.
Рекомендуется хранить базу TempDB на отдельном диске для повышения производительности работы системы.
Для переноса базы TempDB на отдельный диск необходимо:

Запускаем SQL Server Management Studio и вводим данные для подключения к серверу баз данных.

Кликаем правой кнопкой мыши по серверу и выбираем Свойства:

В открывшемся окне переходим на вкладку «Память» и ограничиваем потребление оперативной памяти в графе «Максимальный размер памяти сервера (МБ)»:

* максимальный размер рассчитывается так: вся оперативная память сервера минус 4096 Мб (на нужды системы) минус 1536 * количество процессов rphost. Например, если в сервере установлено 32 Гб оперативной памяти и присутствует 2 процесса rphost, расчет будет таким: 32768 - 4096 - (2 * 1536) = 25600.

Теперь переходим на вкладку «Процессоры» и выставляем «Максимальное число рабочих потоков» в значение 2048 и ставим галочку Поддерживать приоритет SQL Server:

Настройки базы данных

В SQL Server Management Studio раскрываем «Базы данных», кликаем правой кнопкой мыши по рабочей базе и нажимаем Свойства :

Теперь переходим на вкладку «Файлы» и в настройках Авторасширения настраиваем расширение файла базы до 250 Мб и лога до 100. Также не лишним будет ограничить размер лога до 4096 Мб:

Нажимаем OK и закрываем Management Studio

Результат

Для проверки результатов оптимизации, запустим отладку, замер производительности и сформируем оборотно-сальдовую ведомость.

Читайте также: