Базовая настройка производительности Microsoft SQL Server: пошаговое руководство

Настройка СУБД — это задача, с которой регулярно сталкивается любой администратор Windows-серверов или системный инженер, обслуживающий базы данных. На практике первичный тюнинг Microsoft SQL Server можно выполнить еще до проведения комплексного профилирования и глубокого анализа конкретной рабочей нагрузки. В этом материале я собрал универсальный чек-лист базовых настроек, которые подходят для большинства развертываний SQL Server. Эти рекомендации позволяют устранить типовые узкие места и обеспечить надежный фундамент для работы системы.

Используйте привилегию «Lock Pages In memory»

По умолчанию операционная система Windows имеет право выгружать страницы памяти из буферного пула (Buffer Pool) SQL Server в файл подкачки на диске при возникновении нехватки свободной ОЗУ. Вытеснение страниц памяти в своп приводит к драматическому падению скорости обработки запросов и резкому росту задержек дискового ввода-вывода.

Чтобы предотвратить сброс страниц памяти на диск, сервисной учетной записи, под которой запущен инстанс SQL Server, необходимо явно предоставить право Lock Pages In Memory (LPIM). Настройка производится в системной оснастке управления политиками безопасности:

• Откройте оснастку «Локальная политика безопасности» (команда secpol.msc).
• Перейдите по пути: Локальные политики (Local Policies) → Назначение прав пользователя (User Right Assignment).
• Найдите параметр Закрепление страниц в памяти (Lock pages in memory) и добавьте учетные записи всех используемых экземпляров SQL Server.

Включение данного параметра гарантирует, что выделенная буферному пулу память останется в физической ОЗУ, предотвращая деградацию производительности из-за подкачки.

Используйте привилегию «Perform Volume Maintenance Tasks»

Другим критически важным системным правом является Perform Volume Maintenance Tasks (Выполнение задач по обслуживанию тома). Данная привилегия активирует механизм быстрой инициализации файлов данных (Instant File Initialization, IFI).

При отсутствии этого права операционная система при создании или авторасширении файлов данных (.mdf / .ndf) вынуждена заполнять выделяемый объем нулями, что вызывает ощутимые паузы в работе СУБД. С включенным механизмом IFI расширение файлов данных происходит практически мгновенно. Начиная с версии SQL Server 2016, инсталлятор предлагает включить эту опцию автоматически при установке, однако на ранее настроенных серверах ее наличие стоит проверить через оснастку secpol.msc.

Настройте файловую систему

Грамотная подготовка дисковых томов крайне важна для быстродействия всей дисковой подсистемы. В своей практике я рекомендую соблюдать следующие правила размешивания файлов:

• Выделяйте изолированные тома под файлы данных баз и отдельные тома под журналы транзакций (.ldf).
• Форматируйте дисковые тома в файловой системе NTFS с размером кластера 64 КБ (Размер единицы распределения / Allocation Unit Size).

Размер экстента в SQL Server составляет 64 КБ (8 страниц по 8 КБ). Когда размер кластера файловой системы точно совпадает с величиной экстента, дисковый контроллер считывает и записывает блок данных за одну операцию ввода-вывода вместо 16 операций по 4 КБ. Это кратно снижает число обращений к файловой системе, что особенно критично при работе с HDD и RAID-массивами.

Настройте tempdb

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

Для оптимизации работы tempdb следует придерживаться проверенных правил:

• Размещайте tempdb на наиболее быстрых доступных накопителях (например, NVMe SSD или высокоскоростных массивах СХД).
• Разбивайте tempdb на несколько файлов данных. Оптимальное соотношение — 1 файл на каждое логическое ядро процессора (разумный верхний предел составляет 8 файлов).
• Установите для всех файлов tempdb одинаковый начальный размер и равный шаг автоматического прироста в мегабайтах.

Это позволяет распределить операции записи и кардинально снизить конкуренцию потоков за страницы распределения (PAGELATCH).

Ограничьте Max Server Memory

По умолчанию SQL Server стремится занять всю доступную на сервере оперативную память. Если не ограничить параметр Max Server Memory (Максимальный размер памяти сервера), операционная система и вспомогательные службы начнут испытывать дефицит ОЗУ, что приведет к системным задержкам.

Я рекомендую ограничивать максимальный объем буферного пула так, чтобы операционной системе и сторонним процессам оставалось минимум 6–8 ГБ физической памяти. Это предотвратит вытеснение системных служб в своп и удержит сервер в стабильном состоянии.

Ограничьте параллелизм запросов

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

Для правильной настройки параметра параллелизма задействуйте следующие значения:

• Cost Threshold for Parallelism (Порог стоимости для параллелизма): повысьте со стандартного значения 5 до 50. Это отсечет простые запросы от ненужного распараллеливания.
• MAXDOP (Maximum Degree of Parallelism): ориентируйтесь на половину доступных ядер, при этом специалисты по администрированию баз данных рекомендуют выбирать значение от 4 до 6 (не более 8).
• Для баз данных под управлением 1С:Предприятие стандартной рекомендацией вендора является установка MAXDOP = 1 (полное отключение параллелизма).

Задайте достаточный размер журнала транзакций

На журнал транзакций (.ldf) действие быстрой инициализации файлов не распространяется — пространство всегда зануляется при расширении (за исключением SQL Server 2022 при шаге прироста до 64 МБ). Процесс обнуления задерживает транзакции, поэтому частый рост журнала недопустим.

• Задайте шаг Auto Grow с фиксированным объемом в 1–4 ГБ (для гигантских баз этот шаг может быть выше). Никогда не задавайте прирост в процентах.
• Не устанавливайте ограничение максимального размера файла журнала транзакций.

Если журнал транзакций достигнет установленного лимита и заполнится, база остановит работу. Для исправления ситуации потребуется выполнить команду ALTER DATABASE, но для самой этой транзакции в журнале уже не будет места, и придется производить ручной BACKUP LOG.

Оптимизируйте индексы

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

Я рекомендую применять известный набор скриптов Ola Hallengren (SQL Server Maintenance Solution). Они позволяют настроить регулярные автозадания SQL Server Agent для интеллектуального перестроения и дефрагментации индексов, а также обновления статистики.

Обратите внимание на настройку Optimize for Ad Hoc Workloads

Параметр Optimize for Ad Hoc Workloads позволяет экономить оперативную память кэша планов. При первом исполнении одноразового (Ad Hoc) запроса в памяти сохраняется только небольшая компилируемая заглушка (plan stub), а полноценный план создается лишь при повторном вызове.

Оценить целесообразность включения настройки можно с помощью SQL-запроса, вычисляющего процент памяти под разовые планы (порогом для включения считаются 25%):

SELECT AdHoc_Plan_MB, Total_Cache_MB, AdHoc_Plan_MB*100.0 / Total_Cache_MB AS 'AdHoc %'
FROM (
    SELECT SUM(CASE WHEN objtype = 'adhoc' THEN convert(float,size_in_bytes) ELSE 0 END) / 1048576.0 AdHoc_Plan_MB,
           SUM(convert(float,size_in_bytes)) / 1048576.0 Total_Cache_MB
    FROM sys.dm_exec_cached_plans
) T;

Дополнительный запрос для детализации структуры кэша планов:

SELECT SUM(CONVERT(bigint, size_in_bytes)) / 1048576.0 AS Total_Cache_MB,
       SUM(CASE WHEN objtype = 'Adhoc' THEN CONVERT(bigint, size_in_bytes) ELSE 0 END) / 1048576.0 AS AdHoc_MB,
       SUM(CASE WHEN objtype = 'Adhoc' AND usecounts = 1 THEN CONVERT(bigint, size_in_bytes) ELSE 0 END) / 1048576.0 AS AdHoc_1use_MB
FROM sys.dm_exec_cached_plans;

Используйте Activity Monitor

Для диагностики узких мест в реальном времени применяйте встроенную оснастку Activity Monitor (Монитор активности). Изучение колонки «Ожидания» (Wait Statistics) позволяет точно определить причинно-следственные связи задержек — будь то дисковая подсистема, блокировки или нехватка процессора — и не действовать наугад.

Повышайте когнитивные функции

Описанные рекомендации составляют базовый фундамент. Для погружения в точную отладку и архитектуру системы я рекомендую изучить профильную литературу, в частности книгу Дмитрия Короткевича «SQL Server. Наладка и оптимизация для профессионалов», где подробно разобраны тонкие нюансы работы СУБД.

Локальный ИИ класса Opus 4.6 «задешево»: две Tesla P100, 28-поточный Xeon и тесты на реальных задачах

Как специалист, работающий не только со стандартным ПО для Windows, но и с локальными вычислительными системами для искусственного интеллекта, я регулярно тестирую аппаратные решения. К примеру, развертывание домашнего сервера на базе двух карт Nvidia Tesla P100 (суммарно 32 ГБ памяти) и 28-поточного процессора Intel Xeon позволяет запускать нейросети локально со скоростью 10–50 токенов в секунду, обеспечивая полную независимость от сторонних облачных сервисов.

Источник: habr.com