Problemy z optmalizacją procedur składowanych w SQL (Opcja WITH RECOMPILE)

27-Maj-2011

Każde zapytanie realizowane przez SQL serwer podlega procesowi optymalizacji. Serwer ‘wymyśla sobie’ kilka sposobów z pomocą, których może dostać się do właściwych rekordów i zwrócić je klientom. Optymalizowane są zapytania, praktycznie za każdym razem, kiedy są uruchamiane, no chyba, że uda się je sparametryzować. Podobnie optymalizowane są procedury. Z tym, że tu można natknąć się na pewien problem. Procedura jest optymalizowana tylko jeden raz. Dzieje się to podczas pierwszego uruchomienia procedury.

Jeżeli parametry przekazane do procedury są specyficzne, to wymyślony przez optymalizator SQL plan wykonania jest również specyficzny i w przypadku uruchomienia procedury z innymi parametrami SQL serwer będzie próbował wykonać ją nadal według swojego specyficznego planu.

Zobaczymy to na przykładzie. Przyjrzymy się ile stron musi być odczytanych z dysku do zrealizowania zapytania w różnych okolicznościach. Przyglądając się prostym przykładom zobaczysz działanie przełącznika ‘WITH RECOMPILE’ oraz procedury ‘sp_recompile’. Przykład bazuje na tabeli Person.Address z bazy danych AdventureWorks. (Artykuł na Mobilo  przedstawia skąd pobrać przykładowe bazy danych)

Zaczynamy. Pracujemy w AdventureWorks

Podczas analizy zapytań interesować nas będzie ilość odczytów z dysku, stąd też włączymy wyświetlanie takich informacji poprzez:

Od tej pory na karcie Messages będzie można przeczytać komunikat o ilości odczytanych stron z dysku. Część stron jest rzeczywiście wczytywana z dysku, a część w wyniku optymalizacji działania serwera już się znajduje w pamięci podręcznej, stąd obserwować będziemy wartość ‘logical reads’

W tabeli Person.Address znajduje się jeden rekord z kodem pocztowym 92173 i aż 215 rekordów z kodem pocztowym 98168. O tym pierwszym kodzie dalej będę mówił ‘mały’, a o tym drugim ‘duży’, ale wynika to wyłącznie z ilości zwracanych rekordów.

Przyjrzyjmy się poleceniom:

Liczba w komentarzu określa ile stron musiał przeczytać serwer, aby zrealizować polecenie. Widać, że różnica między małym a dużym kodem pocztowym nie jest wielka. Dzieje się tak, dlatego że:

  • W przypadku małego kodu pocztowego brakowało optymalnego indeksu. Serwer troszkę „pokombinował”, przejrzał nieco inny indeks i dobrał sobie brakujące dane z indeksu clustrowanego. Chociaż zwraca tylko jeden rekord, to jednak trochę się nad tym napracował!

  • W przypadku dużego kodu pocztowego serwer stwierdził, że przewidywana liczba zwróconych rekordów wynosi 215, co oznacza, że zapytanie nie jest selektywne. Wobec braku lepszych indeksów postanowił, więc przeczytać wszystkie dane z indeksu clustrowanego i odrzucić te, które nie są kodem pocztowym 98168.

Spróbujmy trochę pomóc serwerowi. Może utworzenie indeksu nieklastrowanego  na kodzie pocztowym pomoże w szybszym wyszukiwaniu danych?

Sprawdźmy teraz nasze zapytania:

Brawo! Mały kod pocztowy ruszył z kopyta, bo ma właściwy indeks! Tylko cztery odczyty z dysku, to duży sukces.

Jednak zapytanie z dużym kodem pocztowym nie skorzystało z tego indeksu. Zapytanie nadal nie jest selektywne.

Całe szczęście, że serwer za każdym razem na nowo wymyśla sobie plan zapytania. Dzięki temu w obu tych przypadkach może posłużyć się innym planem zapytania. Inaczej jednak dzieje się w przypadku procedur składowanych. Tutaj plan wykonania jest wymyślany podczas pierwszego wykonania procedury. Przeanalizujmy procedurę składowaną, która składać się będzie wyłącznie z analizowanych wcześniej zapytań:

Jeżeli mam rację, to teraz decydujące jest pierwsze uruchomienie procedury. Zobaczmy:

Uruchamiamy procedurę dla małego kodu pocztowego:

Cztery strony. To dobry wynik. A co z zapytaniem o duży kod pocztowy?

Fatalnie – jakby sql serwer zupełnie nie optymalizował zapytania. Przecież 432 strony to więcej niż cała tabela (zapisana na indeksie clustrownaym). Serwer zastosowal plan wykonania małego kodu pocztowego!

No dobrze- to jak to poprawić? Z pomocą może przyjść opcja WITH RECOMPILE podawana podczas uruchamiania procedury. Sprawia ona, że serwer na potrzeby tego jednego uruchomienia tworzy sobie nowy plan zapytania.

Tak, teraz serwer zadziałał jak dawniej, czyli przejrzał tabelę Person.Address i liczba odczytów spadła znowu do 280. Plan nie będzie zachowany na przyszłe wykonanie i zostanie zaraz wyrzucony. Jeżeli więc wiesz, że masz problem z tym, że serwer stosuje jakiś inny plan wykonania (dla innych parametrów), to możesz wywoływać procedurę z WITH RECOMPILE.

A gdybyśmy tak chcieli, żeby zmienić plan wykonania? Gdyby większość zapytań dotyczyła ‘dużych kodów pocztowych’?  Można wtedy oznaczyć procedurę do wymiany planu wykonania. Poprzez:

Jeżeli teraz wykonasz najpierw zapytanie o duży kod pocztowy to serwer przygotuje dla niego odpowiedni plan zapytania i zapamięta go do przyszłego wykorzystania:

Dobrze. Sytuacja się poprawiła. Zapytanie o duży kod pocztowy przeczesuje tylko 280 stron! A co się teraz stanie z zapytaniem o ‘mały kod pocztowy’?

No nie. Teraz procedura działa z planem wykonania dla ‘dużego kodu pocztowego’. Czy więc nie ma innego wyjścia jak zrezygnować z procedur składowanych na rzecz zwykłych zapytań? Otóż nie! Można oznaczyć procedurę w taki sposób, aby była ona rekompilowana za każdym razem przy uruchomieniu. Robisz to przełącznikiem WITH RECOMPILE podanym podczas tworzenia/zmiany definicji procedury:

Od tej pory wykonywane w dowolnej kolejności polecenia będą się posługiwać swoimi zoptymalizowanymi planami wykonania

Mimo braku specjalnego przełącznika WITH RECOMPILE są za każdym uruchomieniem na nowo optymalizowane.

Jakie stąd wnioski?

  • Tworząc procedurę zastanów się, czy działanie procedury zależy od  przekazanych do niej parametrów.
  • Unikaj tez skomplikowanych wyrażeń IF.
  • Jedna procedura powinna mieć najlepiej jedne określony prosty cel.

Na koniec można jeszcze posprzątać po naszych eksperymentach:

PLi źródłowy można pobrać stąd: proc_with_recompile

Zainteresował Cię artykuł? Zapraszam na moje autoryzowane szkolenia Microsoft w Opolu, Katowicach i Warszawie w zakresie MS SQL w bit Polska. O projektowaniu bazy danych Microsoft SQL Server 2008 opowiada kurs Microsoft 6232. (Implementing a Microsoft SQL Server 2008 Database) Szczegóły:

http://www.bit-polska.pl/szkolenia-it/microsoft/

Dodaj komentarz:

Autor: Rafał Kraik