Najlepsze rozwiązania dotyczące zarządzania magazynem zapytań

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

W tym artykule opisano zarządzanie magazynem zapytań programu SQL Server i otaczającymi funkcjami.

• Aby uzyskać więcej informacji na temat konfigurowania magazynu zapytań i administrowania nimi, zobacz Monitorowanie wydajności przy użyciu magazynu zapytań.

Notatka

W programie SQL Server 2022 (16.x) magazyn zapytań jest teraz domyślnie włączony dla wszystkich nowo utworzonych baz danych programu SQL Server, aby ułatwić lepsze śledzenie historii wydajności, rozwiązywanie problemów związanych z planem zapytań i włączanie nowych możliwości procesora zapytań.

Domyślne ustawienia Query Store w Azure SQL Database

W tej sekcji opisano optymalne wartości domyślne konfiguracji w usłudze Azure SQL Database, które zostały zaprojektowane w celu zapewnienia niezawodnej operacji magazynu zapytań i funkcji zależnych. Domyślna konfiguracja jest zoptymalizowana pod kątem ciągłego zbierania danych, czyli minimalnego czasu spędzonego w stanach OFF/READ_ONLY. Aby uzyskać więcej informacji o wszystkich dostępnych opcjach Query Store, zobacz ALTER DATABASE SET options (Transact-SQL).

Konfiguracja	Opis	Domyślny	Komentarz
MAX_STORAGE_SIZE_MB	Określa limit przestrzeni danych, którą magazyn zapytań może przejąć wewnątrz bazy danych klienta	100 przed programem SQL Server 2019 (15.x) 1000 począwszy od programu SQL Server 2019 (15.x)	Wymuszane w przypadku nowych baz danych
DŁUGOŚĆ_PRZEDZIAŁU_MINUTY	Definiuje okres czasu, w którym zebrane statystyki dotyczące czasu działania dla planów zapytań są agregowane i utrwalane. Każdy aktywny plan zapytania ma co najwyżej jeden wiersz przez okres czasu określony w ramach tej konfiguracji.	60	Wymuszone dla nowych baz danych
STALE_QUERY_THRESHOLD_DAYS (Próg dni dla nieaktualnych zapytań)	Zasady oczyszczania oparte na czasie, które kontrolują okres przechowywania utrwalonych statystyk środowiska uruchomieniowego i nieaktywnych zapytań	30	Wymuszane dla nowych baz danych i baz danych z poprzednią wartością domyślną (367)
Tryb czyszczenia w oparciu o rozmiar	Określa, czy automatyczne czyszczenie danych odbywa się, gdy rozmiar danych magazynu zapytań zbliża się do limitu	AUTOMATYCZNIE	Wymuszane dla wszystkich baz danych
TRYB_PRZECHWYTYWANIA_ZAPYTANIA	Określa, czy śledzone są wszystkie zapytania, czy tylko podzbiór zapytań	AUTOMATYCZNIE	Wymuszane dla wszystkich baz danych
DATA_FLUSH_INTERVAL_SECONDS	Określa maksymalny okres, w którym przechwycone statystyki środowiska uruchomieniowego są przechowywane w pamięci przed opróżnieniem na dysk	900	Wymuszane dla nowych baz danych

Ważny

Te wartości domyślne są automatycznie stosowane na ostatnim etapie aktywacji magazynu zapytań w usłudze Azure SQL Database. Po jej włączeniu usługa Azure SQL Database nie zmieni wartości konfiguracji ustawionych przez klientów, chyba że negatywnie wpłynie na podstawowe obciążenie lub niezawodne operacje magazynu zapytań.

Notatka

Nie można wyłączyć Query Store w usłudze Azure SQL Database: pojedynczej bazie danych i puli elastycznej. Wykonanie ALTER DATABASE [database] SET QUERY_STORE = OFF zwróci ostrzeżenie 'QUERY_STORE=OFF' is not supported in this version of SQL Server.

Jeśli chcesz zachować ustawienia niestandardowe, użyj ALTER DATABASE z opcjami magazynu zapytań, aby przywrócić konfigurację do poprzedniego stanu. Zapoznaj się z Najlepszymi Praktykami w Magazynie Zapytań, aby dowiedzieć się, jak wybrać optymalne parametry konfiguracji.

Ustawianie optymalnego trybu przechwytywania magazynu zapytań

Zachowaj najbardziej odpowiednie dane w Query Store. W poniższej tabeli opisano typowe scenariusze dla każdego Trybu Przechwytywania Query Store:

Tryb przechwytywania zapytań w Query Store	Scenariusz
Wszystkie	Dokładnie przeanalizuj obciążenie pod względem kształtów wszystkich zapytań oraz ich częstotliwości wykonywania i innych statystyk. Zidentyfikuj nowe zapytania w twoim obciążeniu. Wykryj, czy zapytania ad hoc są używane do identyfikowania możliwości parametryzacji użytkownika lub automatycznej parametryzacji. Uwaga: jest to domyślny tryb przechwytywania w programie SQL Server 2016 (13.x) i programie SQL Server 2017 (14.x).
Auto	Skoncentruj uwagę na odpowiednich i praktycznych zapytaniach. Przykładem są zapytania, które są wykonywane regularnie lub które mają znaczne zużycie zasobów. Uwaga: w programie SQL Server 2019 (15.x) i nowszych wersjach jest to domyślny tryb przechwytywania.
Brak	Zestaw zapytań, który chcesz monitorować w środowisku uruchomieniowym, został już przechwycony i chcesz wyeliminować rozproszenie uwagi, które mogą wprowadzać inne zapytania. Żadne nie jest odpowiednie do środowisk testowych i porównawczych. Żadna z nich nie jest również odpowiednia dla dostawców oprogramowania, którzy wysyłają konfigurację magazynu zapytań skonfigurowaną do monitorowania obciążenia aplikacji. Nie należy używać żadnej z ostrożnością, ponieważ możesz przegapić możliwość śledzenia i optymalizowania ważnych nowych zapytań. Unikaj używania opcji Brak, chyba że masz określony scenariusz, który go wymaga.
Niestandardowe	Program SQL Server 2019 (15.x) wprowadził niestandardowy tryb przechwytywania w ramach polecenia ALTER DATABASE ... SET QUERY_STORE. Chociaż "Automatyczny" jest ustawieniem domyślnym i zalecanym, jeśli nadal istnieją jakiekolwiek obawy dotyczące dodatkowego obciążenia, które może wprowadzić magazyn zapytań, administratorzy baz danych mogą używać niestandardowych zasad przechwytywania, aby dalej dostosowywać zachowanie przechwytywania magazynu zapytań. Aby uzyskać więcej informacji i zaleceń, zapoznaj się z sekcją Niestandardowe zasady przechwytywania dalej w tym artykule. Aby uzyskać więcej informacji na temat tej składni, zobacz ALTER DATABASE SET Options.

Notatka

Kursory, zapytania wewnątrz procedur składowanych i natywnie skompilowane zapytania są zawsze przechwytywane, gdy Tryb przechwytywania zapytań w magazynie jest ustawiony na Wszystkie, Autolub Niestandardowe. Aby przechwytywać natywnie skompilowane zapytania, włącz zbieranie statystyk dla poszczególnych zapytań przy użyciu sys.sp_xtp_control_query_exec_stats.

Przechowywanie najbardziej odpowiednich danych w magazynie zapytań

Skonfiguruj magazyn zapytań tak, aby zawierał tylko odpowiednie dane, tak aby był uruchamiany w sposób ciągły i zapewnia doskonałe środowisko rozwiązywania problemów z minimalnym wpływem na regularne obciążenie.

Poniższa tabela zawiera najlepsze rozwiązania:

Najlepsze rozwiązanie	Ustawienie
Ogranicz zachowywane dane historyczne.	Skonfiguruj politykę czasową, aby aktywować automatyczne czyszczenie.
Odfiltruj nieistotne zapytania.	Skonfiguruj tryb przechwytywania magazynu zapytań na Auto.
Usuń mniej istotne zapytania po osiągnięciu maksymalnego rozmiaru.	Aktywuj politykę czyszczenia bazującą na rozmiarze.

Niestandardowe zasady przechwytywania

Po włączeniu trybu niestandardowego przechwytywania magazynu zapytań, dodatkowe konfiguracje magazynu zapytań są dostępne w ramach nowego ustawienia polityki przechwytywania, co pozwala precyzyjniej dostosować zbieranie danych na określonym serwerze.

Nowe ustawienia niestandardowe definiują, co dzieje się podczas wewnętrznego progu czasowego polityki przechwytywania. Jest to okres czasu, w którym oceniane są konfigurowalne warunki i, jeśli którykolwiek z nich jest spełniony, zapytanie może zostać przechwycone przez Query Store.

Tryb przechwytywania w Magazynie Zapytań określa zasady przechwytywania zapytań dla Magazynu Zapytań.

• Wszystkie: przechwytuje wszystkie zapytania. Ta opcja jest domyślna w programach SQL Server 2016 (13.x) i SQL Server 2017 (14.x).
• auto: rzadko tworzone zapytania i zapytania z nieistotnym czasem kompilowania i wykonywania są ignorowane. Progi liczby wykonań, kompilacji i czasu trwania wykonywania są określane wewnętrznie. Począwszy od programu SQL Server 2019 (15.x), jest to opcja domyślna.
• None: Magazyn zapytań zatrzymuje przechwytywanie nowych zapytań.
• niestandardowy: umożliwia dodatkową kontrolę i dokładne dostosowanie zasad zbierania danych. Nowe ustawienia niestandardowe definiują, co dzieje się podczas wewnętrznego progu czasowego polityki przechwytywania. Jest to okres, w którym oceniane są konfigurowalne warunki i, jeśli którykolwiek jest prawdziwy, zapytanie kwalifikuje się do przechwycenia przez „Query Store”.

Dostosowanie właściwej niestandardowej polityki przechwytywania dla danego środowiska należy rozważyć, gdy:

• Baza danych jest bardzo duża.
• Baza danych ma dużą liczbę unikatowych zapytań ad hoc.
• Baza danych ma określone ograniczenia rozmiaru lub wzrostu.

SSMS

Pobierz najnowszą wersję programu SQL Server Management Studio (SSMS)

Aby wyświetlić bieżące ustawienia w programie Management Studio:

1. W Eksploratorze obiektów programu SQL Server Management Studio kliknij prawym przyciskiem myszy bazę danych.
2. Wybierz właściwości .
3. Wybierz opcję Magazyn zapytań. Na stronie Magazyn Zapytan sprawdź, czy Tryb operacji (żądany) jest Odczyt i zapis.
4. Zmień tryb przechwytywania magazynu zapytań na niestandardowy.
5. Zwróć uwagę, że cztery pola zasad przechwytywania w zasady przechwytywania magazynu zapytań są teraz włączone i konfigurowalne.

języka T-SQL

Użyj polecenia ALTER DATABASE . USTAW syntaksę do ustawienia QUERY_CAPTURE_MODE = CUSTOM, a następnie określ opcje dla QUERY_CAPTURE_POLICY. Aby uzyskać szczegółowe informacje na temat opcji zasad przechwytywania niestandardowego, zobacz następujące przykłady języka T-SQL i ALTER DATABASE SET (Transact-SQL).

Przykładowe zasady przechwytywania niestandardowego

Poniższy przykład ustawia QUERY_CAPTURE_MODE na auto i ustawia niestandardowy tryb przechwytywania. Każde z poniższych poleceń ustawia niestandardowe zasady przechwytywania na wartość domyślną w programie SQL Server 2022 (16.x). Rozważ dostosowanie tych wartości, aby zmniejszyć liczbę przechwyconych zapytań, a tym samym zmniejszyć ślad na dysku magazynu zapytań. Zaleca się stopniowe zmienianie tych wartości przez małe przyrosty.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

Poniższe przykładowe zapytanie zmienia istniejący magazyn zapytań, aby użyć niestandardowych zasad przechwytywania, które zastępują ustawienia domyślne dla EXECUTION_COUNT i TOTAL_COMPILE_CPU_TIME_MS.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        EXECUTION_COUNT = 100,
        TOTAL_COMPILE_CPU_TIME_MS = 10000
      )
    );

