Jakie czynniki kosztowe wpływają na optymalizator przy wyborze różnych typów szpul?

Szpula

W SQL Server istnieje kilka rodzajów buforów. Dwa, które mnie interesują, to bufory tabel i indeks , poza zapytaniami dotyczącymi modyfikacji .

Zapytania tylko do odczytu, szczególnie po wewnętrznej stronie sprzężenia zagnieżdżonych pętli, mogą używać buforu tabeli lub indeksu, aby potencjalnie zmniejszyć liczbę operacji we / wy i poprawić wydajność zapytań. Te szpule mogą być chętne lub leniwe . Tak jak ty i ja.

Moje pytania to:

• Jakie czynniki decydują o wyborze Tabeli vs. Szpuli indeksu
• Które czynniki wpływają na wybór między chętnymi a leniwymi szpulami

Jest to trochę szerokie, ale myślę, że rozumiem prawdziwe pytanie i odpowiednio odpowiem. Ale zamierzam porozmawiać o buforze tabel vs indeks. Nie sądzę, że jest całkiem słuszne postrzeganie tam jako wyboru między buforami tabel i indeksów. Jak wiadomo, w jednym poddrzewie można uzyskać szpulę indeksową, szpulę stołową lub zarówno szpulę indeksową, jak i szpulę stołową. Uważam, że ogólnie słusznie jest powiedzieć, że otrzymujesz szpulę indeksu pod następującymi warunkami:

1. Optymalizator zapytań ma powód, aby przekształcić złączenie w aplikację
2. Optymalizator zapytań faktycznie wykonuje transformację do zastosowania
3. Optymalizator zapytań używa reguły, aby dodać bufor buforowania (przynajmniej bufor buforowy musi być bezpieczny w użyciu)
4. Plan ze szpulą indeksu jest wybrany

Możesz zobaczyć większość z nich za pomocą prostych demonstracji. Zacznij od utworzenia pary stosów:

DROP TABLE IF EXISTS dbo.X_10000_VARCHAR_901;
CREATE TABLE dbo.X_10000_VARCHAR_901 (ID VARCHAR(901) NOT NULL);

INSERT INTO dbo.X_10000_VARCHAR_901 WITH (TABLOCK)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;


DROP TABLE IF EXISTS dbo.X_10000_VARCHAR_800;
CREATE TABLE dbo.X_10000_VARCHAR_800 (ID VARCHAR(800) NOT NULL);

INSERT INTO dbo.X_10000_VARCHAR_800 WITH (TABLOCK)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

W przypadku pierwszego zapytania nie ma czego szukać:

SELECT *
FROM dbo.X_10000_VARCHAR_901 a
CROSS JOIN dbo.X_10000_VARCHAR_901 b
OPTION (MAXDOP 1);

Dlatego nie ma powodu, aby optymalizator przekształcił łączenie w aplikację. Skończysz na szpuli ze względu na koszty. To zapytanie kończy się niepowodzeniem w pierwszym teście.

W przypadku kolejnego zapytania można oczekiwać, że optymalizator ma powód, aby rozważyć zastosowanie:

SELECT *
FROM dbo.X_10000_VARCHAR_901 a
INNER JOIN dbo.X_10000_VARCHAR_901 b ON a.ID = b.ID 
OPTION (LOOP JOIN, MAXDOP 1);

Ale to nie ma być:

To zapytanie kończy się drugim testem. Pełne wyjaśnienie znajduje się tutaj . Cytując najistotniejszą część:

Optymalizator nie rozważa tworzenia indeksu w locie, aby umożliwić zastosowanie; raczej sekwencja zdarzeń jest zwykle odwrotna: przekształć, aby zastosować, ponieważ istnieje dobry indeks.

Mogę przepisać zapytanie, aby zachęcić optymalizatora do rozważenia zastosowania:

SELECT *
FROM dbo.X_10000_VARCHAR_901 a
INNER JOIN dbo.X_10000_VARCHAR_901 b ON a.ID >= b.ID AND a.ID <= b.ID
OPTION (MAXDOP 1);

Ale nadal nie ma szpuli indeksu:

To zapytanie kończy się niepowodzeniem trzeciego testu. W SQL Server 2014 obowiązywał limit długości klucza indeksu wynoszący 900 bajtów. Zostało to rozszerzone w SQL Server 2016, ale tylko dla indeksów nieklastrowanych. Indeks szpuli jest indeksem klastrowym, więc limit pozostaje na poziomie 900 bajtów . W każdym razie nie można zastosować reguły buforowania indeksu, ponieważ może to prowadzić do błędu podczas wykonywania zapytania.

Zmniejszenie długości typu danych do 800 w końcu zapewnia plan z buforem indeksu:

Plan szpuli indeksu, co nie jest zaskakujące, jest znacznie tańszy niż plan bez szpuli: 89,7603 jednostek w porównaniu z 588,832 sztuk. Różnicę widać w nieudokumentowanej QUERYRULEOFF BuildSpoolpodpowiedzi do zapytania:

To nie jest pełna odpowiedź, ale mam nadzieję, że to część tego, czego szukałeś.