Uaktualnianie baz danych przy użyciu Asystenta dostrajania zapytań

Dotyczy: SQL Server 2016 (13.x) i nowsze wersje usługa Azure SQL Database usługa Azure Synapse Analytics Analytics Platform System (PDW)

Podczas migracji ze starszej wersji programu SQL Server do programu SQL Server 2014 (12.x) lub nowszej oraz uaktualniania poziomu zgodności bazy danych do najnowszej dostępnej wersji obciążenie może być narażone na ryzyko regresji wydajności. Jest to również możliwe w mniejszym stopniu podczas uaktualniania między programem SQL Server 2014 (12.x) i dowolną nowszą wersją.

Począwszy od programu SQL Server 2014 (12.x) i każdej nowej wersji wszystkie zmiany optymalizatora zapytań są bramowane na najnowszy poziom zgodności bazy danych, więc plany wykonywania nie są zmieniane bezpośrednio w momencie uaktualniania, ale raczej wtedy, gdy użytkownik zmieni opcję COMPATIBILITY_LEVEL bazy danych na najnowszą dostępną. Aby uzyskać więcej informacji na temat zmian optymalizatora zapytań wprowadzonych w programie SQL Server 2014 (12.x), zobacz Narzędzie do szacowania kardynalności. Aby uzyskać więcej informacji na temat poziomów zgodności i sposobu ich wpływu na aktualizacje, zobacz Poziomy zgodności i aktualizacje silnika bazy danych.

Ta funkcja gating zapewniana przez poziom zgodności bazy danych w połączeniu z magazynem zapytań zapewnia wysoki poziom kontroli nad wydajnością zapytań w procesie aktualizacji, jeśli aktualizacja jest zgodna z zalecanym przepływem pracy przedstawionym poniżej. Aby uzyskać więcej informacji na temat zalecanego przepływu pracy na potrzeby uaktualniania poziomu zgodności, zobacz Zmienianie trybu zgodności bazy danych i Używanie magazynu zapytań.

Ta kontrola nad uaktualnieniami została jeszcze bardziej ulepszona dzięki serwerowi SQL Server 2017 (14.x), w którym wprowadzono funkcję automatycznego dostrajania, co umożliwia automatyzację ostatniego kroku w zalecanym przepływie pracy powyżej.

Począwszy od programu SQL Server Management Studio w wersji 18, nowa funkcja Asystenta dostrajania zapytań (QTA) będzie prowadzić użytkowników przez zalecany przepływ pracy, aby zachować stabilność wydajności podczas aktualizacji do nowszych wersji programu SQL Server. Jest to opisane w sekcji „Zachowanie stabilności wydajności podczas aktualizacji do nowszych wersji SQL Server” w „Scenariuszach użycia magazynu zapytań”. Jednak QTA nie wraca do wcześniej znanego dobrego planu, zgodnie z ostatnim krokiem zalecanego przepływu pracy. Zamiast tego QTA będzie śledzić wszelkie regresje znalezione w widoku magazynu zapytań regresji zapytań i iterować przez możliwe permutacje odpowiednich odmian modelu optymalizatora, aby powstał nowy, lepszy plan.

Przepływ pracy Asystenta dostrajania zapytań

Punkt początkowy QTA zakłada, że baza danych z poprzedniej wersji programu SQL Server jest przenoszona (za pośrednictwem CREATE DATABASE ... FOR ATTACH lub RESTORE) do nowszej wersji silnika bazy danych SQL Server, a jej poziom zgodności przed uaktualnieniem nie jest od razu zmieniany. QTA poprowadzi cię przez następujące kroki:

1. Skonfiguruj magazyn zapytań zgodnie z zalecanymi ustawieniami czasu trwania obciążenia (w dniach) ustawionym przez użytkownika. Zastanów się nad czasem trwania obciążenia zgodnym z typowym cyklem biznesowym.
2. Poproś o uruchomienie wymaganego obciążenia, aby magazyn zapytań mógł zebrać dane bazowe obciążenia (jeśli nie są jeszcze dostępne).
3. Uaktualnij do docelowego poziomu zgodności bazy danych wybranego przez użytkownika.
4. Zażądaj, aby zebrano drugi zestaw danych obciążenia w celu porównania i wykrycia regresji.
5. Iteruj wszystkie znalezione regresje na podstawie magazynu zapytań widoku zapytań, poeksperymentuj, zbierając statystyki środowiska uruchomieniowego na temat możliwych permutacji odpowiednich odmian modelu optymalizatora i mierz wynik.
6. Zgłoś zmierzone ulepszenia i opcjonalnie zezwól na utrwalanie tych zmian przy użyciu przewodników planu .

