poniedziałek, 10 lutego 2014

Cachowanie planów wykonania

SQL Server domyślnie przechowuje plany wykonania dla wszystkich zapytań. Każdy plan zajmuje jednak trochę miejsca i jak tego miejsca na serwerze będzie za mało to inne procesy mogą z tego powodu działać znacznie wolniej. Poza planami wykonania zapytań server w cachu przechowuje też dane, które były zwrócone przez zapytania, tak by w przypadku ponownego uruchomienia tego samego zapytanie nie trzeba było odczytywać ponownie danych z dysku.

Dlatego, też jeżeli server ma w pamięci zbyt dużo planów wykonania, które nie podnoszą wydajności serwera mniej miejsca będzie dostepnego dla danych.

Jak już wspomniałem, SQL server domyślnie przechowuje wszystkie plany, ale w zależności od rodzaju zapytania, mogą być one w różnej postaci.
--Query 1  (ad hoc)
select * from [sys].[objects] where object_id = 3
go
 
--Query 2 (ad hoc)
declare @id int = 3
select * from [sys].[objects] where object_id = @id
go
Powyższe dwa zapytania są klasycznym przykładam zapytań ad hoc. Wykonanie tego skryptu spowoduje, że w cachu pojawią się dwa plany wykonania. Zmiana wartości predykatu wzapytaniu spowoduje wygenerowanie nowego planu. Niekonieczie musi być to zła wiadomość z punktu widzenia poszczególnych zapytań (lepsze wykorzystanie statystyk). Trochę więcej na ten temat pisałem tutaj.

Zdarza się, że tego typu zapytania znajdują się w aplikacjach. Z reguły oznacza to kiepską architekturę systemu. Choć pewnie może wynikać też z faktu, że kiedyś kawałek kodu został napisany tylko “tymczasowo” i tak już zostało. Czasami też po prostu łatwiej skorzystać z zapytania ad hoc niż tworzyć kolejną procedurę składowaną. Tak czy siak plan wykonania będzie w cachu.

Innym podejściem jest wykorzystanie sp_executesql.
--Query 3 (prepared statement)
declare @id int = 3
declare @sql nvarchar(max)
set @sql = N'select * from sys.objects where object_id = @id'
exec sp_executesql @sql, N'@id int', @id = @id
go
W tym przypadku plan wykonania będzie jeden niezależnie od wartości predykatu. W zależności od liczebności zbioru danych i jakości statystyk niewłaściwe indeksy mogą być wykorzystane dla konkretnego warunku. Dobra wiadomość jest taka, że tylko jeden plan wykonania będzie zapisany. Jest to zdecydowana poprawa w stosunku do poprzedniej opcji.

Jeżeli zapytanie jest wykonywane bardzo sporadycznie, do tego stopnia, że nie przeszkadza nam ponowne wygenerowanie planu wykonania, warto zastanowić się nad OPTION(RECOMPILE). W przypadku zastosowania tej opcji w zapytaniu, jego plan nie zostanie dodany do cachu. Skutkiem ubocznym, będzie to, że każde wykonanie zapytania będzie wymagało jego kompilacji.

Poniżej przykład tego typu zapytania
--Query 4 (recompile)
select * from sys.objects where object_id = 3 option(recompile)
go
OPTION(RECOMPILE) może być także użyte w przypadku sql_executesql.
Warto mieć powyższe na uwadze projektując aplikacje i dostęp do bazy danych. Nie każde zapytanie jest takie samo i różne sposoby ich wykonywania mogą poprawić wydejność serwera.

Prześlij komentarz