[PL] Metoda AddWithValue i cache planów wykonania
Kontynuując niejako temat cache'u planów wykonania zapytań w SQL Server 2005 (vide Parametry procedur składowanych a plan wykonania), tym razem zajmę się problemem wykorzystywania przez programistów .NET (a ściślej - programistów używających ADO.NET) metody AddWithValue kolekcji Parameters klasy SqlCommand (właściwie należałoby mówić o metodzie AddWithValue klasy SqlParameterCollection). Metoda ta pobiera dwa parametry: nazwę parametru występującego w zapytaniu/procedurze - typu string oraz wartość tegoż parametru - typu object. Ogólnie boxing (czyli zamknięcie zmiennej dowolnego typu w zmiennej typu object) nie jest zapewne niczym złym, ale... Za chwilę do tego "ale" powrócę.
Przenieśmy się teraz na drugą stronę aplikacji - do serwera baz danych. SQL Server 2005 ma taką cechę, że potrafi skompilowane plany wykonania przechowywać w cache'u planów wykonania (celowo nie piszę "cache procedur", bo nie tylko plany procedur w nim siedzą). Co więcej, potrafi te skompilowane plany z cache'u wykorzystywać ponownie (ang. plan reuse). I tak, do cache'u trafiają plany wykonania procedur, ale także plany wykonania składni T-SQL wykonywanych ad-hoc. Te ostatnie mogą być planami tzw. sparametryzowanymi lub nie. Jeśli plan nie jest sparametryzowany, to w cache'u pojawia się sporo planów wykonania różniących się jedynie wartościami argumentów wyszukiwania (czyli argumentów pojawiających się w klauzuli WHERE). Jeśli jednak optymalizatorowi uda się sprametryzować plan, to zobaczymy jeden plan dla jednego typu danych SQL Servera (przy czym char(10) nie jest tym samym co char(20)). Jeżeli wykonasz polecenie T-SQL ad-hoc z poziomu Management Studio, to okaże się, że SQL Server utworzy w cache'u dwa plany wykonania, jeden dla dokładnie takiego polecenia (z uwzględnieniem również wartości argumentów w klauzuli WHERE), jakie zostało wykonane, drugi - sparametryzowany (o ile jest co parametryzować), przy czym parametry o typach z definiowalną długością mają maksymalny rozmiar (na przykład dla nvarchar rozmiar będzie 4000).
Przykład. W Management Studio uruchom następujący fragment kodu:
DBCC FREEPROCCACHE -- czyszczenie cache'u
USE AdventureWorks
GO
SELECT *
FROM HumanResources.Department
WHERE [Name] = N'Engineering'
GO
-- podgląd zawartości cache'u dla bazy AdventureWorks
SELECT
sql,
DB_NAME(dbid) AS db,
objtype,
refcounts,
usecounts
FROM sys.syscacheobjects
WHERE dbid = DB_ID('AdventureWorks')
Wynik ostatniego polecenia pokazuje, że dla zapytania SELECT wybierającego dane z tabeli HumanResources.Department optymalizator stworzył dwa plany: jeden dla składni dokładnie takiej, jaką uruchomiono i drugi, sparametryzowany, którego pseudokod SQL (zwracany w kolumnie sql) wygląda tak:
(@1 nvarchar(4000))SELECT * FROM [HumanResources].[Department] WHERE [Name]=@1
Tak wygladają plany dla zapytań ad-hoc wykonywanych z poziomu Management Studio. A jak sprawa ma się dla aplikacji .NET?
Jeżeli przechwycimy za pomocą aplikacji Profiler składnie, jakie faktycznie wysyła aplikacja .NET w czasie wykonania na przykład metody ExecuteReader klasy SqlCommand, to okaże się, że będzie to coś na kształt:
exec sp_executesql N'SELECT * FROM HumanResources.Department WHERE [Name] = @param',
N'@param nvarchar(11)', @param = N'Engineering'
Powyższy kod jest przesyłany do serwera, gdy użyjesz wspomnianej metody AddWithValue, na przykład:
(...)
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT * FROM HumanResources.Department WHERE [Name] = @param";
cmd.Parameters.AddWithValue("@param", "Engineering");
(...)
Co jest charakterystycznego w przesyłanym na serwer kodzie, oprócz użycia procedury sp_executesql? Charakterystyczne jest to, że .NET zgaduje (może to za mocne określenie, ale chcę, by brzmiało negatywnie) długość parametru na podstawie danych, które otrzymuje jako wartość parametru w metodzie AddWithValue. Zobaczmy teraz, jaki plan (plany?) wykonania zostały zapisane w cache'u przy wykonaniu tego kodu.
(@param nvarchar(11))SELECT * FROM HumanResources.Department WHERE [Name] = @param
Tylko jeden plan i to na dodatek sparametryzowany. Świetnie, możnaby rzec. Nie do końca. Jeżeli teraz wyobrazimy sobie, że aplikacja przesyła na serwer bardzo dużo zapytań ad-hoc i każde z nich otrzymuje w kodzie C# wartości parametrów z użyciem metody AddWithValue, a wywołania różnią się wartościami parametrów (długością tych wartości! tu było to 11 znaków), to dojdziemy do wniosku, że nasz cache może być wkrótce pełen bardzo podobnych planów wykonania powstałych przy wykonywaniu z poziomu aplikacji .NET zapytań ad-hoc ze zmiennymi wartościami parametrów. Idąc dalej tym tropem: po pierwsze - zajmowane jest miejsce w cache'u (które mogłoby być spożytkowane na plany procedur lub poprawnie sparametryzowanych zapytań ad-hoc), a poza tym tak parametryzowane zapytania mają mniejsze szanse na ponowne użycie już skompilowanego planu.
Lekarstwo na tak działającą metodę jest proste. Zamiast metody AddWithValue należy stosować metodę Add kolekcji SqlParameterCollection lub polecenia ad-hoc należy zamienić na porcedury składowane. W przypadku stosowania metody Add podajemy jawnie maksymalną długość wartości parametru, więc tylko jeden plan dla zapytania ad-hoc wędruje do cache'u, zaś w przypadku użycia procedur składowanych nie ma znaczenia, której metody (Add/AddWithValue) użyjemy, bo informacje o rozmiarach parametrów są trzymane w definicjach procedur.
Po namyśle dochodzę do wniosku, że jest jeszcze jeden, nieco mniejszy, minus wynikający ze stosowania metody AddWithValue. Gdy nie określamy maksymalnej wielkości parametru, tracimy jeden poziom skromnej, ale zawsze, walidacji danych.
I na koniec coś związanego z tematem, a mianowicie przykład użycia DMV do wyciągania zawartości z cache'u:
SELECT
st.[text], cp.objtype,
cp.refcounts,
cp.usecounts,
cp.size_in_bytes,
qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
ORDER BY size_in_bytes DESC
Wykorzystanie DMV kilka zalet w porównaniu z widokiem katalogowym sys.syscacheobjects. Po pierwsze można dzięki nim uzyskać w prosty sposób całą składnię wsadu (dla porównania w sys.syscacheobjects kolumna sql zawiera pierwsze 3900 znaków wsadu). Po drugie można wybrać plany wykonania zapisane w formacie XML (które można zapisać jako pliki .sqlplan i otworzyć w postaci graficznej w Management Studio). Wydajnościowe próby pokazały, że szybciej wykonywane jest zapytanie do sys.syscacheobjects, co mnie nie dziwi, jako że DMV zwracają kilka kolumn LOB (typu XML i nvarchar(max)).
Podsumowując - metoda AddWithValue nie jest w moich oczach tym, co programiści .NET powinni wykorzystywać przy przekazywaniu sparametryzowanych poleceń ad-hoc do serwera baz danych. Biorąc pod uwagę fakt, że z każdym Service Packiem do SQL Servera maksymalny rozmiar cache'u planów wykonania się zmniejsza, należy dbać o to, by nie zaśmiecać cache'u niepotrzebnymi planami. Na koniec dodam tylko, że natknąłem się już kilka razy na sytuacje, w których brak miejsca w cache'u powodował, że, by wrzucić do cache'u nowe plany, serwer musiał usuwać stare, niekoniecznie mało istotne i szybko się rekompilujące.