Aby uzyskać więcej informacji na temat dołączania bazy danych, zobacz Odłączanie i dołączanie bazy danych.

Zobacz poniżej, jak QTA zmienia tylko ostatnie kroki zalecanego przepływu pracy w celu uaktualnienia poziomu zgodności przy użyciu Magazynu Zapytań, jak pokazano powyżej. Zamiast wybierać między aktualnie nieefektywnym planem wykonania a ostatnim znanym dobrym planem wykonania, Asystent Dostrajania Zapytań przedstawia opcje dostrajania specyficzne dla wybranych zapytań, aby stworzyć nowy, ulepszony stan z dostosowanymi planami wykonania.

Dostrajanie wewnętrznego obszaru wyszukiwania

Funkcja QTA jest przeznaczona tylko dla zapytań SELECT, które można wykonywać z magazynu zapytań (Query Store). Zapytania sparametryzowane kwalifikują się, jeśli jest znany skompilowany parametr. Zapytania zależne od konstrukcji środowiska uruchomieniowego, takich jak tabele tymczasowe lub zmienne tabeli, nie kwalifikują się obecnie.

Funkcja QTA celuje w znane możliwe wzorce regresji zapytań z powodu zmian w wersjach Estymatora Kardynalności (CE). Na przykład podczas uaktualniania bazy danych z programu SQL Server 2012 (11.x) i poziomu zgodności bazy danych 110 do programu SQL Server 2017 (14.x) i poziomu zgodności bazy danych 140 niektóre zapytania mogą ulec pogorszeniu, ponieważ zostały zaprojektowane specjalnie do pracy z wersją CE, która istniała w programie SQL Server 2012 (11.x) (CE 70). Nie oznacza to, że przywrócenie z CE 140 do CE 70 jest jedyną opcją. Jeśli tylko określona zmiana w nowszej wersji wprowadza regresję, można zasugerować, że zapytanie będzie używać tylko odpowiedniej części poprzedniej wersji CE, która działała lepiej dla konkretnego zapytania, jednocześnie używając wszystkich innych ulepszeń nowszych wersji CE. Ponadto zezwalaj na inne zapytania w obciążeniu, które nie ustąpiły, aby korzystać z nowszych ulepszeń CE.

Wzorce CE przeszukiwane przez QTA są następujące:

• Independence vs. Correlation: Jeśli założenie niezależności zapewnia lepsze oszacowania dla określonego zapytania, wskazówka zapytania USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') powoduje, że program SQL Server generuje plan wykonania przy użyciu minimalnej selektywności podczas szacowania AND predykatów dla filtrów do uwzględnienia korelacji. Aby uzyskać więcej informacji, zajrzyj do wskazówek dotyczących zapytań USE HINT i Wersje CE.
• Simple Containment vs. Base Containment: Jeśli inne zawieranie sprzężeń zapewnia lepsze oszacowania dla określonego zapytania, to wskazówka zapytania USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') powoduje, że SQL Server generuje plan wykonania, używając założenia Simple Containment zamiast domyślnego założenia Base Containment. Aby uzyskać więcej informacji, zobacz wskazówki dotyczące zapytań USE HINT i Wersje ce.
• Funkcja z wieloma instrukcjami (MSTVF) stałe oszacowanie liczby wierszy z 100 wierszy w porównaniu do 1 wiersza: Jeśli domyślne stałe oszacowanie dla funkcji TVF wynoszące 100 wierszy nie prowadzi do bardziej wydajnego planu niż użycie stałego oszacowania dla funkcji TVF z 1 wierszem (odpowiadającego wartości domyślnej w modelu optymalizatora zapytań CE programu SQL Server 2008 R2 (10.50.x) i wcześniejszych wersjach), to stosuje się wskazówkę zapytania QUERYTRACEON 9488, aby wygenerować plan wykonania. Aby uzyskać więcej informacji na temat funkcji MSTVF, zobacz Create User-defined Functions (Database Engine).

