Monumentalna różnica w czasie wykonywania między zapytaniami przy użyciu podpowiedzi RECOMPILE

Mam dwa prawie identyczne zapytania uruchomione w tej samej instancji SQL Server 2005:

1. Pierwszym z nich jest oryginalne SELECTzapytanie wygenerowane przez LINQ (wiem, wiem ... nie jestem programistą aplikacji, tylko DBA :).
2. Drugi jest dokładnie taki sam jak pierwszy, dodany OPTION (RECOMPILE)na końcu.

Nic innego się nie zmieniło.

Pierwszy trwa 55 sekund za każdym razem.
Drugi trwa 2 sekundy.

Oba zestawy wyników są identyczne.

Dlaczego ta wskazówka miałaby generować tak dramatyczny wzrost wydajności?

Wpis Books Online RECOMPILEnie zawiera zbyt szczegółowego wyjaśnienia:

Nakazuje aparatowi bazy danych SQL Server odrzucić plan wygenerowany dla zapytania po jego wykonaniu, co zmusza optymalizator kwerendy do ponownej kompilacji planu kwerendy przy następnym wykonaniu tego samego zapytania. Bez określania parametru RECOMPILE aparat bazy danych buforuje plany zapytań i wykorzystuje je ponownie. Podczas kompilowania planów zapytań podpowiedź RECOMPILE wykorzystuje bieżące wartości dowolnych zmiennych lokalnych w zapytaniu, a jeśli zapytanie znajduje się w procedurze składowanej, bieżące wartości są przekazywane do dowolnych parametrów.

RECOMPILE jest użyteczną alternatywą dla tworzenia procedury składowanej, która wykorzystuje klauzulę WITH RECOMPILE, gdy tylko podzbiór zapytań wewnątrz procedury przechowywanej, zamiast całej procedury przechowywanej, musi zostać ponownie skompilowany. Aby uzyskać więcej informacji, zobacz Ponowna kompilacja przechowywanych procedur. RECOMPILE jest również przydatny podczas tworzenia przewodników po planach. Aby uzyskać więcej informacji, zobacz Optymalizowanie zapytań we wdrożonych aplikacjach za pomocą Przewodników po planach.

Ponieważ moje zapytanie zawiera wiele zmiennych lokalnych, domyślam się, że SQL Server jest w stanie (poważnie) zoptymalizować je, kiedy korzystam ze OPTION (RECOMPILE)wskazówki dotyczącej zapytania.

Gdziekolwiek spojrzę, ludzie mówią, że OPTION (RECOMPILE)należy tego unikać. Wyjaśnienie tego jest na ogół takie, że użycie tej wskazówki SQL Server nie jest w stanie ponownie użyć tego planu egzekucji i dlatego musi tracić czas na jego rekompilację za każdym razem.
(Ale) Biorąc pod uwagę gigantyczną przewagę wydajności, jestem skłonny sądzić, że skorzystanie z tej wskazówki zapytania tym razem byłoby dobrą rzeczą.

Czy powinienem tego użyć? Jeśli nie, to czy mogę zmusić SQL Server do korzystania z lepszego planu wykonania bez tej wskazówki i bez zmiany aplikacji?

Jak udokumentowano w artykule Statystyka używana przez Optymalizator zapytań w Microsoft SQL Server 2005

Jeśli użyjesz zmiennej lokalnej w predykacie zapytania zamiast parametru lub literału, optymalizator zastosuje oszacowanie o obniżonej jakości lub zgadnie selektywność predykatu. W zapytaniu używaj parametrów lub literałów zamiast zmiennych lokalnych

Gdy optymalizator nie ma żadnych użytecznych statystyk dla kolumny, zgadnie, że =predykat będzie pasował do 10% wierszy, BETWEEN9%, a dowolny z nich >, >=, < and <=będzie pasował do 30%. Jeśli dostępne są statystyki kolumn, =predykat będzie traktowany inaczej niż poniżej.

Nawet gdy zmienne lokalne są używane w zapytaniu, w przypadku predykatów równości używana jest wartość lepsza niż przypuszczenie. Selektywność dla warunków postaci „ @local_variable = column_name” jest szacowana przy użyciu średniej częstotliwości wartości z histogramu dla nazwa_kolumny. Na przykład, jeśli kolumna nazwa_kolumny zawiera wszystkie unikalne wartości, 1/(number of unique values in column)zostanie zastosowane oszacowanie selektywności , które jest dokładne.

Jest to w zasadzie to samo, co używanie dla OPTIMIZE FOR (UNKNOWN). Może to być dokładniejsze niż zwykłe 10%zgadywanie, ale nie jest dostosowane do konkretnych wartości, o które pytasz.

Aby zmusić program SQL Server do optymalizacji zapytania za każdym razem, gdy jest uruchamiane, i użyć wartości zmiennych lokalnych do oszacowania liczności i kosztu podczas optymalizacji zapytania, dodaj RECOMPILEwskazówkę dotyczącą zapytania do zapytania.

Dzięki RECOMPILEtobie prawdopodobnie otrzymujesz dokładniejsze oszacowania liczności, a zatem inny plan z zleceniami łączenia / typami połączeń bardziej dopasowanymi do liczby wierszy zwracanych z różnych części twojego zapytania.