Wydajność MSSQL + raport

0

Hej,

Zebrało mi się kilka rzeczy związanych z bazą MSSQL 2017 STD podpięta pod Comrach ERP XL. Może ktoś spotkał się z podobnymi problemami i wie jak sobie z tym ogarnąć. Albo pomoże w zlokalizowaniu problemu i jego rozwiązaniu.

  1. Coraz więcej ludzi w firmie chce pracować zdalnie i łączyć się VPN'em do sieci i pracować z wdrożonym systemem ERP. Niestety ale chodzi to masakrycznie wolno, czasami zrywając sesję nie tyle z VPN'em co SQL'em. VPN postawiony jest na mikrotiku IPSEC L2TP. Łącze w firmie przynajmniej 100/100
    Szukałem na to rozwiązania i chyba jedyna opcja to RDP i praca bezpośrednio na serwerze, ale nie koniecznie mi się to podoba, aby szary ludzik mi się tam pałętał ;)

  2. Sama wydajność CDN XL jest tragiczna, ale wg. producenta i partnera i tak u nas to szybko chodzi :D Czy ktoś ma jakieś sprawdzone metody na zwiększenie wydajności działania? Najbardziej spadki wydajności widać na komputerach pracujących po WiFi, ale nie tylko. Chwilowo pomaga restart serwera, ale też nie o to w tym chodzi.
    Na chwilę obecną do serwera wydelegowałem ponad 32GB RAM (wykorzystujemy 29) baza i serwer pracują na SSDkach.

  3. I ostatnia rzecz, kombinuję dla księgowości nad raportem z Planu kont i mam dylemat jak to zrobić.Domyślnie ma być prezentowany w BI comarchowskim (mamy jakiś podstawowy bez OLAP'a)
    Przykładowa tabela z danymi: http://sqlfiddle.com/#!18/24227f/14

I już tłumaczę w czym mam problem z punktem 3:
Muszę przygotować strukturę jak w ERP, czyli: Konto nadrzędne-> poziom 2-> poziom 3. I ok, to mogę zrobić używając self join na polach KKS_Gidnumer (ID konta) do pola KKS_SynNumer (ID poziomu nadrzędnego).
Problem pojawia się w inny miejscu, aby podać salda na kontach per miesiąc. Comarch nie trzyma tego na sztywno w bazie tylko wywoływane jest to procedurą po odpaleniu określonego konta (m.in jego ID). I teraz jak wywołać procedurę, aby leciała dla wszystkich kont i rozbiła to na miesiące. Przykładowe wywołanie funkcji:

EXEC CDN.ObliczObrotySaldaEx @CDN_Data_od=79627,@CDN_Data_do=79991,@CDN_Bufor=1,@KKSGIDNumer=432,@Konto='',@CDN_FiltrWybraneKonta=0,@CDN_FiltrUsera1='',@CDN_FiltrUsera2='',@CDN_FiltrUsera3='',@Wyrazenie='',@Syntetyka=1,@WgMiesiecy=1,@BezStanow=0,@DolaczNazwy=0,@CDN_FiltrDekrety='',@CDN_FiltrDekretyID=0,@CDN_KwotyWmr=0,@CDN_FiltrWmr='',@CDN_OpeNumer=2;

Czy jedyna opcją będzie przepisanie tego i wywoływanie zapytania zamiast procedury?

0

Masz problem z wydajnoscia sieci, dysku, bazy, systemu czy procesora?
Ktore zapytania sa najwolniejsze?
Czy CPU jest rowno obiazony na wszystkich rdzeniach?
Czy SSD sa zdrowe?
Ile RAMu masz wolnego?
Jaki masz transfer plikow po wifi?
Czy VPN korzysta z firmy trzeciej spoza sieci lokalnej?
Ilu uzytkownikow jest szczytowo?

0

Pod względem wydajności hardware nie widzę żadnego wąskiego gardła.
Nie ważne czy odpalam ERP'a na serwerze czy na końcówce działa podobnie. Fakt na końcówce kilka ms wolniej, po VPN tragedia.
Dyski SSD nowe, wydajność odpowiednia (wczepione bezpośrednio w wirtualkę)

Wpływu na zapytania za bardzo nie mam, jak to w rozwiązaniach Comarchu ;) Ale ogólnie każda operacja potrafi zwolnić. Locków na w SSMS też specjalnie nie widziałem.

