Optymalizacja działania serwera SQL
SOLIDWORKS PDM jest środowiskiem bazodanowym, wykorzystującym popularną platformę Microsoft SQL Server. Jak w każdym rozwiązaniu bazodanowym szybkość działania całego systemu zależy przede wszystkim od szybkości przetwarzania zapytań SQL. W niniejszym artykule opiszę krok po kroku jak zoptymalizować ustawienia serwera SQL, aby Twój EPDM działał jeszcze wydajniej!
KROK 1 >> Ustawienie „Auto Shrink” i „Recovery model”
Otwieramy „SQL Management Studio” i łączymy się do serwera. Rozwijamy drzewo do poziomu Server -> Databases. Ustawienia zmieniamy dla produkcyjnej bazy danych (nazwa taka sama jak nazwa przechowalni plików) oraz bazy „ConisioMasterDb”. W tym celu klikamy prawym przyciskiem myszy na bazę danych i wybieramy „Properties”, a następnie „Options”.
- „Recovery model” – powinien być ustawiony na „Simple”
- „Auto Shrink” – powinien być ustawiony na „False”
KROK 2 >> Ustawienie rozmiaru loga i bazy danych
Mając otwarte okno „Properties” przechodzimy do zakładki „Files”, gdzie ustawiamy automatyczny przyrost wielkości (Autogrowth) oraz maksymalny rozmiar bazy oraz loga. Ustawienia wykonujemy dla produkcyjnej bazy danych oraz „ConisioMasterDb”.
Klikamy na przycisk 
w kolumnie „Autogrowth / Maxsize”. Zaznaczamy „Enable Autogrowth”.
Dla bazy danych ustawiamy:
- File Growth : 10 %
- Maximum File Size : 20GB (20000MB)
Dla loga bazy danych ustawiamy:
- File Growth : 15 %
- Maximum File Size : 10GB (10000MB)
KROK 3 >> Ustawienia TempDB
TempDB jest systemową bazą danych, dostępną dla wszystkich użytkowników podłączonych do serwera. TempDB służy do przechowywania obiektów tymczasowych. Poniżej przedstawiam skrypt zmieniający rozmiar plików loga oraz bazy dla TempDB.
–Zmiana rozmiaru loga TempDB, należy podać prawidłową ścieżkę pliku!
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘C:\SQLData\templog.ldf’, SIZE = 5120MB);
GO
–Zmiana rozmiaru pliku bazy danych, należy podać prawidłową ścieżkę pliku!
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘C:\SQLData\tempdb.mdf’, SIZE = 2560MB, FILEGROWTH = 10%);
GO
Aby móc wykorzystać więcej rdzeni procesora należy „podzielić” TempDB. Każdy rdzeń procesora wykorzystuje oddzielny plik z danymi (data file; .mdf; .ndf). Jeżeli istnieje tylko jeden główny plik .mdf, wówczas serwer SQL wykorzystuje tylko 1 rdzeń procesora, nawet jeżeli pracuje pod dużym obciążeniem. Chcąc wykorzystać jeszcze jeden dodatkowy rdzeń należy utworzyć 1 dodatkowy plik dla bazy danych (tym razem będzie to format .ndf). Zaleca się utworzenie nie więcej niż 3 dodatkowych plików .ndf, tak aby serwer wykorzystywał maksymalnie 4 rdzenie procesora.
–Utworzenie 3 dodatkowych plików dla bazy danych, należy podać prawidłową ścieżkę pliku!
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev2, FILENAME = ‘C:\SQLData\tempdev2.ndf’, SIZE = 2560MB, FILEGROWTH = 10%);
GO
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev3, FILENAME = ‘C:\SQLData\tempdev3.ndf’, SIZE = 2560MB, FILEGROWTH = 10%);
GO
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev4, FILENAME = ‘C:\SQLData\tempdev4.ndf’, SIZE = 2560MB, FILEGROWTH = 10%);
GO
Podział bazy danych na kilka plików przyspieszy przetwarzanie informacji tymczasowych, a co za tym idzie poprawi wydajność serwera SQL.
KROK 4 >> Przebudowa i reorganizacja indeksów w tabelach
Po dokonaniu ustawień opisanych w krokach 1-3 pozostało ustawienie planu przebudowy i reorganizacji indeksów w tabelach w produkcyjnej bazie danych. Poniższe zapytanie wyświetli wszystkie indeksy wraz z średnią fragmentacji (wartość w %):
USE <nazwa DB>
SELECT object_name(IPS.object_id) AS [TableName],
SI.name AS [IndexName], IPS.Index_type_desc, IPS.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(db_id(N'<nazwa DB>’), NULL, NULL, NULL , ‘DETAILED’) IPS
JOIN sys.tables ST WITH (nolock) ON IPS.object_id = ST.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE ST.is_ms_shipped = 0
ORDER BY 1,3
Zapytanie powinno wyświetlić dane w poniższej postaci:
Ostatnia kolumna wyświetla średnią fragmentacji indeksów (wartość w %). Zgodnie z zaleceniami Microsoft, jeżeli indeksy posiadają wartość ‘avg_fragmentation_in_percent’ pomiędzy 5 a 30% wówczas powinniśmy dokonać ich reorganizacji. Jeżeli wartość ta jest większa niż 30% wówczas powinny być przebudowane.
Producent zaleca cykliczną reorganizację i przebudowę indeksów w produkcyjnej bazie danych. W jakich częstotliwościach?
- reorganizacja >> co tydzień
- przebudowa >> co miesiąc
Należy pamiętać aby obydwa procesy odbywały się poza godzinami pracy użytkowników, najlepiej w godzinach nocnych.
W dalszej części artykułu pokażę jak utworzyć nowy „Maintenance Plan” w celu reorganizacji i przebudowy indeksów we wszystkich tabelach w bazie danych oraz jak ustawić harmonogram, tak aby indeksy się reorganizowały i przebudowywały automatycznie.
Na początek otwieramy nowy „Maintenance Plan Wizard”, tak jak jest to pokazane poniżej:
Następnie podajemy nazwę planu, zaznaczamy opcję „Separate schedules for each task” oraz klikamy „Next”:
W kolejnym oknie wybieramy zadania do wykonania w naszym planie, są to „Reorganize Index” i „Rebuild Index”, klikamy „Next”.
Ustawiamy kolejność wykonania zadań tak jak poniżej:
W kolejnym etapie konfigurujemy zadanie reorganizacji indeksów. Klikamy na listę rozwijaną w wierszu Databases:
Wybieramy z listy produkcyjną bazę danych.
Z listy rozwijanej „Object” wybieramy „Tables and views”.
Ustawiamy cykliczną reorganizację indeksów w odstępach tygodniowych w określony dzień tygodnia i godzinie.
Następnie powtarzamy te same kroki w celu skonfigurowania zadania przebudowy indeksów.
Wybieramy produkcyjną bazę danych oraz wybieramy typy obiektów: „Tabele i widoki” (Tables and views).
Ustawiamy cykliczną przebudowę indeksów w odstępach miesięcznych w określony dzień miesiąca i godzinie.
Na koniec wybieramy katalog w którym zostanie zapisany raport z reorganizacji i przebudowy.
Odpowiednia konfiguracja serwera SQL oraz cykliczna reorganizacja i przebudowa indeksów zdecydowanie poprawią wydajność samego serwera a co za tym idzie polepszy się komfort pracy użytkowników w systemie SOLIDWORKS PDM.
