Parameter sniffing
Concept
Zachowanie silników w stylu SQL Server, które kompilują plan dla wartości parametrów widzianych w czasie kompilacji i ponownie wykorzystują zbuforowany plan, co może skutkować słabą wydajnością przy bardzo różnych wartościach parametrów; nazywane także wrażliwością parametrów.
Opis
Parameter sniffing to zachowanie silników baz danych w stylu SQL Server, w którym plan zapytania jest kompilowany dla wartości parametrów widzianych w czasie kompilacji, a następnie zbuforowany plan jest ponownie wykorzystywany. Nazywa się je także wrażliwością parametrów. Samo zachowanie nie jest błędem: silnik analizuje bieżące wartości parametrów podczas kompilacji lub rekompilacji zapytania i przekazuje je optymalizatorowi zapytań, który może dzięki temu wygenerować wydajniejszy plan wykonania dla tych wartości. Problem pojawia się, gdy zbuforowany plan jest później używany dla wartości parametrów, które bardzo różnią się od pierwotnych, ponieważ plan odpowiedni dla jednej wartości może działać słabo dla innej.
Przewodnik po architekturze przetwarzania zapytań w SQL Server firmy Microsoft opisuje parameter sniffing w sekcji poświęconej wrażliwości parametrów. Zgodnie z tym przewodnikiem proces dotyczy procedur składowanych, zapytań uruchamianych przez sp_executesql oraz zapytań przygotowanych. Ma on bezpośredni związek z ponownym użyciem planów, ponieważ skompilowane plany są przechowywane w pamięci podręcznej planów i mogą być pobierane przez kolejne wykonania. Pierwszy zestaw wartości, który wywołuje kompilację, kształtuje więc plan otrzymywany przez kolejne wywołania, dlatego wyniki mogą się różnić w zależności od tego, które wywołanie skompilowało plan.
Przewodnik wskazuje jedno rozwiązanie: wskazówkę zapytania OPTION (RECOMPILE). Dzięki niej optymalizator traktuje parametry i zmienne lokalne jako stałe znane w czasie kompilacji, które mogą zostać wstawione jako literały. Używane są wartości obowiązujące tuż przed uruchomieniem instrukcji, a niekoniecznie wartości przekazane początkowo. Może to pomóc, gdy dane wejściowe są bardzo zmienne lub niesymetryczne. Przewodnik ostrzega jednak, że wskazówka powoduje rekompilację zapytania przy każdym wykonaniu, co może być kosztowne, i radzi zmierzyć wpływ na całe obciążenie przed jej wdrożeniem. Przewodnik odsyła także do osobnego artykułu o optymalizacji Parameter Sensitive Plan, w celu dalszej lektury.
Powiązany szczegół z tej samej dokumentacji dotyczy zmiennych lokalnych. Ich wartości nie są analizowane w czasie kompilacji. Zamiast tego optymalizator szacuje wartości na podstawie informacji o gęstości, a gdy brak statystyk, stosuje stałe procentowe przybliżenia dla porównań równościowych i zakresowych. To rozróżnienie ma znaczenie przy diagnozowaniu wolnego zapytania, ponieważ parametr i zmienna lokalna mogą prowadzić do różnych szacunków, a w konsekwencji do różnych planów.
W praktyce parameter sniffing objawia się zapytaniami, które działają szybko dla jednych danych wejściowych, a wolno dla innych, lub zmianą wydajności po usunięciu planu z pamięci podręcznej i jego ponownej kompilacji. Odcinek M365.FM "Fix Bad Data Upstream with T-SQL Contracts" wymienia parameter sniffing wśród typowych awarii w przedsiębiorstwach, opisując go jako zjawisko, które może wyglądać jak losowość. Odcinek przedstawia też kształty planów jako kwestię zarządzania, twierdząc, że jeśli kształty planów się zmieniają, zmieniają się wraz z nimi koszty i niezawodność, czyli zachowanie planu przekłada się na stabilność kosztów i ryzyko operacyjne.
Odcinek "Design Reliable and Resilient Azure Architectures" umieszcza parameter sniffing w procesie rozwiązywania problemów w Azure SQL Database. Sugeruje włączenie Intelligent Insights i Query Performance Insight, przechwytywanie długo działających zapytań, analizę planów wykonania oraz identyfikowanie brakujących indeksów lub parameter sniffing. Wspomina też o zaleceniach automatycznego dostrajania Azure SQL oraz sugestiach indeksów w Azure Advisor, a także o optymalizacji zapytań, odpowiednim indeksowaniu i rozważeniu skalowania DTU lub vCore albo przejścia na Hyperscale.
Łącznie źródła traktują parameter sniffing jako kompromis związany z buforowaniem planów: ponowne użycie skompilowanego planu oszczędza pracę kompilatora, ale plan dostrojony do jednej wartości parametru może nie pasować do innych. Typowe decyzje w tym zakresie obejmują analizę planów wykonania, sprawdzanie, czy rozkład danych jest niesymetryczny, oraz ważenie kosztu kompilacji takich opcji jak OPTION (RECOMPILE) względem korzyści z lepiej dopasowanych planów.
Overview compiled with AI from M365.FM episodes and the facts on this page. You can improve it with the edit button below.
| Typ | Concept |
|---|
Często zadawane pytania
Questions marked with AI were written with AI from the facts on this page.
Czym jest parameter sniffing? (AI)
To zachowanie silników w stylu SQL Server, które kompilują plan zapytania dla wartości parametrów widzianych w czasie kompilacji i ponownie wykorzystują zbuforowany plan. Może to działać słabo, gdy późniejsze wartości parametrów znacznie się różnią. Nazywa się to także wrażliwością parametrów.
Jak OPTION (RECOMPILE) wpływa na problemy z parameter sniffing? (AI)
Zgodnie z przewodnikiem Microsoft po przetwarzaniu zapytań wskazówka sprawia, że optymalizator traktuje parametry i zmienne lokalne jako stałe znane w czasie kompilacji, które mogą zostać wstawione jako literały, co może pomóc przy bardzo zmiennych lub niesymetrycznych danych. Zapytanie jest rekompilowane przy każdym wykonaniu, co może być kosztowne, dlatego najpierw warto zmierzyć wpływ na całe obciążenie.
Czy zmienne lokalne są analizowane w taki sam sposób jak parametry? (AI)
Nie. Dokumentacja stwierdza, że wartości zmiennych lokalnych nie są analizowane w czasie kompilacji. Optymalizator szacuje je zamiast tego na podstawie informacji o gęstości, a bez statystyk stosuje stałe procentowe przybliżenia dla porównań równościowych i zakresowych.
Community wiki
Short info above, community knowledge below: add practical tips, examples or corrections. New contributions are reviewed first; trusted members publish directly.
Eksperci i firmy w tym temacie
Nikt jeszcze nie wskazał tego tematu. Dodaj go do swojego profilu.
Maintained by Mirko Peters (Microsoft MVP) · Last updated: