Użycie opcji OPTION (RECOMPILE) dla instrukcji

Zacząłem tę serię postem zatytułowanym: Little Bobby Tables, SQL Injection i EXECUTE AS. Następnie przeszedłem do omawiania niektórych różnic w poście zatytułowanym: EXEC i sp_executesql – czym się różnią?

Dzisiaj chciałbym odnieść się do kilku komentarzy, jak również kontynuować kilka wskazówek i sztuczek używając tych poleceń.

Po pierwsze – czy mogliśmy pomóc w wydajności instrukcji sp_executesql?

Tak…

Jeśli wiemy, że instrukcja zwraca zmienną ilość danych (na podstawie dostarczonych parametrów), możemy użyć funkcji SQL Server 2005 WITH RECOMPILE, aby powiedzieć SQL Server, że wykonywana instrukcja powinna mieć utworzony własny plan i że wcześniejsze plany (jeśli istnieją) nie powinny ponownie użyć instrukcji. Mówi to również SQL Serverowi, że ten konkretny plan jest „planem jednorazowym”, który nie powinien być ponownie użyty przez kolejnych użytkowników. Aby zobaczyć kombinację tych wszystkich rzeczy – użyję niektórych DMV, które śledzą cache planu i wykorzystanie planu.

DBCC FREEPROCCACHE
GO

SELECT st.text, qs.EXECUTION_COUNT -, qs.*, cp.*
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS cp
WHERE st.text like '%FROM dbo.member%'
GO

W tej chwili zapytanie zwraca 0 wierszy.

Wykonam poniższe polecenie, a następnie ponownie sprawdzę cache planu:

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = 'SELECT * FROM dbo.member WHERE lastname LIKE @lastname'
EXEC sp_executesql @ExecStr, N’@lastname varchar(15)', 'Tripp'
GO

Teraz mamy wiersz dla naszego sparametryzowanego planu zapytania:

text EXECUTION_COUNT(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname 1

Więc, co to nam pokazuje… pokazuje nam, że istnieje plan w cache dla tego zapytania. A jeśli jesteśmy zainteresowani zobaczeniem planu, możemy usunąć zakomentowane cp.* w powyższym zapytaniu, aby uzyskać kolumnę cp.query_plan. Po kliknięciu na plan XML, SSMS przejdzie BEZPOŚREDNIO do graficznego okna planu zapytania (od 2008):

I po raz kolejny widzimy optymalny plan (użycie indeksu i wykonanie zakładki lookup), ponieważ zapytanie to jest bardzo selektywne (tylko 1 wiersz).

Wykonamy dokładnie to samo polecenie ponownie – używając wartości Anderson tylko po to, aby ustawić się w punkcie, w którym byliśmy w zeszłym tygodniu:

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = 'SELECT * FROM dbo.member WHERE lastname LIKE @lastname'
EXEC sp_executesql @ExecStr, N’@lastname varchar(15)', 'Anderson'
GO

I widzimy, że używa on dokładnie tego samego planu (patrząc na showplan). W rzeczywistości możemy zobaczyć, że od sprawdzenia naszej pamięci podręcznej planu, jak również:

text EXECUTION_COUNT(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname 2

And, while we know that this plan (to use the index) is good for the highly selective value of 'Tripp' it’s is not good for the value of Anderson as there are many rows that match. Jeśli podejrzewalibyśmy to i/lub wiedzielibyśmy o tym podczas wykonywania (z poziomu klienta) wtedy moglibyśmy użyć OPCJI (RECOMPILE) aby zmusić SQL Server do uzyskania nowego planu:

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = 'SELECT * FROM dbo.member WHERE lastname LIKE @lastname OPTION (RECOMPILE)'
EXEC sp_executesql @ExecStr, N’@lastname varchar(15)', 'Anderson'
go

Plan zapytania jako wynik tego wykonania to:

I jest to bardziej optymalny plan biorąc pod uwagę ten parametr. W tym momencie pytania (IMO) są następujące:

1. Czy ktoś naprawdę byłby w stanie programowo oszacować, że parametr przesłany od klienta jest „nietypowy” i / lub że uzasadnia rekompilację? I, jak sądzę, mogę odpowiedzieć tak – dla niektórych parametrów – takich jak te z wieloznacznikiem. Ale jeśli mówimy tylko o dwóch różnych wartościach w odniesieniu do pojedynczej kolumny, byłoby to zgadywanie statystyk danych.
2. Czy to konkretne oświadczenie powinno być zawsze wykonywane w celu rekompilacji i nigdy nie zapisywać planu?
3. Co SQL Server zrobił z samym planem?

Pójdę z odpowiedzią #3 jako, że ta jest łatwa do odpowiedzi. Używając tego samego stwierdzenia, ponownie zapytam o cache planu:

text EXECUTION_COUNT(@lastname varchar(15))SELECT * FROM dbo.member WHERE lastname LIKE @lastname 2

Mimo, że to już trzeci raz wykonujemy to stwierdzenie, to ostatnie wykonanie NIE zostało umieszczone w cache. Został on użyty wyłącznie do wykonania z OPCJĄ (RECOMPILE). I NIE będzie miało wpływu na przyszłe wykonania. Jeśli wrócę i wykonam bez OPCJI (RECOMPILE) to otrzymam wcześniejszy plan (użycie indeksu).

Teraz, pozostałe dwa pytania – te są o wiele bardziej interesujące i to właśnie tutaj uważam, że procedury składowane powinny być używane. Osobiście uważam, że programiści, którzy znają dane i znają aplikację – będą o wiele lepsi w tworzeniu PRAWIDŁOWEGO kodu, zwłaszcza gdy zrozumieją wszystkie dostępne dla nich opcje.

Oto sposób, w jaki myślę o procedurach składowanych i rekompilacji:

• Jeśli wiem, że dane oświadczenie zawsze zwraca tę samą liczbę wierszy i używa tego samego planu (i, wiedziałbym to z testów), wtedy utworzę procedurę składowaną normalnie i pozwolę, aby plan został zbuforowany.
• Jeśli wiem, że dane stwierdzenie dziko zmienia się z wykonania na wykonanie i optymalny plan się zmienia (ponownie, powinienem to wiedzieć z testowania wielu przykładowych wykonań), wtedy utworzę procedurę przechowywaną normalnie i użyję OPTION (RECOMPILE), aby upewnić się, że plan oświadczenia nie jest buforowany lub zapisywany w procedurze przechowywanej. Przy każdym wykonaniu procedura składowana otrzyma inne parametry, a szczególnie paskudna instrukcja otrzyma nowy plan przy każdym wykonaniu.

Jednakże jest to również miejsce, w którym rzeczy stają się bardziej wymagające. Często widziałem procedury składowane, w których ludzie próbują używać logiki warunkowej, aby rozbić różne typy parametrów, a to zwykle nie działa tak dobrze, jak oczekiwano (będę blogować to następny). I, to jest zawsze, gdzie niektórzy decydują, że chcą dynamicznie budować oświadczenie, które zostanie wykonane – teraz, musimy określić, czy powinniśmy użyć sp_executesql lub EXEC. I w tym momencie jest naprawdę kilka opcji. Ostatecznie, w jednym lub dwóch kolejnych postach – pokażę Ci w końcu gdzie EXEC jest wyraźnym zwycięzcą nad sp_executesql, ponieważ nawet OPCJA (RECOMPILE) nie zawsze pomaga WSZYSTKIM rodzajom planów (a zwłaszcza jednemu z bardziej powszechnych rodzajów planów jakie widzę).