м. Київ, вул. Кирилівська 102

21 жовтня 2026 р.

Оптимізація Microsoft SQL Server

Оптимізація Microsoft SQL Server

Оптимізацію Microsoft SQL Server варто починати не зі зміни десятків параметрів, а з пошуку конкретного обмеження. Повільна відповідь застосунку може бути наслідком невдалого плану виконання, блокування, дефіциту оперативної пам’яті, перевантаженого сховища, застарілої статистики або запиту, який читає мільйони рядків замість кількох десятків.

Найнебезпечніший підхід — одночасно змінити конфігурацію сервера, перебудувати всі індекси та перезапустити службу. Після цього продуктивність іноді покращується, але встановити справжню причину вже неможливо. Інженерний процес має інший вигляд: зафіксувати базові показники, знайти домінуючий тип очікування, перевірити плани виконання, внести одну обґрунтовану зміну та порівняти результат.

Перед початком робіт потрібно переконатися, що операційна система, диски й резервне копіювання перебувають у контрольованому стані. Для комплексного супроводу такого контуру може застосовуватися обслуговування серверів, адже налаштування рушія бази даних не компенсує проблем із фізичним сховищем, Windows Server або віртуалізацією.

Діагностика SQL Server перед оптимізацією

Спочатку зафіксуйте версію рушія, час останнього запуску, конфігурацію процесорів і пам’яті. Це допоможе правильно трактувати накопичені лічильники: більшість DMV очищуються після перезапуску служби SQL Server.

sql
SELECT
    @@SERVERNAME AS server_name,
    @@VERSION AS sql_version,
    sqlserver_start_time,
    cpu_count,
    hyperthread_ratio,
    physical_memory_kb / 1024 AS physical_memory_mb
FROM sys.dm_os_sys_info;

Далі перевірте очікування. Вони показують, на що витрачали час робочі потоки: читання даних, блокування, журнал транзакцій, паралельне виконання або нестачу ресурсів. Системні фонові очікування потрібно відфільтрувати, інакше вони спотворять картину.

sql
SELECT TOP (20)
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms,
    CAST(100.0 * wait_time_ms /
        NULLIF(SUM(wait_time_ms) OVER (), 0) AS decimal(6,2)) AS wait_percent
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH',
    'WAITFOR', 'LAZYWRITER_SLEEP', 'XE_TIMER_EVENT',
    'XE_DISPATCHER_WAIT', 'BROKER_TO_FLUSH',
    'BROKER_TASK_STOP', 'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT'
)
ORDER BY wait_time_ms DESC;

Високий `PAGEIOLATCH_SH` часто вказує на повільне читання зі сховища або надмірний обсяг прочитаних сторінок. `WRITELOG` пов’язаний із затримкою запису журналу транзакцій. `LCK_M_*` означає блокування, а `SOS_SCHEDULER_YIELD` може супроводжувати процесорне навантаження. `CXPACKET` і `CXCONSUMER` не слід автоматично вважати помилкою: спочатку потрібно перевірити дорогі паралельні запити, MAXDOP і нерівномірний розподіл роботи між потоками.

Для історичного аналізу активуйте Query Store у потрібній базі. Він зберігає тексти запитів, плани та статистику виконання, тому дозволяє знайти регресію після оновлення застосунку, індексів або самого SQL Server.

sql
ALTER DATABASE CURRENT SET QUERY_STORE = ON;

ALTER DATABASE CURRENT SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_STORAGE_SIZE_MB = 2048,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30)
);

Розмір Query Store потрібно підібрати відповідно до інтенсивності навантаження. Після ввімкнення перевіряйте, чи сховище не перейшло в режим `READ_ONLY` через заповнення квоти.

Пам’ять, процесор і паралельне виконання

За замовчуванням SQL Server може використати майже всю доступну пам’ять. На виділеному сервері це також небажано: ресурс потрібен Windows, драйверам, антивірусу, агентам резервного копіювання та іншим службам. Якщо на вузлі працюють додаткові компоненти, резерв для операційної системи має бути ще більшим.

Переглянути поточні параметри можна так:

sql
EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE;

EXEC sys.sp_configure 'min server memory (MB)';
EXEC sys.sp_configure 'max server memory (MB)';
EXEC sys.sp_configure 'max degree of parallelism';
EXEC sys.sp_configure 'cost threshold for parallelism';

Наприклад, на сервері зі 64 ГБ пам’яті для SQL Server можна спочатку залишити операційній системі 8–12 ГБ, але це не універсальна формула. Остаточне значення залежить від обсягу баз, активності сторонніх служб, використання SSIS, антивірусу та резервного копіювання. Якщо після вимірювання вирішено надати рушію 52 ГБ, параметр задається в мегабайтах:

sql
EXEC sys.sp_configure 'max server memory (MB)', 53248;
RECONFIGURE;

