Optymalizacja pracy serwera MS SQL pod kątem wydajnej pracy systemu Statlook.

Przed przystąpieniem do wprowadzania poniższych sugestii oraz zaleceń, należy zwrócić uwagę na zasoby sprzętowe jakie mamy do wykorzystania na komputerze lub serwerze, na którym zainstalowany jest MS SQL. Wiedza ta pozwoli odpowiednio rozdysponować zasoby.

1.   Zdefiniowanie minimalnej ilości pamięci dla serwera/instancji oraz dla poszczególnych zapytań.

Korzystając z MS SQL Management Studio klikamy prawym przyciskiem myszy na nazwie instancji i wybieramy z menu kontekstowego „Properties”/Właściwości.

Następnie w sekcji „Memory” wypełniamy pole „Minimum serwer memory”. Dla większości instalacji wystarczyć powinno 2048 MB. W przypadku większych baz oraz gdy dysponujemy większą ilością wolnych zasobów pamięci można rozważyć przydzielenie większej ilości.

Na tym samym oknie, nieco poniżej, znajduje się pole „Minimum memory per query”.

Dla większości baz wystarczającą wartością powinna być 1024KB, w celu zwiększenia wydajności zapytań można ustawić 2048KB i więcej. Oczywiście należy każdorazowo zwrócić uwagę, czy taka zmiana nie spowoduje wyczerpania puli wolnej pamięci RAM.

2.   Jeżeli na serwerze zainstalowanych jest kilka instancji serwera SQL, należy rozważyć ustawienie limitu wykorzystywanej maksymalnej ilości pamięci RAM oraz liczby procesorów dla każdej z instancji.

Parametry te znajdziemy przy wykorzystaniu MS SQL Management Studio klikając prawym przyciskiem myszy na nazwie instancji i wybierając z menu kontekstowego „Properties”/Właściwości (podobnie jak w pkt. 1).

Następnie możemy dokonać edycji pól w sekcjach:

Memory – pozycja Maximum server memory – maksymalna ilość pamięci RAM,

Processors – pozycja Maximum worker threads – maksymalna liczba obsługujących procesorów.

Odpowiednie dopasowanie wykorzystywanej maksymalnej ilości pamięci oraz procesorów na poszczególnych instancjach do wymagań pracującego na nich oprogramowania, powinno zwiększyć płynność pracy. Przy wyznaczaniu limitów należy pamiętać, aby nie rozdysponować całej pamięci RAM, gdyż może to spowodować spadek wydajności systemu operacyjnego.

3.   Dzielenie plików bazy tempdb na 2-8 części.

Przy dużych bazach danych optymalne wykorzystanie bazy temp może znacznie przyspieszyć wykonywanie zapytań. Wobec powyższego zalecane jest posiadanie 2,4 lub 8 plików bazy tempb, zgodnie z zależnością ile „core’s” procesora na serwerze tyle plików bazy tempdb. Wówczas baza ta może być obsługiwana przez kilka rdzeni jednocześnie. W celu dodania kolejnego pliku bazy można skorzystać z przykładowego skryptu, tworząc kolejne pliki, np. tempdev3 itd.:

ALTER DATABASE tempdb
ADD FILE (NAME = tempdev2, FILENAME = 'W:\tempdb2.mdf', SIZE = 256);

Dobrym rozwiązaniem jest umieścić każdy z tych plików na osobnym dysku fizycznym serwera, jeżeli posiada ich więcej.

4.   Okresowe zadnia odbudowy i reorganizacji indeksów bazy danych.

Intensywne wykorzystywanie bazy danych może powodować problemy z fragmentaryzacją indeksów w bazie. Aby temu przeciwdziałać zalecane jest utworzenie skryptów, które okresowo będą te indeksy odbudowywać i reorganizować, najlepiej wg harmonogramu:

– reorganizacja – 1 na tydzień, przykład (od wersji sql 2008):

USE [WstawNazwęBazy];
GO
ALTER INDEX ALL ON [WstawNazwęTabeli]
REORGANIZE;
GO

– odbudowa – 1 na miesiąc, przykład (od wersji sql 2008):

USE [WstawNazwęBazy];
GO
ALTER INDEX ALL ON [WstawNazwęTabeli]
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB=ON, STATISTICS_NORECOMPUTE=ON);
GO

Możliwe są również rozwiązania, które dokonują reorganizacji lub odbudowy indeksów na wszystkich tabelach w bazie, np. przy wykorzystaniu procedury „sp_MSforeachtable”. Oba harmonogramy najlepiej uruchamiać po za godzinami najintensywniejszego wykorzystywania serwera. Dobrą praktyką jest poprzedzić je automatycznym backupem baz.

5.   Wyłączenie plików baz danych *mdf, *ndf, *ldf spod skanowania antywirusa.

Większość również komercyjnych antywirusów negatywnie wpływa podczas skanowania na wydajność pracy serwera sql. Rozwiązaniem tej kwestii może być wyłączenie spod skanowania wszystkich plików *.mdf, *.ndf oraz *.ldf znajdujących się w katalogu DATA odpowiednim dla serwera SQL, np. C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016EXPR. Oczywiście taka modyfikacja powinna zostać poprzedzona odpowiednią analizą, czy nie spowoduje ono nadmiernego ryzyka dla bezpieczeństwa danych.

6.   Włączenie flag śledzenia 1117 oraz 1118

Szczególną rolę dla efektywnego działania baz, może mieć włączenie flag śledzenia. Role poszczególnych flag i ich dokładne działanie należy dokładnie poznać jeszcze przed ich ewentualnym włączeniem, korzystając z dokumentacji serwera SQL. Poniżej jedynie krótka informacja na co mają one wpływ.

Włączenie na instancji flagi 1117 spowoduje, że pliki baz danych znajdujące się w pojedynczej grupie będą ulegać automatycznemu powiększeniu w tym samym czasie. Ostatecznie doprowadzi to do sytuacji, w której rozkład alokacji  będzie równomierny po wszystkich plikach.

Skrypty uruchamiający 1117:

ALTER DATABASE <dbname> MODIFY FILEGROUP <filegroup> { AUTOGROW_ALL_FILES | AUTOGROW_SINGLE_FILE } 

Włączenie flagi 1118 spowoduje, że serwer zacznie alokować jako jednolite zakresy wszystkie alokacje w tempdb, których rozmiar nie przekracza 64 kB.

Skrypt uruchamiający dla 1118:

ALTER DATABASE <dbname> SET <strong>MIXED_PAGE_ALLOCATION</strong> { ON | OFF }

7.   Instant File Initialization.

Kolejną możliwością optymalizacji i przyspieszenia pracy serwera może być włączenie w systemie Windows opcji Instant File Initialization. W celu jego uruchomienia należy w systemie Windows uruchomić jako administrator polecenie secpol.msc – zasady zabezpieczeń lokalnych. Następnie klikamy „Zasady lokalne” i „Przypisywanie praw użytkownika”. We właściwościach „Wykonuj zadania konserwacji woluminów” dodajemy użytkownika, który jest jednocześnie użytkownikiem, na którym działa usługa SQLServer interesującej nas instancji serwera. Po jego dodaniu, zapisaniu zmian oraz restarcie usługi SQLServer funkcja powinna zostać uruchomiona.

Należy jednocześnie pamiętać, iż pochodną tej zmiany jest udostępnienie użytkownikom systemu Windows o odpowiednich uprawnieniach możliwości odczytywania starej zawartości baz danych serwera. Stąd najlepiej uruchamiać tę funkcję jedynie na dedykowanych serwerach o ograniczonym dostępie jedynie dla administratorów.