Zrozumienie statystyk, planów wykonania i „rosnącego kluczowego problemu”

Staram się lepiej zrozumieć (koncepcyjnie) związek między statystykami, planami wykonania, wykonywaniem procedury składowanej.

Czy mam rację mówiąc, że statystyki są używane tylko podczas tworzenia planu wykonania dla procedury składowanej i nie są używane w rzeczywistym kontekście wykonania? Innymi słowy, jeśli jest to prawdą, po utworzeniu planu (i przy założeniu, że zostanie on ponownie wykorzystany), jak ważne są statystyki „aktualne”?

Szczególnie motywował mnie artykuł, który przeczytałem ( Statystyka, szacunki wierszy i kolumna z rosnącą datą ), który opisuje scenariusz bardzo podobny do tego, z którym codziennie spotykam się z kilkoma bazami danych naszych klientów.

Mamy kolumnę z rosnącą datą / czasem w jednej z naszych największych tabel, do której regularnie wysyłamy zapytania przy użyciu określonej procedury składowanej.

W jaki sposób zapobiegasz starzeniu się planów wykonania, gdy dziennie dodawanych jest sto tysięcy wierszy?

Jeśli często aktualizujemy statystyki w celu rozwiązania tego problemu, czy warto zastosować wskazówkę OPCJE (RECOMPILE) w zapytaniu dotyczącym tej procedury składowanej?

Wszelkie porady i zalecenia będą mile widziane.

Aktualizacja : Używam SQL Server 2012 (SP1).

Czy mam rację mówiąc, że statystyki są używane tylko podczas tworzenia planu wykonania dla procedury składowanej i nie są używane w rzeczywistym kontekście wykonania?

Nie, zdarza się, że plan wykonania dla procedury składowanej jest buforowany. Zakładając, że dostępna jest wystarczająca ilość pamięci, aby kontynuować utrzymywanie planu, nie zmieni się, chyba że nastąpi jedna z poniższych sytuacji (od buforowania planu wykonania i ponownego użycia w dokumentacji programu SQL Server, podkreślenie dodane):

• Zmiany wprowadzone w tabeli lub widoku, do których odwołuje się zapytanie (ALTER TABLE i ALTER VIEW).
• Zmiany wprowadzone w pojedynczej procedurze, która spowoduje usunięcie wszystkich planów tej procedury z pamięci podręcznej (ALTER PROCEDURE).
• Zmiany w indeksach używanych przez plan wykonania.
• Aktualizacje statystyk używanych przez plan wykonania, generowane jawnie z instrukcji, takich jak UPDATE STATISTICS, lub generowane automatycznie.
• Usunięcie indeksu używanego przez plan wykonania.
• Wyraźne wywołanie do sp_recompile.
• Duża liczba zmian w kluczach (generowanych przez instrukcje INSERT lub DELETE od innych użytkowników, którzy modyfikują tabelę, do której odwołuje się zapytanie).
• W przypadku tabel z wyzwalaczami, jeśli liczba wierszy we wstawionych lub usuniętych tabelach znacznie wzrośnie.
• Wykonywanie procedury składowanej przy użyciu opcji Z RECOMPILE.

Jeśli więc statystyki zostaną zaktualizowane, buforowany plan automatycznie uwzględni nowe statystyki i zostanie ponownie skompilowany.

W jaki sposób zapobiegasz starzeniu się planów wykonania, gdy dziennie dodawanych jest sto tysięcy wierszy?

Jednym ze sposobów jest, jeśli istnieje wiele aktualizacji tabeli, jak wspomniano powyżej. Kilkaset tysięcy zmienionych rzędów może spełnić ten warunek. Ale jeśli chcesz mieć pewność lub mieć bardziej szczegółową kontrolę: aktualizując swoje statystyki. Możesz zezwolić SQL Serverowi na automatyczne tworzenie statystyk i zarządzanie nimi lub ręcznie zrobić to sam. Więcej informacji na temat obu metod można znaleźć w Automatycznej aktualizacji programu SQL Server i Opcjach automatycznego tworzenia statystyk . Kiedy / jeśli przeprowadzasz cotygodniową odbudowę indeksów, spowoduje to również aktualizację planów. Przeprowadź testy, aby zobaczyć, co jest dla Ciebie najbardziej korzystne, ponieważ zbyt częste aktualizowanie statystyk może nie przynieść żadnych rzeczywistych wyników.