Не встановлюйте велике значення `min server memory` без чіткої причини. Цей параметр не резервує пам’ять одразу після запуску, але може завадити рушію повернути її операційній системі після зростання кешу.

`MAXDOP` визначає максимальну кількість процесорних потоків для одного паралельного оператора. Значення потрібно узгоджувати з кількістю логічних процесорів і NUMA-вузлів. Для багатьох серверів безпечним стартом є значення не більше восьми потоків на один NUMA-вузол, однак фінальне рішення приймають після аналізу планів і навантаження.

sql
EXEC sys.sp_configure 'max degree of parallelism', 8;
RECONFIGURE;

EXEC sys.sp_configure 'cost threshold for parallelism', 40;
RECONFIGURE;

Стандартний поріг вартості паралелізму `5` часто занадто низький для сучасного обладнання. Підвищення до 25–50 може зменшити кількість дрібних паралельних запитів, але конкретне значення потрібно перевіряти під типовим навантаженням. Змінювати MAXDOP і поріг одночасно небажано: краще оцінювати вплив кожного параметра окремо.

Якщо SQL Server працює разом з іншими ролями Windows, корисно узгодити зміни з процесом адміністрування серверів Windows і Linux. Це дає змогу перевіряти не лише DMV рушія, а й черги дисків, paging, NUMA, драйвери контролера та системні події.

TempDB, дискова підсистема та файли бази

`tempdb` використовується для сортування, хешування, тимчасових таблиць, версіонування рядків і проміжних результатів. Конкуренція за сторінки розподілу або повільне сховище tempdb можуть гальмувати весь екземпляр.

Спочатку перегляньте файли та параметри автоматичного зростання:

sql
USE tempdb;
GO

SELECT
    name,
    type_desc,
    size * 8 / 1024 AS size_mb,
    CASE
        WHEN is_percent_growth = 1 THEN CONCAT(growth, '%')
        ELSE CONCAT(growth * 8 / 1024, ' MB')
    END AS autogrowth,
    physical_name
FROM sys.database_files;

Файли даних tempdb повинні мати однаковий початковий розмір і однаковий крок зростання. На сервері з великою кількістю процесорів зазвичай починають із чотирьох або восьми файлів, а потім оцінюють конкуренцію. Створювати по одному файлу на кожен логічний процесор без вимірювань не потрібно.

Приклад зміни розміру й кроку зростання для наявних файлів:

sql
ALTER DATABASE tempdb
MODIFY FILE (
    NAME = tempdev,
    SIZE = 8192MB,
    FILEGROWTH = 512MB
);

ALTER DATABASE tempdb
MODIFY FILE (
    NAME = templog,
    SIZE = 4096MB,
    FILEGROWTH = 512MB
);

Логічні імена файлів у конкретному екземплярі можуть відрізнятися, тому їх необхідно перевірити перед виконанням команди. Розмір також підбирають за фактичним піковим використанням, а не копіюють із прикладу.

Для файлів користувацьких баз краще задавати приріст у мегабайтах, а не у відсотках. Відсотковий крок стає непередбачуваним зі збільшенням бази. Часті події autogrowth створюють паузи, фрагментують файли на рівні файлової системи та можуть несподівано заповнити диск.

Дані, журнал транзакцій, tempdb і резервні копії бажано розділяти за профілем введення-виведення. Особливо важливо забезпечити низьку затримку запису для журналу. Для перевірки накопиченої затримки використовуйте статистику файлів:

sql
SELECT
    DB_NAME(vfs.database_id) AS database_name,
    mf.type_desc,
    mf.physical_name,
    vfs.num_of_reads,
    vfs.io_stall_read_ms,
    CASE WHEN vfs.num_of_reads = 0 THEN 0
         ELSE vfs.io_stall_read_ms / vfs.num_of_reads END AS avg_read_ms,
    vfs.num_of_writes,
    vfs.io_stall_write_ms,
    CASE WHEN vfs.num_of_writes = 0 THEN 0
         ELSE vfs.io_stall_write_ms / vfs.num_of_writes END AS avg_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
  ON mf.database_id = vfs.database_id
 AND mf.file_id = vfs.file_id
ORDER BY avg_write_ms DESC;

Ці значення накопичуються від моменту запуску SQL Server. Для коректного порівняння краще знімати два зрізи за визначений інтервал, особливо під час пікового навантаження.

Індекси, статистика та повільні запити

Індекс корисний лише тоді, коли зменшує загальну вартість читання більше, ніж збільшує вартість запису й обслуговування. Автоматичне створення всіх індексів із рекомендацій missing indexes часто призводить до дублювання, надмірного використання диска та повільніших операцій `INSERT`, `UPDATE` і `DELETE`.

Пошук дорогих запитів у кеші планів можна почати так:

sql
SELECT TOP (20)
    qs.execution_count,
    qs.total_worker_time / 1000 AS total_cpu_ms,
    qs.total_elapsed_time / 1000 AS total_elapsed_ms,
    qs.total_logical_reads,
    qs.total_logical_writes,
    qs.total_worker_time / NULLIF(qs.execution_count, 0) / 1000 AS avg_cpu_ms,
    SUBSTRING(
        st.text,
        (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
          END - qs.statement_start_offset) / 2) + 1
    ) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_logical_reads DESC;

Кеш не є довготривалим джерелом: записи можуть бути витіснені, а після перезапуску вони зникають. Для стабільного спостереження використовуйте Query Store.

Під час аналізу фактичного плану звертайте увагу на великі розбіжності між estimated rows і actual rows. Вони часто вказують на застарілу статистику, нерівномірний розподіл значень або parameter sniffing. Також перевіряйте дорогі `Sort`, `Hash Match`, сканування великих таблиць, key lookup із великою кількістю повторів і попередження про spill у tempdb.

Статистику для критичної таблиці можна оновити окремо:

sql
UPDATE STATISTICS dbo.Orders
WITH FULLSCAN;

`FULLSCAN` дає точнішу картину, але на великих таблицях потребує більше часу та ресурсів. Не варто запускати його для всієї бази щогодини. Спочатку визначте об’єкти з помилками оцінки кардинальності та виберіть відповідний рівень вибірки.

Фрагментація індексів також не є достатньою причиною для суцільного `REBUILD`. На малих індексах вона майже не впливає на продуктивність, а перебудова великих об’єктів створює журнал транзакцій, навантажує сховище й може блокувати запити. Рішення потрібно приймати з урахуванням кількості сторінок, щільності сторінок, характеру читання та доступного вікна обслуговування.

Parameter sniffing виникає, коли план, побудований для одного значення параметра, погано працює для іншого. Не слід одразу вимикати його на рівні сервера. Спочатку підтвердьте проблему через Query Store і фактичні плани. Для конкретного запиту можна розглянути `OPTION (RECOMPILE)`, `OPTIMIZE FOR`, переписування процедури або Query Store hints у SQL Server 2022. Кожен варіант має компроміси: наприклад, `RECOMPILE` покращує адаптацію до параметрів, але додає процесорні витрати на компіляцію.

Якщо екземпляр ще не розгорнутий або його конфігурацію потрібно звірити з базовими вимогами, послідовність компонентів і початкових параметрів описана в матеріалі про встановлення SQL Server 2022.

Типові помилки під час оптимізації

**Зміна всіх параметрів за одним шаблоном.** Конфігурація з іншого сервера не враховує NUMA, характер запитів, обсяг пам’яті та швидкість сховища. Кожне значення потрібно підтвердити метриками.

**Очищення кешу планів у робочий час.** Команда `DBCC FREEPROCCACHE` змушує SQL Server повторно компілювати запити та може спричинити різкий стрибок процесорного навантаження. Для перевірки одного проблемного плану краще застосовувати точкові методи.

**Регулярний SHRINK бази.** Стиснення файлів створює фрагментацію та додатковий I/O. Його використовують лише після виняткового одноразового видалення великого обсягу даних, коли простір справді не буде потрібен повторно.

**Перебудова всіх індексів щоночі.** Такий сценарій генерує великий журнал, збільшує тривалість резервного копіювання та витрачає ресурс на індекси, яким обслуговування не потрібне.

**Ігнорування блокувань.** Додавання пам’яті не виправить довгу транзакцію, яка утримує блокування. Потрібно знайти головний блокуючий сеанс, перевірити межі транзакції та скоротити час між її початком і завершенням.

**Оптимізація лише середнього часу.** Середнє значення може приховувати рідкісні, але критичні затримки. Варто аналізувати пікові періоди, максимальну тривалість і розподіл часу виконання.

**Відсутність контрольного вимірювання.** Якщо до зміни не зафіксовано CPU, logical reads, elapsed time, waits і кількість виконань, неможливо довести, що оптимізація дала результат.

Висновок

Ефективна оптимізація Microsoft SQL Server — це повторюваний цикл вимірювання, локалізації причини, точкової зміни та перевірки результату. Починати варто з Query Store, статистики очікувань, файлових затримок і найдорожчих запитів. Після цього можна обґрунтовано налаштовувати пам’ять, MAXDOP, поріг паралелізму, tempdb, індекси та статистику.

Найкращий результат дає не максимальна кількість налаштувань, а мінімальна зміна, яка усуває конкретне вузьке місце. Зберігайте початкові метрики, документуйте команди й забезпечуйте можливість відкотити кожне рішення. Саме такий підхід робить продуктивність SQL Server прогнозованою, а подальшу діагностику — значно швидшою.

📞