Wolna wydajność Wstawianie kilku rzędów do ogromnego stołu

Mamy proces, który pobiera dane ze sklepów i aktualizuje tabelę zapasów w całej firmie. Ta tabela zawiera wiersze dla każdego sklepu według daty i pozycji. U klientów z wieloma sklepami stół ten może stać się bardzo duży - rzędu 500 milionów rzędów.

Ten proces aktualizacji zapasów zwykle uruchamia się wiele razy dziennie, gdy sklepy wprowadzają dane. Te uruchomienia aktualizują dane tylko z kilku sklepów. Klienci mogą jednak uruchomić tę funkcję, aby zaktualizować np. Wszystkie sklepy w ciągu ostatnich 30 dni. W takim przypadku proces rozwija 10 wątków i aktualizuje zapasy każdego sklepu w osobnym wątku.

Klient narzeka, że ​​proces ten zajmuje dużo czasu. Profilowałem proces i stwierdziłem, że jedno zapytanie, które INSERT wprowadza do tej tabeli, zajmuje znacznie więcej czasu, niż się spodziewałem. INSERT czasami kończy się w 30 sekund.

Kiedy uruchamiam ad-hoc polecenie SQL INSERT dla tej tabeli (ograniczone przez BEGIN TRAN i ROLLBACK), ad-hoc SQL kończy się w kolejności milisekund.

Wolno działające zapytanie znajduje się poniżej. Chodzi o to, aby WSTAWIĆ rekordy, których nie ma, a następnie zaktualizować je, gdy obliczamy różne bity danych. Wcześniejszym krokiem w tym procesie było zidentyfikowanie elementów, które należy zaktualizować, wykonanie niektórych obliczeń i umieszczenie wyników w tabeli tempdb Update_Item_Work. Ten proces działa w 10 osobnych wątkach, a każdy wątek ma swój własny identyfikator GUID w aktualizacji_Item_Work.

INSERT INTO Inventory
(
    Inv_Site_Key,
    Inv_Item_Key,
    Inv_Date,
    Inv_BusEnt_ID,
    Inv_End_WtAvg_Cost
)
SELECT DISTINCT
    UpdItemWrk_Site_Key,
    UpdItemWrk_Item_Key,
    UpdItemWrk_Date,
    UpdItemWrk_BusEnt_ID,
    (CASE UpdItemWrk_Set_WtAvg_Cost WHEN 1 THEN UpdItemWrk_WtAvg_Cost ELSE 0 END)
FROM tempdb..Update_Item_Work (NOLOCK)
WHERE UpdItemWrk_GUID = @GUID
AND NOT EXISTS
    -- Only insert for site/item/date combinations that don't exist
    (SELECT *
    FROM Inventory (NOLOCK)
    WHERE Inv_Site_Key = UpdItemWrk_Site_Key
    AND Inv_Item_Key = UpdItemWrk_Item_Key
    AND Inv_Date = UpdItemWrk_Date)

Tabela zapasów ma 42 kolumny, z których większość śledzi ilości i liczy się z różnymi dostosowaniami zapasów. sys.dm_db_index_physical_stats mówi, że każdy wiersz ma około 242 bajtów, więc spodziewam się, że około 33 wierszy zmieści się na jednej stronie o wielkości 8 KB.

Tabela jest skupiona na unikalnym ograniczeniu (Inv_Site_Key, Inv_Item_Key, Inv_Date). Wszystkie klucze są DECIMAL (15,0), a data to SMALLDATETIME. Istnieje klucz podstawowy TOŻSAMOŚĆ (nieklastrowany) i 4 inne indeksy. Wszystkie indeksy i klastrowane ograniczenie są zdefiniowane jawnie (FILLFACTOR = 90, PAD_INDEX = ON).

Zajrzałem do pliku dziennika, żeby policzyć podziały stron. Zmierzyłem około 1027 podziałów na indeksie klastrowym i 1724 podziałów na innym indeksie, ale nie rejestrowałem, w jakim okresie wystąpiły. Półtorej godziny później zmierzyłem 7035 podziałów stron na indeks klastrowany.

Plan zapytań przechwycony w narzędziu do profilowania wygląda następująco:

Rows         Executes     StmtText                                                                                                                                             
----         --------     --------                                                                                                                                             
490          1            Sequence                                                                                                                                             
0            1              |--Index Update
0            1              |    |--Collapse
0            1              |         |--Sort
0            1              |              |--Filter
996          1              |                   |--Table Spool                                                                                                                 
996          1              |                        |--Split                                                                                                                  
498          1              |                             |--Assert
0            0              |                                  |--Compute Scalar
498          1              |                                       |--Clustered Index Update(UK_Inventory)
498          1              |                                            |--Compute Scalar
0            0              |                                                 |--Compute Scalar
0            0              |                                                      |--Compute Scalar
498          1              |                                                           |--Compute Scalar
498          1              |                                                                |--Top
498          1              |                                                                     |--Nested Loops
498          1              |                                                                          |--Stream Aggregate
0            0              |                                                                          |    |--Compute Scalar
498          1              |                                                                          |         |--Clustered Index Seek(tempdb..Update_Item_Work)
498          498            |                                                                          |--Clustered Index Seek(Inventory)
0            1              |--Index Update(UX_Inv_Exceptions_Date_Site_Item)
0            1              |    |--Collapse
0            1              |         |--Sort
0            1              |              |--Filter
996          1              |                   |--Table Spool
490          1              |--Index Update(UX_Inv_Date_Site_Item)
490          1                   |--Collapse
980          1                        |--Sort
980          1                             |--Filter
996          1                                  |--Table Spool                                                                                       

Patrząc na zapytania w porównaniu z różnymi dmv, widzę, że zapytanie czeka na PAGEIOLATCH_EX na czas 0 na stronie w tej tabeli ekwipunku. Nie widzę żadnych oczekiwań ani blokowania zamków.

To urządzenie ma około 32 GB pamięci. Działa pod kontrolą SQL Server 2005 Standard Edition, ale wkrótce zostaną uaktualnione do wersji 2008 R2 Enterprise Edition. Nie mam liczb na temat tego, jak duża jest tabela zapasów pod względem wykorzystania dysku, ale mogę to uzyskać, jeśli to konieczne. Jest to jedna z największych tabel w tym systemie.

Uruchomiłem zapytanie przeciwko sys.dm_io_virtual_file_stats i zobaczyłem, że średni czas oczekiwania na zapis względem tempdb wynosi powyżej 1,1 sekundy . Baza danych, w której przechowywana jest ta tabela, ma średni czas oczekiwania na zapis wynoszący ~ 350 ms. Ale ponownie uruchamiają serwer co około 6 miesięcy, więc nie mam pojęcia, czy te informacje są istotne. tempdb jest rozłożony na 4 różne pliki Mają 3 różne pliki dla bazy danych, która zawiera tabelę Inventory.

Dlaczego to zapytanie zajmuje tak dużo czasu, aby WSTAWIĆ kilka wierszy po uruchomieniu z wieloma różnymi wątkami, gdy pojedynczy WSTAW jest bardzo szybki?

-- AKTUALIZACJA --

Oto liczby opóźnień na dysk, w tym odczytane bajty. Jak widać, wydajność tempdb jest wątpliwa. Tabela Inventory znajduje się w PDICompany_252_01.mdf, PDICompany_252_01_Second.ndf lub PDICompany_252_01_Third.ndf.

ReadLatencyWriteLatencyLatencyAvgBPerRead AvgBPerWriteAvgBPerTransferDriveDB                     physical_name
         42        1112    623       62171       67654          65147R:   tempdb                 R:\Microsoft SQL Server\Tempdb\tempdev1.mdf
         38        1101    615       62122       67626          65109S:   tempdb                 S:\Microsoft SQL Server\Tempdb\tempdev2.ndf
         38        1101    615       62136       67639          65123T:   tempdb                 T:\Microsoft SQL Server\Tempdb\tempdev3.ndf
         38        1101    615       62140       67629          65119U:   tempdb                 U:\Microsoft SQL Server\Tempdb\tempdev4.ndf
         25         341     71       92767       53288          87009X:   PDICompany             X:\Program Files\PDI\Enterprise\Databases\PDICompany_Third.ndf
         26         339     71       90902       52507          85345X:   PDICompany             X:\Program Files\PDI\Enterprise\Databases\PDICompany_Second.ndf
         10         231     90       98544       60191          84618W:   PDICompany_FRx         W:\Program Files\PDI\Enterprise\Databases\PDICompany_FRx.mdf
         61         137     68        9120        9181           9125W:   model                  W:\Microsoft SQL Server\MSSQL.3\MSSQL\Data\modeldev.mdf
         36         113     97        9376        5663           6419V:   model                  V:\Microsoft SQL Server\Logs\modellog.ldf
         22          99     34       92233       52112          86304W:   PDICompany             W:\Program Files\PDI\Enterprise\Databases\PDICompany.mdf
          9          20     10       25188        9120          23538W:   master                 W:\Microsoft SQL Server\MSSQL.3\MSSQL\Data\master.mdf
         20          18     19       53419       10759          40850W:   msdb                   W:\Microsoft SQL Server\MSSQL.3\MSSQL\Data\MSDBData.mdf
         23          18     19      947956       58304         110123V:   PDICompany_FRx         V:\Program Files\PDI\Enterprise\Databases\PDICompany_FRx_1.ldf
         20          17     17      828123       55295         104730V:   PDICompany             V:\Program Files\PDI\Enterprise\Databases\PDICompany.ldf
          5          13     13       12308        4868           5129V:   master                 V:\Microsoft SQL Server\Logs\mastlog.ldf
         11          13     13       22233        7598           8513V:   PDIMaster              V:\Program Files\PDI\Enterprise\Databases\PDIMaster.ldf
         14          11     13       13846        9540          12598W:   PDIMaster              W:\Program Files\PDI\Enterprise\Databases\PDIMaster.mdf
         13          11     11       22350        1107           1110V:   msdb                   V:\Microsoft SQL Server\Logs\MSDBLog.ldf
         17           9      9      745437       11821          23249V:   PDIFoundation          V:\Program Files\PDI\Enterprise\Databases\PDIFoundation.ldf
         34           8     31       29490       33725          30031W:   PDIFoundation          W:\Program Files\PDI\Enterprise\Databases\PDIFoundation.mdf
          5           8      8       61560       61236          61237V:   tempdb                 V:\Microsoft SQL Server\Logs\templog.ldf
         13           6     11        8370       35087          16785W:   SAHost_Company01       W:\Program Files\PDI\Enterprise\Databases\SAHostCompany.mdf
          2           6      5       56235       33667          38911W:   SAHost_Company01       W:\Program Files\PDI\Enterprise\Databases\SAHost_Company_01_log.LDF

