1. Aktualizacja statystyk
2. Czyszczenie pamięci podręcznej procedur
3. Indeksy defragmentacji
4. Ponowne indeksowanie tabel bazy danych

Jedną z najczęstszych przyczyn nieoptymalnej pracy systemu jest nieprawidłowe lub przedwczesne wykonywanie rutynowych operacji na poziomie DBMS. Szczególnie ważne jest przeprowadzenie tych procedur regulacyjnych w dużych systemach informatycznych, które działają pod znacznym obciążeniem i obsługują jednocześnie dużą liczbę użytkowników. Specyfiką takich systemów jest to, że zwykłe działania wykonywane przez DBMS automatycznie (w oparciu o ustawienia) nie wystarczają do efektywnego działania.

Jeśli w działającym systemie występują jakiekolwiek objawy problemów z wydajnością, należy sprawdzić, czy wszystkie zalecane rutynowe operacje na poziomie DBMS są poprawnie skonfigurowane i regularnie wykonywane w systemie.

Wykonywanie rutynowych procedur powinno być zautomatyzowane. Aby zautomatyzować te operacje, zaleca się korzystanie z wbudowanych narzędzi MS SQL Server: Plan konserwacji. Istnieją również inne sposoby automatyzacji tych procedur. W tym artykule dla każdej procedury regulacyjnej podano przykład jego konfiguracji przy użyciu planu konserwacji dla MS SQL Server 2005.

W przypadku MS SQL Server zaleca się wykonywanie następujących operacji regulacyjnych:

Aktualizowanie statystykCleaning cache proceduralnejDefragmentowanie indeksówReindeksowanie tabel bazy danych

Zaleca się regularne monitorowanie terminowości i poprawności tych procedur regulacyjnych.

Aktualizacja statystyk

MS SQL Server buduje plan zapytań na podstawie informacji statystycznych o rozkładzie wartości w indeksach i tabelach. Informacje statystyczne są zbierane na podstawie części (próbki) danych i są automatycznie aktualizowane po zmianie tych danych. Czasami nie wystarcza to, aby MS SQL Server konsekwentnie budował najbardziej optymalny plan wykonania dla wszystkich zapytań.

W takim przypadku mogą wystąpić problemy z wydajnością zapytań. Jednocześnie plany zapytań wykazują charakterystyczne znaki pracy nieoptymalnej (operacje nieoptymalne).

Aby zagwarantować najlepszą wydajność optymalizatora MS SQL Server, zaleca się regularne aktualizowanie statystyk bazy danych MS SQL.

Aby zaktualizować statystyki wszystkich tabel bazy danych, należy uruchomić następujące zapytanie SQL:

Aktualizacja statystyk nie prowadzi do blokowania tabeli i nie zakłóca pracy innych użytkowników. Statystyki mogą być aktualizowane tak często, jak to konieczne. Należy pamiętać, że obciążenie serwera DBMS podczas statystyk aktualizacji wzrośnie, co może niekorzystnie wpłynąć na ogólną wydajność systemu.

Optymalna częstotliwość aktualizacji statystyk zależy od wielkości i charakteru obciążenia systemu i jest określana eksperymentalnie. Zaleca się aktualizowanie statystyk co najmniej raz dziennie.

Powyższe zapytanie aktualizuje statystyki dla wszystkich tabel w bazie danych. W prawdziwym systemie działającym różne tabele wymagają innej częstotliwości aktualizacji statystyk. Analizując plany zapytań, możesz określić, które tabele są bardziej niż inne potrzebne do częstej aktualizacji statystyk, i ustawić dwie (lub więcej) różne procedury rutynowe: dla często aktualizowanych tabel i dla wszystkich innych tabel. Takie podejście znacznie skróci czas aktualizacji statystyk i wpływ procesu aktualizacji na cały system.

Konfigurowanie automatycznej aktualizacji statystyk (MS SQL 2005)

Uruchom MS SQL Server Management Studio i połącz się z serwerem DBMS. Otwórz folder Zarządzanie i utwórz nowy plan konserwacji:

Utwórz podplan (Dodaj sublan) i nazwij go „Aktualizuj statystyki”. Dodaj do tego zadanie Zadanie aktualizacji statystyk z paska zadań:

Ustaw harmonogram aktualizacji statystyk. Zaleca się aktualizowanie statystyk co najmniej raz dziennie. W razie potrzeby częstotliwość aktualizacji statystyk można zwiększyć.

Skonfiguruj ustawienia zadania. Aby to zrobić, kliknij dwukrotnie zadanie w prawym dolnym rogu okna. W wyświetlonym formularzu podaj nazwę bazy danych (lub kilku baz danych), dla których statystyki będą aktualizowane. Dodatkowo możesz określić, dla których tabel aktualizować statystyki (jeśli nie wiesz dokładnie, które tabele musisz określić, ustaw wartość Wszystkie).

Aktualizacja statystyk powinna być przeprowadzona z włączoną opcją Pełne skanowanie.

Zapisz utworzony plan. W czasie określonym w harmonogramie aktualizacja statystyk zostanie uruchomiona automatycznie.

Czyszczenie pamięci podręcznej procedur

MS SQL Server Optimizer buforuje plany zapytań w celu ich ponownego wykonania. Odbywa się to w celu zaoszczędzenia czasu poświęcanego na kompilowanie zapytania, jeśli to samo zapytanie zostało już wykonane, a jego plan jest znany.

