Wydajność aplikacji korzystających z Microsoft SQL Server

Wydajność baz danych SQL to problem, który prędzej czy później spotyka każdego administratora systemu. Dzieje się tak, ponieważ developerzy tworząc nowe aplikacje niejednokrotnie korzystają z niewielkich ilości danych testowych, nie poświęcając wiele czasu jej wydajności. Po uruchomieniu aplikacji w środowisku produkcyjnym z biegiem czasu ilość danych zgromadzonych w bazie zwiększa się, przez co aplikacja działa coraz wolniej… do tego stopnie, iż czasami uniemożliwia pracę użytkownikom.

Na szczęście w przypadku aplikacji korzystających z serwera bazy danych Microsoft SQL Server administratorzy mają do dyspozycji narzędzia, które w wielu przypadkach mogą istotnie poprawić wydajność takich aplikacji. Cały ten proces optymalizacji wydajności jest nazywany strojeniem bazy danych i składa się z kilku kroków.

Podstawową optymalizacji jest znalezienie zasobu będącego wąskim gardłem. Takim wydajnościowym wąskim gardłem może być dostęp do dysku, wielkość pamięci operacyjnej, obciążenie CPU, ale także zbyt duża liczba użytkowników sięgająca do tych samych danych i w efekcie blokująca się wzajemnie. Diagnozę można postawić sprawdzając wartości liczników systemowych (procent wykorzystania CPU, wolna pamięć operacyjna, kolejka dyskowa) jak i bazy danych (stopień wykorzystania buforów i pamięci podręcznej, oraz liczniki dotyczące blokad).

Następnie administrator aplikacji powinien znaleźć zapytania będące źródłem problemów. Kluczowym narzędziem w tym procesie jest SQL Profiler, oraz wbudowane raporty bazodanowe - w szczególności „Top Queries by Total CPU Time”, a także „Top Queries By Total IO”.  W przypadku, gdy wykorzystanie dysków i procesora jest niewielkie, a zapytania kończą się timeoutem należy sprawdzić również „Object Execution Statistics”, „Top Queries By Average CPU Time” i „Top Queries By Average IO”.

Częstym problemem obniżającym wydajność są brakujące indeksy. Można je znaleźć analizując zapytania będące źródłem problemu za pomocą wbudowanego „Database Engine Tuning Advisor” lub sprawdzając odpowiednie widoki dynamiczne gromadzące wiedzę o brakujących indeksach na podstawie danych historycznych (google for: „missing index script”). Należy przy tym pamiętać, że zbyt duża liczba niepotrzebnych indeksów również może być przyczyną problemów wydajnościowych, ponieważ musza one być utrzymywane przy operacjach wstawienia, modyfikacji i kasowania rekordu. W niektórych przypadkach narzędzia te nie wskażą rozwiązania problemu i wymagana będzie ręczna analiza planu zapytania (przykład braku indeksu na kolumnie funkcyjnej).

Jeżeli mimo założonych indeksów baza wybiera błędny plan zapytania, problemem mogą być nieaktualne statystyki lub ich zupełny bark. Rozwiązaniem jest ustawienie odpowiednich opcji na bazie danych (auto create/update statistics), lub ustawienie odpowiedniego planu utrzymaniowego zawierającego przeliczanie statystyk. Możliwe jest również wymuszenie odpowiedniego planu dla zapytania, ale jest to zwykle ostateczność i należy być w tym przypadku bardzo ostrożnym.

W przypadku niektórych problemów związanych z blokowaniem istotne jest ustawienie odpowiedniego poziomu izolacji dla transakcji. Pomóc tutaj może poziom izolacji - snapshot, który można włączyć we właściwościach bazy danych. W niektórych przypadkach wskazane może być przepisanie niektórych funkcji, procedur lub widoków w bazie danych, jeżeli aplikacja z takich obiektów korzysta.

W przypadku aplikacji biznesowych często problem jest generowany przez raporty wykonujące się w czasie normalnej pracy aplikacji. Rozwiązaniem problemu może być przeniesienie generowania raportów na godziny nocne, lub rozdzielenie części raportowej od części odpowiedzialnej za bieżące działanie aplikacji.

Na zakończenie warto również wspomnieć o defragmentacji indeksów, partycjonowaniu danych i odpowiednim ułożeniu struktur na dysku (np. plik logu transakcyjnego powinien być na innym dysku niż plik bazy danych, baza tempdb powinna być na innym dysku, niż bazy użytkownika, a indeks może być na innym dysku, niż tabela, z której są pobierane dane). Oczywiście w tym ostatnim przypadku chodzi o fizyczne dyski – przypisywanie plików do różnych partycji na tym samym dysku lub na macierzy RAID5 nie ma większego sensu.

Powyższe metody są o tyle istotne, że pozwalają administratorom rozwiązać problem z wydajnością aplikacji bez jej modyfikacji. Właściwe modelowanie baz danych, czy zmiana kodu aplikacji pod kontem wydajności baz danych to już tematy na osobne rozważania.