SQL i jego wydajność - problem z wydajnością przy większym obciążeniu SQL

0

Witajcie.
Piszę do Was w sprawie problemów jakie mam z silnikiem MSSQL w wersji 2016.
Korzystamy z ERPa CDN Optima XL. Od początku roku zaczął się nam problem z wydajnością SQLa, nie bardzo mam z czym to powiązać i dlatego nie mając pomysłu postanowiłem napisać do Was.
Otóż, maszyna na której stoi SQL jest zwirtualizowana, to jest hiper-v server core pracujący w klastrze FO. Całość jest obsługiwana przez nowiutką SASową macierz HP 12G all flash.
Maszyna wirtualna stoi na Windows 2016 server standard i ma przydzielone 128 GB RAM z czego wykorzystuje może 30 GB.
Problem wygląda tak, że kiedy równolegle wykonujemy „stresujące” dla bazy operacje, silnik bazy danych zdaje się blokować przetwarzanie kolejnych żądań do czasu zakończenia tych które są uruchamiane okresowo. Innymi słowy wygląda to tak, że podczas normalnej pracy w ERP wszystko chodzi przyzwoicie, kiedy jednak chcemy rozpocząć proces zamykania zleceń zaczyna się problem, w czasie zamykania poszczególnych zleceń nie można nic innego robić na bazie. Jeszcze w grudniu ubiegłego roku taki problem nie występował, nie bardzo rozumiem co się mogło stać.

Szerzej mówiąc wygląda to tak, że jeśli chodzi o Activity Monitor z SSMS podczas zamykania ZP i pracy w XL-u. Procesor Time normalnie nie wskakuje maks powyżej 30%.
mon2.png
Przy zamykaniu zleceń ciągle ma Piki do 100% obciążenia. tak samo wygląda na serwerze w monitorze wydajności.
Waiting Tasks wzrasta masakrycznie szybko….
Database I/O nie ma żadnego obciążenia.
Batch Requests/sec też rosną strasznie…
mon1.png
Próbowaliśmy już przenieść wirtualkę na fizyczne dyski SSD po za macierz, bez efektu. Czyli to nie macierz. Na najmocniejszym serwerze z trzech w klastrze zostawiliśmy tylko tą maszynę, też to niewiele dało. To tak wygląda jakby SQL się zapchał i prawie zatrzymał.
Zastanawiam się, czy w konfiguracji SQL NIE MA czegoś takiego co limituje ilość jednoczesnych operacji, faktem jest, że w czasie zamykania zleceń serwer ma więcej pracy, ale nie jest to na tyle dużo aby całkowicie zaprzestać możliwości pracy pozostałych pracowników w systemie.
Podpowiecie coś ? Z góry dziękuję

1

A patrzyłeś jakie zapytania w tym czasie idą?
Może jakieś duże update i tabele są blokowane na czas operacji.

0

Jaka wersja SQL (Express, Standard). Jaka wersja XLa?

2

Zaznaczam na wstępie, że nie znam się na MSSQLu (ostatnia styczność to MS SQL 2000 :-) ), ale na podstawie wykresów coś można powiedzieć.
Widać, że skoki CPU skorelowane są oczekującymi taskami. Skoro są waity, to musi być kolejka do jakiegoś zasobu. Niekoniecznie musi to być zasób fizyczny typu RAM/CPU/Dysk.
Może to być np. blok indeksu, blok tabeli, kolejka do puli wątków, lock etc.

Z dokumentacji widać, że jest możliwość sprawdzenia na czym taski czekają: sys.dm_os_waiting_tasks
https://docs.microsoft.com/en[...]sact-sql?view=sql-server-2016

Możesz użyć sys.dm_os_waiting_tasks jako punktu startowego i zobaczyć na czym są najdłuższe czasy oczekiwania, pogrupować po zasobach, typach waitów.
Zrozumieć skąd się biorą te waity i spróbować pozbyć się tych, które są najczęstsze, bądź tych, którego generują długą listę oczekujących.

