Program do prowadzenia magazynu przy wzroście liczby oddziałów i transakcji

Program magazynowy uruchomiony w jednym magazynie z kilkuset indeksami pracuje zwykle poprawnie, bo baza mieści się w pamięci, a użytkowników jest niewielu. Problemy zaczynają się, gdy rośnie liczba oddziałów albo liczba równoczesnych operacji. Objawy są podobne: terminal czeka na odpowiedź, raport trwa minuty, a przesunięcie między magazynami wymaga ręcznej korekty.

Ten tekst opisuje, co się dzieje po stronie modelu danych i silnika SQL Server, gdy program do prowadzenia magazynu rośnie. Wybór między instalacją lokalną a chmurą omówiono w tekście o skalowalności programu magazynowego w chmurze, a tu opisano strukturę bazy oraz mechanizm blokad.

Program do prowadzenia magazynu a dwie osie wzrostu

Wzrost ma dwa niezależne wymiary. Pierwszy to szerokość, czyli liczba oddziałów i związanych z nimi słowników. Drugi to głębokość, czyli liczba transakcji na sekundę i rozmiar tabel z historią ruchów. Każdy wymiar łamie inną część systemu, więc wymaga innego środka zaradczego.

Objawy przeciążenia widoczne na hali

Pracownik nie widzi planu zapytania, widzi tylko czas oczekiwania na ekranie terminala. Pierwszym sygnałem bywa przekroczenie limitu czasu przy skanowaniu w godzinach szczytu. Drugim jest to, że ten sam skan przechodzi natychmiast rano i wolno po południu, czyli obciążenie zależy od liczby równoległych użytkowników.

Takie objawy rzadko wynikają z braku mocy serwera. Częściej powodują je blokady zapisów albo zapytania bez właściwego indeksu. Diagnozę zaczyna się od pomiaru, a nie od zakupu sprzętu.

Model wielooddziałowy w jednej bazie

Najprostszy sposób obsługi wielu magazynów to jedna baza z kolumną identyfikatora magazynu w tabelach stanów i ruchów. Identyfikator wchodzi do kluczy złożonych i jest pierwszą kolumną indeksów, więc zapytanie oddziału czyta tylko swój wycinek danych. Centrala, przeciwnie, zapytuje bez filtra i widzi całą sieć.

Alternatywą jest osobna baza dla oddziału, a nawet osobna instancja. Wariant ten zwiększa izolację, ale kosztuje w administracji: aktualizacja schematu musi przejść przez każdą bazę, a raport zbiorczy wymaga zapytań łączących bazy.

Reguła: identyfikator magazynu jest pierwszą kolumną kluczy złożonych i indeksów, a filtr po nim wymuszany jest w widokach lub w politykach bezpieczeństwa wierszy.

Parametry konfiguracji także mają dwa poziomy. Wartość globalna obowiązuje w całej sieci, a wartość oddziału ją nadpisuje. Program odczytuje najpierw wartość oddziału, a gdy jej nie ma, sięga po globalną, na przykład funkcją COALESCE. Dzięki temu nowy oddział zaczyna pracę na ustawieniach sieci i zmienia tylko te, które naprawdę się różnią.

Wymuszanie filtra po stronie bazy chroni przed błędem w pojedynczym zapytaniu. SQL Server oferuje w tym celu zabezpieczenia na poziomie wierszy, dostępne od wersji 2016. Uprawnienia pracowników do oddziałów opisuje tekst o rolach i użytkownikach w systemie WMS.

Przesunięcia międzyoddziałowe

Towar jadący z oddziału A do B nie jest w żadnym z nich. Model danych obsługuje to stanem w drodze: dokument wydania w A zmniejsza stan A i tworzy pozycję w drodze, a dokument przyjęcia w B zamyka pozycję i zwiększa stan B. Oba dokumenty są osobnymi transakcjami, bo dzielą je godziny transportu.

Suma stanów obu oddziałów i towaru w drodze musi pozostać stała. Zapytanie kontrolne sprawdzające tę sumę wykrywa dokumenty przyjęte tylko z jednej strony. Zasady integracji między systemami opisuje tekst o integracji systemów magazynowych, a strukturę zarządzania wieloma oddziałami omawia artykuł o przedsiębiorstwie wielooddziałowym.

Numeracja dokumentów w oddziałach

Każdy oddział potrzebuje własnej ciągłej numeracji dokumentów, na przykład przyjęć i wydań. Prosty licznik w tabeli, zwiększany w każdej transakcji, staje się wąskim gardłem, bo wszystkie dokumenty oddziału czekają na ten sam wiersz. Obiekt SEQUENCE z buforem numerów przydziela wartości bez blokowania i wytrzymuje wysoką częstotliwość zapisu.

