Ważną cechą optymalizacji zapytań w SQL Server jest to, że raz przygotowany plan wykonania (execution plan) jest zapisywany w cache procedur. Kiedy po raz kolejny użytkownik uruchomi takie samo zapytanie, SQL serwer nie tworzy nowego planu zapytań, ale realizuje stary zapamiętany plan. Oczywiście zdarzają się sytuacje kiedy plan trzeba utworzyć od nowa. Plan utworzy się automatycznie od nowa jeżeli np. zmieni się struktura tabel, przeliczą się statystki lub cache zostanie wyczyszczony za pomocą polecenia:
Dbcc freeproccache
Jak można podejrzeć aktualny cache? Proponuję poniższe zapytanie (z sqlauthority):
SELECT DISTINCT
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
s.creation_time AS LogCreatedOn
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time DESC
Świeżo po wyczyszczeniu (przez wspomniane dbcc freeproccache), zapytanie to może nic nie wyświetlić – cache jest pusty. Ale już drugie uruchomienie tego zapytania powinno wyświetlić informację o zapamiętanym planie wykonania dla tego właśnie zapytania!
Cóż, widać naocznie że serwer zapamiętuje plany wykonania. Kolumna ‘execution count’ mówi ile razy był wykorzystywany dany plan zapytania. Jeżeli więc ponowisz zapytanie kilka razy zaobserwujesz rosnącą wartość w tej kolumnie.
Podobnie ma się sprawa z planami dla procedur. Zdarzają się jednak sytuacje kiedy wolałbyś aby cache procedur został zupełnie pominięty.
Rozważmy taką sytuację. Masz procedurę składowaną, która została przy pierwszym uruchomieniu zoptymalizowana zgodnie z otrzymanymi parametrami i będzie zwracać 10 rekordów z 1 miliona. Plan wykonania tej procedury zapisał się w cachu. Ale jeżeli zmienisz parametry i procedura będzie teraz zwracać 500 tys. Rekordów, to jej plan wykonania powinien wyglądać inaczej!
W takim przypadku można użyć przełącznika WITH RECOMPILE w poleceniu tworzącym procedurę, a już każdorazowo przy wywołaniu procedury będzie ona na nowo optymalizowana i kompilowana.
Tylko czy warto? Zwłaszcza gdyby się miało okazać, że właściwie plan wykonania dla procedury jest stale taki sam, a zmienia się jedynie sposób wykonania pojedynczej instrukcji SELECT w tej procedurze… Wtedy z pomocą przychodzi query hint RECOMPILE:
SELECT * FROM master.sys.all_columns OPTION (RECOMPILE)
Tak zapisane polecenie SELECT wewnątrz procedury, która wcale nie jest oznaczona jako WITH RECOMPILE, sprawia, że plan wykonania procedury został utworzony raz, a plan wykonania kluczowego SELECT w tej procedurze będzie zawsze rozpatrywany od nowa.
Łatwo to sprawdzić. Wykonaj powyższe polecenie SELECT z query hint OPTION (RECOMPILE), a polecenie wyświetlające cache procedur wcale go nie wykaże. Jeżeli jednak opuścisz query hint i kilka razy wywołasz SELECT, to zaraz odnajdziesz nowy zapis w cache procedur.