1

Może też być tak, że jest jakiś bubel w zapytaniu zrobionym w nowej wersji Optimy, którą macie i się wywala coś.

PS> nie ma czegoś takiego jak OPTIMA XL :D Jest albo OPTIMA albo COMRACH ERP XL. Na której wersji i którym dokładnie programie pracujesz? :)

2

Dużo poświeciłeś konfiguracji sprzętowej, jednak nie wiemy:

  1. Jaka edycja SQL Servera
  2. Jak duża jest baza

Skoro wczesniej problem się nie pojawiał to pytanie zacząłbym od tego jak wygląda jej utrzymanie:

  1. Backup - to może wpływać na rozrośnięcie się pliku loga
  2. Przebudowywanie indeksów
  3. Aktualizacje statystyk

Dopiero później możemy się bawić w dostrajanie samego silnika, np przez ustawianie MAXDOP

2

@Tomasz Hinz:

Problem wygląda tak, że kiedy równolegle wykonujemy „stresujące” dla bazy operacje, silnik bazy danych zdaje się blokować przetwarzanie kolejnych żądań do czasu zakończenia tych które są uruchamiane okresowo. Innymi słowy wygląda to tak, że podczas normalnej pracy w ERP wszystko chodzi przyzwoicie, kiedy jednak chcemy rozpocząć proces zamykania zleceń zaczyna się problem, w czasie zamykania poszczególnych zleceń nie można nic innego robić na bazie. Jeszcze w grudniu ubiegłego roku taki problem nie występował, nie bardzo rozumiem co się mogło stać.

Ktoś serwisuje tę bazę danych?
Nie chodzi o przeniesienie jej na inną maszynę, nowe dyski, itd.
Chodzi o np. higienę indeksów - ktoś coś robił?
Nie?
To może trzeba tam w końcu zajrzeć.

Ale najpierw pytanie; czy była jakaś aktualizacja Optimy od grudnia?
Mogła coś popsuć, ale to wróżenie z fusów..

Natomiast jeśli nie było żadnych zmian w bazie danych pod kątem to problem leży gdzie indziej.
A jeśli nawet zmiana była, to poniższe też jak najbardziej warto wykonać.

Szerzej mówiąc wygląda to tak, że jeśli chodzi o Activity Monitor z SSMS podczas zamykania ZP i pracy w XL-u. Procesor Time normalnie nie wskakuje maks powyżej 30%.
Przy zamykaniu zleceń ciągle ma Piki do 100% obciążenia. tak samo wygląda na serwerze w monitorze wydajności.
Waiting Tasks wzrasta masakrycznie szybko….

Database I/O nie ma żadnego obciążenia.

Przy takiej ilości RAM dla Optimy to nie problem, pod warunkiem że SQL Server co najmniej w wersji Standard.
Przepraszam, ale ilu masz userów Optimy?
100?

Batch Requests/sec też rosną strasznie…

To nie jest strasznie, to jest w miarę normalnie.

Próbowaliśmy już przenieść wirtualkę na fizyczne dyski SSD po za macierz, bez efektu. Czyli to nie macierz. Na najmocniejszym serwerze z trzech w klastrze zostawiliśmy tylko tą maszynę, też to niewiele dało. To tak wygląda jakby SQL się zapchał i prawie zatrzymał.

To nic nie da imo.

Zastanawiam się, czy w konfiguracji SQL NIE MA czegoś takiego co limituje ilość jednoczesnych operacji, faktem jest, że w czasie zamykania zleceń serwer ma więcej pracy, ale nie jest to na tyle dużo aby całkowicie zaprzestać możliwości pracy pozostałych pracowników w systemie.

Nie ma tak prosto.
Da się to zmienić, ale nie tu leży problem.
A poza tym, jak to zmienisz to Optima może się wyłożyć na plecki do góry kołami.
No, nie.

Podpowiecie coś ? Z góry dziękuję

Możemy bawić się w doktoryzowanie problemu, ale mi się nie chce.
Z całym szacunkiem, ale za mało wiesz, a problem jest za szeroki i zbyt złożony aby to wyjaśniać na forum.
Zatem mam propozycję, która może pomóc i każdy sobie z tym poradzi (teraz wszyscy zakładowi informatycy mogą na mnie pluć, że im fach odbieram 🤣), a na pewno nie zaszkodzi.

Przelicz/przebuduj indeksy.
Tu masz program do tego:
https://github.com/sergiisyrovatchenko/SQLIndexManager/releases

Odpalasz, logujesz się do serwera, wybierasz bazę, klikasz refresh, zaznaczasz ptaszki, klikasz fix indexes i...
Daj znać czy to coś zmieniło :)

Te zabawy z SQL Index Manager powinieneś robić, kiedy nikt nie pracuje z bazą danych.

1

@wloochacz: Witajcie, dziękuję za sporo informacji, chciałem odpowiedzieć na kwestie które poruszyliście.

  1. Oczywiście jest to SQL w wersji standard. Jest to dokładnie SQL Server 2016.
    sql-properties.png
  2. Rozmiar bazy danych programu to 88GB, log tej bazy ma rozmiar 22 GB, jest jeszcze kilka innych baz na tym serwerze ale są one znacznie mniejsze.
  3. System ERP to COMRACH ERP XL
  4. Program serwisowany jest przez autoryzowanego partnera Comarch.
  5. Wersja programu to 2021.0100, Aktualizacja bazy była wykonana na początku stycznie i wtedy problemy się zaczęły, jednakże bazę wysyłaliśmy już do producenta, ale ten nie stwierdził nieprawidłowości, co prawda zasugerował zainstalowanie jakiejś łaty, co zrobiłem ale bez efektów.
  6. Baza jest codziennie indeksowana wraz z aktualizacją statystyk, za pomocą skryptów Ola Hallengrena, sam Comarch na szkoleniu to proponował ....
  7. Jeśli chodzi o backup to wykonujemy go co godzinę, ale on zajmuje 7 minut i nie wpływa w sposób znaczący na prędkość działania systemu, właściwie w ogóle nie mam informacji od userów o czasowych spowolnień podczas robienia kopii.
  8. Userów XL’a jest około 80, ale mamy dość rozbudowaną produkcję i samą aplikację.

Co do posta wloochacza, to jasne, zwróciliśmy się do naszej firmy serwisowej, to powiedzieli, że to może sprzęt, pewnie macierz, sprawdziliśmy to, przerzuciliśmy na fizyczne dyski jak pisałem - problem ten sam, teraz podejrzenia poszły na serwer czyt. dalej sprzęt. Oczywiście patrzeli na system, ale nie niemieli pomysłu, wyjaśnienia. Nie mam im tego za złe być może sam bym tak celował jak bym nie wiedział co jest grane. W każdym razie kiedy firma serwisowa się wypstrykała - poszliśmy do Comarchu, tutaj również bez efektu (to znaczy - baza jest ok) jak pisałem i dlatego piszę na forum, więc przyznasz wloochacz'u, że kolejność jest właściwa.

0

Z tego co wiem to Comarch zaleca SQL Server 2019 do najnowszego XL'a. Może korzystają z jakiś funkcji, których 2016 nie ma. Trzeba przyznać, że przeskok w funkcjonalności i ogólnej pracy jest spory.

1

@Tomasz Hinz: ponawiam sugestię sprawdzenia na czym są waity, https://www.sqlskills.com/blo[...]lease-tell-me-where-it-hurts/ i na tej podstawie podejmowanie dalszych kroków.
Zgadywanie i randomowe akcje niekoniecznie są dobrą strategią optymalizacji.

1 użytkowników online, w tym zalogowanych: 0, gości: 1, botów: 0