Uaktualnianie baz danych przy użyciu Asystenta dostrajania zapytań

Dotyczy: SQL Server 2016 (13.x) i nowsze wersje

Podczas migracji ze starszej wersji programu SQL Server do programu SQL Server 2014 (12.x) lub nowszych wersji 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ą.

W programie SQL Server 2014 (12.x) i nowszych wersjach wszystkie zmiany optymalizatora zapytań są ograniczone do najnowszego poziomu zgodności bazy danych, więc plany wykonania nie są natychmiast zmieniane podczas aktualizacji, ale raczej wtedy, gdy użytkownik zmieni COMPATIBILITY_LEVEL opcję 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 Szacowanie kardynalności (SQL Server). 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 bramkowania zapewniana przez poziom zgodności bazy danych w połączeniu z Query Store daje znakomity poziom kontroli nad wydajnością zapytań w procesie aktualizacji, jeśli aktualizacja podąża za zalecanym przepływem pracy widocznym na następnym diagramie. Aby uzyskać więcej informacji na temat zalecanego przepływu pracy na potrzeby uaktualniania poziomu zgodności, zobacz Zmienianie poziomu zgodności bazy danych i używanie magazynu zapytań.

Ta kontrola nad uaktualnieniami została jeszcze bardziej ulepszona dzięki programowi SQL Server 2017 (14.x), w którym wprowadzono automatyczne dostrajanie i umożliwia automatyzację ostatniego kroku w zalecanym przepływie pracy.

Począwszy od programu SQL Server Management Studio w wersji 18, funkcja Asystenta dostrajania zapytań (QTA) prowadzi użytkowników przez zalecany przepływ pracy, aby zapewnić stabilność wydajności podczas uaktualniania do nowszych wersji programu SQL Server, zgodnie z opisem w sekcji Zapewnianie stabilności wydajności podczas uaktualniania do nowszychscenariuszy użycia magazynu zapytań programu SQL Server. Jednak QTA nie wraca do wcześniej znanego dobrego planu, co widać w ostatnim kroku zalecanego procesu. Zamiast tego QTA śledzi wszelkie regresje znalezione w widoku Query Store Regressed Queries i przechodzi przez możliwe permutacje odpowiednich wariantów modelu optymalizatora, aby mógł zostać utworzony nowy, lepszy plan.

Ważny

Funkcja QTA nie generuje obciążenia użytkownika. Jeśli uruchamiasz QTA w środowisku, które nie jest używane przez twoje aplikacje, upewnij się, że nadal możesz wykonać reprezentatywne obciążenie testowe na docelowym silniku bazy danych SQL Server w inny sposób.

Przepływ pracy Asystenta dostrajania zapytań

Punkt początkowy asystenta zapytań zakłada, że baza danych z poprzedniej wersji programu SQL Server jest przenoszona (za pomocą dołącz bazę danych lub RESTORE) do nowszej wersji silnika baz danych programu SQL Server, a poziom zgodności bazy danych przed uaktualnieniem nie zostanie natychmiast zmieniony. QTA przeprowadza przez następujące kroki:

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.
Poproś o uruchomienie wymaganego obciążenia, aby magazyn zapytań mógł zebrać dane bazowe obciążenia (jeśli nie są jeszcze dostępne).
Uaktualnij do docelowego poziomu zgodności bazy danych wybranego przez użytkownika.
Zażądaj, aby zebrano drugi zestaw danych obciążenia w celu porównania i wykrycia regresji.
Iteruj przez wszelkie znalezione regresje na podstawie widoku Magazynu Zapytań (Query Store) Zregresowane Zapytania, eksperymentuj, zbierając statystyki środowiska uruchomieniowego dotyczące możliwych permutacji odpowiednich odmian modelu optymalizatora i mierz wynik.
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.

Na poniższym diagramie pokazano, jak QTA zmienia tylko ostatnie kroki zalecanego przepływu pracy, aby uaktualnić poziom zgodności przy użyciu Query Store, widzianego wcześniej. Zamiast wybierać między aktualnie nieefektywnym planem wykonania a ostatnim znanym dobrym planem wykonania, QTA przedstawia opcje dostrajania specyficzne dla wybranych zapytań, które zostały poddane regresji, aby utworzyć nowy, ulepszony stan z dostrojonymi planami wykonania.

Dostrajanie wewnętrznego obszaru wyszukiwania QTA

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.

QTA skupia się na znanych potencjalnych wzorcach regresji zapytań spowodowanych zmianami w wersjach szacowania kardynalności (SQL Server). Na przykład w przypadku 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 pogorszyły się, aby korzystać z nowszych ulepszeń CE.

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

