Project

General

Profile

База знаний »

Общие рекомендации по оптимизации работы БД SQL

Если БД SQL работает медленно, рекомендуется проверить приведённые ниже настройки.

Рекомендуется пройти по всем пунктам и проверить (или поправить).

1. Настройка железа в BIOS

1.1. Проверить настройки производительности и электропитания, должно быть Max Performance (никак не Performance_Per_Watt, Balanced, PowerSaving или проч.)  
Если есть пункт CPU Power Saving, то его поставить в OS Controlled mode, или что-то аналогичное
1.2. если поддерживаются NUMA, то отключить NUMA Node Interleaving (или поставить Enable NUMA)

2. Настройки хоста

2.1. В схеме электропитании поставить Max Performance 
2.2. Для сетевых адаптеров настройках PowerManagement запретить PowerSaving для экономии энергии

3. Настройка виртуальной машины (ВМ)

3.1. Обязательно установить гостевое дополнение для виртуальной машины.
3.2. Настроить ОС на ВМ на Max Performance, запретить отключение устройств для экономии энергии.  
3.3. Если есть NUMA, настроить много виртуальных сокетов по одному ядру, а не один сокет с несколькими ядрами (хотя в последнее время ВМ вроде бы научились правильно распределять NUMA) 

4. Настройка MS SQL

4.1. Настроить Блокировку страниц в памяти

Блокировка страниц в памяти (Lock pages in memory) - дает разрешение определенным учетным записям хранить данные в оперативной памяти, что позволяет избежать перемещения данных на диск в виртуальную память системы. Чтобы дать  разрешение:

  • Откройте Редактор локальной групповой политики Windows: нажмите Пуск и введите "Выполнить" и далее  появившемся поле → "gpedit.msc"

  • В Редакторе локальных групповых политик разверните "Конфигурация компьютера", затем "Конфигурация Windows"

  • Разверните "Параметры безопасности" и "Локальные политики"

  • Выберите директорию "Назначение прав пользователей"

  • В правой части мы увидим политики и их параметры

  • Дважды щелкните на "Блокировка страниц в памяти"

  • Во вкладке "Параметр локальной безопасности" нажмите «Добавить пользователя или группу»

  • В диалоговом окне "Выбор" добавьте учетную запись (или несколько учётных записей), от имени которой запускается служба MS SQL Server

  • Перезагрузите сервер или войдите под учетной записью, с которой работает MS SQL Server для того, чтобы изменения вступили в силу

4.2. Настроить Мгновенную инициализация файлов (Database instant file initialization)

Мгновенная инициализация файлов (Database instant file initialization) - включение данной опции ускоряет работу операций по созданию баз, добавлению файлов, журналов и новых данных в существующие базы + увеличение размера существующих файлов. Для включения:

  • Откройте Редактор локальной групповой политики Windows: нажмите Пуск и введите "Выполнить" и далее  появившемся поле → "gpedit.msc"

  • В Редакторе локальных групповых политик разверните "Конфигурация компьютера", затем "Конфигурация Windows"

  • Разверните "Параметры безопасности" и "Локальные политики"

  • Выберите директорию "Назначение прав пользователей"

  • В правой части мы увидим политики и их параметры

  • Дважды щелкните на "Мгновенную инициализация файлов"

  • Во вкладке "Параметр локальной безопасности" нажмите «Добавить пользователя или группу»

  • В диалоговом окне "Выбор" добавьте учетную запись (или несколько учётных записей), от имени которой запускается служба MS SQL Server

  • Перезагрузите сервер или войдите под учетной записью, с которой работает MS SQL Server для того, чтобы изменения вступили в силу

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

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

  • В меню "Пуск" Windows на сервере найдите и запустите программу "SQL Server Management Studio XX", где XX - год используемой версии программы (например, "SQL Server Management Studio 22")

  • Подключитесь к SQL серверу

  • В открывшемся окне на панели слева выберите пункт с адресом SQL сервера, щёлкните по нему правой кнопкой мыши и в контекстном меню выберите пункт "Свойства"

  • В окне свойства перейдите на вкладку "Дополнительно". Значение параметра "Путь сохранения данных" - это путь к каталогу с файлами БД