Jeśli często aktualizujemy statystyki w celu rozwiązania tego problemu, czy warto zastosować wskazówkę OPCJE (RECOMPILE) w zapytaniu dotyczącym tej procedury składowanej?

Nie musisz go używać RECOMPILE, ponieważ na podstawie powyższego fragmentu możesz zobaczyć, że plan wykonania jest odpowiednio aktualizowany, gdy dostępne są nowe statystyki. Może ci się przydać aktualizacja statystyk na koniec dnia (jeśli naprawdę się martwisz), ale nie sądzę, aby była to wyraźna potrzeba oparta na tym, co powiedziałeś do tej pory. Znowu jednak przetestowałbym to, aby zobaczyć, jaki może to mieć wpływ na wydajność procedury składowanej i odpowiednio zaplanowałem.

Czy mam rację mówiąc, że statystyki są używane tylko podczas tworzenia planu wykonania

Nie, nieaktualne statystyki mogą powodować rekompilację związaną z optymalizacją instrukcji, której dotyczy problem, .

Mamy kolumnę z rosnącą datą / czasem w jednej z naszych największych tabel, do których regularnie wysyłamy zapytania

Nieoptymalne plany wykonania spowodowane tym, że wartości predykatów znajdują się poza (szczególnie powyżej), zakres wartości przechowywanych na odpowiednim histogramie statystycznym jest znany jako problem rosnącego klucza . Odbudowywanie statystyk jest jednym z możliwych rozwiązań, ale może być dość intensywne. Alternatywy obejmują:

• Flagi śledzenia 2389 i 2390 . Wymaga to istnienia indeksu z problematyczną kolumną jako kluczem wiodącym. Nie działa z tabelami partycjonowanymi i działa tylko w SQL Server 2014, jeśli użyty jest oryginalny estymator liczności. Flaga śledzenia 4139 może być również wymagana, jeśli obiekt statystyczny jest oznakowany stacjonarnie.

• Uaktualnienie do SQL Server 2014. Nowy estymator liczności zawiera logikę do szacowania poza histogramem z wykorzystaniem informacji o średniej gęstości. W niektórych ważnych okolicznościach może to być mniej dokładne niż flagi śledzenia 2389/2390.

• Włącz częstsze automatyczne aktualizacje statystyk dla dużych tabel z flagą śledzenia 2371 . Z tą flagą śledzenia, zamiast aktualizować po 20% + 500 zmianach, wymagane są tylko SQRT(1000 * Table rows)modyfikacje . To nie jest tak kompletne rozwiązanie jak te wspomniane wcześniej, ponieważ aktualizacje mogą wciąż nie być uruchamiane wystarczająco często.

Jeśli źródłem problemu nie są tak częste kompilacje planu oparte na wartościach predykatów wykraczających poza histogram, ale więcej o skutkach czasami buforowania tak złego planu w wyniku wąchania parametrów, możesz również rozważyć:

• Wyłączanie wąchania parametrów za pomocą flagi śledzenia 4136
• Używanie OPTIMIZE FOR (@parameter = value)do kompilowania planu dla znanej reprezentatywnej wartości
• Używanie OPTIMIZE FOR (@parameter UNKNOWN)do optymalizacji przy użyciu średniego rozkładu
• Używanie OPTIMIZE FOR UNKNOWN(to samo co 4136, ale na zapytanie)
• Używanie OPTION (RECOMPILE)do kompilacji za każdym razem, wąchanie określonej wartości. Jeśli zdecydowana większość wartości czasu wykonywania znajduje się w histogramie, może to być skuteczne.

Aby uzyskać więcej informacji na temat wykrywania parametrów, osadzania i opcji ponownej kompilacji, zobacz mój artykuł na stronie SQLperformance.com.