Możliwe jest, że MS SQL Server, opierając się na przestarzałych informacjach statystycznych, tworzy nieoptymalny plan zapytań. Ten plan zostanie zapisany w pamięci podręcznej procedur i użyty, gdy ponownie wywołasz to samo żądanie. Jeśli zaktualizowałeś statystyki, ale nie wyczyściłeś pamięci podręcznej procedur, SQL Server może wybrać stary (nieoptymalny) plan zapytań z pamięci podręcznej zamiast budować nowy (bardziej optymalny) plan.

Dlatego zaleca się, aby po aktualizacji statystyk zawsze usuwać zawartość pamięci podręcznej procedur.

Aby wyczyścić pamięć podręczną procedur MS SQL Server, należy uruchomić następujące zapytanie SQL:

To zapytanie powinno zostać wykonane natychmiast po aktualizacji statystyk. W związku z tym częstotliwość jego realizacji powinna pokrywać się z częstotliwością aktualizacji statystyk.

Skonfiguruj pamięć podręczną czyszczenia

dla (MS SQL 2005)

Ponieważ pamięć podręczna procedur musi być czyszczona za każdym razem, gdy statystyki są aktualizowane, zaleca się dodanie tej operacji do już utworzonego podplanu „Aktualizacja statystyk”. Aby to zrobić, otwórz subplan i dodaj zadanie Zadanie wykonywania instrukcji T-SQL do jego schematu. Następnie musisz połączyć zadanie aktualizacji statystyk z nowym zadaniem.

W tekście utworzonego zadania Zadanie wykonania instrukcji T-SQL należy określić zapytanie „ DBCC FREEPROCCACHE ”:

Indeksy defragmentacji

Podczas intensywnej pracy z tabelami bazy danych pojawia się efekt fragmentacji indeksu, który może prowadzić do zmniejszenia wydajności zapytań.

Zalecana jest regularna defragmentacja indeksów. Aby zdefragmentować wszystkie indeksy wszystkich tabel bazy danych, użyj następującego zapytania SQL (po wcześniejszym zastąpieniu nazwy bazy danych):

Defragmentowanie indeksów nie blokuje tabel i nie zakłóca pracy innych użytkowników, ale tworzy dodatkowe obciążenie dla SQL Server. Optymalną częstotliwość tej procedury regulacyjnej należy wybrać zgodnie z obciążeniem systemu i efektem uzyskanym z defragmentacji. Zaleca się defragmentację indeksów przynajmniej raz dziennie.

Możliwe jest wykonanie defragmentacji dla jednej lub kilku tabel, a nie dla wszystkich tabel bazy danych.

Konfigurowanie defragmentacji indeksu (MS SQL 2005)

W uprzednio utworzonym planie konserwacji utwórz nowy plan podrzędny o nazwie „Indeksy defragmentacji”. Dodaj do niego zadanie Reorganizuj zadanie indeksu:

Ustaw harmonogram wykonywania zadania indeksu defragmentacji. Zaleca się wykonywanie zadania co najmniej raz w tygodniu, a przy dużej zmienności danych w bazie danych nawet częściej - raz dziennie.

Skonfiguruj zadanie, określając bazę danych (lub kilka baz danych) i wybierając niezbędne tabele. Jeśli nie wiadomo dokładnie, które tabele należy określić, ustaw wartość na Wszystkie.

Ponowne indeksowanie tabel obejmuje całkowitą przebudowę indeksów tabel bazy danych, co prowadzi do znacznej optymalizacji ich pracy. Zaleca się regularne ponowne indeksowanie tabel bazy danych. Aby ponownie zaindeksować wszystkie tabele bazy danych, uruchom następujące zapytanie SQL:

Ponowne indeksowanie tabel blokuje je na czas ich pracy, co może znacząco wpłynąć na wrażenia użytkownika. W związku z tym zaleca się ponowne indeksowanie podczas minimalnego obciążenia systemu.

Po ponownym indeksowaniu nie ma potrzeby defragmentowania indeksów.

W uprzednio utworzonym planie konserwacji utwórz nowy plan podrzędny o nazwie „Indeksy defragmentacji”. Dodaj do niego zadanie Przebuduj zadanie indeksu:

Ustaw harmonogram wykonywania zadania ponownego indeksowania tabeli. Zaleca się wykonywanie zadania podczas minimalnego obciążenia systemu, przynajmniej raz w tygodniu.

Skonfiguruj zadanie, określając bazę danych (lub kilka baz danych) i wybierając niezbędne tabele. Jeśli nie wiadomo dokładnie, które tabele należy określić, ustaw wartość na Wszystkie.

Ponowne indeksowanie tabel bazy danych

Konieczne jest regularne monitorowanie wdrażania procedur regulacyjnych na poziomie DBMS. Poniżej znajduje się przykład kontroli wykonania planu konserwacji dla MS SQL Server 2005.

Otwórz utworzony plan konserwacji i wybierz element „Wyświetl historię” z menu kontekstowego:

Otworzy się okno z protokołem do implementacji wszystkich określonych procedur.

Pomyślnie wykonane zadania i zadania wykonane z błędami zostaną oznaczone odpowiednimi ikonami. W przypadku zadań wykonywanych z błędami dostępne są szczegółowe informacje o błędach.

Źródło: MS SQL Server Rutynowe operacje. Optymalizacja pracy