Microsoft SQL сървър

На скорошното събитие SQL Saturday 178 ме попитаха дали е възможно да накарам оптимизатора да не спира да оптимизира, когато смята, че вече е намерил добър план или таймаут, а да проучи всички алтернативи. Отговорих, че няма документирани средства или не знам за такива. И това е вярно, но може да има някои недокументирани флагове за проследяване, които могат да повлияят на този процес. Реших да направя малко проучване и в тази публикация ще говоря за резултатите от него.

Гледайки напред, веднага ще докладвам резултатите от проучването, за тези, които не се интересуват от технически подробности, но заключенията са важни. Оказва се, че наистина е възможно да накарате оптимизатора да продължи да търси "докрай", но вероятността той наистина да намери много по-добър план е малка. Това е логично, в противен случай, ако оптимизаторът много често „недостатъчно оптимизирани“ заявки, спирайки търсенията по-рано от очакваното, тогава ще е необходимо да се промени механизмът за определяне на момента, в който се счита, че няма смисъл да се търси план по-нататък. Междувременно оптимизаторът се справя доста добре със задачата си и когато се провали, причината много често не е в самия оптимизатор, а в това, с което трябва да работи (остарели статистики, лошо написан код и т.н.). Въпреки че, в името на справедливостта, си струва да се каже, че има случаи, когато причината е в самия оптимизатор.

След това ще говоря за това как да накарам оптимизатора да премахне ограниченията и да продължи да оптимизира до края.

Основни понятия

Дърво от логически оператори — дърво от обекти, в което всеки възел е логически оператор, съответстващ на една или друга операция в заявката. Това дърво е резултат отв какво се превърна заявката, след като стигна до сървъра и премина през някои етапи на опростяване и трансформация. Накратко, това е, с което оптимизаторът започва да работи.

Правило за трансформация - правило за трансформация. Това е обект, който съдържа методи за преобразуване на някои логически оператори в други логически (или физически) оператори.

Задача за оптимизация - буквално задача за оптимизация, това е операция, предприета от оптимизатора в процеса на намиране на план. Това може да бъде например прилагане на правило за трансформация към възел в дърво на оператор.

Memo е структура в паметта на сървъра, която се използва за съхраняване и анализиране на дървета на оператори, произтичащи от трансформации.

Група е група за еквивалентност, част от структурата Memo, която съхранява еквивалентни изрази (оператори), например - Група 1: (A се присъединява към B) , (B се присъединява към A).

Групов израз е израз в група за еквивалентност, например - Група 1: (A се присъединява към B) , (B се присъединява към A). (A join B) е един от изразите от група 1.

Timeout — определен брой оптимизационни задачи (Optimization Task), които оптимизаторът си задава, преди да започне да оптимизира заявката („Ще отгатна тази мелодия от 5 ноти“!), т.е. определен бюджет за броя реализации. Докато се извършват трансформациите, оптимизаторът гледа този брояч и щом изхарчи цялото определено количество, той спира оптимизацията и издава плана, който има в момента. В същото време, ако погледнете получения план в SSMS, изберете основния оператор SELECT и прегледате свойствата, можете да видите „Причина за предсрочно прекратяване: изчакване“.

Достатъчно добър план е достатъчно добър план, това е още едно условие, при което оптимизациятаспира. Това се случва, ако все още има запас от трансформации, но намереният на този етап план вече удовлетворява вътрешния праг на оптимизатора. Това условие може да се види и в свойствата на плана в SSMS - „Причина за предсрочно прекратяване: Намерен е достатъчно добър план“.

Алгоритъм за генериране на алтернативи

Да кажем, че имаме заявка:

Дървото на съответстващите му логически оператори изглежда така:

Дървото се копира в първоначалното Memo (Copy In):

Сега, на този етап, започва процесът на оптимизация. Правилата започват да се прилагат, генерират се алтернативи, оценяват се разходите, сравнително добър план и изчакване. Увеличен, алгоритъмът за търсене на план може да бъде представен по следния начин:

Оптимизиране на групата

Вход: група, горна граница, задължителни свойства
Запазете най-добрия план в бележка

Разгледайте групата

Итеративно изследване на всеки израз

Разгледайте изразите

Прилагане на правилата
Генериране на алтернативни изрази
Работа с бележка за избягване на повторения (напр. JoinCommute)
Растерното изображение на паметта на шаблона дефинира вече приложени правила

Прилагане на правило

Предшественик - Потомък
Свързване на предшественици с правила
Приложение на правилото
Запазване в бележка (включително нови групи)
Задействайте следващо задание въз основа на тип дете
Boolean - Разгледайте израза
Физически - Оптимизиране на входовете

Оптимизиране на входовете

Изчисляване на най-добрия план
Форсиращи физически свойства
Изхвърляне на неефективни клонове

Всичко започва с факта, че основната група е вход към алгоритъма, необходимите физически свойства също са вход, горната граница, над която (ако цената надхвърли прага) няма смисъл да се търси план. Тъй като планът трябва да съдържа физически оператори, групата трябва да съдържа физически оператори. Рекурсивно извиква оптимизация на дъщерни групи. В процеса на оптимизиране на всяка от групите, групата се изследва (Explore Group), ако групата съдържа няколко израза, тогава групата се изследва в итеративно извикване (Explore Expression). Етапът за изследване на израза дефинира правилата, които могат да бъдат приложени към този израз, брои повторенията, за да се избегнат същите трансформации, прилага правилата (Прилагане на правило).Важен момент: правилата не се прилагат всички подред. А само тези, които съответстват на определен шаблон за конкретен израз на група (оператор). Правилото се прилага към израз (предшественик) и генерира нов израз (дете). В зависимост от детето се изпълнява или задачата Explore Expression, ако детето е логически оператор. Или Оптимизирайте входовете, ако детето е физически оператор. Или Optimize Group, ако прилагането на правилото е генерирало дете, което не е включено в нито една съществуваща група, а формира нова. Етапът за оптимизиране на входове от своя страна предоставя стратегия за отхвърляне (отхвърляне) на неефективни разклонения на плана (коефициент на съкращаване, базиран на разходите), изчисляване на най-добрия план и принудителни физически свойства (например, ако имаме съединение за сливане, което изисква сортиран вход, тогава операцията за сортиране ще бъде принудена).

В резултат на всичко това Memo запазва физическите оператори, които изпълняват най-ефективния план.

След тованай-ефективният план е копиран от Memo (Copy Out):

По време на този процес се използват активно следните две концепции:Изтичане, Фактор на съкращаване на базата на разходите, Отхвърляне. Те са тези, които влияят на това как ще бъде избран планът, и те са тези, които могат да бъдат засегнати от флагове за проследяване.

Да преминем от теория към практика.

Деактивирайте времето за изчакване

Първи флаг за проследяване:8780. Позволява ви да "изключите" Timeout.

За целите на демонстрацията ще използвам същата проста база данни opt, която почти винаги използвам в моите примери. За удобство ще дам още веднъж скрипта за неговото генериране:

Сега нека изпълним следната спекулативна заявка, за да получим времето за изчакване.