Loading…

READY TO DEAL WITH YOUR DATA?

Get some tips on how to do that!
Start exploring

SQL Server Cardinality Estimator – zaproszenie na sesję na SQLDay 2014

Jedną z ogłoszonych nowości w SQL Server 2014 jest nowy mechanizm estymowania liczby wierszy w planach wykonania. Zmiana mechanizmu odpowiadającego za wspomniane estymaty jest o tyle istotna, że estymowana liczba wierszy generowanych przez zapytanie ma bezpośredni wpływ na wybór przez optymalizator ostatecznego planu wykonania zapytania.

O nowym mechanizmie napisano już sporo. Ja najwięcej uwagi poświęciłem ciekawej serii publikacji Milosa Radivojevica. I po lekturze zacząłem proste testy, których celem było potwierdzenie wyczytanych mądrości, a przy okazji, być może znaleźć jakieś ciekawostki.

Do testów użyłem bazy AdventureWorks2012 i SQL Server 2014 CTP2. Pełnych wyników testów oraz wszystkich informacji o estymatorze nie będę publikował przed konferencją SQLDay 2014, gdzie wspólnie z Maćkiem Pileckim poprowadzę prezentację na ten temat.

Na teraz proponuję małą ciekawostkę.

Przygotowanie do testu

Przygotujmy prostą bazę danych do testu.

USE master;
GO
 
IF DB_ID('CETest') IS NOT NULL BEGIN
  ALTER DATABASE CETest
  SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  DROP DATABASE CETest;
END;
GO
 
CREATE DATABASE CETest;
GO
 
USE CETest;
GO
 
SELECT * INTO dbo.Product 
FROM AdventureWorks2012.Production.Product;
GO
 
ALTER TABLE dbo.Product
ADD CONSTRAINT PK_Product
PRIMARY KEY (ProductID);
GO
 
CREATE INDEX IX_Product_Name
ON dbo.Product (Name);
GO

Do stworzenia tabeli dbo.Product można użyć którejkolwiek z baz OLTP AdventureWorks (ja użyłem AdventureWorks2012). Mamy zatem tabelę z dwoma indeksami. Celowo indeks na kolumnie Name nie jest unikalny, żeby nie zaciemniać obrazu w dalszej części testu.

Ot i ciekawostka

Weźmy dwa proste zapytania:

SELECT ProductID, Name
FROM dbo.Product
WHERE Name LIKE 'A%';
GO
 
SELECT ProductID, Name
FROM dbo.Product
WHERE Name >= N'A' AND Name < N'B';
GO

Dlaczego akurat te dwa zapytania? Bo jak zajrzymy w plan wykonania pierwszego z nich, po najechaniu kursorem myszy na operator Index Seek zobaczymy taki obrazek:

image

Gdy spojrzymy na Seek Predicates, to okaże się, że takie zapytanie z LIKE w sensie predykatów jest tłumaczone na zapytanie z wykorzystaniem klasycznych operatorów “większe-równe” i “mniejsze”. Wynik obu podanych zapytań jest taki sam. Zobaczmy plany wykonania obu zapytań:

image

Na pierwszy rzut oka plany są takie same. Ale po dokładnym przyjrzeniu się można znaleźć drobne różnice. Wystarczy najechać kursorem myszy na strzałki łączące operatory:

image image

W obu planach różne są: estymowana liczba wierszy i estymowana wielkość wiersza (a co za tym idzie, także estymowany rozmiar danych wynikowych).

Powstaje pytanie: dlaczego?

W SQL Server 2014 można podejrzeć więcej szczegółów dotyczących tego, w jaki sposób działa estymator liczby wierszy.

Żeby się przekonać, jakie algorytmy wykorzystuje estymator w obu przypadkach, wystarczy stworzyć i uruchomić sesję Extended Events przechwytującą zdarzenia typu query_optimizer_estimate_cardinality:

IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'CESession')
  DROP EVENT SESSION CESession ON SERVER;
GO
 
CREATE EVENT SESSION CESession ON SERVER 
ADD EVENT sqlserver.query_optimizer_estimate_cardinality (
  ACTION (sqlserver.sql_text)
)
ADD TARGET package0.event_file (
  SET filename=N'C:\Temp\CESession.xel', metadatafile = 'C:\Temp\CESession.xem'
);
GO
 
ALTER EVENT SESSION CESession ON SERVER STATE = START;
GO

Uruchamiamy powyższy kod, uruchamiamy zapytania, dla których chcemy zobaczyć działanie estymatora i zatrzymujemy sesję. Wynik możemy oglądać w GUI Management Studio albo podejrzeć zapytaniem, np.:

WITH CTE AS (
  SELECT
    ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS n, 
    CONVERT(xml, event_data) AS xml_data
  FROM sys.fn_xe_file_target_read_file(
    'C:\Temp\CESession*.xel', 'C:\Temp\CESession.xem', NULL, NULL
  )
)
SELECT c.n, x.c.value('(@name)[1]', 'nvarchar(4000)') AS name, y.z.query('.')
FROM CTE AS c
CROSS APPLY c.xml_data.nodes('/event/data') AS x(c)
CROSS APPLY x.c.nodes('value/node()') AS y(z);
GO

Interesuje nas podejrzenie pola calculator dla operatora logicznego LogOp_Select. W przypadku zapytania z operatorem LIKE zdarzenie wygląda mniej więcej tak:

image

Do estymowania liczby wierszy został użyty algorytm (“kalkulator”) CSelCalcTrieBased (definicja Trie na Wikipedii).

Z kolei w przypadku zapytania z operatorami porównania zastosowanie znalazł algorytm CSelCalcColumnInterval (mam wrażenie, że tu został wykorzystany wprost histogram ze statystyk pod indeks IX_Product_Name).

image

W obu przypadkach różne algorytmy dały w rezultacie różną selektywność, co tłumaczy różną estymatę liczby wierszy w obu zapytaniach.

Podsumowanie

Zadaniem tego wpisu jest jedynie pobudzenie ciekawości czytelnika i – mam nadzieję – zachęcenie do uczestnictwa w SQLDay 2014, a w szczególności w sesji pt. SQL Server Cardinality Estimation. Jeśli lubisz ciężkie zabawy z liczbami i analizy “śrubek” we wnętrzu SQL Servera, zapraszam! :-)

Leave a Reply