Kod tworzy inny plan, gdy jest uruchamiany ad-hoc vs. w procedurze przechowywanej

Mam instrukcja usuwania, która używa złego planu, gdy jest uruchamiana w ramach procedury składowanej, ale wybiera znacznie lepszy plan, gdy jest uruchamiany ad-hoc.

Odbudowałem wszystkie indeksy dla tabel używanych w zapytaniu i usunąłem wszystkie pamięci podręczne. Optymalizator nadal wybiera zły plan dla procedury składowanej.

Chciałbym wiedzieć, dlaczego optymalizator korzysta z innego planu wykonania dla procedury składowanej niż SQL ad-hoc.

AKTUALIZACJA: Wydaje mi się, że musiały to być parametry - kiedy uruchomiłem kod ad-hoc ze zmienną na stałe, mogę uzyskać „zły” plan o właściwej wartości (jest to data, wartości, które mają roczek wydaje się generować „dobry” plan). Teraz spróbuj wymusić „dobry” plan na proc przy użyciu wskazówki zapytania.

ROZWIĄZANIE: Skończyło się na tym, że otrzymałem plan, który chciałem, korzystając z podpowiedzi OPTYMALIZUJ DLA NIEZNANEJ.

Zwykli podejrzani:

1. stałe w adhoc, parametry w kodzie
2. niedopasowanie typów danych w kodzie
3. wąchanie parametrów

Punkt 1: optymalizator może wybrać najlepszy plan dla stałych.
Zmień stałe = zmień plan. Sparametryzowana powierzchnia jest możliwa do powtórzenia

Punkt 2 wprowadzi niejawne konwersje ze względu na pierwszeństwo typu danych,
np. Kolumna varchar w porównaniu do parametru nvarchar

Punkt 3: użyj maskowania parametrów lub OPTYMALIZUJ DLA NIEZNANEJ
Edycja: Aby przetestować: uruchom zapisany proc, uruchom sp_updatestats, uruchom ponownie. Spowoduje to unieważnienie buforowanych planów, co jest lepsze niż wyczyszczenie pamięci podręcznej planu

Edycja: po komentarzu jcolebrand

Możesz wyłączyć wąchanie na kilka sposobów. Główne 3 to

• RECOMPILE. To głupie IMO.
• OPTYMALIZUJ (sic) DLA NIEZNANE
• Maskowanie parametrów

Maskowanie parametrów:

DECLARE @MaskedParam varchar(10)
SELECT @MaskedParam = @SignaureParam

SELECT...WHERE column = @MaskedParam

Maskowanie i wskazówka OPTIMIZE mają ten sam efekt (może z różnych powodów). Oznacza to, że optymalizator musi korzystać ze statystyk i dystrybucji danych ( Uwaga: wciąż testowany przez Mark Storey-Smith ) ocenia parametry na podstawie własnych zalet ? , a nie to, co było ostatnim telefonem. Optymalizator może dokonać ponownej kompilacji lub nie. SQL Server 2005 dodał rekompilację na poziomie instrukcji, dzięki czemu wpływ był mniejszy

Dlaczego plan z parametrami „wąchania” jest „lepki” w porównaniu do parametrów maskowanych / „nieznanych”, nie jestem pewien.

Używam maskowania parametrów od SQL Server 2000 dla wszystkich kodów oprócz najprostszego. Zauważyłem, że może się to zdarzyć przy bardziej złożonym kodzie. A w mojej starej pracy mam kilka raportów, w których mógłbym zmienić domyślne parametry planu. Uważam, że podejście „kultu ładunku” było łatwiejsze niż wezwanie do pomocy.

Edytuj 2, 12 października 2011 r., Po czacie

• Maskowanie parametrów i OPTYMALIZACJA DLA NIEZNANYM mają taki sam efekt, o ile mogę stwierdzić
  . Podpowiedź jest czystsza niż maskowanie, ale została dodana do SQL Server 2008.
  

• Wykrywanie parametrów odbywa się w czasie kompilacji.
  Z RECOMPILE generuje nowy plan przy każdym wykonaniu. Oznacza to, że zły wybór ustawień domyślnych wpłynie na plan. Na moim ostatnim zadaniu mogłem to łatwo wykazać za pomocą kodu raportu: zmiana wartości domyślnych parametrów zmieniła plan niezależnie od dostarczonych parametrów.

• Ten artykuł MS Connect jest interesujący: Nieoptymalne użycie indeksu w ramach procedury składowanej (wspomniane w jednej z odpowiedzi SO poniżej)

• Bob Beauchemin też o tym wspomina

Nierozstrzygnięte problemy

• Czy wąchanie nadal obowiązuje w przypadku Z RECOMPILE? Oznacza to, że jeśli optymalizator wie, jak odrzucić plan, czy zamierza on ponownie wykorzystać?

• Dlaczego plany wąchania są „lepkie”?

Linki z SO:

• https://stackoverflow.com/q/6919166/27535
• https://stackoverflow.com/q/5236631/27535
• https://stackoverflow.com/q/1392307/27535
• https://stackoverflow.com/q/2905440/27535
• https://stackoverflow.com/q/467513/27535

• https://stackoverflow.com/q/272726/27535

• Remus Rusanu (zespół MS SQL) i ja toczyłem bitwę tutaj na SO

• Oficjalny dokument na MSDN w połączeniu z moją analizą SO: /programming/414336/why-does-the-sqlserver-optimizer-get-so-confused-with-parameters/414368#414368

Nie zapominaj, że ustawienia ANSI skonfigurowane dla planu połączenia mają wpływ na wybór planu wykonania. Gdy aplikacja wywołuje procedurę przechowywaną, prawdopodobnie ma inne ustawienia ANSI niż połączenie SSMS.