Zakładając, że „koszt” jest pod względem czasu (choć nie jestem pewien, co jeszcze może być pod względem ;-), to przynajmniej powinieneś być w stanie go zrozumieć, wykonując coś takiego:
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
SET STATISTICS TIME ON;
EXEC sp_help 'sys.databases'; -- replace with your proc
SET STATISTICS TIME OFF;
Pierwszy element zgłaszany na karcie „Wiadomości” powinien być:
Czas analizy i kompilacji programu SQL Server:
Uruchomiłbym to co najmniej 10 razy i uśredniłem zarówno „CPU”, jak i „Elapsed” milisekundy.
Najlepiej byłoby uruchomić to w dziale Production, aby uzyskać prawdziwy szacunek czasu, ale rzadko ludzie mogą wyczyścić pamięć podręczną planu w Production. Na szczęście, począwszy od SQL Server 2008 stało się możliwe usunięcie konkretnego planu z pamięci podręcznej. W takim przypadku możesz wykonać następujące czynności:
DECLARE @SQL NVARCHAR(MAX) = '';
;WITH cte AS
(
SELECT DISTINCT stat.plan_handle
FROM sys.dm_exec_query_stats stat
CROSS APPLY sys.dm_exec_text_query_plan(stat.plan_handle, 0, -1) qplan
WHERE qplan.query_plan LIKE N'%sp[_]help%' -- replace "sp[_]help" with proc name
)
SELECT @SQL += N'DBCC FREEPROCCACHE ('
+ CONVERT(NVARCHAR(130), cte.plan_handle, 1)
+ N');'
+ NCHAR(13) + NCHAR(10)
FROM cte;
PRINT @SQL;
EXEC (@SQL);
SET STATISTICS TIME ON;
EXEC sp_help 'sys.databases' -- replace with your proc
SET STATISTICS TIME OFF;
Jednak w zależności od zmienności wartości przekazywanych dla parametru (ów) powodującego „zły” buforowany plan, istnieje inna metoda, aby uznać, że jest to środek pomiędzy : OPTION(RECOMPILE)a OPTION(OPTIMIZE FOR UNKNOWN)dynamicznym SQL. Tak, powiedziałem to. Mam na myśli nawet nie sparametryzowany dynamiczny SQL. Oto dlaczego.
Wyraźnie masz dane, które mają nierównomierny rozkład, przynajmniej pod względem jednej lub więcej wartości parametrów wejściowych. Wady wymienionych opcji to:
OPTION(RECOMPILE)wygeneruje plan dla każdego wykonania i nigdy nie będą mogli korzystać z dowolnego planu ponownego użycia, nawet jeśli wartości parametr przekazany ponownie są identyczne z wcześniejszym okresie (S). W przypadku proc, które są często wywoływane - raz na kilka sekund lub częściej - uratuje cię to od czasu do czasu okropnej sytuacji, ale wciąż pozostawia cię w niezbyt dobrej sytuacji.
OPTION(OPTIMIZE FOR (@Param = value)) wygeneruje plan na podstawie tej konkretnej wartości, co może pomóc w kilku przypadkach, ale nadal pozostawia Cię otwartym na bieżący problem.
OPTION(OPTIMIZE FOR UNKNOWN)wygeneruje plan na podstawie średniej dystrybucji, co pomoże niektórym zapytaniom, ale zaszkodzi innym. Powinno to być takie samo, jak opcja używania zmiennych lokalnych.
Jednak dynamiczny SQL, jeśli zostanie wykonany poprawnie , pozwoli, aby różne wartości były przekazywane, aby mieć własne oddzielne plany zapytań, które są idealne (cóż, o ile będą). Głównym kosztem jest to, że wraz ze wzrostem różnorodności przekazywanych wartości rośnie liczba planów wykonania w pamięci podręcznej i zajmują one pamięć. Niewielkie koszty to:
sprawdzanie poprawności parametrów ciągu, aby zapobiec wstrzyknięciom SQL
być może konieczne będzie skonfigurowanie Certyfikatu i Użytkownika opartego na Certyfikacie, aby zachować idealną abstrakcję bezpieczeństwa, ponieważ Dynamiczny SQL wymaga bezpośrednich uprawnień do tabeli.
Oto, jak poradziłem sobie z tą sytuacją, gdy miałem procy, które były wywoływane więcej niż raz na sekundę i trafiały na wiele tabel, każda z milionami wierszy. Próbowałem, OPTION(RECOMPILE)ale okazało się, że jest to zbyt szkodliwe dla procesu w 99% przypadków, w których nie wystąpił problem z wąchaniem parametru / błędnym planem buforowanym. I pamiętaj, że jeden z tych procesów zawierał około 15 zapytań i tylko 3 - 5 z nich zostało przekonwertowanych na Dynamic SQL, jak opisano tutaj; Dynamiczny SQL nie był używany, chyba że był konieczny dla konkretnego zapytania.
Jeśli do procedury składowanej jest wiele parametrów wejściowych, dowiedz się, które z nich są używane z kolumnami, które mają bardzo zróżnicowane rozkłady danych (a zatem powodują ten problem), a które z nich są używane z kolumnami, które mają bardziej równomierne rozkłady (i nie powinny powodując ten problem).
Zbuduj ciąg dynamicznego SQL przy użyciu parametrów parametrów wejściowych proc, które są powiązane z równomiernie rozmieszczonymi kolumnami. Ta parametryzacja pomaga zmniejszyć wynikowy wzrost planów wykonania w pamięci podręcznej związanej z tym zapytaniem.
Dla pozostałych parametrów, które są powiązane z bardzo zróżnicowanymi dystrybucjami, należy je połączyć w Dynamiczny SQL jako wartości dosłowne. Ponieważ unikalne zapytanie jest określane przez wszelkie zmiany w tekście zapytania, posiadanie WHERE StatusID = 1jest innym zapytaniem, a zatem innym planem zapytań niż posiadanie WHERE StatusID = 2.
Jeśli którykolwiek z parametrów wejściowych proc, które mają być konkatenowane w tekście zapytania, to ciągi, należy je zweryfikować, aby zabezpieczyć przed iniekcją SQL (choć jest to mniej prawdopodobne, jeśli przekazywane ciągi są generowane przez aplikacja, a nie użytkownik, ale nadal). Przynajmniej REPLACE(@Param, '''', '''''')upewnij się, że pojedyncze cudzysłowy stają się znakami pojedynczymi.
W razie potrzeby utwórz certyfikat, który będzie używany do utworzenia użytkownika i podpisz procedurę składowaną, aby bezpośrednie uprawnienia do tabeli były przyznawane tylko nowemu użytkownikowi opartemu na certyfikacie, a nie użytkownikom [public]lub użytkownikom, którzy w przeciwnym razie nie powinni mieć takich uprawnień .
Przykładowy proces:
CREATE PROCEDURE MySchema.MyProc
(
@Param1 INT,
@Param2 DATETIME,
@Param3 NVARCHAR(50)
)
AS
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'
SELECT tab.Field1, tab.Field2, ...
FROM MySchema.SomeTable tab
WHERE tab.Field3 = @P1
AND tab.Field8 >= CONVERT(DATETIME, ''' +
CONVERT(NVARCHAR(50), @Param2, 121) +
N''')
AND tab.Field2 LIKE N''' +
REPLACE(@Param3, N'''', N'''''') +
N'%'';';
EXEC sp_executesql
@SQL,
N'@P1 INT',
@P1 = @Param1;
