Online-курс "Основи APM": Оптимізація продуктивності SQL сервера

Оптимізація буферного кешу

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

Оновлення та видалення теж виробляються в кеші, а якийсь час опісля їх вивантажать на диск. Такий механізм дозволяє SQL сервера оптимізувати операції введення / виводу:

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

Є два індикатори роботи буферного кешу:

MSSQL $ Instance: Buffer Manager \ Buffer cache hit ratio - співвідношення запитаних сторінок, які були знайдені в кеші, до сторінок, яких там не було (і які довелося завантажити з диска). Чим більше це співвідношення, тим більш ефективний кеш. Але слід враховувати, що навіть при великому співвідношенні може бути присутнім ефект перевантаження кеша (cache thrashing).

MSSQL $ Instance: Buffer Manager \ Page Life Expectancy - час життя сторінок в кеші. Вважається, що якщо цей показник більше п'яти хвилин, то це нормально. Якщо значення нижче, то або не вистачає пам'яті, або присутній перевантаження кеша.

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

Якщо після аналізу лічильників з'явилася підозра на перевантаження кешу, можна спробувати оптимізувати роботу з кешем. Щоб зрозуміти, які таблиці і індекси займають багато місця в кеші, можна звернутися до sys.dm_os_buffer_descriptors.

SELECT o.name, i.name, bd. *
FROM sys.dm_os_buffer_descriptors bd
INNER JOIN sys.allocation_units a
ON bd.allocation_unit_id = a.allocation_unit_id
INNER JOIN
sys.partitions p
ON (a.container_id = p.hobt_id AND a.type IN (1, 3))
OR (a.container_id = p.partition_id AND a.type = 2)
INNER JOIN sys.objects o ON p.object_id = o.object_id
INNER JOIN sys.indexes i
ON p.object_id = i.object_id AND p.index_id = i.index_id

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

Пошук «поганих» індексів

Для визначення корисності індексів треба спершу зрозуміти, як сервер використовує наявні індекси. sys.dm_db_index_operational_stats містить інформацію про низькорівневих операціях введення / виводу, блокування, зверненнях для кожного індексу. Цю інформацію можна використовувати для відповіді на питання:

• Чи є популярні індекси? Чи є боротьба за них? Чи є очікування блокувань?
• Чи є індекс, який використовується неефективно? Які індекси призводять до вузького місця в роботі з введенням / висновком? page_io_latch_wait_ms покаже, чи були очікування завантаження сторінок індексу в буферний кеш - хороший індикатор сканування індексів.
• Як застосовуються індекси, які шаблони доступу використовуються?

А за допомогою sys.dm_db_index_usage_stats можна дізнатися, скільки і яких операцій застосовувалося до індексу і коли останній раз була застосований певний тип операції. Це допоможе відповісти на питання:

• Як користувачі працюють з індексом? Які операції найбільш часто використовуються?
• Як підтримується і наскільки часто оновлюється індекс?
• Коли індекс використовувався в останній раз і для чого?

За допомогою різних запитів можна отримувати і уточнювати інформацію про індекси і в кінцевому підсумку отримати кандидатів на оптимізацію. Причому оптимізувати можна і запити, які використовують індекс - починаючи з SQL сервера 2005, ця інформація доступна для будь-якого індексу.

Є різні стратегії оптимізації - правильна архітектура, об'єднання індексів, зміна підтримки. Це один з найскладніших, але і найпродуктивніших аспектів оптимізації, і набагато більш докладно висвітлено на відповідних сайтах і в web-касти.

Не можна розповісти все і відразу, але надана інформація повинна дати хороший старт будь-яким ініціативам в питаннях оптимізації продуктивності SQL сервера.

Команда ALG DevOps Team буде вдячна за Ваші відповіді на наступні питання: