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
USE AdventureWorks
Podczas analizy zapytań interesować nas będzie ilość odczytów z dysku, stąd też włączymy wyświetlanie takich informacji poprzez:
SET STATISTICS IO ON
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:
SELECT * FROM Person.Address
WHERE PostalCode='92173'
--218 logical reads
SELECT * FROM Person.Address
WHERE PostalCode='98168'
--280 logical reads
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?
CREATE NONCLUSTERED INDEX IX_Person_Address_PostalCode
ON Person.Address(PostalCode)
Sprawdźmy teraz nasze zapytania:
SELECT * FROM Person.Address
WHERE PostalCode='92173'
--4 logical reads
SELECT * FROM Person.Address
WHERE PostalCode='98168'
--280 logical reads
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ń:
CREATE PROCEDURE Person.GetByPostalCode @vPostalCode VARCHAR(6)
AS
SELECT * FROM Person.Address
WHERE PostalCode=@vPostalCode
Jeżeli mam rację, to teraz decydujące jest pierwsze uruchomienie procedury. Zobaczmy:
Uruchamiamy procedurę dla małego kodu pocztowego:
EXECUTE Person.GetByPostalCode '92173'
--logical reads 4
Cztery strony. To dobry wynik. A co z zapytaniem o duży kod pocztowy?
EXECUTE Person.GetByPostalCode '98168'
--logical reads 432
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.
EXECUTE Person.GetByPostalCode '98168' WITH RECOMPILE
--280
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:
EXECUTE sp_recompile 'Person.GetByPostalCode'
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:
EXECUTE Person.GetByPostalCode '98168'
--280
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’?
EXECUTE Person.GetByPostalCode '92173'
--logical reads 280
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:
ALTER PROCEDURE Person.GetByPostalCode @vPostalCode VARCHAR(6)
WITH RECOMPILE
AS
SELECT * FROM Person.Address
WHERE PostalCode=@vPostalCode
Od tej pory wykonywane w dowolnej kolejności polecenia będą się posługiwać swoimi zoptymalizowanymi planami wykonania
EXECUTE GetByPostalCode '92173'
--logical reads 4
EXECUTE GetByPostalCode '98168'
--280
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:
DROP INDEX IX_Person_Address_PostalCode ON Person.Address
DROP PROCEDURE Person.GetByPostalCode
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/
