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

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%.

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…

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ę

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

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

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-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-waiting-tasks-transact-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.

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? :)

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

@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.

@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.
   
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.

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.

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

@yarel: Jasne, skorzystam z tej rady, dziękuję za link.

@yarel: Problem z bazą mamy co do zasady przy zamykaniu zleceń produkcyjnych czyli raz w miesiącu, pewnie informacje pozyskane ze skryptu będą bardziej przydatne, gdyby je zrobić w zawężeniu na okres w którym te problemy się pojawią przy kolejnym zamykaniu.

Na teraz to wygląda tak:

Widzę odważny jesteś wgrywając nową rocznikową wersję XL'a na produkcję :)
Ja zawsze się wstrzymuję przynajmniej 3 miesiące, jak wypuszczą pierwszego dużego hotfixa, taką mamy politykę z naszym partnerem. Na razie siedzimy jeszcze na 2020.2.
Przeglądając sam release note dla wersji 2021 widzę sporo zmian w module produkcji, ale nie widzę tego, że do poprawnego działania XL'a 2021 potrzebny jest SQL 2019. Jedyna informacja jest taka, że XL 2021 jako pierwszy działa poprawnie na SQL Server 2019.

@Tomasz Hinz: Dokładnie, zawężenie okna pozwoli zorientować się lepiej w sytuacji. sys.dm_os_wait_stats daje Ci statystyki, więc tylko pogląd gdzie te waity są.
Statystyki można wyczyścić przed zamykaniem miesiąca, https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-sqlperf-transact-sql?view=sql-server-2016

CXPACKET

1. W properties, które pokazałeś są 24 procesory, ale wspomniałeś, że to wirtualna maszyna. Jaka fizyczna architektura? Ile i jakich fizycznych CPU?
2. Jakie ustawienie 'Maximum Degree of Parallelism' ? (Properties -> Advanced -> sekcja Parallelism)

To pozwoli skonfrontować ustawienia bazy vs sprzęt vs zalecenia MS.

W trakcie zamykania miesiąca możesz patrzeć na to co się dzieje (sys.dm_os_waiting_tasks) i zidentyfikować sesje, które doświadczają danego waita.
Możliwe, że jakieś zapytanie jest często wywoływane, ale ma bardzo słaby plan zapytania, więc te waity po ~40 sekund blokują inne sesje.

LATCH_EX
Tu możesz zerknąć jak się te locki rozkładają sys.dm_os_latch_stats

LCK_M_IX
3871 waitów, ale z gigantycznym średnim czasem oczekiwania -> 41 sekund * 3871 => prawie 2 dni czekania :)
Znowu, trzeba sprawdzić jakie zapytania wiszą i być może jedno z nich ma słaby plan wykonania.

Ja się wtrącę z przewrotnym pytaniem. Dlaczego nie zapytasz o to w Comarchu? Skoro pracujesz na XLu, to pewnie ktoś Ci go sprzedał i wdrożył. A skoro wdrożył, to zapewne jakąś umowę utrzymaniową masz. Sądzę, że Comarch najlepiej powinien wiedzieć, co się w tym obnażającym serwer procesie dzieje.
A jeśli się z dostawcą softu pokłóciłeś, to odpal Profilera, uruchom to całe zamykanie zleceń i zobacz, co się tam pod maską dzieje.

Ciężko doradzić, ja nie znam CDN ale jest tu jakiś schemat który doprowadził do zaistniałej sytuacji:

1. Było dobrze
2. Aktualizacja
3. Jest źle

Zabawy z profilerem mogą wnieść wiele wiedzy, pytanie jak producent zareaguje na to co wykryjecie i będzie skłonny do zmian.
Nie za bardzo wiadomo co ta aktualizacja zrobiła, że nagle system jest kompatybilny z SQL 2019, ale może sprobować takiego scenariusza bez dotykania zapytań i SQL (robiłbym to na środowisku testowym)

