Проблема з періодично довго виконуваними запитів в MS SQL Server

Передмова
Є інформаційна система, яку я адмініструється. Система складається з наступних компонент:

1. База даних MS SQL Server
2. Серверний додаток
3. Клієнтські програми

Дані інформаційні системи встановлені на декількох об'єктах. Інформаційна система активно і цілодобово використовується одночасно від 2-х до 20-ти користувачів на кожному об'єкті. Тому не можна виконувати регламентні роботи все і відразу. Т е припадає дефрагментацію індексів розмазувати на цілий день, а не одним махом усі потрібні фрагментовані індекси дефрагментувати. Аналогічно і з іншими роботами.

Автооновлення статистики виставлено у властивостях самої бази даних. Також статистика оновлюється по дефрагментированному індексу.

Проблема
Близько року тому зіткнувся з наступною проблемою:

Періодично всі запити виконувалися довго. Причому час гальмувань були випадковими. Це відбувалося на кожному об'єкті випадковий день. Більше того, коли став аналізувати як часто відбуваються гальмування (профайлером), вдалося з'ясувати, що вони відбуваються щодня в випадковий час. Просто користувачі не завжди звертають на це увагу, а сприймають як єдину випадкову затримку, і потім система знову швидко працює.

Шлях до порятунку
Були переглянуті самі запити, які виконувалися довго. Але найцікавіше, це те, що всі запити в випадковий час виконувалися довго. Навіть найпростіші типу витягнути останній запис з таблиці в кілька тисяч рядків.

Далі, були проведені наступні роботи:

  1. Проаналізовані журнали MS SQL Server і Windows Server – причину гальмувань не вдалося знайти
  2. Проаналізовані індекси (фрагментація і т д) – додані відсутні і видалити невикористовувані
  3. Проаналізовані запити – покращено деякі запити
  4. Проаналізовані завдання в SQL Agent – не вдалося завдання прив'язати до проблеми гальмувань
  5. Проаналізовані завдання в Планувальнику завдань – не вдалося завдання прив'язати до проблеми гальмувань
  6. Profiler теж видавав наслідок, а не причину гальмування.
  7. Проведена перевірка на взаємоблокування – не було виявлено довгих блокувань взагалі
В результаті було витрачено більше 3-х місяців на безуспішні пошуки причини періодичних гальмувань. Однак, вдалося виявити цікавий факт – у всіх запитів виростав показник очікування Elapsed, а не сам показник виконання Worker. Що наштовхнуло на те, що можливо щось з дисками. Теж їх перевірив — все нормально.

Рішення
Дивним було те, що випадковим чином вдалося встановити, що коли запит до додатку виконується повільно, то в самому SSMS він виконується швидко. Тоді для рішення допомогла стаття (вона принаймні надалі наштовхнула на ідею).

З цієї статті процитую наступний абзац:
На практиці найбільш важливою опцією SET є ARITHABORT, тому що значення за замовчуванням для цієї опції відрізняється для додатків і для SQL Server Management Studio. Це пояснює, чому ви можете виявити повільний запит у вашому додатку, і потім отримати хорошу швидкість, виконуючи його в SSMS. Додаток використовує план, який був побудований для набору значень відрізняється від актуальних, правильних значень. Тоді як якщо ви запускаєте запит в SSMS, то найімовірніше у кеші поки ще не є плану виконання для ARITHABORT ON, і тому SQL Server побудує план для ваших поточних значень.
Різниця у виконанні була у параметрі SET ARITHABORT. Для усіх запитів, які виконуються в SSMS цей параметр увімкнено, а для запитів із зовні (додатків) – вимкнений. І його не можна включити навіть простим запитом для додатків:

SET ARITHABORT ON;

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

Для подальшої ручної перевірки перед запитом в SSMS необхідно писати:

SET ARITHABORT OFF;

Тоді запит буде виконуватися, як якщо б він прийшов з програми. Коли запит виконувався довго, то я очищав процедурний кеш. І завжди це лікувало. Т е до чищення процедурного кешу, запит міг виконуватися до 20-30 секунд, а після – 0 секунд.

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

--очистити кеш по id БД
DBCC FLUSHPROCINDB(@db_id);

Після цього всі запити стали просто виконуватися дуже швидко (менше 0,05 сек.), були лише поодинокі викиди до 5-10 секунд виконання, але користувачі вже зависань не помічали. Більш того, оновлення статистики не покращувало результати, тому я прибрав оновлення статистики.

Після ще декількох місяців досліджень вдалося встановити, що поодинокі зависання відбуваються, коли на самому сервері або все з'їдає кеш, і вільної пам'яті нічого не залишається або залишається, але не менше 1 ГБ ОЗУ, або служба MS SQL Server з'їдає всю виділену їй оперативну пам'ять (через Диспетчер завдань). Але друге відбувалося всього 2 рази за все дослідження.

Справа в тому, що в кеш записується в буквальному сенсі все, а ось звільняється кеш не завжди вчасно. Проблему з кешем вдалося вирішити з допомогою програми EmptyStandbyList.exe.

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

Єдине, що залишилося незрозумілим, так це рідкісні випадки, коли один запит зависне на 5-10 секунд 1 раз на місяць випадковий день і в випадковий час. Всього було за півроку 4 таких випадки і то на двох об'єктах, а не на всіх. При цьому служба MS SQL Server з'їдає на короткий час всю виділену їй оперативну пам'ять.

Зробив кроки, описані статті, але це рішення не допомогло.

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

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

Джерела
» РазДваТриЧетыреПятьШістьСімВісім
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.