Як SQL Server кожні дві-три години переключався на використання не оптимального плану виконання запиту

Останні пару днів працював над цікавим завданням і хотів би поділитися цікавим досвідом з спільнотою.
 
 У чому проявляється проблема:
Запускаю збережену процедуру (хранімку) за вибіркою даних для звіту — виконується три секунди, дивлюся профайлером на бою — у користувачів ті ж результати. Але проходить три години і та ж хранімка, з тими ж параметрами виконується вже 2 хвилини, і аналогічно у користувачів. Причому дані у використовувані таблиці не вставляти / віддалялися, оточення не міняли і адміни не робили налаштувань.
 
Локалізував до запиту:
 
 
INSERT INTO @table_variable1

 
SELECT ...

 
FROM   dbo.[<i>view_with_unions</i>] v1 WITH (READUNCOMMITTED) 

 
LEFT JOIN @<i>table_variable2</i> AS t1

 
ON  t1.Code = v1.DirectionDimensionCode	       

 
LEFT JOIN <i>other_table</i> v2 WITH (READUNCOMMITTED)

 
ON  v2.Code = v1.SaleType	            

 
WHERE  ...

 
Нацькував профайлер на плани виконання і зауважив, що при збільшенні часу виконання хранімкі змінюється і план виконання проблемного запиту.
 
Вже щось!
 
Далі почав дивитися уважніше на те, що ж змінюється в плані виконання. Виявилося, що в довгому плані виконання використовуються NestedLoop об'єднання, а в швидкому — HashMatch.
 
 Швидкий план:
 
 
 
 Повільний (на який SQL Server перемикається через 2 години):
 
 
 
Просто прописувати HINT'и для використання HASH JOIN'ов не хотілося, тому що потрібно зрозуміти чому SQL Server вибирає все-таки не правильний план.
 
Перша думка була, що щось не так зі статистикою, але на плані виконання з профайлера Actual Number Of Rows був 0, а Estimated Number Of Rows дорівнює 1. Т.ч. чином різниця не така велика, щоб досліджувати проблеми із статистикою і Cardinality.
 
 
 
Однак дивлячись на Actual Number Of Rows = 0 з разу в раз, у мене виникли сумніви — невже завжди; не повертається ні одного рядка. Виявилося це не так, просто профайлер перехоплює план виконання до того як запит виконався і стали відомі Actual-дані. А відповідно не може нічого відобразити крім того як нуль в Actual Number Of Rows.
 
Ок, тепер дивимося справжні значення Actual Number Of Rows!
 
Далі питання постало — чому ж Estimated Number Of Rows завжди дорівнює одиниці? Адже індекс використовується, у нього актуальна статистика. А значення Estimated Number Of Rows кожен раз одно 1. Але тут без сюрпризів — SQL Server не використовує статистику, якщо вона починається з низько селективної колонки (тобто якщо кількість різних значень мало, наприклад: 0, 1, NULL). Тому перемістив першу колонку в ключі індексу на останнє місце. Попередньо переконавшись, що всі умови по цих колонкам накладаються в WHERE і перераховані через AND, а значить індекс з його статистикою раніше підходить для використання.
 
 Діагноз:
 
     
Профайлер не відображує на планах виконання Actual Number Of Rows, причому пише не n / a, а нуль. Потрібно пам'ятати про це!
 SQL Server не використовує статистику для визначення Estimated Number Of Rows, в нашому випадку.
 А навіть якщо починає використовувати статистику, то сильно помиляється.
 
 
 Рішення:
 
     
Запускаємо вручну довгі запити з профайлера і дивимося реальний Actual Number Of Rows
 Потрібно дати SQL Server'у можливість використовувати статистику на індексі, для цього перша колонка в ключі повинна мати багато різних значень (наприклад, не три 0, 1, NULL). Т.к. якщо перший стовпець має мало різних значень (низько селективний), то SQL Server не має можливості адекватно прогнозувати кількість рядків і тому не використовує таку статистику.
 Після перебудови індексу потрібно оновити статистику з опцією WITH FULLSCAN, щоб підвищити якість прогнозів Estimated Number Of Rows:
 
UPDATE STATISTICS [dbo]. [table_from_union_for_view ] WITH FULLSCAN;
GO
 
 
І ось тепер всі запити виконуються не більш ніж за 2 секунди, план виконання використовується як мікс з попередніх, а Actual Number Of Rows дивимося в Management Studio:
 
 
 
Але й це не все!
 
Така стратегія роботи все одно буде кешувати плани виконання, що має як позитивну сторону, так і негативну.
 
Позитивна:
швидкість виконання дійсно 1-2 секунди
 
Негативна:
періодично запит виконується близько 20-40 секунд, а потім продовжує виконуватися знову 1-2 секунди. Це відбувається коли відбувається скачок зміни кількості рядків у проблемному запиті (або з великого на мале, або з маленького на велике).
 
Але SQL Server нам надає можливість і це побороти!
 
Для цього можна використовувати опцію OPTION (RECOMPILE), яка буде перебудовувати план виконання при кожному виконанні. Це призведе до збільшення часу виконання кожного запиту до 3-4 секунд, але не буде виконань по 20-40 секунд протягом дня. До речі OPTION (RECOMPILE) так само допомагає отримувати і максимально правильну оцінку Cardinality при використанні тимчасових об'єктів і табличних змінних, яка використовується при визначенні Estimated Number of Rows і далі при виборі плану виконання запиту. (Докладніше про тимчасові об'єкти і суть опції RECOMPILE в них, описано у вкрай хорошому пості — sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx )
 
Тут вже нам самим потрібно визначитися, що важливіше — щоб більшість запитів виконувалося 1-2 секунди або щоб жоден запит не виконувався довше 20 секунд протягом дня.

Джерело: Хабрахабр

0 коментарів

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