Самое короткое руководство по базовой настройке производительности Microsoft SQL Server

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

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

Откройте оснастку «Local Security Policy». В параметре «Local Policies — User Right Assignment — Lock pages In memory» задайте учетные записи под которыми запускаются все используемые инстансы.

Подробнее

Плохо:

Хорошо:

Это позволит избежать вытеснения страниц буферного пула в своп.

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

Откройте оснастку Local Security Policy. В параметре Local Policies — User Right Assignment — Perform Volume Maintenance Tasks задайте учетные записи всех используемых инстансов. Начиная с SQL Server 2016 эту привилегию предложит добавить инсталлятор.

Скрытый текст

Хорошо:

Это позволит мгновенно расширять файлы данных баз.

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

Используйте выделенные тома для баз данных и журналов транзакций. Форматируйте диск в NTFS с кластером 64k. Это соответствует размеру экстента SQL и позволит снизить число операций ввода вывода, что особенно важно для фрагментированных данных на HDD. При создании RAID массива на СХД так же учитывайте эту особенность.

Скрытый текст

Хорошо:

Плохо:

Это позволит кратно снизить обращения к файловой системе — читать не 16 кластеров по 4Kb, а один на 64Kb.

Настройте tempdb

Расположите tempdb на самом быстром хранилище. Это высоконагруженная база данных, которую использует как сам SQL Server, так и все другие базы данных. Разбивайте tempdb на несколько файлов. Создайте по одному файлу на каждое ядро, разумный максимум — 8 файлов. Укажите для каждого файла одинаковый изначальный размер и одинаковый размер увеличения в мегабайтах. Размер файлов зависит от фактической потребности ^[1] и может быть достаточно небольшим.

Скрытый текст

Это позволит снизить конкуренцию за tempdb

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

Снизьте максимальный объем буферного пула, оставив операционной системе и другим компонентам SQL Server разумное количество памяти ^[2] (6–8Gb).

Скрытый текст

Судя по Unused, на этом сервере памяти с избытком. Прибавка MAX_MEMORY не меняет картину — базы данных маленькие с небольшой нагрузкой.

Это позволит избежать чрезмерного своппинга.

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

Параллелизм имеет издержки на разбиение запроса и сборку результата его выполнения. Установите MAXDOP равным половине доступных ядер, а «Cost Threshold for Parallelism» = 50.

Это позволит разбивать на части ограниченное число действительно продолжительных запросов.

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

На журнал транзакций не действует Instant File Initialization, он всегда зануляется. Для расширения используйте Auto Grow с шагом 1–4Gb. Для больших баз данных эта цифра может быть значительно выше. Никогда не ограничивайте полный размер журнала транзакций.

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

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

Используйте скрипты Ola Hallergen ^[3] для создания ежедневных заданий оптимизации и перестроения индексов.

Это позволит поддерживать базу данных в тонусе и существенно повысить скорость выполнения запросов.

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

Изучите колонку «Ожидания» в мониторе активности и разберитесь, что является узким местом.

Это позволит не действовать наугад.

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

Прочитайте книгу Дмитрия Короткевича «SQL Server. Наладка и оптимизация для профессионалов»

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

PS: Я не DBA, критика приветствуется. Написать статью побудил этот манускрипт — Настройка MS SQL Server под 1С: планирование и развёртывание / Хабр ^[4]. Просто не люблю лонгриды и 1С.