Maksymalny rozmiar magazynu zapytań

Domyślna maksymalna wartość rozmiaru magazynu zapytań wynosi 1000 MB, począwszy od programu SQL Server 2019 (15.x). W poprzednich wersjach wartość domyślna to 100 MB. Zwiększenie maksymalnego limitu rozmiaru magazynu zapytań jest odpowiednie w zajętej bazie danych z wieloma unikatowymi planami zapytań. Dostosowanie zasad przechwytywania (zobacz poprzednią sekcję) jest ważniejsze, aby ograniczyć rozmiar magazynu zapytań na dysku i zapobiec wejściu magazynu zapytań w tryb READ_ONLY. Magazyn zapytań zbiera zapytania, plany wykonywania i statystyki, ale jego rozmiar w bazie danych rośnie do momentu osiągnięcia tego limitu. W takim przypadku magazyn zapytań automatycznie zmienia tryb działania na READ_ONLY i zatrzymuje zbieranie nowych danych, co oznacza, że analiza wydajności nie jest już dokładna.

• W programach SQL Server i Azure SQL Managed Instance limit MAX_STORAGE_SIZE_MB nie jest ściśle wymuszany.
• W usłudze Azure SQL Database maksymalna dozwolona wartość MAX_STORAGE_SIZE_MB wynosi 10 240 MB.

Rozmiar magazynu jest sprawdzany tylko wtedy, gdy magazyn zapytań zapisuje dane na dysku. Ten interwał jest ustawiany przez opcję DATA_FLUSH_INTERVAL_SECONDS lub opcję okna dialogowego Magazyn zapytań programu Management Studio Interwał opróżniania danych.

• Wartość domyślna interwału to 900 sekund (lub 15 minut).
• Jeśli Query Store przekroczy limit MAX_STORAGE_SIZE_MB pomiędzy kolejnymi sprawdzaniami rozmiaru magazynu, przechodzi w tryb tylko do odczytu.
• Jeśli SIZE_BASED_CLEANUP_MODE jest włączony, mechanizm oczyszczania w celu wymuszenia limitu MAX_STORAGE_SIZE_MB również zostaje uruchomiony.
  • Gdy zostanie zwolniona wystarczająca ilość miejsca, tryb magazynu zapytań automatycznie przełączy się z powrotem na tryb READ_WRITE.

Aby uzyskać dalsze informacje, zobacz ALTER DATABASE SET OPTION MAX_STORAGE_SIZE_MB.

Interwał opróżniania danych (w minutach)

Interwał opróżniania danych określa, jak często zbierane statystyki środowiska uruchomieniowego są zapisywane na dysk. W programie SQL Server Management Studio wartość jest wyrażona w minutach, ale w Transact-SQL jest wyrażona w sekundach. Wartość domyślna to 15 minut (900 sekund).

• Zwiększenie interwału opróżniania danych może zmniejszyć ogólny wpływ operacji we/wy magazynu zapytań, ale obciążenie we/wy magazynu może być bardziej spiky, z mniejszym, ale cięższym wpływem na wykorzystanie dysku. Rozważ użycie wyższej wartości, jeśli obciążenie nie generuje dużej liczby różnych zapytań i planów lub jeśli możesz wytrzymać dłuższy czas utrwalania danych przed zamknięciem bazy danych.
• Zmniejszenie interwału opróżniania danych zmniejsza ilość danych magazynu zapytań, które zostaną utracone w przypadku zamknięcia, utraty zasilania lub przejścia w tryb failover. Może również złagodzić wpływ operacji we/wy magazynu zapytań, zapisując na dysku częściej, ale z mniejszymi danymi.

Uwaga

Użycie flagi śledzenia 7745 uniemożliwia zapisywanie danych magazynu zapytań na dysku w przypadku awaryjnego przełączenia (failover) lub polecenia zamknięcia. Aby uzyskać więcej informacji, zobacz Używanie magazynu zapytań na serwerach o znaczeniu krytycznym.

