Parameters sniffing – SQL Server

Kilka ostatnich wpisów poświęciłem na temat SQL Server. Ostatnio pisałem o SQL Statistics, które wpływa na wygenerowanie planu wykonania. Jak wiemy, plan jest zawsze buforowany, co wpływa zwykle pozytywnie na wydajność. Analiza zapytania i wygenerowanie planu są dość czasochłonne, dlatego zwykle plan jest zapisywany.

Problem w tym, że nie zawsze plan przechowywany w cache jest najbardziej optymalny. Zwykle pierwsze wykonanie danego zapytania powoduje zapis planu w pamięci. Co jeśli kolejne zapytania będą używały innych parametrów, a co za tym idzie, inny plan może być skuteczniejszy?

Problem pokażę, na przykładzie bazy AdventureWorks, którą można ściągnąć z stąd.
Do dyspozycji mamy tam tabelę Address.

Zapytanie:

SELECT* FROM [AdventureWorks2014].[Person].[Address]

Wynik:

1

Jeden z indeksów pokrywa kolumnę StateProvinceID. Wykonajmy poniższe zapytanie, aby dowiedzieć się trochę więcej o dystrybucji danych:

SELECT StateProvinceID,count(*)
  FROM [AdventureWorks2014].[Person].[Address] group by StateProvinceID order by count(*) desc

Wynik:

2

Widzimy, że bardzo dużo wierszy ma StateProvinceId równy 9. Z kolei 119 ma tylko jedno wystąpienie. Wykonajmy zatem  poniższe zapytania na tej kolumnie, aby zobaczyć wygenerowane plany wykonania:

 SELECT * FROM [AdventureWorks2014].[Person].[Address] where StateProvinceID=9
 GO
 SELECT * FROM [AdventureWorks2014].[Person].[Address] where StateProvinceID=119

Plany wykonania:

3

Widzimy, że pierwsze zapytanie nie korzysta z indeksu, ponieważ jest mało selektywne i LookUp kosztował by więcej niż skanowanie. Drugie zapytanie, które zwraca wyłącznie jeden wiersz jest dużo lepszym kandydatem na przeszukiwanie indeksu.
Plan nie został zbuforowany bo to fizycznie dwa różne zapytanie.

Stwórzmy teraz procedurę, która wykonuje analogiczne zapytanie:

CREATE PROCEDURE GetAddressByStateId
	@stateId int
AS
BEGIN
  
    SELECT * FROM [AdventureWorks2014].[Person].[Address] where StateProvinceID=@stateId
END
GO

Wykonajmy również procedurę przekazując dwa różne identyfikatory stanów:

  exec GetAddressByStateId 9
  GO
  exec GetAddressByStateId 119

Plany wykonania:

4

Co się stało? Pierwsze wykonanie zbuforowało plan wykonania. Drugie wywołanie zatem nie będzie próbowało znaleźć optymalnego planu, a skorzysta z tego zawartego w cache. Problem w tym, że dla skomplikowanych procedur nie zawsze pierwszy plan wykonania jest najlepszy.

Jednym z “obejść” jest rekompilacja procedury za każdym razem za pomocą WITH RECOMPILE:

ALTER PROCEDURE GetAddressByStateId
	@stateId int  WITH RECOMPILE
AS
BEGIN
  
    SELECT * FROM [AdventureWorks2014].[Person].[Address] where StateProvinceID=@stateId
END
GO

Innym rozwiązaniem może być skorzystanie z Query Hints. Za pomocą wskazówek można ułatwić SQL Server wygenerowanie najlepszego planu. W praktyce jednak, jeśli mamy problemy z parameter sniffing oznacza to, że procedura jest zbyt skomplikowana i część logiki powinna być wykonywana np. w osobnej funkcji.

2 thoughts on “Parameters sniffing – SQL Server”

  1. Jeszcze jednym sposobem jest przepisywanie parametrów wejściowych do lokalnych zmiennych na poziomie procedury i korzystanie z nich. W szczególności, jeżeli te parametry wchodzą bezpośrednio jako zmienne klauzuli WHERE w zapytaniach. Jest to mniej czasochłonna metoda niż recompile na poziomie całej procedury. A niestety, SQL server przy skomplikowanym ciele procedur, potrafi w makabryczny sposób zwielokrotniać czas zwrotu wyników. Ale artykuł bardzo dobry!

Leave a Reply

Your email address will not be published.