Wydelegowane na serwer jest 16 procesorów wirtualnych, Średnie obciążenie rdzenia to 6%.
RAM na chwilę obecną jest wolny na poziomie 3GB
Transfer plików po WiFi ~500Mbps
Co do VPN jedynie mamy przekierowaną domenę w hostingu na nasz stały IP w firmie. Nie korzystamy z rozwiązań firmy trzeciej.
Na razie w szczycie po VPN było 10 osób. Z aplikacji po SQL korzystało w szczycie 3-4 osoby. Z samego ERP korzysta ~30

Sama baza obecnie ma objętość 2GB + 37GB log (+ druga bliźniacza 700MB + 4GB log)

0

A jak plan robienia backupów bazy wygląda? bo sądząc po rozmiarze loga to w ogóle nie jest backupowany.
Jeśli przywracanie bazy do danego punktu w czasie nie jest potrzebne to w ogóle można przełączyć bazę z Full recovery model na Simple i przy tak małych bazach robić często backupy.
Jeśli nie jest włączony na bazie Read Committed Snapshot to też można włączyć, powinno pomóc o ile nie używają wyższych poziomów izolacji w CDN.
Można by się też przyjrzeć wykorzystaniu dysków, i jeśli i/o byłoby problematyczne to przenieść niektóre fragmenty bazy na różne dyski.

Wprawdzie CDN nie znam, ale znając oprogramowanie Comarchowe nie zdziwiłbym się jakby czytali całe tabele do pamięci, a potem robili filtrowanie po stronie aplikacji, co by tłumaczyło katastrofalną wydajność po sieci, a z tym się niestety wiele nie da zrobić.

0

Co do planu backupów to mam zewnętrzny skrypt w Powershellu realizowany poza SSMS, odpalający się raz dziennie. Może faktycznie w tym będzie leżał problem.

W CDN używają izolacji, muszę po testować czy włączenie opcji nie zaburzy niczego.
I/O dysków nie jest problemem. To w pierwszej kolejności sprawdzałem.

No i właśnie po części jest tak jak piszesz, czyli czyta całą tabelkę z wszystkimi joinami i filtruje ją w aplikacji. Jedynie przy grubszych tabelach zapytanie przenoszone jest do silnika z wydzielonym zakresem do przefiltrowania.

0

Co do planu backupów to mam zewnętrzny skrypt w Powershellu realizowany poza SSMS

Zweryfikuj to, bo rozmiar loga świadczy o tym, że baza jest w trybie full recovery, a ty nigdy nie zrobiłeś pełnego backapu, bo lug jest za duży, poczytaj:Business continuity and database recovery - SQL Server

Pytanie czy reguralnie reindeksujesz bazę i odświeżasz statystyski?

Uporaj się z backup'em, aby log miał "normalny" rozmiar, zrób reindeksacje i odśwież statystyki i zobacz czy się poprawi.

0

Ok z backupem będę musiał pomyśleć.
Na chwilę obecną mam taki skrypt w ps:

$serverName = ".\***"
$backupDirectory = "\\***"
$daysToStoreDailyBackups = 7
$daysToStoreWeeklyBackups = 28
$monthsToStoreMonthlyBackups = 3

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
 
$mySrvConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$mySrvConn.ServerInstance=$serverName
$mySrvConn.LoginSecure = $false
$mySrvConn.Login = "***"
$mySrvConn.Password = "*****"

$server = new-object Microsoft.SqlServer.Management.SMO.Server($mySrvConn)

$dbs = $server.Databases
$startDate = (Get-Date)
"$startDate"

Get-ChildItem "$backupDirectory\*_daily.bak" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToStoreDailyBackups)} |% {Remove-Item $_ -force }
"removed all previous daily backups older than $daysToStoreDailyBackups days"

