Zoptymalizowane wymuszanie planu z użyciem magazynu zapytań

Dotyczy: SQL Server 2022 (16.x) Azure SQL Databasebaza danych SQL w Microsoft Fabric

Optymalizacja zapytań to wielofazowy proces generowania "wystarczająco dobrego" planu wykonywania zapytania. W niektórych przypadkach kompilacja zapytań, część optymalizacji zapytań, może reprezentować duży procent ogólnego czasu wykonywania zapytań i zużywać znaczne zasoby systemowe. Zoptymalizowane wymuszanie planu jest częścią inteligentnej rodziny funkcji przetwarzania zapytań. Zoptymalizowany plan wymuszania zmniejsza obciążenie kompilacji przy powtarzaniu wymuszonych zapytań i wymaga włączenia Magazynu zapytań w trybie "odczyt-zapis". Po wygenerowaniu planu wykonywania zapytania określone kroki kompilacji są przechowywane do ponownego użycia jako skrypt ponownego odtwarzania optymalizacji. Skrypt odtwarzania optymalizacji jest przechowywany jako część skompresowanego kodu XML programu showplan w Magazynu zapytań, w ukrytym atrybucie OptimizationReplay.

Zoptymalizowany plan wymuszający implementację

Gdy zapytanie najpierw przechodzi przez proces kompilacji, próg oparty na szacowaniu czasu spędzonego w optymalizacji (na podstawie drzewa wejściowego optymalizatora zapytań) określa, czy został utworzony skrypt odtwarzania optymalizacji.

Po zakończeniu kompilacji dostępnych jest kilka metryk środowiska uruchomieniowego, aby ocenić, czy poprzednie oszacowanie jest poprawne. Jeśli aparat bazy danych potwierdzi przekroczenie progu, skrypt odtwarzania optymalizacji kwalifikuje się do trwałości. Te metryki środowiska uruchomieniowego obejmują liczbę obiektów, do których uzyskuje się dostęp, liczbę sprzężeń, liczbę zadań optymalizacji wykonywanych podczas optymalizacji oraz rzeczywisty czas optymalizacji.

Potencjalna korzyść użycia skryptu odtwarzania optymalizacji jest również porównywana z obciążeniem przechowywania skryptu odtwarzania optymalizacji. Szacowanie względnego czasu ponownego odtwarzania skryptu ponownego odtwarzania optymalizacji jest porównywane z czasem spędzonym na wykonywaniu normalnego procesu optymalizacji. To oszacowanie jest oparte na liczbie zadań optymalizacji przechowywanych w skryscie odtwarzania optymalizacji oraz liczbie zadań optymalizacji wykonywanych podczas normalnej kompilacji. Jeśli wykonanie skryptu odtwarzania optymalizacji przynosi znaczne korzyści w skróceniu czasu kompilacji, skrypt odtwarzania optymalizacji jest zachowywany.

Considerations

Po włączeniu funkcji wymuszania zoptymalizowanego planu kryteria kwalifikowalności dla wymuszania zoptymalizowanego planu to:

1. Kwalifikują się tylko plany zapytań przechodzące przez pełną optymalizację, które mogą być weryfikowane przez obecność StatementOptmLevel="FULL" właściwości.

2. Instrukcje z podpowiedzią RECOMPILE i zapytaniami rozproszonymi nie kwalifikują się.

Jednakże, jeśli magazyn zapytań niezależnie przechwytuje plan zapytania wymuszony w ramach zoptymalizowanego planowania, skrypt odtwarzania optymalizacji zostanie utworzony dla drugiej rekompilacji tego samego zapytania, zgodnie z domyślnymi zdarzeniami rekompilacji. Dowiedz się więcej na temat ponownej kompilacji w temacie Ponowne komkompilowanie planów wykonania.

Nawet jeśli skrypt odtworzenia optymalizacji został wygenerowany, może nie zostać zapisany w magazynie zapytań, jeśli skonfigurowane kryteria zasad przechwytywania w magazynie zapytań nie zostaną spełnione, w szczególności liczba wykonań tej instrukcji i czas skumulowanego kompilowania oraz wykonywania. W takim przypadku nieprawidłowy skrypt odtwarzania optymalizacji jest usuwany z pamięci asynchronicznie.

Włączanie i wyłączanie wymuszania zoptymalizowanego planu

Możesz włączyć lub wyłączyć wymuszenie zoptymalizowanego planu dla bazy danych. Po włączeniu optymalizacji wymuszania planu dla bazy danych można ją wyłączyć dla poszczególnych zapytań przy użyciu DISABLE_OPTIMIZED_PLAN_FORCING wskazówki dotyczącej zapytania. Można również wyłączyć zoptymalizowany plan wymuszający dla planu zapytania, który jest wymuszany w magazynie zapytań.

Włączanie lub wyłączanie zoptymalizowanego planu wymuszania dla bazy danych

Zoptymalizowane wymuszanie planu jest domyślnie włączone dla nowych baz danych utworzonych w programie SQL Server 2022 (16.x) i nowszych. Magazyn zapytań musi być włączony dla każdej bazy danych, w której stosowane jest wymuszanie zoptymalizowanego planu. Uaktualnione wystąpienia z istniejącymi bazami danych lub bazami danych przywróconymi z niższej wersji programu SQL Server mają domyślnie włączoną funkcję wymuszania zoptymalizowanego planu.

Aby wymusić zoptymalizowany plan na poziomie bazy danych, użyj konfiguracji dla bazy danych o określonym zakresie ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON. Jeśli magazyn zapytań nie został jeszcze włączony, musisz włączyć magazyn zapytań. Znajdź przykładowy kod w Przykładzie A, lub dowiedz się więcej o Query Store w Monitorowanie wydajności za pomocą Query Store.

