Dlaczego dołączenie do eliminacji nie działa z sys.query_store

Poniżej przedstawiono uproszczenie problemu z wydajnością występującego w magazynie zapytań:

CREATE TABLE #tears
(
    plan_id bigint NOT NULL
);

INSERT #tears (plan_id) 
VALUES (1);

SELECT
    T.plan_id
FROM #tears AS T
LEFT JOIN sys.query_store_plan AS QSP
    ON QSP.plan_id = T.plan_id;

plan_idKolumna jest udokumentowana jako klucz podstawowy sys.query_store_plan, ale plan wykonania nie używa dołączyć eliminacji jak można by się spodziewać:

Z DMV nie są rzutowane żadne atrybuty.
Klucz podstawowy DMV plan_idnie może powielać wierszy z tabeli tymczasowej
LEFT JOINUżywa się A , więc nie Tmożna wyeliminować żadnych wierszy .

Plan wykonania

Dlaczego tak jest i co można zrobić, aby uzyskać eliminację przyłączenia tutaj?

— Paul White 9
źródło

Dokumentacja jest nieco myląca. DMV jest widokiem niematerialnym i nie ma klucza podstawowego jako takiego. Podstawowe definicje są trochę skomplikowane, ale uproszczona definicja sys.query_store_planto:

CREATE VIEW sys.query_store_plan AS
SELECT
    PPM.plan_id
    -- various other attributes
FROM sys.plan_persist_plan_merged AS PPM
LEFT JOIN sys.syspalvalues AS P
    ON P.class = 'PFT' 
    AND P.[value] = plan_forcing_type;

Co więcej, sys.plan_persist_plan_mergedjest to również widok, chociaż trzeba się połączyć przez Dedicated Administrator Connection, aby zobaczyć jego definicję. Ponownie uproszczone:

CREATE VIEW sys.plan_persist_plan_merged AS   
SELECT     
    P.plan_id as plan_id,    
    -- various other attributes
FROM sys.plan_persist_plan P 
    -- NOTE - in order to prevent potential deadlock
    -- between QDS_STATEMENT_STABILITY LOCK and index locks   
    WITH (NOLOCK) 
LEFT JOIN sys.plan_persist_plan_in_memory PM
    ON P.plan_id = PM.plan_id;

Indeksy na sys.plan_persist_planto:

╔════════════════════════╦════════════════════════ ══════════════╦═════════════╗
Name nazwa_indeksu ║ opis_indeksu ║ klucze_indeksu ║
╠════════════════════════╬════════════════════════ ══════════════╬═════════════╣
║ plan_persist_plan_cidx ║ klastrowany, unikalny zlokalizowany na PODSTAWOWYM ║ plan_id ║
║ plan_persist_plan_idx1 ║ nieklastrowany znajduje się na PODSTAWOWYM ║ identyfikator_danych (-) ║
╚════════════════════════╩════════════════════════ ══════════════╩═════════════╝

Więc plan_idmusi być wyjątkowy sys.plan_persist_plan.

Teraz sys.plan_persist_plan_in_memoryjest funkcją cenioną w tabeli przesyłania strumieniowego, prezentującą tabelaryczny widok danych przechowywanych tylko w strukturach pamięci wewnętrznej. Jako taki nie ma żadnych unikalnych ograniczeń.

Zasadniczo wykonywane zapytanie jest zatem równoważne z:

DECLARE @t1 table (plan_id integer NOT NULL);
DECLARE @t2 table (plan_id integer NOT NULL UNIQUE CLUSTERED);
DECLARE @t3 table (plan_id integer NULL);

SELECT 
    T1.plan_id
FROM @t1 AS T1 
LEFT JOIN
(
    SELECT 
        T2.plan_id
    FROM @t2 AS T2
    LEFT JOIN @t3 AS T3 
        ON T3.plan_id = T2.plan_id
) AS Q1
    ON Q1.plan_id = T1.plan_id;

... co nie powoduje eliminacji złączeń:

Wracając do sedna problemu, problemem jest wewnętrzne zapytanie:

DECLARE @t2 table (plan_id integer NOT NULL UNIQUE CLUSTERED);
DECLARE @t3 table (plan_id integer NULL);

SELECT 
    T2.plan_id
FROM @t2 AS T2
LEFT JOIN @t3 AS T3 
    ON T3.plan_id = T2.plan_id;

... najwyraźniej lewe sprzężenie może skutkować @t2powieleniem wierszy , ponieważ @t3nie ma ograniczenia jednoznaczności plan_id. Dlatego złączenia nie można wyeliminować:

Aby obejść ten problem, możemy wyraźnie powiedzieć optymalizatorowi, że nie wymagamy żadnych zduplikowanych plan_idwartości:

DECLARE @t2 table (plan_id integer NOT NULL UNIQUE CLUSTERED);
DECLARE @t3 table (plan_id integer NULL);

SELECT DISTINCT
    T2.plan_id
FROM @t2 AS T2
LEFT JOIN @t3 AS T3 
    ON T3.plan_id = T2.plan_id;

Zewnętrzne sprzężenie z @t3można teraz wyeliminować:

Zastosowanie tego do prawdziwego zapytania:

SELECT DISTINCT
    T.plan_id
FROM #tears AS T
LEFT JOIN sys.query_store_plan AS QSP
    ON QSP.plan_id = T.plan_id;

Podobnie możemy dodać GROUP BY T.plan_idzamiast DISTINCT. W każdym razie optymalizator może teraz poprawnie uzasadnić plan_idatrybut na podstawie zagnieżdżonych widoków i wyeliminować oba połączenia zewnętrzne według potrzeb:

Zauważ, że plan_idunikanie w tabeli tymczasowej nie wystarczyłoby do wyeliminowania łączenia, ponieważ nie wykluczałoby to niepoprawnych wyników. Musimy wyraźnie odrzucić zduplikowane plan_idwartości z końcowego wyniku, aby umożliwić optymalizatorowi działanie tutaj.

— Paul White 9
źródło

Dlaczego dołączenie do eliminacji nie działa z sys.query_store_plan?