Automatyczne dostrajanie

Dotyczy: SQL Server 2017 (14.x) i nowsze wersjeAzure SQL DatabaseAzure SQL Managed Instancebaza danych SQL w Microsoft Fabric

Automatyczne dostrajanie to funkcja bazy danych, która zapewnia wgląd w potencjalne problemy z wydajnością zapytań, polecanie rozwiązań i automatyczne rozwiązywanie zidentyfikowanych problemów.

Automatyczne dostrajanie wprowadzone w programie SQL Server 2017 (14.x) powiadamia o każdym wykryciu potencjalnego problemu z wydajnością i umożliwia zastosowanie akcji naprawczych lub umożliwia aparatowi bazy danych automatyczne rozwiązywanie problemów z wydajnością. Automatyczne dostrajanie programu SQL Server identyfikuje i rozwiązuje problemy z wydajnością spowodowane regresją wyboru planu wykonywania zapytań. Automatyczne dostrajanie w usłudze Azure SQL Database i bazie danych SQL w usłudze Microsoft Fabric powoduje również utworzenie niezbędnych indeksów i porzucenie nieużywanych indeksów. Aby uzyskać więcej informacji na temat planów wykonywania zapytań, zobacz Plany wykonywania.

Silnik bazy danych SQL Server monitoruje zapytania wykonywane w bazie danych i automatycznie poprawia wydajność pracy. Aparat bazy danych ma wbudowany mechanizm analizy, który może automatycznie dostroić i poprawić wydajność zapytań, dynamicznie dostosowując bazę danych do obciążenia. Dostępne są dwie funkcje automatycznego dostrajania:

  • Automatyczna korekta planu identyfikuje problematyczne plany wykonywania zapytań, takie jak kwestie związane z wrażliwością parametrów lub sniffingiem parametrów, i usuwa problemy z wydajnością dotyczące planu wykonywania zapytań, przywracając ostatni znany stabilny plan przed wystąpieniem regresji. Dotyczy: SQL Server (począwszy od programu SQL Server 2017 (14.x)), usługi Azure SQL Database i bazy danych SQL w usłudze Microsoft Fabric oraz usługi Azure SQL Managed Instance

  • Automatyczne zarządzanie indeksami identyfikuje indeksy, które powinny zostać dodane w bazie danych, oraz indeksy, które powinny zostać usunięte. Dotyczy: Usługa Azure SQL Database i baza danych SQL w usłudze Microsoft Fabric

Note

W tym artykule funkcje i zachowania usługi Azure SQL Database dotyczą również bazy danych SQL w usłudze Microsoft Fabric.

Dlaczego automatyczne dostrajanie?

Trzy główne zadania administracji klasycznej bazy danych to monitorowanie obciążenia, identyfikowanie krytycznych zapytań Transact-SQL oraz identyfikowanie indeksów, które należy dodać w celu zwiększenia wydajności lub indeksów, które są rzadko używane i mogą zostać usunięte w celu zwiększenia wydajności. Aparat bazy danych programu SQL Server zapewnia szczegółowy wgląd w zapytania i indeksy, które należy monitorować. Jednak ciągłe monitorowanie bazy danych jest trudnym i żmudnym zadaniem, zwłaszcza w przypadku obsługi wielu baz danych. Zarządzanie ogromną liczbą baz danych może być niemożliwe do wydajnego wykonania. Zamiast ręcznie monitorować i dostrajać bazę danych, możesz rozważyć delegowanie niektórych akcji monitorowania i dostrajania do aparatu bazy danych przy użyciu funkcji automatycznego dostrajania.

Jak działa automatyczne dostrajanie?

Automatyczne dostrajanie to ciągły proces monitorowania i analizy, który stale uczy się o cechach obciążenia i identyfikuje potencjalne problemy i ulepszenia.

Proces automatycznego dostrajania.

Ten proces umożliwia dynamiczne dostosowanie bazy danych do obciążenia przez znalezienie indeksów i planów, które mogą poprawić wydajność obciążeń i jakie indeksy wpływają na obciążenia. Na podstawie tych ustaleń automatyczne dostrajanie stosuje akcje dostrajania, które zwiększają wydajność obciążenia. Ponadto automatyczne dostrajanie stale monitoruje wydajność bazy danych po zaimplementowaniu wszelkich zmian w celu zapewnienia, że zwiększa wydajność obciążenia. Każda akcja, która nie poprawiła wydajności, zostanie automatycznie przywrócona. Ten proces weryfikacji jest kluczową funkcją, która zapewnia, że wszelkie zmiany wprowadzone przez automatyczne dostrajanie nie zmniejszają ogólnej wydajności obciążenia.

Automatyczna korekta planu

Automatyczna korekta planu to funkcja automatycznego dostrajania, która identyfikuje regresję wyboru planu wykonania i automatycznie rozwiązuje problem, wymuszając ostatni znany dobry plan. Aby uzyskać więcej informacji na temat planów wykonywania zapytań i optymalizatora zapytań, zobacz Przewodnik po architekturze przetwarzania zapytań.

Important

Automatyczna korekta planu zależy od włączenia magazynu zapytań w bazie danych na potrzeby śledzenia obciążeń.

Co to jest regresja wyboru planu wykonania?

Silnik bazy danych programu SQL Server może używać różnych planów wykonywania do wykonywania zapytań Transact-SQL. Plany zapytań zależą od statystyk, indeksów i innych czynników. Optymalny plan, który powinien zostać użyty do wykonania zapytania Transact-SQL, może ulec zmianie w czasie w zależności od zmian w tych czynnikach. W niektórych przypadkach nowy plan może nie być lepszy niż poprzedni, a nowy plan może spowodować regresję wydajności, na przykład problem związany z czułością parametrów lub wykrywaniem parametrów.

Regresja wyboru planu wykonywania zapytań.

Za każdym razem, gdy zauważysz, że wystąpiła regresja wyboru planu, należy znaleźć poprzedni dobry plan i wymusić jego zastosowanie zamiast bieżącego. Można to zrobić przy użyciu sp_query_store_force_plan procedury. Silnik bazy danych w programie SQL Server 2017 (14.x) zawiera informacje o zregresowanych planach i zalecanych działaniach naprawczych. Ponadto aparat bazy danych umożliwia w pełni zautomatyzowanie tego procesu i umożliwienie aparatowi bazy danych rozwiązania wszelkich problemów związanych ze zmianą planu.

Important

Automatyczna korekta planu powinna być używana w zakresie uaktualniania poziomu zgodności bazy danych po przechwyceniu punktu odniesienia, aby automatycznie ograniczyć ryzyko uaktualniania obciążenia. Aby uzyskać więcej informacji na temat tego przypadku użycia, zobacz Zapewnianie stabilności wydajności podczas uaktualniania do nowszego programu SQL Server.

Automatyczna korekta wyboru planu

Aparat bazy danych może automatycznie przełączyć się do ostatniego znanego dobrego planu za każdym razem, gdy zostanie wykryta regresja wyboru planu.

Korekta wyboru planu wykonywania zapytań.

Aparat bazy danych automatycznie wykrywa wszelkie potencjalne regresje wyboru planu, w tym plan, który powinien być używany zamiast niewłaściwego planu. Wynikowy plan wykonania wymuszony przez automatyczną korektę planu będzie taki sam lub podobny do ostatniego znanego dobrego planu. Ponieważ wynikowy plan może nie być identyczny z ostatnim dobrym planem, wydajność wymuszonego planu może się różnić. W rzadkich przypadkach różnica wydajności może być znacząca i ujemna; w takim przypadku automatyczna korekta planu automatycznie przestanie podejmować próby wymuszenia planu zastępczego.

Gdy aparat bazy danych stosuje ostatni znany dobry plan przed wystąpieniem regresji, automatycznie monitoruje wydajność wymuszonego planu. Jeśli plan wymuszony nie jest lepszy niż zregresowany plan, nowy plan zostanie niewymuszony, a silnik bazy danych skompiluje nowy plan. Jeśli aparat bazy danych sprawdzi, czy wymuszony plan jest lepszy niż plan regresji, plan wymuszony zostanie zachowany. Zostanie zachowany do momentu ponownego kompilowania (na przykład przy następnej aktualizacji statystyk lub zmianie schematu). Aby uzyskać więcej informacji na temat wymuszania planu i typów planów, które można wymusić, zobacz Ograniczenia w wymuszaniu planów.

Note

Jeśli wystąpienie programu SQL Server zostanie uruchomione ponownie przed zweryfikowaniem planu wymuszania działania, ten plan zostanie automatycznie niewymuszony. W przeciwnym razie wymuszanie planu jest utrwalane podczas ponownego uruchamiania programu SQL Server.

Włącz automatyczną korektę wyboru planu

Możesz włączyć automatyczne dostrajanie dla bazy danych i określić, że ostatni dobry plan powinien zostać wymuszony za każdym razem, gdy zostanie wykryta regresja zmian planu. Automatyczne dostrajanie jest włączone przy użyciu następującego polecenia:

ALTER DATABASE <yourDatabase>
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

Po włączeniu tej opcji aparat bazy danych automatycznie wymusi każde zalecenie, w którym szacowany zysk wydajności CPU jest większy niż 10 sekund lub liczba błędów w nowym planie jest większa niż liczba błędów w zalecanym planie i sprawdza, czy plan wymuszony jest lepszy niż bieżący.

Aby włączyć automatyczne dostrajanie w usługach Azure SQL Database i Azure SQL Managed Instance, zobacz Włączanie automatycznego dostrajania w usłudze Azure SQL Database przy użyciu witryny Azure Portal.

Alternatywa — korekta wyboru planu ręcznego

Bez automatycznego dostrajania użytkownicy muszą okresowo monitorować system i szukać kwerend, które wykazują regresję. Jeśli jakikolwiek plan uległ regresji, użytkownik powinien znaleźć poprzedni dobry plan i wymusić go zamiast bieżącego przy użyciu sp_query_store_force_plan procedury. Najlepszym rozwiązaniem byłoby wymusić ostatni znany dobry plan, ponieważ starsze plany mogą być nieprawidłowe z powodu zmian statystyk lub indeksów. Użytkownik, który wymusza ostatni znany dobry plan, powinien monitorować wydajność zapytania wykonywanego przy użyciu wymuszonego planu i sprawdzić, czy wymuszony plan działa zgodnie z oczekiwaniami. W zależności od wyników monitorowania i analizy plan powinien zostać wymuszony lub użytkownik powinien znaleźć inny sposób optymalizacji zapytania, na przykład ponowne zapisywanie go. Ręcznie wymuszone plany nie powinny być wymuszane na zawsze, ponieważ aparat bazy danych powinien mieć możliwość stosowania optymalnych planów. Użytkownik lub administrator bazy danych powinien ostatecznie wyłączyć wymuszanie planu przy użyciu sp_query_store_unforce_plan procedury i pozwolić silnikowi bazy danych znaleźć optymalny plan.

Tip

Alternatywnie możesz użyć widoku Magazyn zapytań z wymuszonymi planami, aby zlokalizować i odwymusić plany.

Program SQL Server udostępnia wszystkie niezbędne widoki i procedury wymagane do monitorowania wydajności i rozwiązywania problemów w magazynie zapytań.

W programie SQL Server 2016 (13.x) można znaleźć regresje wyboru planu przy użyciu widoków systemowych magazynu zapytań. Począwszy od SQL Server 2017 (14.x), mechanizm bazy danych wykrywa i pokazuje potencjalne regresje wyboru planu oraz zalecane działania, które należy zastosować w widoku DMV sys.dm_db_tuning_recommendations (Transact-SQL). DMV pokazuje informacje o problemie, znaczeniu problemu i szczegóły, takie jak zidentyfikowane zapytanie, identyfikator planu, który uległ regresji, identyfikator planu, który został użyty jako punkt odniesienia do celów porównania, oraz instrukcja Transact-SQL, którą można wykonać w celu rozwiązania problemu.

typ description data/godzina wynik details ...
FORCE_LAST_GOOD_PLAN Czas procesora CPU zmienił się z 4 ms na 14 ms 3/17/2017 83 queryId recommendedPlanId regressedPlanId T-SQL
FORCE_LAST_GOOD_PLAN Czas procesora CPU zmienił się z 37 ms na 84 ms 3/16/2017 26 queryId recommendedPlanId regressedPlanId T-SQL

Niektóre kolumny z tego widoku są opisane na poniższej liście:

  • Typ zalecanej akcji FORCE_LAST_GOOD_PLAN.
  • Opis zawierający informacje, dlaczego aparat bazy danych uważa, że ta zmiana planu jest potencjalną regresją wydajności.
  • Data/godzina wykrycia potencjalnej regresji.
  • Wynik tej rekomendacji.
  • Szczegółowe informacje o problemach, takich jak identyfikator wykrytego planu, identyfikator planu regresji, identyfikator planu, który powinien zostać zmuszony do rozwiązania problemu, Transact-SQL skrypt, który może zostać zastosowany w celu rozwiązania problemu itp. Szczegóły są przechowywane w formacie JSON.

Użyj następującego zapytania, aby uzyskać skrypt, który rozwiązuje problem i dodatkowe informacje na temat szacowanego zysku:

SELECT reason, score,
      script = JSON_VALUE(details, '$.implementationDetails.script'),
      planForceDetails.*,
      estimated_gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount)
                  * (regressedPlanCpuTimeAverage - recommendedPlanCpuTimeAverage)/1000000,
      error_prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES','NO')
FROM sys.dm_db_tuning_recommendations
CROSS APPLY OPENJSON (Details, '$.planForceDetails')
    WITH (  [query_id] int '$.queryId',
            regressedPlanId int '$.regressedPlanId',
            recommendedPlanId int '$.recommendedPlanId',
            regressedPlanErrorCount int,
            recommendedPlanErrorCount int,
            regressedPlanExecutionCount int,
            regressedPlanCpuTimeAverage float,
            recommendedPlanExecutionCount int,
            recommendedPlanCpuTimeAverage float
          ) AS planForceDetails;

Oto zestaw wyników.

reason wynik skrypt query_id bieżący identyfikator planu zalecane plan_id szacowany_zysk podatny na błędy
Czas procesora CPU zmienił się z 3 ms na 46 ms 36 EXEC sp_query_store_force_plan 12, 17; 12 28 17 11.59 0

Kolumna estimated_gain reprezentuje szacowaną liczbę sekund, która zostanie zapisana, jeśli zalecany plan będzie używany do wykonywania zapytań zamiast bieżącego planu. Zalecany plan działań powinien być stosowany zamiast bieżącego planu, jeśli przyrost czasu wynosi więcej niż 10 sekund. Jeśli w bieżącym planie występuje więcej błędów (na przykład przekroczenia limitu czasu lub przerwanych wykonań) w bieżącym planie niż w zalecanym planie, kolumna error_prone zostanie ustawiona na wartość YES. Plan podatny na błędy jest kolejnym powodem, dla którego zalecany plan powinien zostać wymuszony zamiast bieżącego planu.

Mimo że aparat bazy danych udostępnia wszystkie informacje wymagane do zidentyfikowania regresji wyboru planu, ciągłe monitorowanie i rozwiązywanie problemów z wydajnością może stać się żmudnym procesem. Automatyczne dostrajanie znacznie ułatwia ten proces.

Note

Dane w dynamicznym widoku zarządzania sys.dm_db_tuning_recommendations nie są zachowywane po ponownym uruchomieniu silnika bazy danych. Użyj kolumny sqlserver_start_time w sys.dm_os_sys_info , aby znaleźć ostatni czas uruchamiania aparatu bazy danych.

Automatyczne zarządzanie indeksami

W usłudze Azure SQL Database zarządzanie indeksami jest łatwe, ponieważ usługa Azure SQL Database uczy się o obciążeniu i zapewnia, że dane są zawsze indeksowane optymalnie. Odpowiedni projekt indeksu ma kluczowe znaczenie dla optymalnej wydajności obciążenia, a automatyczne zarządzanie indeksami może pomóc w optymalizacji indeksów. Automatyczne zarządzanie indeksami może rozwiązać problemy z wydajnością niepoprawnie indeksowanych baz danych lub obsługiwać i ulepszać indeksy w istniejącym schemacie bazy danych. Automatyczne dostrajanie w usłudze Azure SQL Database wykonuje następujące akcje:

  • Identyfikuje indeksy, które mogą zwiększyć wydajność zapytań Transact-SQL odczytujących dane z tabel.
  • Identyfikuje nadmiarowe indeksy lub indeksy, które nie były używane w dłuższym okresie czasu, które można usunąć. Usunięcie niepotrzebnych indeksów zwiększa wydajność zapytań aktualizujących dane w tabelach.

Dlaczego potrzebujesz zarządzania indeksami?

Indeksy przyspieszają niektóre zapytania odczytujące dane z tabel, jednak mogą spowolnić zapytania aktualizujące dane. Należy dokładnie przeanalizować, kiedy utworzyć indeks i jakie kolumny należy uwzględnić w indeksie. Niektóre indeksy mogą nie być potrzebne po pewnym czasie. W związku z tym należy okresowo identyfikować i usuwać te indeksy, które nie przynoszą żadnych korzyści. Jeśli zignorujesz nieużywane indeksy, wydajność zapytań aktualizujących dane zostanie zmniejszona bez żadnych korzyści dla zapytań odczytujących dane. Nieużywane indeksy wpływają również na ogólną wydajność systemu, ponieważ dodatkowe aktualizacje wymagają niepotrzebnego rejestrowania.

Znalezienie optymalnego zestawu indeksów zwiększających wydajność zapytań odczytujących dane z tabel i mających minimalny wpływ na aktualizacje może wymagać ciągłej i złożonej analizy.

Usługa Azure SQL Database używa wbudowanej inteligencji i zaawansowanych reguł, które analizują Twoje zapytania, identyfikują optymalne indeksy dla bieżących obciążeń oraz indeksy, które mogą wymagać usunięcia. Usługa Azure SQL Database zapewnia minimalny wymagany zestaw indeksów, które optymalizują zapytania odczytujące dane, przy minimalnym wpływie na inne zapytania.

Automatyczne zarządzanie indeksami

Oprócz wykrywania usługa Azure SQL Database może automatycznie stosować zidentyfikowane rekomendacje. Jeśli okaże się, że wbudowane reguły zwiększają wydajność bazy danych, możesz zezwolić usłudze Azure SQL Database na automatyczne zarządzanie indeksami.

Gdy usługa Azure SQL Database stosuje zalecenie CREATE INDEX lub DROP INDEX, automatycznie monitoruje wydajność zapytań, na które ma wpływ indeks. Nowy indeks zostanie zachowany tylko wtedy, gdy wydajność zapytań, których dotyczy problem, zostanie zwiększona. Porzucany indeks zostanie automatycznie utworzony ponownie, jeśli niektóre zapytania działają wolniej z powodu braku indeksu.

Zagadnienia dotyczące automatycznego zarządzania indeksami

Akcje wymagane do utworzenia niezbędnych indeksów w usłudze Azure SQL Database mogą zużywać zasoby i tymczasowo wpływać na wydajność obciążenia. Aby zminimalizować wpływ tworzenia indeksu na wydajność obciążenia, usługa Azure SQL Database znajdzie odpowiedni przedział czasu dla każdej operacji zarządzania indeksami. Akcja dostrajania jest odkładana, jeśli baza danych potrzebuje zasobów do wykonania obciążenia i jest uruchamiana ponownie, gdy baza danych ma wystarczającą ilość nieużywanych zasobów, których można użyć do zadania konserwacji. Jedną z ważnych funkcji automatycznego zarządzania indeksami jest weryfikacja akcji. Gdy usługa Azure SQL Database tworzy lub odrzuca indeks, proces monitorowania analizuje wydajność obciążenia, aby sprawdzić, czy akcja poprawiła ogólną wydajność. Jeśli akcja nie przynosi znacznej poprawy, zostanie natychmiast cofnięta. Dzięki temu usługa Azure SQL Database zapewnia, że akcje automatycznego dostrajania nie wpływają negatywnie na wydajność obciążenia. Indeksy utworzone przez automatyczną optymalizację są przejrzyste dla operacji konserwacyjnych w podstawowym schemacie. Zmiany schematu, takie jak upuszczanie lub zmienianie nazw kolumn, nie są blokowane przez obecność automatycznie utworzonych indeksów. Indeksy tworzone automatycznie przez usługę Azure SQL Database są natychmiast porzucane po usunięciu powiązanej tabeli lub kolumn.

Alternatywa — ręczne zarządzanie indeksami

Bez automatycznego zarządzania indeksami użytkownik lub administrator bazy danych musi ręcznie wykonać zapytanie dotyczące widoku sys.dm_db_missing_index_details (Transact-SQL) lub użyć raportu pulpitu nawigacyjnego wydajności w programie Management Studio, aby znaleźć indeksy, które mogą poprawić wydajność, utworzyć indeksy przy użyciu szczegółów podanych w tym widoku i ręcznie monitorować wydajność zapytania. Aby znaleźć indeksy, które powinny zostać usunięte, użytkownicy powinni monitorować statystyki użycia operacyjnego indeksów w celu znalezienia rzadko używanych indeksów.

Usługa Azure SQL Database upraszcza ten proces. Usługa Azure SQL Database analizuje obciążenie, identyfikuje zapytania, które mogą być wykonywane szybciej przy użyciu nowego indeksu i identyfikuje nieużywane lub zduplikowane indeksy. Aby uzyskać więcej informacji na temat identyfikacji indeksów, które należy zmienić, zobacz Znajdowanie zaleceń dotyczących indeksów w witrynie Azure Portal.

Dalsze kroki

  • Last updated on