Posts tagged plany wykonania

Dawno, dawno temu dowiedziałem się z bloga Tibora Karasziego (SQL Server MVP), że optymalizator może wykorzystać obiekty constraint, takie jak CHECK czy FOREIGN KEY (klucz obcy) do optymalizacji określonych klas zapytań.

Jakiś czas temu próbowałem pokazać koledze z firmy, jak to działa i przy okazji dowiedziałem się ciekawej rzeczy, na którą dotąd nie natrafiłem. A w zasadzie, można powiedzieć, normalnie aż zgłupiałem :-)

Na początek weźmy tabelę:

USE tempdb;
GO
IF OBJECT_ID('dbo.Employee') IS NOT NULL
  DROP TABLE dbo.Employee;
CREATE TABLE dbo.Employee (
  EmployeeId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
  FirstName varchar(50) NOT NULL,
  LastName varchar(50) NOT NULL,
  Income money NOT NULL DEFAULT (0)
);
GO
ALTER TABLE dbo.Employee ADD CONSTRAINT CK_Employee_Income CHECK (Income >= 0);
GO
INSERT INTO dbo.Employee (FirstName, LastName, Income)
SELECT NEWID(), NEWID(), RAND(CHECKSUM(NEWID())) * v1.number + v2.number
FROM master.dbo.spt_values AS v1, master.dbo.spt_values AS v2
WHERE v1.type = 'P' AND v2.type = 'P'
AND v1.number BETWEEN 1 AND 1000
AND v2.number BETWEEN 1 AND 1000;
GO

Tabela dość typowa. I zawiera milion rekordów. To, co ważne, to fakt, że założyłem w niej warunek ograniczający możliwe wartości kolumny Income (muszą być nieujemne). Świeżo założony w powyższy sposób warunek CHECK jest “trusted” (patrz wpis na blogu Tibora). Weźmy teraz zapytanie:

SELECT *
FROM dbo.Employee
WHERE Income < 0;
GO

Plany wykonania na mojej maszynie i przy konfiguracji serwera z MAXDOP = 0 wygląda u mnie tak:

Hę? Spodziewałem się, że zobaczę operator Constant Scan i… tyle! A tymczasem jakiś skan, z jakimś parallelismem… Horror!

Pobawiłem się trochę tym zapytaniem. Pierwsza myśl – wyeliminować parallelism:

SELECT *
FROM dbo.Employee
WHERE Income < 0
OPTION (MAXDOP 1);
GO

Plan wykonania:

Noooo, to lubimy :-) Żadnego zbędnego zaglądania do tabeli, po prostu od razu na podstawie zaufanego warunku CHECK generowana jest odpowiedź, że nie ma rekordów spełniających warunek z zapytania.

A teraz deser – przyznaję, że sam tego nie wymyśliłem (zbyt fikuśne :-)), choć testowałem różne śmieszne warianty zapytań (m.in. dodawałem grupowania po wszystkich kolumnach tabeli – pomagało i wymuszało Constant Scan). Maciek Pilecki, z którym ten problem przedyskutowałem i który, podobnie jak ja, ma wrażenie, że coś tu nie gra, wypróbował taki kwiatek:

SELECT *
FROM dbo.Employee
WHERE Income < 0
OPTION (MAXDOP 0);
GO

Efekt? Sami sprawdźcie, ale gwarantuję pełne zaskoczenie :-) Oczywiście, Constant Scan! I o so chosi?

Wygląda na to, że optymalizator pozostawiony samemu sobie i stojący przed “trudnym” wyborem (nie wpisując hinta powodujemy, że optymalizator ma do dyspozycji wszystkie możliwe ścieżki optymalizacji zapytania) robi głupotę. A może to nowy bug? Ale plan dla zapytania z MAXDOP = 0 powoduje już moją konsternację. Trzeba będzie z kimś z grupy produktowej o tym porozmawiać, bo bardzo nie lubię tłumaczyć się przed kolegami, którym demonstruję “niby dobrze znane” triki optymalizacyjne, a tymczasem optymalizator krzyżuje moje plany i wychodzę na głupca :-)

Wszelkie przemyślenia własne na temat powyższego mile widziane. Moje przemyślenie – nigdy nie bądź niczego pewnym na 100% bez sprawdzenia organoleptycznie :-)

Dodam, że przełączenie MADXOP = 1 na poziomie serwera nie zmienia sytuacji – dalej mamy skan całej tabeli, jeśli nie dodamy hinta lub nie skomplikujemy zapytania! O co tutaj chodzi??? :-)

I na sam koniec – kupa śmiechu. Jaki jest bodaj najprostszy możliwy hint wymuszający Constant Scan w omawianym przypadku? Opadają mi ręce, gdy pomyślę o mojej SQL-owej niewiedzy…

SELECT *
FROM dbo.Employee
WHERE Income < 0 AND 1=1;
GO

[EDIT] Jak słusznie zauważył Patrik Třeštik komentując ten wpis, problem jest najpewniej spowodowany przez parametryzację zapytania (i zarazem zjawisko parameter sniffing). Jakimś cudem zero w warunku WHERE przestaje być stałą i optymalizator zaczyna traktować je jak wartość nieznaną, a więc jednocześnie nie może porównać jej do wartości zaszytej w ograniczeniu CHECK. Tylko powstaje pytanie – po co ta parametryzacja? :-) [/EDIT]