Remus pomocnie zauważył, że maksymalna długość VARCHARkolumny wpływa na szacowany rozmiar wiersza, a zatem zapewnia pamięć zapewnianą przez SQL Server.
Próbowałem przeprowadzić nieco więcej badań, aby rozwinąć część jego odpowiedzi „z tego na kaskadę rzeczy”. Nie mam pełnego lub zwięzłego wyjaśnienia, ale oto, co znalazłem.
Skrypt repro
Utworzyłem pełny skrypt, który generuje fałszywy zestaw danych, na którym tworzenie indeksu zajmuje około 10 razy więcej czasu na mojej maszynie dla VARCHAR(256)wersji. Dane te wykorzystywane są dokładnie takie same, ale pierwsza tabela wykorzystuje rzeczywiste max długości 18, 75, 9, 15, 123, i 5, podczas gdy wszystkie kolumny użyć max długość 256w drugiej tabeli.
Kluczowanie do oryginalnego stołu
Tutaj widzimy, że oryginalne zapytanie kończy się w około 20 sekund, a logiczne odczyty są równe wielkości tabeli ~1.5GB(195 tys. Stron, 8 tys. Na stronę).
-- CPU time = 37674 ms, elapsed time = 19206 ms.
-- Table 'testVarchar'. Scan count 9, logical reads 194490, physical reads 0
CREATE CLUSTERED INDEX IX_testVarchar
ON dbo.testVarchar (s1, s2, s3, s4)
WITH (MAXDOP = 8) -- Same as my global MAXDOP, but just being explicit
GO
Kluczowanie tabeli VARCHAR (256)
W przypadku VARCHAR(256)tabeli widzimy, że upływ czasu dramatycznie się wydłużył.
Co ciekawe, ani czas procesora, ani logiczne odczyty nie rosną. Ma to sens, biorąc pod uwagę, że tabela zawiera dokładnie te same dane, ale nie wyjaśnia, dlaczego upływ czasu jest o wiele wolniejszy.
-- CPU time = 33212 ms, elapsed time = 263134 ms.
-- Table 'testVarchar256'. Scan count 9, logical reads 194491
CREATE CLUSTERED INDEX IX_testVarchar256
ON dbo.testVarchar256 (s1, s2, s3, s4)
WITH (MAXDOP = 8) -- Same as my global MAXDOP, but just being explicit
GO
Statystyki I / O i czekania: oryginalne
Jeśli przechwycimy nieco więcej szczegółów (używając p_perfMon, procedura, którą napisałem ), możemy zobaczyć, że znaczna większość operacji we / wy jest wykonywana na LOGpliku. Widzimy stosunkowo niewielką liczbę operacji we / wy w rzeczywistym ROWS(głównym pliku danych), a podstawowym typem oczekiwania jest LATCH_EX, co wskazuje na niezgodność stron w pamięci.
Widzimy też, że mój wirujący dysk jest gdzieś pomiędzy „złym” a „szokująco złym”, jak twierdzi Paul Randal :)
Statystyki operacji we / wy i czekania: VARCHAR (256)
W przypadku VARCHAR(256)wersji I / O i statystyki oczekiwania wyglądają zupełnie inaczej! Widzimy tutaj ogromny wzrost liczby operacji we / wy w pliku danych ( ROWS), a czasy przeciągnięcia powodują, że Paul Randal po prostu mówi „WOW!”.
Nic dziwnego, że teraz jest typ oczekiwania nr 1 IO_COMPLETION. Ale dlaczego generowane jest tak dużo operacji we / wy?
Aktualny plan zapytań: VARCHAR (256)
Z planu zapytań widzimy, że Sortoperator ma wyciek rekurencyjny (głębokość 5 poziomów!) W VARCHAR(256)wersji zapytania. (W oryginalnej wersji nie ma wycieków).
Postęp zapytania na żywo: VARCHAR (256)
Możemy użyć sys.dm_exec_query_profiles do przeglądania postępu zapytań na żywo w SQL 2014+ . W oryginalnej wersji całość Table Scani Sortsą przetwarzane bez żadnych wycieków ( spill_page_countpozostaje 0przez cały czas).
W VARCHAR(256)wersji możemy jednak zobaczyć, że strony szybko się gromadzą dla Sortoperatora. Oto migawka postępu zapytania tuż przed zakończeniem zapytania. Dane tutaj są agregowane we wszystkich wątkach.
Jeśli zagłębię się w każdy wątek osobno, widzę, że 2 wątki kończą sortowanie w ciągu około 5 sekund (ogólnie 20 sekund, po 15 sekundach spędzonych na skanowaniu tabeli). Gdyby wszystkie wątki postępowały w tym tempie, VARCHAR(256)tworzenie indeksu zostałoby ukończone mniej więcej w tym samym czasie co oryginalna tabela.
Jednak pozostałe 6 wątków postępuje znacznie wolniej. Może to wynikać ze sposobu przydzielania pamięci i sposobu wstrzymywania wątków przez operacje we / wy podczas przesyłania danych. Nie wiem jednak na pewno.
Co możesz zrobić?
Istnieje kilka rzeczy, które możesz rozważyć wypróbowanie:
- Współpracuj z dostawcą, aby przywrócić poprzednią wersję. Jeśli nie jest to możliwe, pozwól dostawcy, że nie jesteś zadowolony z tej zmiany, aby mógł rozważyć wycofanie jej w przyszłej wersji.
- Podczas dodawania indeksu rozważ użycie
OPTION (MAXDOP X)where, gdzie liczba Xjest niższa niż bieżące ustawienie na poziomie serwera. Gdy korzystałem OPTION (MAXDOP 2)z tego konkretnego zestawu danych na moim komputerze, VARCHAR(256)wersja została ukończona w 25 seconds(w porównaniu do 3-4 minut z 8 wątkami!). Możliwe jest, że zachowanie związane z rozlewaniem zostanie zaostrzone przez większą równoległość.
- Jeśli możliwa jest dodatkowa inwestycja w sprzęt, profiluj we / wy (prawdopodobne wąskie gardło) w systemie i rozważ użycie dysku SSD, aby zmniejszyć opóźnienie we / wy spowodowane przez wycieki.
Dalsza lektura
Paul White ma fajny post na blogu na temat wewnętrznych rodzajów SQL Server, które mogą być interesujące. Mówi trochę o rozlewaniu, pochylaniu wątków i alokacji pamięci dla sortowania równoległego.