Wielu administratorów IT wielokrotnie zmaga się z podobnymi problemami podczas pracy z SQL Server. Nadszedł czas, aby położyć kres temu kłopotowi. W tym artykule wskarzemy przykłady, aby przeanalizować pięć najczęstszych problemów z wydajnością i pokazać odpowiednie rozwiązania.
Popełnianie błędów jest rzeczą ludzką, a częściej wynika z braku wiedzy. Dlatego wszystko, co zostało tu powiedziane, nie ma na celu szkalowania administratorów baz danych (DBA). Wręcz przeciwnie, chcielibyśmy stanąć w obronie wszystkich administratorów IT, którzy oprócz samych baz danych muszą dbać o serwery Windows, sieci, pamięci masowe, poszczególne aplikacje, narzędzia wirtualizacyjne i wiele innych. W wielu przypadkach ich zakres obowiązków jest tak szeroki, że po prostu nie mają czasu na wystarczające zapoznanie się z każdym tematem z osobna.
Kwestia wydajności jest typowym aspektem serwerów SQL, który ma tendencję do prześlizgiwania się przez pęknięcia, czy to podczas początkowego wdrażania, czy też jednorazowej, dobrze przemyślanej instalacji i konfiguracji instancji. I choć pierwsze dwa problemy są identyczne, jeśli chodzi o to, co mówi klient, ich przyczyny są zupełnie inne.
Uważamy, że słysząc tego typu stwierdzenie, pierwszym krokiem jest sprawdzenie indeksów klastrowych i nieklastrowych, a dokładniej ich fragmentacji. Poniższy skrypt dostarcza nazwę każdego indeksu, nazwę i schemat tabeli, dla której został utworzony, a także stopień fragmentacji i liczbę stron indeksu. Ograniczenia w klauzuli WHERE powodują, że pokazywane są tylko indeksy wymagające konserwacji, czyli indeksy o odpowiednim rozmiarze, z odpowiednią fragmentacją i typu wymagającego konserwacji. Skrypt powinien być wykonywany w ramach okna konserwacji, szczególnie w przypadku dużych baz danych, które są często używane, ponieważ wartość średniej fragmentacji jest określana w trybie runtime.
Indeksy columnstore w wielu przypadkach nie mają jeszcze znaczenia, a ich fragmentację można obliczyć w Dynamic Management View (DMV):
W przypadkach, w których indeksy są pokazywane w tym miejscu, należy przejrzeć, czy istnieją plany konserwacji ich utrzymania, a jeśli tak, to jak często są one wykonywane. Może to ujawnić, że plan konserwacji musi być tworzony lub wykonywany częściej, jeśli to możliwe. Dla odbudowy indeksów i reorganizacji indeksów SQL Server dostarcza plan konserwacji, który niestety jest prawie niemożliwy do skonfigurowania. Inną opcję stanowią rozwiązania skryptowe dostępne w firmie lub w inny sposób. Radzimy naszym klientom skorzystać i zaadaptować rozwiązanie autorstwa Oli Hallengrena. Modyfikacja Standard Fill Factor dla indeksów może również stanowić remedium w indywidualnych przypadkach. Należy jednak pamiętać w tym względzie, że ta opcja konfiguracyjna jest uniwersalnie stosowana do całej instancji, a więc będzie używana dla wszystkich baz danych.
Chociaż konserwacja indeksów jest częścią codziennej pracy administratora bazy danych, w wielu przypadkach nie jest ona wykonywana. Oznacza to, że nieutrzymywane indeksy są jedną z głównych przyczyn problemów z wydajnością. Mimo to, administrator bazy danych nie powinien tworzyć indeksów bazodanowych bez konsultacji z dostawcą aplikacji, gdyż mogą one z pewnością przysporzyć dodatkowych problemów, na przykład przy operacjach masowego wstawiania.
Oczywiście użyliśmy tego samego skryptu, który wzięliśmy dla pierwszego przykładu, aby odpowiedzieć na to częste stwierdzenie. Nie pokazały się żadne indeksy wymagające konserwacji. Wszystko wydawało się więc hunky-dory i zgłosiliśmy klientowi, że: "Twoje skrypty są konserwowane". On jednak odpowiedział tylko dobitnym "Nie!". Szybki rzut oka na bieżące zadania potwierdził jego wątpliwości, gdyż zapytanie:
dostarczyła następujących wyników:
| object_id | name | index_id | type | type_desc | ... |
|---|---|---|---|---|---|
| 8 | NULL | 0 | 0 | HEAP | ... |
| 149575571 | NULL | 0 | 0 | HEAP |
... |
| 565577053 | NULL | 0 | 0 | HEAP | ... |
Nawet najlepszy DBA znajdzie w tym momencie swoje granice. Nie będzie on w stanie utrzymać żadnych indeksów, jeśli zdefiniowane są tylko sterty.
Rozwiązanie w tym przypadku było dość proste. Obecna aplikacja została zastąpiona przyrostowo jej następcą, którego baza danych zawierała już brakujące indeksy. Następnie DBA zgodził się zapewnić szybszą migrację poszczególnych części aplikacji. Kontakt z dostawcą aplikacji w celu współpracy nad rozwiązaniem z pewnością byłby alternatywą również w tym przypadku.
DBA nie może naprawić wszystkich problemów i nie jest też odpowiedzialny za ich istnienie. Powinien jednak umieć wskazać, gdzie się one znajdują. Konieczne jest również, aby nie wykorzystywał ich jedynie jako uzasadnienia dla trwających problemów, ale również dążył do rozwiązania problemu, na przykład poprzez współpracę z vendorem.
Szybkie grzebanie w środowisku za pomocą Monitora Wydajności (w nawiasach pasujące liczniki) ujawniło:
Nic do tej pory nie wskazywało na problemy z wydajnością. Jedynie równoległa instalacja SQL Server reporting services nie jest zalecana.
Jednak rzut oka na plik stronicowania (Paging File%Usage) ujawnił czający się problem. Wykorzystanie pliku było bardzo wysokie. Było to spowodowane limitem 7 GB pamięci RAM przypisanej do SQL Server. Był on potrzebny do miesięcznego procesu importu, zarezerwowany i dlatego nie został zwolniony. Oznaczało to, że tylko 1 GB był dostępny dla systemu operacyjnego i Reporting Services, i to właśnie powodowało wysokie obciążenie pliku stronicowania podczas ładowania Reporting Services.
Maksymalna dozwolona pamięć RAM została zmniejszona z 7GB do 5GB, co rozwiązało problem w krótkim okresie czasu. Ale SQL Server nadal potrzebował 7GB raz w miesiącu. Trzeba sobie z tym poradzić w dalszej kolejności. Opcje obejmują.
Należy pamiętać przy konfiguracji maksymalnej dozwolonej pamięci RAM dla SQL Server, że SQL Server będzie rezerwował pamięć RAM, nawet gdy nie jest ona aktualnie używana. Jest to sensowne w innym przypadku, ponieważ zapobiega to powtarzaniu ładowania danych z pamięci masowej w momencie uzyskania do niej dostępu. Ponadto powinniśmy zwrócić uwagę, że liczniki wydajności, które mogą być monitorowane przez Windows Server Performance Monitor, są również tworzone podczas instalacji SQL Server.
Zgodnie z oczekiwaniami, odpowiednia aplikacja pobiera 10GB danych i generuje dodatkowe 20GB. Standardowe liczniki wydajności (RAM, CPU) mają tendencję do wykazywania niskiego obciążenia. Ale rozmiar pliku danych był pierwotnie 10GB i skonfigurowany z automatycznym wzrostem o 1MB. Oznaczało to, że dane zostały zapisane, pamięć masowa zarezerwowana, a następnie nadpisane zerami w krokach przyrostowych o wielkości 1MB.
Auto-growth został zwiększony do 2GB. Dodatkowo kontu serwisowemu serwera SQL przypisano uprawnienie "Perform volume maintenance tasks". Ryzyko, jakie się z tym wiąże, można sprawdzić w Microsoft documentation.
W większości przypadków podczas tworzenia bazy danych konieczna będzie zmiana domyślnych ustawień dotyczących rozmiaru i przyrostu plików, aby dostosować je do wymagań danej aplikacji. Nawet jeśli producenci aplikacji próbują już wymyślać rozwiązania, to i tak konieczne jest, aby DBA sprawdził wiarygodność skonfigurowanych wartości, ponieważ wolumen danych na użytkownika może być różny. Zawsze zalecamy uwzględnienie tego czynnika również podczas planowania migracji.
Wirtualny serwer SQL klienta działał bardzo wolno lub nie reagował. Dwukrotnie uległ awarii, pokazując niebieski ekran i komunikat błędu CLOCK_WATCHDOG_TIMEOUT (101).
Balonowanie pamięci oznaczało, że pamięć masowa przypisana do maszyny wirtualnej (VM) została zmniejszona o dwie trzecie.
VM została przeniesiona na inny host, ponieważ oryginalny host miał niewystarczającą ilość pamięci.
Używanie SQL Servera w środowiskach zwirtualizowanych jest w zasadzie wspierane. Dostawcy aplikacji wirtualizacyjnych często publikowali białe księgi dotyczące tego zagadnienia. Należy je przeczytać na wszystkich kontach, jeśli SQL Server będzie uruchamiany w środowisku zwirtualizowanym.
Przyczyny problemów z wydajnością w SQL Server nie muszą być skomplikowane. DBA muszą jednak odrobić zadanie domowe (utrzymanie indeksów, prawidłowa konfiguracja maksymalnej pamięci RAM instancji, prawidłowa konfiguracja maszyny wirtualnej itp.)
Nasi eksperci ds. oprogramowania optymalizują umowy i wydatki w zakresie oprogramowania
Nasi eksperci ds. oprogramowania optymalizują umowy i wydatki w zakresie oprogramowania
