Duża zmienność czasu wstawiania luzem

Mam więc prosty proces wstawiania luzem, aby pobrać dane z naszej tabeli pomostowej i przenieść je do naszego zestawu danych.

Proces jest prostym zadaniem w zakresie przepływu danych z domyślnymi ustawieniami „Wierszy na partię”, a opcje to „tablock” i „brak ograniczenia sprawdzania”.

Stół jest dość duży. 587,162,986 o rozmiarze danych 201 GB i 49 GB przestrzeni indeksu. Indeks klastrowy dla tabeli to.

CREATE CLUSTERED INDEX ImageData ON dbo.ImageData
(
    DOC_ID ASC,
    ACCT_NUM ASC,
    MasterID ASC
)

A klucz podstawowy to:

ALTER TABLE dbo.ImageData 
ADD CONSTRAINT ImageData 
PRIMARY KEY NONCLUSTERED 
(
    ImageID ASC,
    DT_CRTE_DOC ASC
)

Teraz mamy problem z tym, że BULK INSERTprzez SSIS działa niezwykle wolno. 1 godzina, aby wstawić milion wierszy. Zapytanie wypełniające tabelę jest już posortowane, a zapytanie do wypełnienia zajmuje mniej niż minutę.

Gdy proces jest uruchomiony, widzę zapytanie oczekujące na wstawkę BULK, która trwa od 5 do 20 sekund i pokazuje typ oczekiwania PAGEIOLATCH_EX. Proces jest w stanie INSERTnaraz tylko około tysiąca wierszy.

Wczoraj podczas testowania tego procesu w moim środowisku UAT napotkałem ten sam problem. Uruchomiłem ten proces kilka razy i próbowałem ustalić, jaka jest podstawowa przyczyna tego powolnego wstawiania. Nagle zaczął działać w niecałe 5 minut. Uruchomiłem to jeszcze kilka razy z tym samym rezultatem. Spadła również liczba wkładek luzem, które czekały przez 5 sekund lub dłużej, z setek do około 4.

To jest kłopotliwe, ponieważ nie mamy tak wielkiego spadku aktywności.

Procesor podczas trwania jest niski.

Czasy, w których jest wolniejszy, wydają się być mniejsze na dysku.

Opóźnienie dysku faktycznie wzrasta w czasie, w którym proces był uruchomiony w czasie krótszym niż 5 minut.

I IO był znacznie niższy w czasach, gdy proces ten przebiegał źle.

Sprawdziłem już i nie ma przyrostu plików, ponieważ pliki są wypełnione w 70%. Plik dziennika ma jeszcze 50% do przejścia. Baza danych znajduje się w trybie prostego odzyskiwania. Baza danych ma tylko jedną grupę plików, ale jest rozłożona na 4 pliki.

Zastanawiam się więc : dlaczego widziałem tak duże czasy oczekiwania na tych wkładkach luzem. B: Jaka magia się wydarzyła, dzięki czemu działała szybciej?

Dygresja. Działa dzisiaj jak bzdury.

AKTUALIZACJA jest obecnie podzielony na partycje. Jednak robi się to w sposób, który jest co najwyżej głupi.

CREATE PARTITION SCHEME [ps_Image] AS PARTITION [pf_Image] 
TO ([FG_Image], [FG_Image], [FG_Image], [FG_Image])

CREATE PARTITION FUNCTION [pf_Image](datetime) AS 
RANGE RIGHT FOR VALUES (
      N'2011-12-01T00:00:00.000'
    , N'2013-04-01T00:00:00.000'
    , N'2013-07-01T00:00:00.000'
);

Pozostawia to zasadniczo wszystkie dane z czwartej partycji. Ponieważ jednak wszystko idzie do tej samej grupy plików. Dane są obecnie podzielone dość równo między te pliki.

AKTUALIZACJA 2 Są to ogólne oczekiwania, gdy proces działa źle.

To jest czas oczekiwania, w którym mogłem uruchomić proces, działa dobrze.

Podsystem pamięci jest lokalnie podłączoną macierzą RAID, bez udziału sieci SAN. Dzienniki znajdują się na innym dysku. Kontroler RAID to PERC H800 z pamięcią podręczną 1 GB. (Dla UAT) Prod to PERC (810).

Używamy prostego odzyskiwania bez kopii zapasowych. Jest przywracany z kopii produkcyjnej co noc.

Ustawiliśmy także IsSorted property = TRUEw SSIS, ponieważ dane są już posortowane.

Nie mogę wskazać przyczyny, ale uważam, że domyślną liczbą wierszy na partię dla operacji BULK INSERT jest „all”. Ustawienie limitu w wierszach może ułatwić operację: dlatego jest to opcja. (Tutaj i kontynuuję, patrzę na dokumentację Transact-SQL „BULK INSERT”, więc może być daleko w przypadku SSIS.)

Spowoduje to podzielenie operacji na wiele partii X wierszy, z których każdy działa jako osobna transakcja. W przypadku błędu zakończone partie pozostaną zatwierdzone w tabeli docelowej, a zatrzymana partia zostanie wycofana. Jeśli jest to do zaakceptowania w tym, co robisz, tzn. Możesz uruchomić go później i nadrobić zaległości, spróbuj tego.

Nie jest źle mieć funkcję partycji, która umieszcza wszystkie bieżące wstawki w jednej partycji tabeli, ale nie widzę, jak przydatne jest partycjonowanie w ogóle z partycjami w tej samej grupie plików. Używanie datetime jest kiepskie i właściwie trochę zepsute dla datetime i „RRRR-MM-DD” bez wyraźnej formuły KONWERSJI od SQL Server 2008 (SQL może z radością traktować to jako RRRR-DD-MM: nie żartuje: nie panikuj, po prostu zmień go na „RRRRMMDD”, naprawiono: lub KONWERSJA (datetime, „RRRR-MM-DDT00: 00: 00”, 126), tak myślę. Ale myślę, że użycie proxy dla wartości daty (rok jako int lub rok + kwartał) do partycjonowania będzie działać lepiej.

Być może jest to projekt skopiowany z innego miejsca lub powielony w kilku datamartach. Jeśli to - jest - prawdziwy datamart, zrzut z hurtowni danych, aby dać kierownikom działów trochę danych do zabawy, to nie jest (przez ciebie) wysyłany gdzie indziej i prawdopodobnie tylko do odczytu, jeśli chodzi o użytkowników danych , więc wydaje mi się, że można usunąć funkcję partycji lub zmienić ją, aby jawnie umieścić wszystkie nowe dane na czwartej partycji bez względu na wszystko i nikogo to nie obchodzi. (Być może powinieneś sprawdzić, czy nikogo to nie obchodzi.)

Wygląda to na projekt, w którym planuje się porzucić zawartość partycji 1 w przyszłości i utworzyć kolejną nową partycję, aby uzyskać więcej nowych danych, ale nie brzmi to tak, jakby miało to miejsce tutaj. Przynajmniej tak się nie stało od 2013 roku.

Czasami widziałem tę samą sporadyczną ekstremalną powolność wstawiania do dużych tabel podzielonych na partycje. Czy próbowałeś zaktualizować statystyki tabel docelowych, a następnie uruchomić je ponownie? Skrajny czas oczekiwania może być spowodowany złymi statystykami, a jeśli aktualizacja statystyk zostanie uruchomiona w pewnym momencie podczas testowania, to wyjaśni to wzrost prędkości. Tylko myśl i łatwy test do zweryfikowania.