1. Odtwarzam baze na teście
2. Weryfikuje, ze problem sie pojawia
3. Robię rebuild wszystkich indeksów
4. aktualizuje wszystkie statystyki
5. Czyszcze wszystkie scachowane plany wykonań (https://www.sqlskills.com/blogs/glenn/eight-different-ways-to-clear-the-sql-server-plan-cache/)

Wtedy można diagnozować dalej i próbować wyłuskać te zapytania ktore tak obciażają serwer

UPDATE:

Warto też zapuścić dbcc checkdb aby sprawdzić czy wszystko z baża jest ok

Możesz też sprawdzić czy SQL sam nie "widzi" brakujących indeksów:

SELECT db.[name] AS [DatabaseName]
    ,id.[object_id] AS [ObjectID]
	,OBJECT_NAME(id.[object_id], db.[database_id]) AS [ObjectName]
    ,id.[statement] AS [FullyQualifiedObjectName]
    ,id.[equality_columns] AS [EqualityColumns]
    ,id.[inequality_columns] AS [InEqualityColumns]
    ,id.[included_columns] AS [IncludedColumns]
    ,gs.[unique_compiles] AS [UniqueCompiles]
    ,gs.[user_seeks] AS [UserSeeks]
    ,gs.[user_scans] AS [UserScans]
    ,gs.[last_user_seek] AS [LastUserSeekTime]
    ,gs.[last_user_scan] AS [LastUserScanTime]
    ,gs.[avg_total_user_cost] AS [AvgTotalUserCost]  -- Average cost of the user queries that could be reduced by the index in the group.
    ,gs.[avg_user_impact] AS [AvgUserImpact]  -- The value means that the query cost would on average drop by this percentage if this missing index group was implemented.
    ,gs.[system_seeks] AS [SystemSeeks]
    ,gs.[system_scans] AS [SystemScans]
    ,gs.[last_system_seek] AS [LastSystemSeekTime]
    ,gs.[last_system_scan] AS [LastSystemScanTime]
    ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]
    ,gs.[avg_system_impact] AS [AvgSystemImpact]  -- Average percentage benefit that system queries could experience if this missing index group was implemented.
    ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
    ,'CREATE INDEX [IX_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN '_'
        ELSE ''
        END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN ','
        ELSE ''
        END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]
    ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK) ON gs.[group_handle] = ig.[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK) ON ig.[index_handle] = id.[index_handle]
INNER JOIN [sys].[databases] db WITH (NOLOCK) ON db.[database_id] = id.[database_id]
WHERE  db.[database_id] = DB_ID()
--AND OBJECT_NAME(id.[object_id], db.[database_id]) = 'YourTableName'
ORDER BY ObjectName, [IndexAdvantage] DESC
OPTION (RECOMPILE);

To jest jednak trudne bo indeksowanie tabelktórych się nie zna może coprawda podnieść szybkośc w konkretnym zapytaniu, ale wpłynąć negatywnie na inne

@yarel: Jeśli chodzi o konfigurację sprzętową to serwer ogólnie ma 384GB RAM i dwa procesory Xeon Gold Scalable 2nd gen 6234 3,3GHz natywnie. Jak mówiłem system jest wirtualny jest Windows 2016 Server, a "pod spodem" Hyper-V Server Core 2019. Załączam kilka screenów odnoście konfiguracji SQL.

To poczytaj o MAXDOP:
https://docs.microsoft.com/en-US/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver15
https://littlekendra.com/2016/07/14/max-degree-of-parallelism-cost-threshold-for-parallelism/

@Fac: Dobre pytanie, już pisałem co nieco na ten temat, nasza firma słono płaci za wsparcie firmy wdrożeniowej/serwisowej. Coś tam popatrzeli na to, ale szybko stwierdzili, że to może być macierz (pisałem, że to obaliłem bo na fizycznych dyskach to samo), generalnie odesłali nas z problemem do Comarchu, moim zdaniem za szybko, bo szczerze mówiąc mogli głębiej poszperać, co do Comarchu to otrzymali od nas bazę, trzymali ją dwa tygodnie i stwierdzili, że wszystko po ich stornie jest ok.

To możesz sam sprawdzic bez wysyłania do producenta dbcc checkdb

Faktem jest, że ten MAXDOP trzeba dostroić do waszej instalacji, nie zapomnij też o Cost Threshold dlaczego wyjasnione w drugim linku z mojego posta

@Panczo: Mamy dość podobny plan (apropos 5 punktów, które zaproponowałeś, uzupełnię go o Twoje sugestie), aby na drugim serwerze umieścić zaraz przed zamykaniem zleceń drugą kopię bazy aby operację móc powtarzać w różnych opcjach konfiguracyjnych softwareowych/czy hardwareowych, tylko ten czas i późniejsza praca pracowników przy ręcznym zamykaniu - dramat. Co do spójności bazy to odpalę dzisiaj to sprawdzanie, dzięki.

Sprawdzę też brakujące indexy...

@Tomasz Hinz: szczerze to poszedłbym porozmawiac z userami i maxdop dostrajal na produkcji. sam tego nie przetestujesz (w sensie pracy w systemie), skoro jest zauważalnie źle, to zauważalnie źle x 2 nie zrobi różnicy ;)
Chodzi mi o przekaz do nich: jest pomysł na poprawę, ale robicie 2x to samo (raz na kopii, raz na bazie testowej), albo robicie raz, ale liczycie się, że na początku będzie wolniej/gorzej, aby docelowo było dobrze.
Co do maxdop to kiedy zaczynalem z SQLServerem (od wersji 7.0) to jak pojawił się 2005 to krążyła urban legend wśród DBAdminów, że to powinno się z defaultu ustawiać na 1. Troche się zmieniło, zarówno w warstwie sprzetowej jak i samego silnika, ale jaosobiscie zwiekszam też Cost Threshold, przy nowych procesorach zwiekszenie to raczej nie jest jakis wielki problem, a po to są by pracowały ;)

Moja lista zakłada, w pierwszej kolejności wyeliminowanie nieprawidlowosci sprzętowych (to zrobiłeś) i problemow z samą bazą (to dbcc checkdb).
W następnej kolejności MAXDOP, a na końcu indeksy.

Indeksy są najtrudniejsze, bo to też jest narzut na operacje na bazie i nie zawsze to co podpowiada silnik ma sens, to trzeba korelować z zapytaniami ktore obciążaja najbardziej bazę, a nie ślepo indeksować...

Ja bym jeszcze przed całą zabawą z MAXDOP sprawdził, czy przypadkiem to całe zamykanie zleceń nie odpala długich transakcji - widziałem w swoim życiu już aplikacje, które wykonywały bardzo duże operacje (z dużymi obliczeniami po stronie końcówki w trakcie) w jednej wielkiej transakcji, co blokowało całkiem sporo stron w kluczowych tabelach, a przez to mroziło większość innych operacji na długie minuty...

@Fac: No właśnie tak to wygląda jak by mechanizm blokował całe tabele lub ich zbyt wielką część uniemożliwiając tym samym realizacje kolejnych transakcji które używają w swoim procesie tych czasowo blokowanych tabel. Tylko czy jeśli by tak było rzeczyście, to jakie ja mam możliwości wpływu na zachowanie aplikacji w kontekście konfiguracji sql ?

@Tomasz Hinz: Na tym właśnie polega mechanizm transakcji i locków z nimi związanych, że nie jesteś w stanie. Nie na poziomie konfiguracji serwera. Przynajmniej według mojej wiedzy, choć pewnie mądrzejsi koledzy coś podpowiedzą.

Hej,

Comarch ma tak ze czasem coś zmieni i wtedy są duże problemy. Zwłaszcza że z produkcją dosyć mocno kombinują ostatnio. Pewnie to było zrobione ale czy było sprawdzane:

1. Po pierwsze statystyki lockowania
   

2. Po drugie jest taka wbudowana procedura w SQL Server sp_who2 która pokazuje który proces, skąd itp. co lockuje. Czy to było odpalane podczas zamykania zleceń?

Jeszcze jedna rzecz. Czy czasem nie macie jakichś triggerów, procedur itp. dodatkowych podczas zamykania zleceń?

Zdarzyło mi się kiedyś zapomnieć o dodaniu NOLOCK do zapytania i blokowało prawie cały system podczas wykonywania query ;-)

@Damian Korczowski: Hej przepraszam, nie zauważyłem Twojego posta, dzięki za info - sprawdzę to dzisiaj :-)