В этот краткий сборник рецептов входят советы, применимые к подавляющему большинству развертываний 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 файлов. Укажите для каждого файла одинаковый изначальный размер и одинаковый размер увеличения в мегабайтах. Размер файлов зависит от фактической потребности и может быть достаточно небольшим.

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

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

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

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

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

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

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

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

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

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

Опытные DBA скептически относятся к высоким значениям MAXDOP, и рекомендуют ограничиться значением от 4 до 6, разумным максимумом является 8. Большое спасибо за комментарии @wadegи @Tzimie.

1С рекомендует вовсе отказаться от разбиения запросов и установить MAXDOP=1.

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

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

На журнал транзакций не действует Instant File Initialization, он всегда зануляется (исключение - SQL Server 2022 при размере прироста не более 64Mb, спасибо @katamoto).

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

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

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

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

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

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

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

В качестве примера можно руководствоваться этой статьёй.

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

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

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

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

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

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

PS: Я не DBA, критика приветствуется.