foreach ($database in $dbs | where { $_.IsSystemObject -eq $False})
{
    $dbName = $database.Name      

    $timestamp = Get-Date -format yyyy-MM-dd-HHmmss
    $targetPath = $backupDirectory + "\" +"srvsql"+ $dbName + "_" + $timestamp + "_daily.bak"

    $smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
    $smoBackup.Action = "Database"
    $smoBackup.BackupSetDescription = "Full Backup of AltumSRV" + $dbName
    $smoBackup.BackupSetName = $dbName + " Backup"
    $smoBackup.Database = $dbName
    $smoBackup.MediaDescription = "Disk"
    $smoBackup.Devices.AddDevice($targetPath, "File")
    $smoBackup.SqlBackup($server) 
    "backed up $dbName ($serverName) to $targetPath"               
}

if([Int] (Get-Date).DayOfWeek -eq 0)
{
    Get-ChildItem "$backupDirectory\*_weekly.bak" |? { $_.lastwritetime -le (Get-Date).AddDays(-$daysToStoreWeeklyBackups)} |% {Remove-Item $_ -force }
    "removed all previous daily backups older than $daysToStoreWeeklyBackups days"

    foreach ($database in $dbs | where { $_.IsSystemObject -eq $False})
    {
        $dbName = $database.Name      

        $timestamp = Get-Date -format yyyy-MM-dd-HHmmss
        $targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + "_weekly.bak"

        $smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
        $smoBackup.Action = "Database"
        $smoBackup.BackupSetDescription = "Full Backup of " + $dbName
        $smoBackup.BackupSetName = $dbName + " Backup"
        $smoBackup.Database = $dbName
        $smoBackup.MediaDescription = "Disk"
        $smoBackup.Devices.AddDevice($targetPath, "File")
        $smoBackup.SqlBackup($server) 
        "backed up $dbName ($serverName) to $targetPath"                 
    }
}

if([Int] (Get-Date).Day -eq 1)
{
    Get-ChildItem "$backupDirectory\*_monthly.bak" |? { $_.lastwritetime -le (Get-Date).AddMonths(-$monthsToStoreMonthlyBackups)} |% {Remove-Item $_ -force }
    "removed all previous monthly backups older than $monthsToStoreMonthlyBackups days"

    foreach ($database in $dbs | where { $_.IsSystemObject -eq $False})
    {
        $dbName = $database.Name      

        $timestamp = Get-Date -format yyyy-MM-dd-HHmmss
        $targetPath = $backupDirectory + "\" + $dbName + "_" + $timestamp + "_monthly.bak"

        $smoBackup = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
        $smoBackup.Action = "Database"
        $smoBackup.BackupSetDescription = "Full Backup of " + $dbName
        $smoBackup.BackupSetName = $dbName + " Backup"
        $smoBackup.Database = $dbName
        $smoBackup.MediaDescription = "Disk"
        $smoBackup.Devices.AddDevice($targetPath, "File")
        $smoBackup.SqlBackup($server) 
        "backed up $dbName ($serverName) to $targetPath"                 
    }
}

Tylko problemem może być to, że odpalany jest przez Harmonogram zadań, a nie z poziomu SSMS..

Reindeksacja i odświeżanie statystyk jest wykonywane raz w tygodniu, teraz widzę, że coś się w ostatnim tygodniu wywalała. Muszę sprawdzić o co chodzi.

0

W linijkach

$smoBackup.Action = "Database"

Powinno być

$smoBackup.Action = BackupActionType.Database

No mówiąc szczerze nie wiem na co rzucane jest "Database", dodatkowo, skoro nie robisz pelnego backupu + logi, to rozważ przejście na simple recovery model, z ciekowości: testowałeś przywracanie, czy chcesz się przekonać podczas faktycznej awarii, że jest ok?

0

Ok, zmienię.

Tak testowałem przywracanie z kopii, które są wykonywane. Testowane było na serwerze testowym

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