Number of Rows Read czyli kilka słów o residual predicate pushdown

Wraz z Service Pack 3 dla SQL Server 2012 oraz Service Pack 2 dla SQL Server 2014 i wyżej otrzymaliśmy pewne udogodnienie w odczytywaniu mechanizmu zwanego Residual Predicate Pushdown, który jest niczym innym jak ukrytym filtrowaniem podczas wykonywania zapytania. Jak to działa? Sprawdźmy to.

Na samym początku uruchomimy w bazie AdventureWorksDW2014 następujące zapytanie:

SELECT 
	FirstName,
	LastName,
	BirthDate,
	MaritalStatus 
FROM dbo.DimCustomer
WHERE
	LastName Like '%a%'

Powyższe zapytanie nikomu nie powinno sprawić większych problemów – na ten moment możemy na jego temat powiedzieć kilka rzeczy, a mianowicie:

pobiera cztery atrybuty z tabeli DimCustomer
filtruje dane po jednym z pobieranych atrybutów
stworzony filtr nie jest przyjazny dla tego aby optymalizator wybrał operację Index Seek

Spójrzmy zatem na plan wykonania:

Jak można zobaczyć plan wykonania jest bardzo prosty – jednak jest w nim coś dziwnego, a mianowicie w jaki sposób odzwierciedlony został nasz filtr? Czy nie powinniśmy zobaczyć na powyższym planie wykonania operatora Filter? Aby lepiej przyjrzeć się zaistniałemu zjawisku zajrzyjmy we właściwości operatora Clustered Index Scan.

W otrzymanym zbiorze właściwości interesują nas tak naprawdę dwie z nich tzn: Actual Number of Rows oraz Number of Rows Read. Pierwsza z podanych właściwości mówi o tym ile wierszy zostało przekazanych do kolejnego operatora planu wykonania, drugi z kolei mówi jaka ich liczba została właściwie odczytana z dysku! Jak widać różnica jest dosyć znaczna tj. z 18485 wierszy odczytanych z tabeli tylko 9751 zostało przekazanych dalej – oznacza to, iż nastąpiła filtracja. To dosyć ciekawe spostrzeżenie gdyż znamy logiczny kierunek przetwarzania i wiemy, że filtracja w klauzuli WHERE następuje po sekcji FROM – z fizycznego punktu widzenia optymalizator przeniósł filtrację na jak najwcześniejszy etap tj. na operator Index Scan. Sam warunek filtracji możemy zauważyć w dolnej części okna właściwości w sekcji Predicate (nie mylić z Seek Predicate, który oznacza warunek wyszukiwania operatora Index Seek). Czy możliwe jest występowanie zarówno Predicate jak i Seek Predicate? Oczywiście, że tak! Zobrazujmy to na przykładzie – na początku stwórzmy jednokolumnowy indeks niezgrupowany:

CREATE NONCLUSTERED INDEX IX_LastName ON [dbo].[DimCustomer](LastName)

Następnie wykonajmy zapytanie, które będzie miało dwa warunki: jeden pozwoli na użycie indeksu, drugi nie.

SELECT 
	CustomerKey,
	LastName
FROM DimCustomer
WHERE
	LastName Like 'G%' AND
	LastName LIKE '%z'

Spójrzmy na plan zapytania:

Jak można zauważyć z dysku odczytano 1295 wierszy (Number Of Rows Read), a 366 wierszy zostało zwróconych dalej po filtracji. Możemy również dostrzec, że wyszukiwanie nazwiska zaczynającego się na literę “G” zostało przedstawione jako Seek Predicate, natomiast wyszukiwanie nazwiska kończącego się na literę “z” jest widoczne jako tzw. Residual Predicate w sekcji Predicate.

We wcześniejszych wersjach SQL Server ciężko było ten mechanizm dostrzec – również teraz można to przeoczyć. Aby tego uniknąć polecam używać SQL Sentry Plan Explorer, który w nieco lepszy pokazuje określony plan dając nam ostrzeżenie:

Mechanizm ten jest techniką optymalizacyjną jednakże powinniśmy mięć świadomość jego występowania gdyż w wielu przypadkach będziemy odczytywać znaczne ilości stron danych, a następnie będziemy je odfiltrowywać. Nie można go postrzegać jako coś złego – wręcz przeciwnie – lecz czasem jest on wskaźnikiem źle napisanego warunku bądź też filtracji po mało selektywnym atrybucie. Warto również dodać, że sama właściwość Number of rows read będzie wyświetlana jedynie wtedy gdy występuje mechanizm Predicate pushdown – w przeciwnym wypadku nie będzie wyświetlana gdyż zawsze byłaby równa aktualnej liczbie wierszy.