Є кілька тактик визначення того, що найчастіше викликає очікування SQL Server, тому можна зрозуміти, в якому напрямку оптимізувати продуктивність.
Гленн Беррі (Glenn Berry), Луї Девідсон (Louis Davidson) і Тім Форд (Tim Ford)
- DMO wait_stats покаже, яких подій чекають потоки ОС, що відносяться до SQL Server.
- performance_counters покаже значення лічильників продуктивності SQL Server і то, як їх інтерпретувати.
- ring_buffers, schedulers і wait_stats будуть повідомляти про проблеми, пов'язані з використанням процесора.
- sys_info виведе характеристики комп'ютера, на якому виконується ваш SQL Server.
- sys_memory і process_memory покажуть, як використовується пам'ять в цілому.
- memory_cache_counters або buffer_descriptors покажуть, як використовується кеш-пам'ять.
Очікування SQL Server
У тому ж самому поданні виводяться дані лічильників продуктивності (PerfMon), яке дають специфічну інформацію про використання ресурсів (швидкість передачі даних з дисків, використане процесорний час і т.д.). Зіставляючи статистику очікувань з вимірами споживання ресурсів, можна швидко знайти ресурси системи, за які йде найбільша конкуренція, і виділити потенційні вузькі місця.
Очікування і черги
Завдання планувальника - рівномірно розподілити навантаження серед наявних робочих потоків. Якщо робочий потік сеансу виконується процесором, станом сеансу (відображається в колонці Status DMV sys.dm_exec_requests) буде Running.
Якщо потік готовий до виконання, але планувальник, яким він призначений, в даний час виконує інший сеанс, потік поміщається в чергу потоків, готових до виконання (runnable). Це просто означає, що він стоїть в черзі на доступ до процесора. Це називають очікуванням сигналу (signal wait).
очікування сигналу
Час очікування сигналу виводиться в стовпці signal_wait_time_ms. Воно відображає виключно час очікування процесора. Якщо сеанс чекає, поки стане доступним ще якийсь ресурс, наприклад, заблокована сторінка, або якщо виконується сеансу потрібно виконати вхід-видобуток, то він переміщається в список очікування. Це очікування ресурсів, і стан такого очікує сеансу запам'ятовується як «suspended». Причина очікування зберігається і відображається в стовпці wait_type DMV sys.dm_os_wait_stats.
Загальний час очікування показується в стовпці wait_time_ms, так що можна обчислити час очікування ресурсів наступним чином:
Очікування ресурсів = Загальний час очікування - Час очікування сигналів (Або (wait_time_ms) - (signal_wait_time_ms))
Очікування сигналів неминучі в OLTP-системах (online transaction processing, онлайнові системи обробки транзакцій), оскільки в них відбувається велика кількість коротких транзакцій. Ключовий показник, що описує потенційну навантаження на процесор, - відсоток, який час очікування сигналів становить від загального часу очікування.
Великий відсоток означає, що процесор знаходиться під навантаженням. Часто під «великим відсотком» розуміють більше 25 відсотків, але це залежить від вашої системи. Значення більше 10-15 відсотків - привід для занепокоєння. Загалом, статистика очікувань - відмінний засіб діагностики часу відгуку вашої системи. Говорячи найпростішими словами, ви або працюєте, або чекаєте.
Якщо час відгуку занадто велике і ви бачите, що очікування не займають чимало часу або що в основному є очікуваннями сигналів, потрібно сфокусувати увагу на процесорі. Якщо ви бачите, що час відгуку в основному складається з часу очікування інших ресурсів (таких як мережа, введення-виведення і т.д.), ви повинні зосередити зусилля на оптимізацію використання цих ресурсів.
Профілювання для оптімізіаціі продуктивності
Цей простий запит обчислює відсоток, який становить час очікування сигналів і час очікування ресурсів від загального часу очікування, щоб діагностувати потенційну навантаження на процесор:
Якщо ваш екземпляр SQL Server виконується досить давно, і ви внесли істотну зміну, наприклад, додали новий важливий індекс, слід подумати про видалення старої статистики. Інакше накопичена стара статистика приховає вплив вашого зміни на час очікування.
Наш другий приклад сценарію (рис. 1) показує, як за допомогою DMV sys.dm_os_wait_stats визначити, на очікування якихось ресурсів SQL Server витрачає найбільше часу.
Мал. 1. Цей сценарій генерує звіт за основними причинами очікувань
Сценарій на рис. 1 дозволяє знайти найголовніші вузькі місця на рівні примірника. Це дозволяє сфокусувати зусилля по оптимізації на конкретних типах проблем. Наприклад, якщо згідно накопиченої статистикою перше місце займають очікування, пов'язані з дисковим введенням-висновком, ви повинні зробити подальше дослідження цієї проблеми, задіявши DMV-запити і лічильники PerfMon, що відносяться до дисків.
Лічильники продуктивності
DMV, що показує дані лічильників PerfMon, - sys.dm_os_performance_counters. Воно повертає по запису для кожного лічильника продуктивності, підтримуваного сервером. Це корисне DMV, але, можливо, не зовсім зрозуміло, як з ним працювати.
Сценарій на рис. 2 допомагає при дослідженні незвичайних ситуацій при заповненні журналу транзакцій. Він повертає модель відновлення, опис факторів, які викликали б затримку усічення журналу (log reuse wait), розмір журналу транзакцій, використаний простір журналу, відсоток використаного простору, рівень сумісності і параметри перевірки сторінок для кожної бази даних поточного екземпляра SQL Server.
Мал. 2. Визначте, як заповнюється журнал транзакцій, за допомогою цього сценарію
Запит на рис. 2 корисний для оцінки незнайомого сервера баз даних. Крім того, він ще більш корисний з точки зору спостереження. Наприклад, якщо опис фактора, що перешкоджає усіканню журналу, має якесь незвичайне значення на зразок ACTIVE_TRANSACTION, і ваш журнал транзакцій заповнений на 85 відсотків, час бити на сполох.
Таке спостереження вкрай корисно при ідентифікації джерел та причин очікувань і інших проблем продуктивності. Ці дослідження допоможуть направити ваші зусилля по оптимізації продуктивності в потрібну сторону.
Гленн Беррі (Glenn Berry) - працює архітектором баз даних в компанії NewsGator Technologies з міста Денвера, штат Колорадо. Він володіє званням MVP в області SQL Server, і володіє цілим набором сертифікатів Microsoft, в тому числі MCITP, MCDBA, MCSE, MCSD, MCAD і MCTS, що означає, що йому дійсно подобається здавати сертифікаційні іспити.
Луї Девідсон (Louis Davidson) - працює в галузі ІТ вже більше 16 років в якості розробника і архітектора корпоративних баз даних. Він носив звання SQL Server MVP протягом шести років і написав чотири книги з проектування баз даних. В даний час він займається розробкою архітектури баз даних і трохи адмініструванням БД в компанії Christian Broadcasting Network, підтримуючи роботу філій в м Вірджинія-Біч, штат Вірджинія, і р Нешвілл, штат Теннесі.
Дізнайтеся більше про «SQL Server DMV Starter Pack» на сайті red-gate.com.