Modyfikowanie domyślnych ustawień magazynu zapytań

Skonfiguruj magazyn zapytań na podstawie obciążenia i wymagań dotyczących rozwiązywania problemów z wydajnością. Parametry domyślne są wystarczająco dobre, aby rozpocząć, ale należy monitorować zachowanie magazynu zapytań w czasie i odpowiednio dostosować jego konfigurację.

Wyświetlanie bieżących ustawień magazynu zapytań

Wyświetl bieżące ustawienia magazynu zapytań w programie SQL Server Management Studio (SSMS) lub T-SQL.

SSMS

Pobierz najnowszą wersję programu SQL Server Management Studio (SSMS)

Aby wyświetlić bieżące ustawienia w programie Management Studio:

1. W Eksploratorze obiektów programu SQL Server Management Studio kliknij prawym przyciskiem myszy bazę danych.
2. Wybierz właściwości .
3. Wybierz pozycję Magazyn zapytań.

T-SQL

Wykonaj następujący skrypt języka T-SQL, aby uzyskać najnowsze informacje o rozmiarze magazynu zapytań:

USE [QueryStoreDB];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
 max_storage_size_mb, readonly_reason
FROM sys.database_query_store_options;

Poniższy skrypt ustawia nową wartość dla Maksymalnego Rozmiaru (MB):

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);

Użyj programu SQL Server Management Studio lub Transact-SQL, aby ustawić inną wartość dla interwału opróżniania danych :

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);

Statistics Collection Interval: definiuje poziom szczegółowości dla zebranej statystyki uruchomieniowej, wyrażony w minutach. Wartość domyślna to 60 minut. Rozważ użycie niższej wartości, jeśli potrzebujesz bardziej szczegółowego lub krótszego czasu, aby wykryć i rozwiązać problemy. Należy pamiętać, że wartość ma bezpośredni wpływ na rozmiar danych magazynu zapytań. Użyj programu SQL Server Management Studio lub Transact-SQL, aby ustawić inną wartość dla interwału zbierania statystyk :

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60);

Próg zapytań nieaktywnych (dni): Polityka czyszczenia oparta na czasie, która kontroluje okres przechowywania utrwalonych statystyk czasu wykonania i zapytań nieaktywnych, wyrażana w dniach. Domyślnie magazyn zapytań jest skonfigurowany do przechowywania danych przez 30 dni, co może być niepotrzebnie długie dla danego scenariusza.

Unikaj przechowywania danych historycznych, których nie planujesz używać. Ta praktyka ogranicza zmiany w statusie tylko do odczytu. Rozmiar danych magazynu zapytań oraz czas wykrywania i eliminowania problemu będzie bardziej przewidywalny. Użyj programu Management Studio lub następującego skryptu, aby skonfigurować zasady oczyszczania oparte na czasie:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90));

tryb oczyszczania na podstawie rozmiaru: określa, czy automatyczne czyszczenie danych odbywa się, gdy rozmiar danych magazynu zapytań zbliża się do limitu. Aktywuj czyszczenie oparte na rozmiarach, aby upewnić się, że magazyn zapytań zawsze działa w trybie odczytu i zapisu i zbiera najnowsze dane. W przypadku dużych obciążeń nie ma gwarancji, że czyszczenie magazynu zapytań będzie stale utrzymywać rozmiar danych w ramach limitu. Istnieje możliwość, że automatyczne czyszczenie danych będzie opóźniać się i przełączy się (tymczasowo) na tryb tylko do odczytu.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

Tryb przechwytywania magazynu zapytań: Określa zasady przechwytywania zapytań w magazynie zapytań.

