Bardzo często gdy przeprowadzamy proces optymalizacji zapytań przeglądamy wygenerowane plany zapytań. Na tej podstawie można dokonać pewnych optymalizacji, ale czy zawsze ?
Często mamy doczynienia z procedurami składowanymi czy też sparametryzowanymi zapytaniami ad-hoc.
Przeglądając zapytania dochodzimy do wniosku, że tak do końca nie jesteśmy wstanie stwierdzić jak powinien być wygenerowany plan zapytań, rodzi się pytanie czy taki plan jest dobry.
Skąd te wątpliwości ?
Często w predykatach czy w wywoływanych procedurach mamy użyte zmienne (parametry). Jeśli w klauzuli WHERE używamy np. predykatu
Select * FROM dbo.Foo
WHERE ColumnX = @parameter1
to nie znamy wartości tego parametru. W związku z tym, nie wiemy jaka jest liczebność takiej tabeli po odfiltrowaniu, czy indeks jest wykorzystany (jeśli oczywiście istnieje), czy prawidłowo jest w planie użycie LookUpa czy może scan ?
W tym momencie można sprawdzić czy w planie zapytań nie ma czasem zapisanych wartości parametrów, dla których to plan został skompilowany. Jeśli zapiszemy np. do tabeli nasze plany to następującym zapytaniem możemy wyszukać istniejące wartości parametrów.
SELECT query_plan
,TRY_CONVERT(XML,TRY_CONVERT(nvarchar(max), query_plan)
).query('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan" ; //p:QueryPlan/p:ParameterList/p:ColumnReference')
AS query_plan_parameters
FROM [monitor].[sp_whoisactive12_log]
where query_plan is not null
W rezultacie otrzymujemy również XML. I tak dla zapytania
DELETE [jobmonitor].[JobsHistoryHistogram] WHERE [ShortSN]=@1
otrzymamy następujący wynik
<p:ColumnReference
xmlns:p="http://schemas.microsft.com/sqlserver/2004/07/showplan"
Column="@1"
ParameterDataType="tinyint"
ParameterCompiledValue="(2)"
/>