Niezależność a korelacja: Jeśli założenie niezależności zapewnia lepsze oszacowania dla określonego zapytania, wskazówka USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') zapytania powoduje wygenerowanie planu wykonania przez program SQL Server 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.
Proste zawieranie a podstawowe zawieranie: jeśli inne zawieranie sprzężeń zapewnia lepsze oszacowania dla określonego zapytania, to wskazówka USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') zapytania powoduje, że program SQL Server generuje plan wykonania przy założeniu prostego zawierania zamiast domyślnego założenia podstawowego zawierania. Aby uzyskać więcej informacji, zajrzyj do wskazówek dotyczących zapytań USE HINT i Wersje CE.
Funkcja tabelaryczna z wieloma instrukcjami (MSTVF) z oszacowaniem stałej liczby wierszy wynoszącym 100 wierszy w porównaniu do 1 wiersza: Jeśli domyślne oszacowanie stałej liczby wierszy w przypadku funkcji TVF wynoszące 100 wierszy nie prowadzi do bardziej wydajnego planu niż użycie oszacowania dla 1 wiersza (co odpowiada wartości domyślnej w modelu optymalizatora zapytań CE programu SQL Server 2008 R2 (10.50.x) i wcześniejszych wersjach), 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).

W ostateczności, jeśli wąsko zakreślone wskazówki nie dają wystarczająco dobrych wyników dla kwalifikujących się wzorców zapytań, należy także rozważyć pełne wykorzystanie CE 70, używając wskazówki zapytania USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') do wygenerowania planu wykonania.

Ważny

Każda wskazówka wymusza pewne zachowania, które mogą zostać rozwiązane w przyszłych aktualizacjach programu SQL Server. Zalecamy stosowanie wskazówek tylko wtedy, gdy nie istnieje żadna inna opcja, i zaplanuj ponowne zapoznanie się z kodem sugerowanym przy każdym nowym uaktualnieniu. Poprzez wymuszanie zachowań, możesz uniemożliwiać swoim obciążeniom korzystanie z ulepszeń wprowadzonych w nowszych wersjach programu SQL Server.

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

W programie SQL Server Management Studio otwórz Eksplorator Obiektów i połącz się z silnikiem bazy danych.
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.
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ń). Służy do zaproponowania zalecanych ustawień Query Store, aby umożliwić wstępne zbieranie pełnej bazy wyjściowej. 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 zawierają bieżący stan magazynu zapytań w docelowej bazie danych i 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 nieaktualnej kwerendy wynosi dwukrotność oczekiwanej wartości czasu trwania obciążenia, wyrażonej w dniach. Dzieje się tak, ponieważ magazyn zapytań musi przechowywać informacje na temat obciążenia punktu odniesienia i obciążenia po uaktualnieniu bazy danych.
  Po zakończeniu wybierz pozycję Dalej.
  
  Ważny
  
  Proponowany maksymalny rozmiar to dowolna wartość, która może być odpowiednia dla krótkiego obciążenia. Jednak może to być niewystarczające do przechowywania informacji na temat obciążeń bazowych i po uaktualnieniu bazy danych na potrzeby 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.
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

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.
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 usuwa ż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 do przywrócenia zmian. Zamiast tego wyszukaj przewodniki planu w tabeli systemowej sys.plan_guides i ręcznie usuń je, korzystając z sp_control_plan_guide.
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ę bez zmian.

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 obciążenia sprawdź Zakończono przebieg obciążenia i wybierz Dalej.
  
  Notatka
  
  Okno QTA można zamknąć podczas działania obciążenia. Powracając do sesji, która pozostaje aktywna, można ją wznowić od tego samego kroku, w którym została przerwana.
2. Uaktualnij bazę danych wyświetla monit o uprawnienie do uaktualnienia 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 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ń wartość Zapytania do pokazania, aby ograniczyć liczbę wyświetlanych zapytań. Na kolejność listy ma wpływ Metryka (Czas trwania lub Czas CPU) oraz Agregacja (średnia jest ustawieniem domyślnym). Wybierz również liczbę zapytań do wyświetlenia. Po zakończeniu tego obciążenia sprawdź Zakończono przebieg 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.
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.

Nie można wybrać zapytań z ustawieniem Tunable na False do testowania.

Ważny

Monit informuje, że po przejściu QTA do fazy eksperymentowania powrót do strony Analizy nie jest 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.
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 tekst oświadczenia, które można rozwinąć, wybierając przycisk ....
- Status: wyświetla bieżący status eksperymentu dla zapytania.
- Metryka bazowa: wybrana metryka (czas trwania lub czas procesora) w ms dla zapytania wykonanego w Kroku 2, Podkroku 3, reprezentująca zapytanie z 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.
- % Zmiana: określa zmianę procentową 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.
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 jej przewodnik planu usunięty.

Jeśli w późniejszym terminie konieczne jest wycofanie proponowanej optymalizacji, wybierz odpowiednie zapytanie i wybierz pozycję 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 usuwa żadnych wcześniej wdrożonych poradników planu. Jeśli usuniesz sesję, w której wdrożono przewodniki planu, nie możesz użyć QTA do przywrócenia zmian. Zamiast tego wyszukaj przewodniki planu w tabeli systemowej sys.plan_guides i ręcznie usuń je, korzystając z sp_control_plan_guide.

Uprawnienia

Wymaga członkostwa w roli db_owner.

Sprzężenie zwrotne

Czy ta strona była pomocna?

Last updated on 2025-10-10