Aby wyłączyć zoptymalizowane wymuszanie planu na poziomie bazy danych, użyj konfiguracji ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = OFF o określonym zakresie bazy danych.

Wyłącz wymuszanie zoptymalizowanego planu za pomocą wskazówki zapytania

Gdy funkcja wymuszania zoptymalizowanego planu jest włączona w bazie danych, można wyłączyć wymuszanie zoptymalizowanego planu dla pojedynczego zapytania przy użyciu DISABLE_OPTIMIZED_PLAN_FORCINGpodpowiedzi zapytania.

Znajdź przykład zastosowania tej wskazówki dotyczącej zapytania w przykładzie E.

Wymuszanie planu z magazynem zapytań, ale wyłączanie wymuszania zoptymalizowanego planu

Procedura sp_query_store_force_plan zawiera disable_optimized_plan_forcing parametr . Aby użyć tego parametru, dodatkowy parametr jest wymagany przez procedurę sp_query_store_force_plan składowaną. Dodatkowy parametr nosi nazwę @replica_group_id. Domyślnie podstawowy @replica_group_id ma wartość jedną (1) nawet w przypadku, gdy nie ma skonfigurowanych replik pomocniczych.

Znajdź przykład zastosowania odpowiednich parametrów do procedury składowanej w sp_query_store_force_plan.

sys.query_store_plan Widok wykazu zawiera kolumny wskazujące, czy plan ma skojarzony skrypt odtwarzania optymalizacji, i dodaje nowy stan do istniejącej kolumny przyczyny awarii specyficznej dla skojarzonego skryptu odtwarzania optymalizacji. Dowiedz się więcej w sys.query_store_plan.

Examples

Przykłady kodu w tym artykule korzystają z przykładowej bazy danych AdventureWorks2025 lub AdventureWorksDW2025, którą można pobrać ze strony głównej Przykładów programu Microsoft SQL Server i projektów społeczności.

A. Włączanie magazynu zapytań i zoptymalizowanego planu wymuszania dla bazy danych

Poniższy kod włącza Query Store w bazie danych, a następnie włącza wymuszanie zoptymalizowanego planu na bazie danych. Dowiedz się więcej o opcjach umożliwiających włączenie Query Store w poleceniu ALTER DATABASE SET.

Przed uruchomieniem kodu połącz się z odpowiednią bazą danych użytkownika.

ALTER DATABASE CURRENT 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 = 1024,
    INTERVAL_LENGTH_MINUTES = 60
);
GO

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_PLAN_FORCING = ON;
GO

B. Wybierz wszystkie zapytania, które mają skrypt ponownego odtwarzania optymalizacji

Poniższy przykładowy kod wybiera wszystkie identyfikatory zapytań, które mają skrypt odtwarzania optymalizacji w Query Store. Połącz się z odpowiednią bazą danych użytkownika przed uruchomieniem przykładowego kodu.

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.has_compile_replay_script = 1;
GO

C. Wymuszanie planu i wyłączanie wymuszania zoptymalizowanego planu w repozytorium zapytań

Poniższy kod wymusza plan w Query Store, ale wyłącza wymuszanie planu zoptymalizowanego. Przed uruchomieniem poniższego kodu zastąp element @query_id i @plan_id kombinacją odpowiednią dla danego wystąpienia. Procedura składowana sp_query_store_force_plan oczekuje, że parametr @replica_group_id jest przekazywany jako trzecia wartość parametru podczas próby wyłączenia wymuszenia zoptymalizowanego planu w Query Store. Może to służyć do wyłączania zoptymalizowanego planu wymuszania dla określonego wymuszonego planu na określonej repliki. Wartość @replica_group_id = 1 jest używana do wyłączenia funkcji na replike podstawowej.

EXECUTE sp_query_store_force_plan
    @query_id = 148,
    @plan_id = 4,
    @replica_group_id = 1,
    @disable_optimized_plan_forcing = 1;
GO

Dowiedz się więcej na temat sp_query_store_force_plan.

D. Zaznacz wszystkie zapytania, w których wymuszanie zoptymalizowanego planu jest wyłączone przez magazyn zapytań

Poniższy przykład zapytuje o wszystkie plany, które zostały wymuszone w Magazynie Zapytań, gdzie is_optimized_plan_forcing_disabled ustawiono na wartość 1. Przed uruchomieniem kodu połącz się z odpowiednią bazą danych użytkownika.

SELECT q.query_id,
       t.query_sql_text,
       p.plan_id,
       TRY_CAST (p.query_plan AS XML) AS query_plan,
       p.is_forced_plan,
       p.count_compiles
FROM sys.query_store_plan AS p
     INNER JOIN sys.query_store_query AS q
         ON p.query_id = q.query_id
     INNER JOIN sys.query_store_query_text AS t
         ON q.query_text_id = t.query_text_id
WHERE p.is_optimized_plan_forcing_disabled = 1;
GO

E. Wyłączanie wymuszania optymalnego planu w zapytaniu

Poniższy przykład wyłącza wymuszanie zoptymalizowanego planu dla zapytania przy użyciu DISABLE_OPTIMIZED_PLAN_FORCINGpodpowiedzi zapytania.

SELECT ProductID,
       OrderQty,
       SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (USE HINT('DISABLE_OPTIMIZED_PLAN_FORCING'));
GO

Treści powiązane

• sys.query_store_plan (Transact-SQL)
• ZMIEŃ KONFIGURACJĘ ZAKRESU BAZY DANYCH (Transact-SQL)
• sp_query_store_force_plan (Transact-SQL)
• inteligentne przetwarzanie zapytań w bazach danych SQL