Полная оптимизация SQL Server: от настройки с нуля до переноса баз на SSD-массивы

СОДЕРЖАНИЕ:
1. Оптимизация SQL-запросов
2. Настройка сервера с нуля
3. Конфигурация новой базы данных
4. Перенастройка действующей базы
5. Распределение по RAID-массивам
6.Стратегия размещения на SSD
7. Правила настройки дисков и ОС
8. Перенос существующих баз
9. Версии SQL Server: память и объёмы

1. Оптимизация SQL-запросов:

По данным практики большинства DBA, до 70–80% проблем производительности решаются оптимизацией запросов и индексов - ещё до настройки железа или конфигурации сервера.

Индексы

Правильно спроектированные индексы - единственный наиболее эффективный инструмент оптимизации

 

Планы выполнения

Анализ Actual Execution Plan выявляет неэффективные операции быстрее любых метрик

 

Статистика

Устаревшая статистика ведёт к плохим планам даже при хорошей схеме индексов

 

Покрывающие индексы

INCLUDE-колонки
устраняют Key Lookup и снижают нагрузку на I/О

Ключевые правила написания запросов -  избегайте SELECT *, функций над индексируемыми столбцами в WHERE, неявных преобразований типов и курсоров там, где достаточно одного set-based запроса.

Совет: Системное представление sys.dm_db_missing_index_details покажет, какие индексы SQL Server рекомендует создать.

Однако не создавайте их слепо: СУБД предлагает их под конкретный запрос, не анализируя общую картину. Избыток индексов серьезно замедлит операции INSERT/UPDATE/DELETE».

Но не создавайте все подряд: каждый лишний индекс замедляет INSERT, UPDATE и DELETE - особенно на нагруженных таблицах.

2. Настройка сервера с нуля:

Первоначальная установка и конфигурация закладывает фундамент производительности на годы вперёд. Исправлять плохие настройки на работающем production - значительно дороже.

Внимание: TempDB

TempDB пересоздаётся при каждом перезапуске. Все настройки размера файлов TempDB нужно прописывать в конфигурации, а не задавать вручную после старта.

3. Конфигурация новой базы данных:

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

6. Стратегия размещения на SSD:

Правильное разнесение компонентов базы данных (на примере MS SQL Server, PostgreSQL или MySQL) по разным SSD-накопителям позволяет минимизировать конкуренцию за ввод-вывод (I/O) и изолировать разные типы нагрузки.

Характер нагрузки на базы данных делится на три типа: случайное чтение/запись (файлы данных), строго последовательная запись (журналы транзакций) и интенсивная временная нагрузка (TempDB / tmpdir).

Подготовка накопителей: 

1. Перед распределением файлов обязательно отформатируйте SSD под нужды СУБД.

2. Размер кластера (файловая система): Для дисков с базами данных, логами и TempDB всегда выбирайте размер кластера 64 КБ вместо стандартных 4 КБ. Это оптимизирует скорость чтения-записи для крупных блоков данных СУБД.

Вариант 1: У вас есть 2 SSD

• SSD 1: Операционная система, сам движок SQL, Журнал транзакций (.ldf / WAL) и база TempDB.
• SSD 2: Основные файлы данных (.mdf / .ndf).
• Файлы данных генерируют случайный ввод-вывод. Если посадить их на один диск с логами (последовательная запись), общая скорость упадет из-за смешивания паттернов нагрузки

Вариант 2: У вас есть 3 SSD (Оптимальный баланс)

• SSD 1: ОС, файлы СУБД + Журнал транзакций (.ldf / WAL).
• SSD 2: Основные файлы данных (.mdf).
• SSD 3: Временная база данных TempDB (или tmpdir для MySQL/PostgreSQL).
• TempDB используется для тяжелых сортировок, промежуточных вычислений и соединений (особенно актуально для 1С). Выделение под нее отдельного SSD убирает нагрузку с основных баз. 

 Вариант 3: У вас есть 4 и более SSD (Максимальная производительность)

• SSD 1: Операционная система и файлы развертывания СУБД.
• SSD 2: Только журналы транзакций (максимально быстрый SSD на запись).
• SSD 3: Файлы данных (высокая скорость случайного чтения).
• SSD 4: Выделенный диск под TempDB.

NVMe vs SATA SSD: NVMe даёт в 3–5× более высокий случайный IOPS по сравнению с SATA SSD. Размещайте на NVMe: данные (.mdf) и TempDB. Журнал транзакций (.ldf) - последовательная запись, SATA SSD справляется хорошо.

Архитектурные хитрости для крупных БД:

Если у вас одна огромная база данных, которая не помещается на один быстрый SSD, используйте внутренние механизмы СУБД:

• Начать стоит с создания вторичной файловой группы: ALTER DATABASE [db] ADD FILEGROUP [FG_Archive], затем перенести в неё старые таблицы. Это не требует изменения кода приложения.
• Файловые группы (Filegroups): Разделите одну базу данных на несколько файлов данных и распределите их по разным SSD. SQL Server умеет писать в них параллельно (метод Round-Robin), увеличивая общую скорость. 
• Секционирование таблиц (Partitioning): Перенесите «горячие» данные (например, документы за текущий год) на самый быстрый NVMe SSD, а «холодные» архивные таблицы (прошлые года) — на более дешевый и емкий SATA SSD или HDD.
• Разделение TempDB на файлы: Создайте несколько файлов данных для TempDB (обычно по числу ядер процессора, но не более 8). Разместите их строго на одном выделенном SSD, чтобы СУБД параллельно обрабатывала временные таблицы.

Запрещено: 

• Не используйте RAID 5/6 для журналов транзакций. Эти массивы имеют низкую скорость записи из-за просчета контрольных сумм (Parity Write Penalty). Для логов лучше использовать RAID 10 или одиночный быстрый NVMe.
• Не оставляйте TempDB на диске C:. Если база временных таблиц разрастется и забьет диск целиком, операционная система «упадет» или намертво зависнет.
• Не смешивайте бэкапы с рабочими базами. Файлы бэкапов должны складываться на физически другие накопители (лучше всего на сетевое хранилище NAS или емкие HDD). Если SSD с базой выйдет из строя, вы потеряете и базу, и бэкапы.

7. Правила настройки дисков и ОС:

8. Перенос существующих баз:

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

Перемещение файлов базы на новые диски (без Detach) USE master;

1. Обновить пути в метаданных:

ALTER DATABASE [ProductionDB] MODIFY FILE (NAME = N'ProductionDB', FILENAME = N'E:\SQLData\ProductionDB.mdf'); ALTER DATABASE [ProductionDB] MODIFY FILE (NAME = N'ProductionDB_log', FILENAME = N'F:\SQLLog\ProductionDB_log.ldf');

2. Перевести базу в offline:

ALTER DATABASE [ProductionDB] SET OFFLINE WITH ROLLBACK IMMEDIATE;

3. Скопировать файлы вручную (xcopy / robocopy)

4. Перевести базу обратно в online:

ALTER DATABASE [ProductionDB] SET ONLINE;

5. Проверить целостность:

DBCC CHECKDB ([ProductionDB]) WITH NO_INFOMSGS;

 

9. Версии SQL Server: память и объёмы:

Лимиты памяти и возможности конфигурирования существенно различаются в зависимости от версии и редакции SQL Server. Выберите версию ниже для детального сравнения.

Редакция	Макс. RAM	Макс CPU ядер	Макс. размер БД	Ключевые фичи
Enterprise	Неограничена (ОС)	Неограничена	524 ПБ	Always On AG, In-Memory OLTP, Query Store, R Services
Standard	128 ГБ буферный пул	4 сокета / 24 ядра	524 ПБ	Basic Always On (2 узла), Query Store
Express	1,4 ГБ	4 ядра	10 ГБ	Только для малых приложений и разработки

SQL - 2016; SQL - 2017 ; SQL - 2019; SQL - 2022

Версия	Главное отличие от предыдущей
2016	Query Store, Always Encrypted, R Services
2017	Linux-поддержка, Python, адаптивные планы
2019	Intelligent Query Processing (IQP), ADR - восстановление за секунды вместо минут
2022	IQP v2, Contained AG, Azure Arc, Ledger Tables

SQL Server 2016 - статус

Mainstream support завершён. Расширенная поддержка до июля 2026. Рекомендуется планировать миграцию на 2019 или 2022.

128 ГБ

RAM для Standard Edition

524 ПБ

Макс. размер БД (Enterprise)

10 ГБ

Макс. размер БД Express

4

Ядра CPU для Express

Чеклист приоритетов:

✓ Выделите данные, журналы и TempDB на разные диски/RAID
✓ Форматируйте NTFS с allocation unit 64 КБ
✓ Ограничьте max server memory, настройте MAXDOP
✓ Включите Query Store и следите за регрессиями планов
✓ Предварительно выделите файлы баз нужного размера
✓ Никогда не включайте Auto Shrink в production 
✓ Регулярно обслуживайте индексы (Rebuild / Reorganize)
✓ Планируйте переход на SQL Server 2019 / 2022

✓ Распределяйте данные по типу накопителя для баланса производительности и стоимости:

• NVMe SSD - файлы баз данных (.mdf, .ndf) и TempDB: максимальный IOPS для горячих данных
• SATA SSD - журналы транзакций (.ldf) и ОС с приложениями: последовательная запись, высокая надёжность
• SATA SSD или HDD - кеш, вторичные и редко используемые данные
• HDD - резервные копии (.bak) и файловые архивы: большой объём по минимальной цене