Dlaczego zmienna tabeli wymusza skanowanie indeksu, podczas gdy tabela temp używa wyszukiwania i wyszukiwania zakładek?

Próbuję zrozumieć, dlaczego użycie zmiennej tabeli uniemożliwia optymalizatorowi korzystanie z wyszukiwania indeksu, a następnie wyszukiwania zakładek w porównaniu ze skanowaniem indeksu.

Wypełnianie tabeli:

CREATE TABLE dbo.Test 
(
    RowKey INT NOT NULL PRIMARY KEY, 
    SecondColumn CHAR(1) NOT NULL DEFAULT 'x',
    ForeignKey INT NOT NULL 
) 

INSERT dbo.Test 
(
    RowKey, 
    ForeignKey
) 
SELECT TOP 1000000 
    ROW_NUMBER() OVER (ORDER BY (SELECT 0)),
    ABS(CHECKSUM(NEWID()) % 10)     
FROM sys.all_objects s1
CROSS JOIN sys.all_objects s2 

CREATE INDEX ix_Test_1 ON dbo.Test (ForeignKey) 

Wypełnij zmienną tabeli jednym rekordem i spróbuj wyszukać klucz podstawowy i drugą kolumnę, wyszukując kolumnę klucza obcego:

DECLARE @Keys TABLE (RowKey INT NOT NULL) 

INSERT @Keys (RowKey) VALUES (10)

SELECT 
    t.RowKey,
    t.SecondColumn
FROM
    dbo.Test t 
INNER JOIN 
    @Keys k
ON
    t.ForeignKey = k.RowKey

Poniżej znajduje się plan wykonania:

Teraz to samo zapytanie przy użyciu tabeli tymczasowej:

CREATE TABLE #Keys (RowKey INT NOT NULL) 

INSERT #Keys (RowKey) VALUES (10) 

SELECT 
    t.RowKey,
    t.SecondColumn
FROM
    dbo.Test t 
INNER JOIN 
    #Keys k
ON
    t.ForeignKey = k.RowKey

W tym planie zapytań zastosowano wyszukiwanie i wyszukiwanie zakładek:

Dlaczego optymalizator chce wyszukiwać zakładki z tabelą tymczasową, ale nie ze zmienną tabelową?

Zmienna tabeli jest używana w tym przykładzie do reprezentowania danych przechodzących przez typ tabeli zdefiniowanej przez użytkownika w procedurze składowanej.

Zdaję sobie sprawę, że wyszukiwanie indeksu może nie być odpowiednie, jeśli wartość klucza obcego wystąpiłaby setki tysięcy razy. W takim przypadku skan byłby prawdopodobnie lepszym wyborem. W stworzonym przeze mnie scenariuszu nie było wiersza o wartości 10. Nadal uważam, że zachowanie jest interesujące i chciałbym wiedzieć, czy istnieje ku temu powód.

SQL Fiddle

Dodanie OPTION (RECOMPILE)nie zmieniło zachowania. UDDT ma klucz podstawowy.

@@VERSION to SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64) (kompilacja 7601: Service Pack 1) (Hypervisor)

Przyczyną takiego zachowania jest to, że SQL Server nie może ustalić, ile wierszy będzie pasować do ForeignKey, ponieważ nie ma indeksu z RowKey jako kolumną wiodącą (można to wywnioskować ze statystyk w tabeli #temp, ale te nie istnieją dla zmiennych tabeli / UDTT), więc szacuje 100 000 wierszy, co jest lepiej obsługiwane przez skanowanie niż wyszukiwanie + wyszukiwanie. Zanim SQL Server zda sobie sprawę, że jest tylko jeden wiersz, jest już za późno.

Być może będziesz w stanie skonstruować swój UDTT inaczej; w bardziej nowoczesnych wersjach SQL Server można tworzyć indeksy wtórne na zmiennych tabeli, ale ta składnia nie jest dostępna w 2008 R2.

BTW, możesz uzyskać zachowanie wyszukiwania (przynajmniej w moich ograniczonych próbach), jeśli spróbujesz uniknąć mapy bitowej / sondy, sugerując dołączenie zagnieżdżonych pętli:

DECLARE @Keys TABLE (RowKey INT PRIMARY KEY); -- can't hurt

INSERT @Keys (RowKey) VALUES (10);

SELECT 
     t.RowKey
    ,t.SecondColumn
FROM
    dbo.Test t 
INNER JOIN 
    @Keys k
ON
    t.ForeignKey = k.RowKey
    OPTION (LOOP JOIN);

Ja nauczyłem się tej sztuczki od Pawła Białej kilka lat temu. Oczywiście należy zachować ostrożność przy umieszczaniu wszelkiego rodzaju wskazówek dotyczących łączenia w kodzie produkcyjnym - może się to nie powieść, jeśli ludzie dokonają zmian w obiektach leżących u podstaw, a ten konkretny rodzaj łączenia nie jest już możliwy lub nie jest już najbardziej optymalny.

W przypadku bardziej złożonych zapytań i po przejściu na SQL Server 2012 lub nowszą wersję, flaga śledzenia 2453 może pomóc. Ta flaga jednak nie pomogła w tym prostym złączeniu. Obowiązują te same zastrzeżenia - jest to po prostu alternatywa, której na ogół nie należy robić bez mnóstwa dokumentacji i rygorystycznych procedur testowania regresji.

Ponadto dodatek Service Pack 1 już dawno nie jest obsługiwany, powinieneś uzyskać dodatek Service Pack 3 + MS15-058 .

Zmienne tabel i tabele temp są obsługiwane na różne sposoby na wiele sposobów. Tutaj jest świetna odpowiedź z mnóstwem szczegółów na temat tego, gdzie się różnią.

Szczególnie w twoim przypadku zgaduję, że winowajcą jest to, że tabele tymczasowe mogą generować dodatkowe statystyki i plany równoległe, podczas gdy zmienne tabel mają bardziej ograniczone statystyki (bez statystyk na poziomie kolumny) i żadnych planów równoległych.

Być może lepiej będzie, jeśli zrzucisz zmienną tabelową do tabeli tymczasowej na czas procedury składowanej.