Wygląda na to, że podział klastrowanej strony indeksu będzie bolesny, ponieważ indeks klastrowany przechowuje rzeczywiste dane, co wymaga przydzielenia nowych stron i przeniesienia danych do nich. Może to spowodować zablokowanie strony, a tym samym zablokowanie.

Pamiętaj również, że klastrowany klucz indeksu ma 21 bajtów i będzie musiał być przechowywany we wszystkich indeksach pomocniczych jako zakładka.

Czy zastanawiałeś się nad tym, aby kolumna tożsamości klucza głównego była indeksem klastrowym, nie tylko zmniejszy to rozmiar innych indeksów, ale także zmniejszy liczbę podziałów stron w indeksie klastrowym. Warto spróbować, jeśli możesz przebudować indeksy.

Dzięki podejściu wielowątkowemu uważam na wstawkę do tabeli, z której musisz najpierw sprawdzić wcześniejsze istnienie klucza. Taki rodzaj mówi mi, że istnieje problem współbieżności tego indeksu PK z tą tabelą, bez względu na liczbę wątków. Z tego samego powodu nie podoba mi się wskazówka NOLOCK w tabeli ekwipunku, ponieważ wydaje się, że wystąpiłby błąd, gdyby różne wątki mogły zapisać ten sam klucz (czy schemat partycjonowania usuwa tę możliwość?). Jestem ciekawy, jak duże było przyspieszenie przy początkowym wprowadzeniu wielu wątków, ponieważ w pewnym momencie musiało działać dobrze.

Coś, co można wypróbować, to uczynić z kwerendy bardziej czytać jak operację zbiorczą i przekonwertować „gdzie nie istnieje” na „anti-join”. (ostatecznie optymalizator może zignorować ten wysiłek). Jak wspomniano powyżej, usunę wskazówkę NOLOCK z tabeli docelowej, chyba że partycjonowanie nie zagwarantuje kolizji kluczy między wątkami.

 INSERT INTO i (...)
 SELECT DISTINCT ...             
   FROM tempdb..Update_Item_Work t (NOLOCK) -- nolock okay on read table
   left join Inventory i -- use without NOLOCK because PK is written inter-thread
     on i.Inv_Site_Key = t.UpdItemWrk_Site_Key
    and i.Inv_Item_Key = t.UpdItemWrk_Item_Key
    and i.Inv_Date = t.UpdItemWrk_Date
  where i.Inv_Site_Key is null   -- where not exist in inventory
    and UpdItemWrk_GUID = @GUID  -- for this thread

Czas, który jest uruchamiany jako baza, możesz ponownie uruchomić z podpowiedzią scalania („lewe łączenie” -> „lewe łączenie scalające”) jako inną możliwość. Prawdopodobnie powinieneś mieć indeks w tabeli tymczasowej (UpdItemWrk_Site_Key, UpdItemWrk_Item_Key, UpdItemWrk_Date) na wskazówkę dotyczącą scalania.

Nie wiem, czy nowsze, nieekspresyjne wersje SQL Server 2008/2012 byłyby w stanie automatycznie równolegle wykonywać większe scalenia tego formularza, umożliwiając usunięcie partycjonowania opartego na GUID.

Aby zachęcić do łączenia, aby występowało tylko na odrębnych elementach, a nie na wszystkich elementach, klauzule „wybierz odrębne ... z ...” można wcześniej przekształcić w „wybierz * z (wybierz odrębne ... z ...)” kontynuując dołączenie. Może to mieć zauważalną różnicę tylko wtedy, gdy program różnicujący filtruje wiele wierszy. Ponownie optymalizator może zignorować ten wysiłek.