4.4. Изменить размер автоматического увеличения файлов баз данных

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

  • В меню "Пуск" Windows на сервере найдите и запустите программу "SQL Server Management Studio XX", где XX - год используемой версии программы (например, "SQL Server Management Studio 22")

  • Подключитесь к SQL серверу

  • В открывшемся окне на панели слева разверните пункт с адресом SQL сервера. Разверните пункт "Базы данных", найдите в нём название используемой БД, щёлкните по нему правой кнопкой мыши и в контекстном меню выберите пункт "Свойства"

  • На странице "Файлы" в колонке "Автоувеличение/Максимальный размер" в каждой строке поочередно нажмите кнопку "..." и установите "Увеличение размера файлов" - 512 МБ. При необходимости это значение можно увеличить вплоть до 5 ГБ

4.5. Отключить использование общей памяти

(!) ВНИМАНИЕ: Данный пункт следует выполнять только, если в вашем комплексе SQL Server работает на отдельном сервере от других программ комплекса, обращающихся к базе данных. Если на том же сервере, где установлен SQL Server, работает другая программа комплекса подключающаяся к БД, то данный пункт следует пропустить.

  • В меню "Пуск" Windows на сервере найдите и запустите программу "SQL Server XXXX Configuration Manager", где XXXX - год используемой версии SQL Server (например, "SQL Server 2019 Configuration Manager")

  • В открывшемся окне на панели слева выберите пункт "Сетевая конфигурация SQL Server → Протоколы для Имя_используемого_эксземпляра_сервера"

  • На панели справа выберите пункт "Общая память". Щелкните по нему правой кнопкой мыши, в контекстном меню выберите пункт "Отключить"

  • На панели слева выберите пункт "Службы SQL Server"

  • На панели справа выберите пункт "SQL Server (_имя_используемого_экземляра_sql_сервера)", например, "SQL Server (SQLEXPRESS)". Щелкните по нему правой кнопкой мыши, в контекстном меню выберите пункт "Перезапуск"

4.6. Сделать приоритетным выполнение процессов, связанных с SQL сервером

(!) ВНИМАНИЕ: Данный пункт следует выполнять только, если в вашем комплексе SQL Server работает на отдельном сервере от других программ комплекса, обращающихся к базе данных. Если на том же сервере, где установлен SQL Server, работает другая программа комплекса подключающаяся к БД, то данный пункт следует пропустить.

  • В меню "Пуск" Windows на сервере найдите и запустите программу "SQL Server Management Studio XX", где XX - год используемой версии программы (например, "SQL Server Management Studio 22")

  • Подключитесь к SQL серверу

  • В открывшемся окне на панели слева выберите пункт с адресом SQL сервера, щёлкните по нему правой кнопкой мыши и в контекстном меню выберите пункт "Свойства"

  • В окне свойств перейдите на страницу "Дополнительно" и установите для параметра "Разное → Повысить приоритет SQL Server" значение "True". Нажмите "ОК"

  • Откройте Службы Windows: нажмите Пуск и введите "Выполнить" и далее  появившемся поле → "services.msc"

  • В открывшемся окне в списке служб найдите службу с названием "SQL Server (Название_экземпляра_сервера)". В контекстном меню службы выберите "Перезапустить" для того, чтобы изменения вступили в силу

4.7. Для базы данных настроить ежедневный план обслуживания (backup, проверка целостности, переиндексация, сброс статистики)


Можно в него добавить для сброса планов выполнения и чистки буферов
DBCC FREEPROCCACHE WITH NO_INFOMSGS;  
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;

Add picture from clipboard (Maximum size: 742 MB)