• Wszystkie: Przechwytuje wszystkie zapytania. Ta opcja jest domyślna w programach SQL Server 2016 (13.x) i SQL Server 2017 (14.x).
• auto: rzadko tworzone zapytania i zapytania z nieistotnym czasem kompilowania i wykonywania są ignorowane. Progi liczby wykonań, kompilacji i czasu trwania środowiska uruchomieniowego są określane wewnętrznie. Począwszy od programu SQL Server 2019 (15.x), jest to opcja domyślna.
• None: Magazyn zapytań zatrzymuje przechwytywanie nowych zapytań.
• Niestandardowy: umożliwia dodatkową kontrolę i możliwość precyzyjnego dostosowania zasad zbierania danych. Nowe ustawienia niestandardowe definiują, co dzieje się podczas wewnętrznego progu czasu zasad przechwytywania. Jest to okres czasu, podczas którego oceniane są konfigurowalne warunki i, jeśli którykolwiek z nich jest prawdziwy, zapytanie kwalifikuje się do przechwycenia przez magazyn zapytań.

Ważny

Kursory, zapytania wewnątrz procedur składowanych i natywnie skompilowane zapytania są zawsze przechwytywane, gdy tryb przechwytywania zapytań jest ustawiony na Wszystkie, Automatycznylub Niestandardowy. Aby przechwytywać natywnie skompilowane zapytania, włącz zbieranie statystyk dla poszczególnych zapytań przy użyciu sys.sp_xtp_control_query_exec_stats.

Następujący skrypt ustawia QUERY_CAPTURE_MODE na AUTO:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

Przykłady

Poniższy przykład ustawia QUERY_CAPTURE_MODE na AUTO i ustawia inne zalecane opcje w programie SQL Server 2016 (13.x):

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60
    );

Poniższy przykład ustawia QUERY_CAPTURE_MODE na auto i ustawia inne zalecane opcje w programie SQL Server 2017 (14.x) w celu uwzględnienia statystyk oczekiwania:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON
    );

W poniższym przykładzie ustawiono niestandardową zasadę przechwytywania na domyślne ustawienia programu SQL Server 2019 (15.x) zamiast nowego domyślnego trybu AUTO przechwytywania. Aby uzyskać więcej informacji na temat niestandardowych opcji zasad przechwytywania i wartości domyślnych, zobacz <query_capture_policy_option_list>.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

Konserwacja magazynu zapytań

Ta sekcja zawiera wskazówki dotyczące zarządzania samą funkcją magazynu zapytań.

Stan magazynu zapytań

Magazyn zapytań przechowuje swoje dane wewnątrz bazy danych użytkownika i dlatego ma limit rozmiaru (skonfigurowany przy użyciu MAX_STORAGE_SIZE_MB). Jeśli dane w magazynie zapytań osiągną limit, magazyn zapytań automatycznie zmieni stan z odczytu i zapisu na tryb tylko do odczytu i przestanie zbierać nowe dane.

Zapytanie sys.database_query_store_options, aby określić, czy magazyn zapytań jest obecnie aktywny i czy obecnie zbiera statystyki środowiska uruchomieniowego, czy nie.

SELECT actual_state, actual_state_desc, readonly_reason,
    current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Stan Query Store jest określany przez kolumnę actual_state. Jeśli jest inny niż żądany stan, kolumna readonly_reason może dostarczyć więcej informacji. Gdy rozmiar magazynu zapytań przekracza limit przydziału, funkcja przełączy się do trybu tylko do odczytu i poda przyczynę. Aby uzyskać informacje na temat przyczyn, zobacz sys.database_query_store_options.

Uzyskaj opcje magazynu zapytań

Aby uzyskać szczegółowe informacje o stanie magazynu zapytań, wykonaj następujące czynności w bazie danych użytkownika.

SELECT * FROM sys.database_query_store_options;

Ustaw interwał magazynu zapytań

Możesz zmienić interwał agregacji statystyk czasu wykonania zapytania (wartość domyślna to 60 minut). Nowa wartość interwału jest widoczna za pośrednictwem widoku sys.database_query_store_options.

ALTER DATABASE <database_name>
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

Dowolne wartości nie są dozwolone dla INTERVAL_LENGTH_MINUTES. Użyj jednego z następujących interwałów: 1, 5, 10, 15, 30, 60 lub 1440 minut.

Notatka

W przypadku usługi Azure Synapse Analytics dostosowywanie opcji konfiguracji magazynu zapytań, jak pokazano w tej sekcji, nie jest obsługiwane.

Użycie miejsca w Query Store

Aby sprawdzić bieżący rozmiar magazynu zapytań i limit, wykonaj następującą instrukcję w bazie danych użytkownika.

SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Jeśli magazyn zapytań jest pełny, użyj następującej instrukcji, aby rozszerzyć magazyn.

ALTER DATABASE <database_name>
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);

Ustaw opcje magazynu zapytań

Można jednocześnie ustawić wiele opcji magazynu zapytań za pomocą pojedynczej instrukcji ALTER DATABASE.

ALTER DATABASE <database name>
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 3000,
    MAX_STORAGE_SIZE_MB = 500,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 1000,
    WAIT_STATS_CAPTURE_MODE = ON
);

Aby uzyskać pełną listę opcji konfiguracji, zobacz ALTER DATABASE SET Options (Transact-SQL).

Czyszczenie miejsca

Tabele wewnętrzne magazynu zapytań są tworzone w podstawowej grupie plików podczas tworzenia bazy danych i nie można później zmienić tej konfiguracji. Jeśli brakuje miejsca, możesz wyczyścić starsze dane magazynu zapytań przy użyciu następującej instrukcji.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

Alternatywnie można wyczyścić tylko dane zapytań ad hoc, ponieważ jest mniej istotne w przypadku optymalizacji zapytań i analizy planu, ale zajmuje tyle miejsca.

W usłudze Azure Synapse Analytics wyczyszczenie magazynu zapytań jest niedostępne. Dane są automatycznie przechowywane przez ostatnie siedem dni.

Usuwanie zapytań ad hoc

To czyszczenie zapytań ad hoc i wewnętrznych z magazynu zapytań, aby w magazynie zapytań nie zabrakło miejsca i nie usuwało zapytań, które naprawdę musimy śledzić.

SET NOCOUNT ON
-- This purges adhoc and internal queries from
-- the Query Store in the current database
-- so that the Query Store does not run out of space
-- and remove queries we really need to track

DECLARE @id int;
DECLARE adhoc_queries_cursor CURSOR
FOR
    SELECT q.query_id
    FROM sys.query_store_query_text AS qt
    JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
    JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
    JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
    WHERE q.is_internal_query = 1  -- is it an internal query then we dont care to keep track of it
       OR q.object_id = 0 -- if it does not have a valid object_id then it is an adhoc query and we don't care about keeping track of it
    GROUP BY q.query_id
    HAVING MAX(rs.last_execution_time) < DATEADD (minute, -5, GETUTCDATE())  -- if it has been more than 5 minutes since the adhoc query ran
    ORDER BY q.query_id;
OPEN adhoc_queries_cursor ;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
WHILE @@fetch_status = 0
BEGIN
    PRINT 'EXEC sp_query_store_remove_query ' + str(@id);
    EXEC sp_query_store_remove_query @id;
    FETCH NEXT FROM adhoc_queries_cursor INTO @id;
END
CLOSE adhoc_queries_cursor;
DEALLOCATE adhoc_queries_cursor;

Możesz zdefiniować własną procedurę z inną logiką w celu wyczyszczenia danych, których już nie potrzebujesz.

W poprzednim przykładzie użyto rozszerzonej procedury składowanej sp_query_store_remove_query do usunięcia niepotrzebnych danych. Możesz również:

• Użyj sp_query_store_reset_exec_stats, aby wyczyścić statystyki środowiska uruchomieniowego dla danego planu.
• Użyj sp_query_store_remove_plan, aby usunąć pojedynczy plan.

Powiązana zawartość

• opcje ALTER DATABASE SET (Transact-SQL)
• widoki katalogu Query Store (Transact-SQL)
• procedury składowane magazynu zapytań (Transact-SQL)
• Używanie magazynu zapytań z In-Memory OLTP
• przewodnik po architekturze przetwarzania zapytań
• Podpowiedzi dotyczące Magazynu Zapytania
• Monitorowanie wydajności przy użyciu magazynu zapytań
• Dostrajanie wydajności za pomocą Magazynu Zapytań
• Przechowywanie i analiza historycznych zapytań w usłudze Azure Synapse Analytics