Bufor ma jedną wadę: po awarii lub restarcie serwera część numerów zostaje pominięta. Dokumenty objęte wymogiem ciągłej numeracji dostają numer dopiero przy zatwierdzeniu, w krótkiej transakcji na osobnym liczniku. Numery robocze, używane w magazynie do obsługi ruchów, mogą mieć luki bez wpływu na numerację obowiązkową.

Indeksy w tabelach ruchów i stanów

Tabela ruchów rośnie najszybciej, bo każdy skan dopisuje wiersz. Dobrą praktyką jest wąski klucz klastrowy o rosnącej wartości, na przykład liczba całkowita z autonumeracją. Wstawianie odbywa się wtedy na końcu indeksu i nie powoduje podziałów stron. Zapytania biznesowe obsługują indeksy nieklastrowe, w których kolejność kolumn wynika z warunków zapytania.

CREATE TABLE dbo.RuchMagazynowy (
    RuchId    bigint        IDENTITY(1,1) NOT NULL,
    MagazynId smallint      NOT NULL,
    KodTowaru varchar(30)   NOT NULL,
    LokacjaId int           NOT NULL,
    TypRuchu  char(2)       NOT NULL,
    Ilosc     decimal(18,3) NOT NULL,
    DataRuchu datetime2(3)  NOT NULL DEFAULT SYSDATETIME(),
    CONSTRAINT PK_RuchMagazynowy PRIMARY KEY CLUSTERED (RuchId)
);

CREATE NONCLUSTERED INDEX IX_Ruch_Magazyn_Towar_Data
ON dbo.RuchMagazynowy (MagazynId, KodTowaru, DataRuchu DESC)
INCLUDE (TypRuchu, Ilosc, LokacjaId);

-- zapytanie obsługiwane przez indeks bez sortowania i bez odczytu tabeli
SELECT TOP (50) DataRuchu, TypRuchu, Ilosc, LokacjaId
FROM dbo.RuchMagazynowy
WHERE MagazynId = @MagazynId AND KodTowaru = @KodTowaru
ORDER BY DataRuchu DESC;

Indeks pokrywający zawiera wszystkie kolumny potrzebne zapytaniu, więc silnik nie sięga do tabeli bazowej. Kolejność kluczy ma znaczenie: kolumny z warunkiem równości stoją przed kolumną sortowania. Zasady projektowania opisuje przewodnik po projektowaniu indeksów.

Każdy indeks przyspiesza odczyt i spowalnia zapis, bo silnik aktualizuje go przy każdym wstawieniu. W tabeli ruchów pięć indeksów oznacza pięć zapisów na skan. Zbędne indeksy wykrywa się po statystykach użycia, a fragmentację usuwa poleceniem ALTER INDEX w oknie o małym ruchu.

Edycje SQL Server a limity bazy

Edycja silnika wyznacza sufit skalowania pionowego. Poniższe wartości pochodzą z dokumentacji SQL Server 2022 i dotyczą pojedynczej instancji.

Historia ruchów po kilku latach pracy często przekracza 10 GB, zanim wydajność stanie się problemem. Pełne zestawienie limitów zawiera dokumentacja edycji SQL Server 2022.

Blokady i poziomy izolacji przy wielu użytkownikach

Przy poziomie READ COMMITTED bez wersjonowania odczyt zakłada blokadę współdzieloną, więc czeka na wiersze zablokowane przez zapis. W magazynie oznacza to, że kierownik przeglądający stany zatrzymuje kompletację, jeśli jego zapytanie dotyka tych samych wierszy. Rozwiązaniem jest wersjonowanie wierszy.

Wersjonowanie przenosi koszt do bazy tempdb, gdzie silnik trzyma poprzednie wersje wierszy. Długo otwarta transakcja blokuje ich czyszczenie, więc magazyn wersji rośnie. Monitoruje się jego rozmiar oraz wiek najstarszej aktywnej transakcji, bo jedna zapomniana sesja potrafi zapełnić dysk.

ALTER DATABASE Magazyn SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

-- sesje oczekujące na inne sesje
SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time,
       t.text AS Polecenie
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0;

Opcja READ_COMMITTED_SNAPSHOT wymaga wyłącznego dostępu do bazy, więc włącza się ją w oknie serwisowym. Odczyty zaczynają wtedy korzystać z wersji wierszy, a blokady współdzielone znikają. Szczegóły mechanizmu opisuje przewodnik po blokowaniu i wersjonowaniu wierszy.

Reguła: transakcja odkładania lub wydania trwa milisekundy, a oczekiwanie na skan pracownika nigdy nie odbywa się wewnątrz otwartej transakcji.

Eskalacja blokad i zakleszczenia

Gdy pojedyncze polecenie przejmie około pięciu tysięcy blokad na jednej tabeli, silnik próbuje zamienić je na blokadę całej tabeli. Masowa aktualizacja stanów po inwentaryzacji łatwo osiąga ten próg i zatrzymuje wszystkie skanowania. Rozwiązaniem jest podział aktualizacji na partie po kilkaset wierszy, każda w osobnej transakcji.

