wersja DBA klasyka XKCD.

gdy zidentyfikujesz, że wykrywanie parametrów jest problemem, musisz sprawdzić, czy implementacja podpowiedzi „przekompiluj” pomoże zaplanować jakość. (Nie wiesz, co to jest parametr sniffing? Dowiedz się z tego posta na blogu lub tego 30-minutowego darmowego filmu.) Musisz zdecydować: jakiej podpowiedzi lub komendy używasz i gdzie ją umieszczasz? Jakie kompromisy robisz, jeśli chodzi o możliwość dostrojenia wydajności serwera SQL w przyszłości?

ten post przechodzi przez typowe opcje, które musisz nakłonić (lub walnąć) SQL Server do wygenerowania nowego planu wykonania. Podam kilka plusów i minusów dla każdej metody i wyjaśnię, co jest przydatne, a czego warto unikać.

aby wszystko było stosunkowo proste, właśnie omawiam, w jaki sposób odnosi się to do procedur składowanych–ten post nie obejmuje innych form sparametryzowanych (lub nieparametryzowanych) zapytań. Jeśli masz duże pytania w tych obszarach, możesz zasugerować je do przyszłego postu w komentarzach.

Zastrzeżenie: rekompilacja podpowiedzi może zabić wydajność przez podpalenie procesorów, gdy są używane nieprawidłowo. Obchodzić się ostrożnie!

przydatne Dynamiczne widoki zarządzania

Kiedy mówię o wpływie na pamięć podręczną planu wykonania, odniosę się do dwóch DMV:

• sys.dm_exec_query_stats – ten DMV jest pomocny, aby zobaczyć najlepsze instrukcje na serwerze SQL, niezależnie od tego, czy są częścią procedury, czy nie. Sprawdź przykładowe zapytanie tutaj.
• sys.dm_exec_procedure_stats – ten DMV jest dostępny tylko w SQL Server 2008 i wyższych. Śledzi metryki wykonania dla procedur składowanych. Sprawdź przykładowe zapytanie w Books Online.

w przypadku obu tych DMV posiadanie planu wykonania w pamięci podręcznej jest związane z możliwością wyświetlania metryk wykonania: liczby całkowitych wykonań, całkowitego i średniego CPU, odczytów logicznych itp. Gdy plan wykonania jest usuwany z pamięci podręcznej z powodu rekompilacji, ciśnienia pamięci, restartu lub innych działań, powiązane metryki wykonania są również usuwane.

wykonywanie procedury z podpowiedzią rekompilacji środowiska wykonawczego

jedną z opcji, którą uwielbiam do szybkiego i łatwego testowania, jest możliwość wywołania procedury składowanej z podpowiedzi rekompilacji w czasie wykonywania. Jest to świetne, ponieważ nie musisz zmieniać żadnego skompilowanego kodu. Podpowiedź dotyczy również tego,co robisz w czasie wykonywania.

aby to zrobić, wystarczy użyć składni takiej jak ta:

However, there’s a little bit of a gotcha. If you have nested stored procedures, the recompile hint only applies to code in the outermost procedure. Nadal można uzyskać parametr sniffing przy wywołaniach podprocesów wewnątrz samej procedury składowanej.

więc chociaż lubię to czasami do testowania, jeśli masz jakieś zagnieżdżenie w swoich procedurach składowanych, może to nie pomóc w uzyskaniu naprawdę świeżego planu wykonania.

Korzystanie z procedury składowanej systemu sp_recompile

czasami jest to przydatne, jeśli musisz interweniować podczas incydentu produkcyjnego, jeśli masz zły plan, który jest wielokrotnie używany przez przychodzące sesje. You can force recompilation of plans by running sp_recompile against an object– most common choices are a table or a stored procedure. The command looks like this (a table in this example):

gdy jest to uruchomione, powiązane plany nie są natychmiast usuwane z pamięci podręcznej planu wykonania serwera SQL. Zamiast tego magia dzieje się przy następnym uruchomieniu zapytań odnoszących się do rekompilowanego obiektu. W tym momencie istniejące statystyki wykonania w sys. dm_exec_query_stats zostaną zresetowane dla instrukcji w planie.

liczniki wykonania w sys.dm_exec_procedure_stats niekoniecznie będą resetowane przy następnym użyciu, jeśli zaznaczysz tabelę używaną przez procedurę składowaną do rekompilacji. Ale jeśli zaznaczysz samą procedurę składowaną do rekompilacji, zostanie ona zresetowana przy następnym użyciu. (Nie próbuj tego zapamiętać, po prostu przeczytaj następny akapit.)

z tym są duże minusy. To polecenie wymaga wysokich uprawnień-użytkownik, który je uruchomi, wymaga 'alter’ uprawnień w tabeli. Wymaga to również wysokiego poziomu zamka do ukończenia. Na zapracowanych systemach widziałem, że to polecenie bierze udział w niektórych paskudnych łańcuchach blokujących. Ostrożnie z tym!

chociaż czasami może to być przydatne do rozwiązywania problemów i testowania, nie czyń go częścią kodu produkcyjnego.

przekompiluj podpowiedzi w nagłówkach procedur składowanych

gdy masz procedury, które chcesz wygenerować nowe plany, zaczynasz zastanawiać się, jak używać podpowiedzi w ramach procedury. Opcja, którą większość ludzi odkrywa jako pierwszą, to użycie 'WITH RECOMPILE’ w nagłówku procedury składowanej, w ten sposób:

używanie rekompilacji w nagłówku procedury składowanej jest dość drastyczne — procedura nie będzie buforować planu wykonania, gdy zostanie uruchomiona. Oznacza to:

• brak planów wykonania w pamięci podręcznej do przejrzenia
• brak statystyk wykonania zarejestrowanych w sys.dm_exec_query_stats
• brak statystyk wykonania zarejestrowanych w sys.dm_exec_procedure_stats

Wow, ale szkoda. Jeśli chcesz zidentyfikować wpływ tej procedury na twój serwer, musisz uruchomić jakąś sesję śledzenia lub rozszerzonych zdarzeń oraz pobrać i zinterpretować wyniki. Nie jest to szybkie, a ślady biegania mogą mieć wpływ na wydajność.

trochę duży minus. Z tego powodu nie podoba mi się ten wybór.

REKOMPILUJ podpowiedzi na poszczególnych poleceniach

Ta opcja jest nieco lepsza, ale z czasem ma znacznie lepszą wypłatę. With this option you take the RECOMPILE hint and apply it only to statements in the stored procedure where you’d like to implement the recompile, like this:

nawet jeśli musisz użyć podpowiedzi na wszystkich instrukcjach w procedurze, a nie w nagłówku, nadal ma to zalety! Umieszczając podpowiedź rekompilacji na poziomie instrukcji w proc, magicznie otrzymujesz:

• ograniczone plany wykonania w pamięci podręcznej do przeglądu (Ostatnie wykonanie)
• ograniczone statystyki wykonania zapisane w sys.dm_exec_query_stats. otrzymasz statystyki tylko dla ostatniego wykonania, ale kolumna plan_generation_num będzie zwiększana, przynajmniej dając ci wgląd w to, że coś się dzieje.
• statystyki wykonania zapisane w sys.dm_exec_procedure_stats

fakt, że otrzymujesz pewne informacje w tych DMV może być bardzo przydatny z czasem. Zastosowanie rekompilowanych podpowiedzi tylko do instrukcji, które ich potrzebują, jest również bardziej odpowiedzialne– zmniejsza ryzyko poparzenia się z czasem dzięki CPU burn.

DBCC FREEPROCCACHE – opcja jądrowa

nie jest to ściśle podpowiedź rekompilacji– ale na pewno powoduje rekompilację. SQL Server ma polecenie, którego możesz użyć, aby powiedzieć mu: „zacznij od nowa ze świeżymi planami wykonania”. Zwykle używa się go w ten sposób:

This command makes everything start fresh– for every query:

• użycie Procesora może wzrosnąć wraz z kompilacją nowych planów
• statystyki wykonania są usuwane w sys.dm_exec_query_stats (natychmiast)
• statystyki wykonania są usuwane w sys.dm_exec_procedure_stats (natychmiast)

To polecenie nie jest złe– jest czas i miejsce w rozwiązywaniu problemów podczas jego używania może pomóc szybko dotrzeć do głównej przyczyny problemu z wydajnością. Ponieważ jednak ma to wpływ na całą pamięć podręczną i może również wpływać na wydajność, musisz być bardzo ostrożny, używając go. Nie polecam używania tego polecenia w regularnych zadaniach, zautomatyzowanych procesach lub kodzie produkcyjnym.

począwszy od SQL Server 2008, możesz użyć tego polecenia nieco łagodniej– możesz usunąć określony plan z pamięci podręcznej za pomocą „uchwytu planu” lub „uchwytu sql”– ale oczywiście musisz dowiedzieć się, czym jest ten uchwyt i dlaczego chcesz go usunąć. Może się to przydać w niektórych niszowych sytuacjach, ale w praktyce nie pojawia się zbyt wiele. Możesz również wyczyścić pulę gubernatora zasobów, ale cóż, musiałbyś używać gubernatora zasobów.

Co to wszystko znaczy?

podczas testowania w zestawie narzędzi jest miejsce zarówno na procedurę EXEC z REKOMPILACJĄ, jak i procedurę sp_recompile. Używaj ich ostrożnie i uważaj na ewentualne konsekwencje, szczególnie w przypadku sp_recompile.

Kiedy implementujesz podpowiedzi w kodzie, nie masz włączonych przekompilowanych migawek– czasami możesz również z powodzeniem używać podpowiedzi „Optymalizuj dla” (chociaż czasami optymalizujesz dla przeciętnych). Czasami dynamiczny sql może Ci pomóc.

ale jeśli używasz podpowiedzi rekompilacji, zachowaj rekompilacje na poziomie instrukcji– a nie w nagłówkach procedur.

aby dowiedzieć się więcej, zapoznaj się z podstawami klasy parametr Sniffing.