Jak deterministyczne jest tworzenie planu wykonania w SQL Server?

Biorąc pod uwagę następujące stałe:

Ta sama baza danych o tej samej strukturze (tabele, indeksy itp.)
Te same dane
Ta sama konfiguracja SQL Server i sprzęt
Te same statystyki
Te same opcje SET w kliencie
Te same wersje programu SQL Server
Te same flagi śledzenia

Biorąc pod uwagę te stałe, czy SQL Server zawsze tworzy ten sam plan dla danego zapytania?

Jeśli nie, czy są inne względy? Czy należy również rozważyć element niedeterminizmu?

sql-server execution-plan determinism

— James L.
źródło

powiedzenie tego samego planu będzie błędne, ale możemy powiedzieć podobny plan. Wraz z czynnikami zewnętrznymi, które DBA / Developer może kontrolować; „algorytmy optymalizacji planu zapytania” i „wewnętrzne algorytmy kosztu zapytania” są zapisane w silniku serwera SQL. nie mamy kontroli nad powiedzeniem „co wybrać” na wszystkie sposoby. możemy ustawić zewnętrzną env, aby poprowadzić silnik do wyboru najlepszego. dla danego zapytania, jeśli optymalizator zaproponował dwa plany wykonania z powiedzmy różnicą kosztów 0,001, to chyba nie będzie miało znaczenia, jaki wybierze plan wykonania.

— Anup Shah

Nie sądzę, że powinieneś być teoretyczny na ten temat. Pracowałem 25 lat z różnymi bazami danych i prawie zawsze jest możliwe przepisanie zapytania w celu jego lepszego wykonania. A czasem bardzo trudno jest zrozumieć, dlaczego jest to lepsze dzięki jednemu sposobowi napisania zapytania.

Biorąc pod uwagę te stałe, czy SQL Server zawsze tworzy ten sam plan dla danego zapytania? Jeśli nie, czy są inne względy? Czy należy również rozważyć element niedeterminizmu?

O ile mi wiadomo, kompilacja zapytań jest deterministyczna. Jednym z pierwotnych celów projektowych QO było umożliwienie odtworzenia planów wykonania w innym systemie przy użyciu wyłącznie bazy danych zawierającej statystyki . Istnieje kilka subtelności, wokół parametrów konfiguracyjnych, takich jak ilość dostępnej pamięci i liczba procesorów logicznych, ale są one objęte listą rzeczy do synchronizacji.

Zastrzeżenie: To prawda, pod warunkiem, że słowo „to samo” na twojej liście oznacza dokładnie to samo pod każdym względem . Na przykład „te same” statystyki mogą istnieć w dwóch systemach, ale są one dokładnie takie same, jeśli kroki histogramu i informacje o gęstości są identyczne .

To powiedziawszy, proces optymalizacji jest również niezwykle złożony , co oznacza, że może być trudne upewnienie się, że wszystkie dane wejściowe do tego deterministycznego procesu są identyczne i że wszystkie stany wewnętrzne są wystarczająco podobne, aby zapewnić, że ta sama ścieżka kodu zostanie przejęta przez optymalizator dla określonego kompilacja. Jeśli zapytanie zawiera dostęp poza bazą danych (do innej bazy danych lub instancji), środowiska te również muszą być identyczne.

Jedną rzeczą, którą chciałbym dodać do twojej listy, jest sprawdzenie, czy w drugiej bazie danych istnieją również przewodniki po planach .

Zastosowanie funkcji niedeterministycznych, takich jak GETDATE()zapytania, może oznaczać, że otrzymasz także inny plan. Podczas gdy główny optymalizator nie używa tej wartości bezpośrednio, może to być oszacowanie liczności (patrz Ciągłe składanie i ocena wyrażenia podczas szacowania liczności ). Nie jestem pewien, czy ta klasa różnic mieści się w zakresie pytania, ponieważ oba systemy stworzyłyby ten sam plan, gdyby zostały wykonane w tym samym czasie (lub, bardziej ogólnie, z tymi samymi zmiennymi wejściowymi, parametrami i wartościami funkcji).

— Paul White 9
źródło