Zakleszczenia powstają, gdy dwie transakcje sięgają po te same zasoby w odwrotnej kolejności. Reguła jest prosta: każda procedura blokuje zasoby w tym samym porządku, na przykład najpierw lokację, potem stan. Silnik przerywa jedną z transakcji, więc aplikacja terminala musi umieć ją ponowić bez pytania pracownika.

Terminale i praca równoległa w godzinach szczytu

Terminal mobilny wysyła każdy skan do serwera jako osobne żądanie. Przy kilkudziesięciu urządzeniach oznacza to kilkadziesiąt krótkich transakcji na sekundę, a nie kilka dużych. Aplikacja powinna wysyłać żądania asynchronicznie i utrzymywać pulę połączeń, żeby nie otwierać nowego połączenia przy każdym skanie. Sposób pracy aplikacji opisano w tekście o aplikacji magazynowej na Androida.

Sezonowy skok obciążenia mierzy się na tym samym poziomie co codzienny ruch. Mechanizm Query Store zapisuje plany i czasy zapytań, więc po piku widać, które zapytanie zwolniło i czy zmienił się jego plan. Wynik zestawia się z liczbą użytkowników, bo dodatkowe stanowiska sezonowe zwiększają obciążenie proporcjonalnie.

Integracje z systemami zewnętrznymi obciążają bazę inaczej niż terminale. Import zamówień z ERP przychodzi w partiach, a nie pojedynczo. Wykonuje się go w tle, przez kolejkę lub zadanie SQL Agent, w osobnych transakcjach, a nie w tej samej transakcji, która obsługuje skan pracownika. Zbyt duża partia zajęłaby tabele zamówień na tyle długo, że kompletacja stanęłaby na czas importu.

Nowi pracownicy z innych krajów korzystają z tej samej bazy, jeśli interfejs obsługuje wiele języków, co opisuje tekst o wielojęzycznym programie magazynowym. Język jest atrybutem użytkownika, a nie osobną instalacją.

Uruchomienie kolejnego oddziału

Uruchomienie oddziału polega głównie na danych, a nie na kodzie. Powstaje nowy identyfikator magazynu i kopia konfiguracji wzorcowej. Dochodzą lokacje oraz konta pracowników. Przebieg zawiera cztery kroki, które powtarza się przy każdym nowym magazynie.

1. Kopia konfiguracji - słowniki wspólne pozostają bez zmian, lokalne (strefy, typy lokacji) kopiuje się z magazynu wzorcowego.
2. Stan początkowy - zapasy wprowadza się z inwentaryzacji, a nie przez ręczne dokumenty przyjęcia.
3. Role i konta - pracownicy dostają role zdefiniowane dla wszystkich oddziałów, bez tworzenia uprawnień od podstaw.
4. Test pod obciążeniem - symulacja godzin szczytu na wydzielonym magazynie testowym przed przełączeniem produkcji.

Plan zapytania zależy od statystyk rozkładu wartości w kolumnach. Po masowym imporcie towarów nowego oddziału statystyki mogą opisywać dawny rozkład, więc optymalizator wybiera plan dla innych danych niż faktyczne. Aktualizacja statystyk po większym imporcie to tani zabieg, który usuwa część nagłych spadków wydajności bez zmiany indeksów.

Zasady wdrożeń opisuje tekst o wdrożeniu systemu WMS, a ustawienia opisuje konfiguracja programu Studio WMS.net. Liczenie zapasu przy starcie omawia tekst o inwentaryzacji w magazynie.

Kiedy architektura wymaga zmiany

Indeksy i krótkie transakcje wyczerpują możliwości jednego serwera dopiero po dłuższym czasie. Sygnałem granicznym jest sytuacja, w której czas odpowiedzi rośnie mimo poprawnych planów zapytań, a serwer stale pracuje blisko limitu procesora lub pamięci. Wtedy rozważa się kolejne kroki.

Pierwszym z nich jest przeniesienie raportów na replikę do odczytu, dostępną w wyższych edycjach. Drugim jest partycjonowanie tabeli ruchów po dacie i przenoszenie starych partycji do archiwum. Trzecim jest zmiana hostingu na chmurę, opisana w materiale o WMS w chmurze i w modelu lokalnym. Dopiero na końcu rozważa się rozdzielenie oddziałów na osobne bazy.

Analizę przeprowadza się na danych z pomiaru, a nie na przypuszczeniach. Zapisy z Query Store i statystyk oczekiwań pokazują, czy wąskim gardłem jest procesor, dysk czy blokady, a każde z nich ma inne rozwiązanie. Rozmieszczenie towaru w magazynie, opisane w tekście o programie do magazynowania towarów, wpływa natomiast na liczbę operacji na lokacjach, więc także na obciążenie zapisami.