[PL] Parametry procedur składowanych a plan wykonania
Ostatnimi czasy dość często powraca pewien problem z wydajnością procedur składowanych. Najczęściej ludzie definiują ów problem następująco:
Mam procedurę składowaną, która przyjmuje parametr (-y) wykorzystywany we wnętrzu procedury w klauzuli WHERE polecenia SELECT. Jeżeli podaję wartość parametru (-ów) przy wywołaniu procedury, to procedura wykonuje się znacznie dłużej niż w sytuacji, gdy podam wartość parametru wprost.
Czemu tak się dzieje? Gdy definiujemy parametry procedury, które wewnątrz procedury służą do filtrowania rekordów, SQL Server nie wie, jakie wartości tych parametrów będą podawane przez użytkowników. Jeżeli nie będzie w cache'u procedur (cache'u przechowującym skompilowane plany zapytań) planu danej procedury lub też procedura zostanie zrekompilowana (a może zostać zrekompilowana z różnych powodów) i użytkownik wywoła procedurę z wartościami parametrów nie gwarantującymi odpowiedniej selektywności zapytania, SQL Server może zapamiętać plan wykonania procedury, który nie będzie optymalny dla większości jej wywołań. Zdarza się też, że z uwagi na trudne do przewidzenia wartości parametrów przy bardziej złożonych zapytaniach wewnątrz procedur optymalizator zapytań wybiera nieoptymalny plan wykonania. Przykład (z książki "Inside Microsoft SQL Server 2005: T-SQL Programming" - Itzik Ben-Gan, Dejan Sarka i Roger Wolter, MS Press 2007):
USE Northwind
GO
IF OBJECT_ID('dbo.usp_GetOrders') IS NOT NULL
DROP PROC dbo.usp_GetOrders
GO
CREATE PROC dbo.usp_GetOrders
@odate AS DATETIME
AS
SET NOCOUNT ON
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= @odate
GO
Powyższa procedura jest bardzo prosta, ale świetnie nadaje się do zilustrowania problemu. Załóżmy, że pierwsze wykonanie procedury będzie następujące:
EXEC dbo.usp_GetOrders '19960101'
Plan wykonania powyższego wywołania procedury pokazuje skanowanie indeksu clustered.
Plan ten nie jest zły dla tego konkretnego wywołania, ale załóżmy, że nie jest optymalny dla większości wywołań, jakie będą wykonywane na tabeli dbo.Orders. Drugie wywołanie procedury (z inną wartością parametru) zostanie wykonane z tym samym planem wykonania.
EXEC dbo.usp_GetOrders '19980506'
Jeżeli wykonamy teraz zapytanie z wnętrza procedury podstawiając podaną wyżej wartość parametru wprost, to plan wykonania będzie inny (lepszy, bo wykorzystujący indeks na kolumnie OrderDate).
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= '19980506'
Byłoby idealnie, gdyby taki plan wykonania nie był nigdy zastępowany pokazanym wcześniej planem zawierającym skanowanie indeksu clustered. Oczywiście, można uruchomić procedurę po raz drugi z opcją WITH RECOMPILE, co spowoduje, że plan będzie optymalny (Index Seek).
EXEC dbo.usp_GetOrders '19980506' WITH RECOMPILE
Jednak ta metoda nie gwarantuje, że za jakiś czas plan nieoptymalny (Clustered Index Scan) nie zastąpi w cache'u planu optymalnego (Index Seek).
Istnieje co najmniej kilka metod pozwalających na zachowanie planu wykonania w cache'u nawet w przypadku rekompilacji procedury z wartościami parametrów implikującymi niską selektywność.
Najstarszą z metod jest oszukanie optymalizatora przez podanie dodatkowych parametrów z widocznymi wartościami domyślnymi:
ALTER PROC dbo.usp_GetOrders
@odate DATETIME,
@tempdate DATETIME = '19980506'
AS
SET NOCOUNT ON
SET @tempdate = @odate
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= @tempdate;
GO
Oczywiście, wygląda to nieco dziwacznie, ale jest to skuteczna metoda na zachowanie planu. Wystarczy wykonać powyższą składnię ALTER PROC, a następnie wywołać procedurę z odpowiednią wartością parametru @odate (z wartością gwarantującą selektywność). Po wykonaniu tych czynności nawet wywołanie procedury z rekompilacją nie spowoduje zmiany planu w cache'u.
SQL Server 2005 oferuje pewną opcję, która także potrafi zagwarantować plan wykonania. Tą opcją jest OPTIMIZE FOR. Przykład użycia tej opcji w przypadku omawianej procedury usp_GetOrders:
ALTER PROC dbo.usp_GetOrders
@odate DATETIME
AS
SET NOCOUNT ON
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= @odate
OPTION(OPTIMIZE FOR(@odate = '19980506'));
GO
I wreszcie rozwiązanie trzecie - odpowiednie zwłaszcza, gdy w procedurze znajduje się wiele zapytań, a tylko niektóre muszą zmieniać swój plan wykonania w zależności od wartości parametrów procedury. Można częściowo rekompilować procedury powodując, że pewne polecenia wewnątrz procedury będą miały plan wykonania zawsze adekwatny do wywołania procedury.
ALTER PROC dbo.usp_GetOrders
@odate DATETIME
AS
SET NOCOUNT ON
SELECT OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders
WHERE OrderDate >= @odate
OPTION(RECOMPILE);
GO
Tak zdefiniowana procedura gwarantuje, że w przypadku zapytania selektywnego będzie wykonywane skanowanie indeksu, a w przypadku niskiej selektywności optymalizator wybierze skanowanie indeksu clustered.
Temat optymalizacji procedur oraz ich rekompilacji jest oczywiście dużo szerszy, ale powyższy trywialny przykład pokazuje, że używanie procedur nie jest bezproblemowe i czasem trzeba pomóc optymalizatorowi, jeżeli produkowane przez niego plany wykonania nie spełniają naszych oczekiwań.
Ze swojej strony polecam whitepaper Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005, w którym opisane są mechanizmy funkcjonowania cache'u procedur, przyczyny automatycznej rekompilacji oraz metody rekompilacji dostępne dla użytkownika.