Uruchamianie Asystenta dostrajania zapytań na potrzeby uaktualnień bazy danych

QTA to funkcja oparta na sesji, która przechowuje stan sesji w schemacie msqta bazy danych użytkownika, w której jest tworzona sesja po raz pierwszy. Wiele sesji dostrajania można utworzyć w pojedynczej bazie danych w czasie, ale dla danej bazy danych może istnieć tylko jedna aktywna sesja.

Tworzenie sesji uaktualniania bazy danych

1. W programie SQL Server Management Studio otwórz Eksplorator Obiektów i połącz się z Database Engine.

2. W przypadku bazy danych przeznaczonej do uaktualnienia poziomu zgodności bazy danych kliknij prawym przyciskiem myszy nazwę bazy danych, wybierz Tasks, wybierz pozycję Database Upgrade, a następnie wybierz pozycję New Database Upgrade Session.

3. W oknie Kreatora QTA do skonfigurowania sesji są wymagane dwa kroki:

   1. W oknie Konfiguracja skonfiguruj Query Store, aby przechwytywał odpowiednik jednego pełnego cyklu biznesowego danych o obciążeniu w celu analizy i dostrajania.

      • Wprowadź oczekiwany czas trwania obciążenia w dniach (minimum to 1 dzień). Zostanie to użyte do zaproponowania zalecanych ustawień Magazynu Zapytania, aby tymczasowo umożliwić zebranie całej podstawy porównawczej. Przechwytywanie dobrego punktu odniesienia jest ważne, aby zapewnić możliwość przeanalizowania wszystkich zapytań, których dotyczy regresja po zmianie poziomu zgodności bazy danych.
      • Ustaw docelowy poziom zgodności bazy danych, na którym powinna być baza danych użytkownika po zakończeniu przepływu pracy QTA. Po zakończeniu wybierz pozycję Dalej.

      

   2. W oknie Ustawienia dwie kolumny pokazują bieżący stan Magazynu Zapytań w docelowej bazie danych, jak również zalecane ustawienia.

      • Zalecane ustawienia są domyślnie zaznaczone, ale wybranie przycisku radiowego w bieżącej kolumnie akceptuje bieżące ustawienia, a także umożliwia dostrajanie bieżącej konfiguracji magazynu zapytań.
      • Proponowane ustawienie progu nieaktualnego zapytania jest dwukrotnie większe od oczekiwanego trwania obciążenia, mierzonego w dniach. Dzieje się tak dlatego, że magazyn zapytań będzie musiał przechowywać informacje dotyczące obciążenia bazowego i obciążenia po uaktualnieniu bazy danych. Po zakończeniu wybierz pozycję Dalej.

      

      Ważny

      Proponowany maksymalny rozmiar jest dowolną wartością, która może być odpowiednia dla krótkotrwałego zadania obciążeniowego. Należy jednak pamiętać, że może to być niewystarczające do przechowywania informacji na temat obciążeń bazowych i po uaktualnieniu bazy danych w przypadku bardzo intensywnych obciążeń, a mianowicie w przypadku generowania wielu różnych planów. Jeśli przewidujesz, że tak będzie, wprowadź wyższą wartość, która jest odpowiednia.

4. Okno Tuning kończy konfigurację sesji, a także podaje kolejne kroki potrzebne do otwarcia i kontynuowania sesji. Po zakończeniu wybierz pozycję Zakończ.

   

Wykonaj przepływ pracy aktualizacji bazy danych

1. W przypadku bazy danych przeznaczonej do uaktualnienia poziomu zgodności bazy danych kliknij prawym przyciskiem myszy nazwę bazy danych, wybierz Tasks, wybierz pozycję Database Upgrade, a następnie wybierz pozycję Monitor Sessions.

2. Strona zarządzania sesjami zawiera listę bieżących i poprzednich sesji bazy danych w obrębie zakresu. Wybierz żądaną sesję i kliknij pozycję Details.

   Notatka

   Jeśli bieżąca sesja nie jest dostępna, wybierz przycisk Odśwież.

   Lista zawiera następujące informacje:

   • identyfikator sesji
   • nazwa sesji: nazwa wygenerowana przez system składająca się z nazwy bazy danych, daty i godziny tworzenia sesji.
   • stan: stan sesji (aktywny lub zamknięty).
   • Opis: Generowany przez system opis obejmuje wybrany przez użytkownika docelowy poziom zgodności bazy danych oraz liczbę dni odpowiadających cyklowi obciążenia biznesowego.
   • Godzina rozpoczęcia: data i godzina utworzenia sesji.

   

   Notatka

   Usuń sesję usuwa wszystkie dane przechowywane dla wybranej sesji. Jednak usunięcie zamkniętej sesji nie usunąć żadnych wcześniej wdrożonych przewodników planu. Jeśli usuniesz sesję, w której wdrożono przewodniki planu, nie możesz użyć QTA, aby cofnąć zmiany. Zamiast tego wyszukaj przewodniki planu w tabeli systemowej sys.plan_guides i usuń je ręcznie, korzystając z sp_control_plan_guide.

3. Punktem wejścia dla nowej sesji jest krok „Zbieranie danych” oznaczony jako .

   Notatka

   Przycisk Sesji powraca do strony zarządzania sesjami , pozostawiając aktywną sesję as-is.

   Ten krok ma trzy podkroki:

   1. Zbieranie danych bazowych prosi użytkownika o uruchomienie reprezentatywnego cyklu obciążenia, aby "Query Store" mógł zebrać punkt odniesienia. Po zakończeniu tego obciążenia sprawdź Gotowe z uruchomieniem obciążenia i wybierz pozycję Dalej.

      Notatka

      Okno QTA można zamknąć podczas działania obciążenia. Po powrocie do sesji, która pozostaje aktywna, zostanie ona wznowiona od tego samego kroku, na którym została zatrzymana.

      

   2. Uaktualnij bazę danych wyświetli monit o pozwolenie na uaktualnienie poziomu zgodności bazy danych do żądanego miejsca docelowego. Aby przejść do następnego kroku, wybierz pozycję Tak.

      

      Poniższa strona potwierdza, że poziom zgodności bazy danych został pomyślnie uaktualniony.

      

   3. Obserwowane zbieranie danych żąda od użytkownika ponownego uruchomienia reprezentatywnego cyklu obciążenia, aby magazyn zapytań mógł zebrać porównawczy punkt odniesienia, który będzie używany do wyszukiwania szans optymalizacji. W miarę wykonywania obciążenia, użyj przycisku Odśwież, aby aktualizować listę zregresowanych zapytań, jeśli jakieś zostały znalezione. Zmień Zapytania, aby ograniczyć liczbę wyświetlanych zapytań, pokazując wartość. Na kolejność listy ma wpływ Metryka (Czas trwania lub Czas CPU) oraz Aglomeracja (gdzie średnia jest ustawieniem domyślnym). Wybierz również liczbę zapytań do wyświetlenia. Po zakończeniu tego obciążenia sprawdź Gotowe z wykonaniem obciążenia i wybierz Dalej.

      

      Lista zawiera następujące informacje:

      • identyfikator zapytania
      • tekst zapytania: Transact-SQL tekst oświadczenia, które można rozwinąć, wybierając przycisk ....
      • Uruchamia: wyświetla liczbę wykonań tego zapytania dla całej kolekcji obciążeń.
      • Metryka linii bazowej: Wybrana metryka (czas trwania lub czas procesora) w ms do zbierania danych bazowych przed uaktualnieniem zgodności bazy danych.
      • Obserwowany pomiar: Wybrana metryka (czas trwania lub czas CPU) w ms dla zbierania danych po aktualizacji zgodności bazy danych.
      • % Zmień: zmiana procentowa wybranej metryki między stanem zgodności bazy danych przed uaktualnieniem a po uaktualnieniu. Liczba ujemna reprezentuje ilość mierzonej regresji dla zapytania.
      • Możliwość dostosowania: prawda lub fałsz w zależności od tego, czy zapytanie kwalifikuje się do eksperymentowania.

4. View Analysis umożliwia wybór zapytań do eksperymentowania i znajdowania możliwości optymalizacji. Zapytania pokazujące wartość stają się zakresem kwalifikujących się zapytań do eksperymentowania. Po zaznaczeniu żądanych zapytań wybierz pozycję Dalej, aby rozpocząć eksperymentowanie.

   Uwaga

   Kwerendy nie można wybierać do eksperymentowania, jeśli ich parametr Tunable = False.

   Ważny

   Monit informuje, że po przejściu QTA do fazy eksperymentowania powrót do strony Widok analizy nie będzie możliwy.
   Jeśli nie wybierzesz wszystkich kwalifikujących się zapytań przed przejściem do fazy eksperymentowania, musisz utworzyć nową sesję w późniejszym czasie i powtórzyć przepływ pracy. Wymaga to zresetowania poziomu zgodności bazy danych do poprzedniej wartości.

   

5. Wyświetl wyniki umożliwia wybór zapytań w celu wdrożenia proponowanej optymalizacji jako przewodnika po planie.

   Lista zawiera następujące informacje:

   • identyfikator zapytania
   • Tekst zapytania: Transact-SQL zdanie, które można rozwinąć, wybierając przycisk ....
   • Status: wyświetla bieżący status eksperymentu dla zapytania.
   • Metryka linii bazowej: wybrana metryka (czas trwania lub czas procesora CpuTime) w ms dla zapytania wykonywanego w Krok 2 Podkrok 3, reprezentując zapytanie o regresję po uaktualnieniu zgodności bazy danych.
   • pl-PL: Zaobserwowana metryka: wybrana metryka (czas trwania lub czas procesora) w ms dla zapytania po eksperymencie, w przypadku wystarczającej proponowanej optymalizacji.
   • % zmień: zmiana procentowa wybranej metryki między stanem przed i po eksperymentowaniu, reprezentującą ilość mierzonej poprawy dla zapytania z proponowaną optymalizacją.
   • opcja zapytania: Odwołaj się do proponowanej wskazówki, która poprawia wskaźnik wykonywania zapytania.
   • Może wdrażać: true lub false w zależności od tego, czy proponowana optymalizacja zapytań może zostać wdrożona jako przewodnik planu.

   

6. Weryfikacja pokazuje stan wdrożenia wcześniej wybranych zapytań dla tej sesji. Lista na tej stronie różni się od poprzedniej, ponieważ kolumna Can Deploy została zastąpiona kolumną Can Rollback. Ta kolumna może być true lub false w zależności od tego, czy wdrożona optymalizacja zapytań może zostać wycofana, a jego przewodnik planu został usunięty.

   

   Jeśli w późniejszym terminie konieczne jest wycofanie proponowanej optymalizacji, wybierz odpowiednie zapytanie i opcję Wycofaj. Ten przewodnik planu zapytania został usunięty, a lista została zaktualizowana w celu usunięcia wycofanego zapytania. Zwróć uwagę na poniższej ilustracji, że zapytanie 8 zostało usunięte.

   

   Notatka

   Usunięcie zamkniętej sesji nie usunąć żadnych wcześniej wdrożonych przewodników planu. Jeśli usuniesz sesję, w której wdrożono przewodniki planu, wtedy nie możesz użyć QTA do wycofania zmian. Zamiast tego wyszukaj przewodniki planów, używając tabeli systemowej sys.plan_guides, i usuń je ręcznie, używając sp_control_plan_guide.

Uprawnienia

Wymaga członkostwa w roli db_owner.

Zobacz też

• Poziomy zgodności i aktualizacje silnika bazy danych
• narzędzia do monitorowania wydajności i dostrajania
• Monitorowanie wydajności przy użyciu magazynu zapytań
• zmień tryb zgodności bazy danych i użyj magazynu zapytań
• flagi śledzenia
• UŻYJ WSKAZÓWEK do zapytań
• narzędzie do szacowania kardynalności
• automatyczne dostrajanie
• korzystanie z asystenta dostrajania zapytań programu SQL Server