Udostępnij za pomocą

Sugestie dla Query Store

Dotyczy: SQL Server 2022 (16.x) i nowsze wersje Azure SQL DatabaseAzure SQL Managed InstanceSQL database w usłudze Microsoft Fabric

W tym artykule opisano sposób stosowania wskazówek dotyczących zapytań przy użyciu magazynu zapytań. Wskazówki dotyczące magazynu zapytań zapewniają łatwą w użyciu metodę kształtowania planów zapytań bez zmieniania kodu aplikacji.

Caution

Ponieważ optymalizator zapytań programu SQL Server zazwyczaj wybiera najlepszy plan wykonania zapytania, zalecamy używanie wskazówek tylko w ostateczności dla doświadczonych deweloperów i administratorów baz danych. Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące zapytań.

Obejrzyj ten film, aby dowiedzieć się więcej o wskazówkach dotyczących Query Store.

Overview

W idealnym przypadku optymalizator zapytań wybiera optymalny plan wykonywania zapytania.

Jeśli nie wybrano optymalnego planu, deweloper lub administrator bazy danych (DBA) może chcieć ręcznie zoptymalizować pod kątem określonych warunków. Wskazówki dotyczące zapytań są określane za pośrednictwem klauzuli OPTION i mogą służyć do wpływania na zachowanie wykonywania zapytań. Chociaż wskazówki dotyczące zapytań pomagają zapewnić zlokalizowane rozwiązania różnych problemów związanych z wydajnością, wymagają ponownego zapisywania oryginalnego tekstu zapytania. Administratorzy bazy danych i deweloperzy mogą nie zawsze mieć możliwość wprowadzenia zmian bezpośrednio w kodzie Transact-SQL w celu dodania wskazówki dotyczącej zapytania. Transact-SQL może być zakodowane w aplikacji lub generowane automatycznie przez aplikację. Wcześniej deweloper mógł polegać na przewodnikach dotyczących planów, które mogą być skomplikowane w użyciu.

Wskazówki dotyczące magazynu zapytań pozwalają rozwiązać ten problem, umożliwiając wstrzyknięcie wskazówki do zapytania bez bezpośredniego modyfikowania tekstu zapytania Transact-SQL. Aby dowiedzieć się, które podpowiedzi dotyczące zapytań można zastosować, zobacz Obsługiwane podpowiedzi dotyczące zapytań.

Kiedy używać podpowiedzi Query Store

Jak sugeruje nazwa, ta funkcja rozszerza się i zależy od magazynu zapytań. Magazyn zapytań umożliwia przechwytywanie zapytań, planów wykonywania i skojarzonych statystyk środowiska uruchomieniowego. Magazyn zapytań znacznie upraszcza ogólne dostrajanie wydajności środowiska klienta. Program SQL Server 2016 (13.x) po raz pierwszy wprowadził magazyn zapytań, a teraz jest domyślnie włączony w programie SQL Server 2022 (16.x), usłudze Azure SQL Managed Instance, usłudze Azure SQL Database i bazie danych SQL Database w usłudze Microsoft Fabric.

Przepływ pracy wskazówek dla Query Store.

Przykłady, w których wskazówki dotyczące magazynu zapytań mogą pomóc w rozwiązywaniu problemów z wydajnością na poziomie zapytania:

  • Ponownie skompiluj zapytanie przy każdym wykonaniu.
  • Ogranicz rozmiar przydziału pamięci dla operacji wstawiania zbiorczego.
  • Ogranicz maksymalny stopień równoległości podczas aktualizowania statystyk.
  • Użyj sprzężenia haszującego zamiast sprzężenia zagnieżdżonych pętli.
  • Użyj poziomu zgodności 110 dla określonego zapytania, zachowując jednocześnie wszystkie inne elementy w bazie danych na poziomie zgodności 150.
  • Wyłącz optymalizację celu wierszowego dla zapytania SELECT TOP.

Aby użyć opcji Query Store:

  1. Zidentyfikuj magazyn zapytań query_id zapytania, które chcesz zmodyfikować. Można to zrobić na różne sposoby:

  2. Wykonaj sys.sp_query_store_set_hints za pomocą query_id oraz ciągu wskazówek, które chcesz zastosować do zapytania. Ten ciąg może zawierać co najmniej jedną wskazówkę zapytania. Aby uzyskać pełne informacje, zobacz sys.sp_query_store_set_hints.

Po utworzeniu wskazówki dla magazynu zapytań są utrwalane i przetrwają ponowne uruchomienia i przejścia w tryb failover. Wskazówki dotyczące magazynu zapytań zastępują trwale zakodowane wskazówki na poziomie instrukcji i istniejące wskazówki przewodnika po planie.

Jeśli wskazówka zapytania jest sprzeczna z tym, co jest możliwe w optymalizacji zapytań, wykonywanie zapytań nie jest blokowane, a wskazówka nie jest stosowana. W przypadkach, gdy wskazówka spowoduje niepowodzenie zapytania, wskazówka jest ignorowana, a najnowsze szczegóły błędu można wyświetlić w sys.query_store_query_hints.

Przed użyciem wskazówek Query Store

Przed rozpoczęciem korzystania z wskazówek magazynu zapytań należy wziąć pod uwagę następujące kwestie.

  • Ukończ konserwację statystyk i konserwację indeksu (w razie potrzeby) przed oceną zapytań pod kątem potencjalnych nowych wskazówek dotyczących magazynu zapytań. Zarządzanie statystykami, a w mniejszym stopniu utrzymanie indeksu, może rozwiązać problem, który w przeciwnym razie wymaga podpowiedzi zapytania.
  • Przed użyciem wskazówek magazynu zapytań przetestuj bazę danych aplikacji na najnowszym poziomie zgodności , aby sprawdzić, czy rozwiązuje to problem, który wymaga wskazówki dotyczącej zapytania.
    • Na przykład, optymalizacja planów wrażliwych na parametry (PSP) została wprowadzona w systemie SQL Server 2022 (16.x) w ramach poziomu zgodności 160. Używa wielu aktywnych planów dla każdego zapytania, aby rozwiązać problem z niejednorodnymi rozkładami danych. Jeśli środowisko nie może korzystać z najnowszego poziomu zgodności, wskazówki dotyczące magazynu zapytań za pomocą RECOMPILE mogą być używane na dowolnym obsługiwanym poziomie zgodności.
  • Wskazówki dotyczące Query Store zastępują domyślne zachowanie planu zapytania silnika bazy danych. Należy używać wskazówek dotyczących Query Store tylko wtedy, gdy jest to konieczne do rozwiązania problemów związanych z wydajnością.
  • Należy ponownie ocenić podpowiedzi magazynu zapytań, podpowiedzi na poziomie instrukcji, przewodniki dotyczące planu oraz wymuszone plany magazynu zapytań, gdy tylko zmienia się wolumen i dystrybucja danych, a także podczas projektów migracji bazy danych. Zmiany w objętości i dystrybucji danych mogą spowodować, że wskazówki dotyczące Query Store generują nieoptymalne plany wykonania.

Procedury składowane systemu wskazówek Query Store

Aby utworzyć lub zaktualizować wskazówki, użyj sys.sp_query_store_set_hints. Wskazówki są określane w prawidłowym formacie ciągu N'OPTION (...)'.

  • Jeśli tworzysz wskazówkę dla magazynu zapytań, a dla określonego query_id nie istnieje jeszcze żadna wskazówka, zostanie utworzona nowa wskazówka magazynu zapytań.
  • Podczas tworzenia lub aktualizowania podpowiedzi magazynu zapytań, jeśli istnieje już podpowiedź magazynu zapytań dla określonego elementu query_id, ostatnia podana wartość zastępuje wcześniej określone wartości dla skojarzonego zapytania.
  • Jeśli element query_id nie istnieje, zostanie zgłoszony błąd.

Aby uzyskać pełną listę wskazówek obsługiwanych przez magazyn zapytań, zobacz sys.sp_query_store_set_hints.

Aby usunąć wskazówki skojarzone z elementem query_id, użyj sys.sp_query_store_clear_hints.

Tip

Może być konieczne ustawienie lub wyczyszczenie podpowiedzi dla wszystkich query_id wartości odpowiadających skrótowi zapytania.

dbo.sp_query_store_modify_hints_by_query_hash to przykładowa procedura składowana, która wywołuje w pętli procedurę składowaną systemu sys.sp_query_store_set_hints lub sys.sp_query_store_clear_hints w celu wykonania tej czynności.

Atrybuty XML planu wykonywania

Po zastosowaniu wskazówek następujący zestaw wyników jest wyświetlany w elemecie StmtSimple w formacie XML:

Attribute Description
QueryStoreStatementHintText Podpowiedzi dotyczące Zapytania Magazynu zastosowane do rzeczywistego zapytania
QueryStoreStatementHintId Unikatowy identyfikator wskazówki zapytania
QueryStoreStatementHintSource Źródło wskazówki Query Store (na przykład User)

Note

Te elementy XML są dostępne za pośrednictwem danych wyjściowych poleceń Transact-SQL SET STATISTICS XML i SET SHOWPLAN_XML.

Sugestie dla magazynu zapytań i interoperacyjność funkcji

  • Wskazówki dotyczące magazynu zapytań zastępują inne zakodowane wskazówki dotyczące poziomu instrukcji i przewodniki dotyczące planu.
  • Z wyjątkiem ABORT_QUERY_EXECUTION wskazówki zapytania, zapytania z wskazówkami magazynu zapytań zawsze są wykonywane. Przeciwdziałające podpowiedzi dotyczące Query Store są pomijane, co inaczej spowodowałoby błąd.
  • Jeśli wskazówki magazynu zapytań są sprzeczne, silnik bazy danych nie blokuje wykonywania zapytań, a wskazówka magazynu zapytań nie jest stosowana.
  • Podpowiedzi Magazynu Zapytania nie są obsługiwane w przypadku instrukcji SQL, które kwalifikują się do prostej parametryzacji.
  • RECOMPILE Wskazówka nie jest zgodna z wymuszoną parametryzacją ustawioną na poziomie bazy danych. Jeśli baza danych ma wymuszony zestaw parametryzacji, a RECOMPILE wskazówka jest częścią wskazówek magazynu zapytań dotyczących zapytania, aparat bazy danych ignoruje RECOMPILE wskazówkę i stosuje wszelkie inne wskazówki, jeśli zostały określone.
    • Silnik bazy danych wyświetla ostrzeżenie (kod błędu 12461) z informacją, że RECOMPILE podpowiedź została zignorowana.
    • Aby uzyskać więcej informacji na temat zagadnień dotyczących przypadków użycia wymuszonych parametryzacji, zobacz Wytyczne dotyczące używania wymuszonej parametryzacji.
  • Ręcznie utworzone wskazówki do Query Store są wykluczone z tego procesu czyszczenia. Podpowiedź i zapytanie nie są usuwane przez politykę automatycznego przechwytywania danych do przechowywania.
    • Zapytania można usuwać ręcznie przez użytkowników. Spowoduje to również usunięcie powiązanej wskazówki Query Store.
    • Wskazówki dotyczące magazynu zapytań generowane automatycznie przez opinię CE podlegają oczyszczeniu przez automatyczne przechowywanie zasad przechwytywania.
    • Sprzężenie zwrotne DOP i sprzężenie zwrotne przydziału pamięci kształtują zachowanie zapytań bez użycia podpowiedzi Magazynu Zapytań. Gdy zapytania są czyszczone przez politykę automatycznego przechwytywania retencji, informacje zwrotne dotyczące DOP i przydziału pamięci są również czyszczone.
    • Jeśli tworzysz tę samą podpowiedź Magazynu Zapytań, którą opinie CE zostały zaimplementowane ręcznie, zapytanie z podpowiedzią nie podlega już oczyszczeniu przez automatyczną politykę przechwytywania i utrzymywania.

Wskazówki dotyczące magazynu zapytań i repliki pomocnicze

Wskazówki magazynu zapytań nie mają wpływu na repliki pomocnicze, chyba że magazyn zapytań dla replik pomocniczych jest włączony. Aby uzyskać więcej informacji, zobacz Rejestr zapytań dla czytelnych replik drugorzędnych.

  • W programie SQL Server 2022 (16.x) i starszych wersjach wskazówki magazynu zapytań można stosować tylko w repliki podstawowej.
  • W programie SQL Server 2025 (17.x) i nowszych wersjach, gdy magazyn zapytań dla replik pomocniczych jest włączony, wskazówki dotyczące magazynu zapytań można stosować w replikach pomocniczych w grupach dostępności. Aby uzyskać pełną obsługę platformy, zobacz Query Store dla możliwych do odczytu replik wtórnych.

W których replikach pomocniczych magazyn zapytań jest obsługiwany:

Examples

A. Demonstracja wskazówek dotyczących Query Store

Poniższy przegląd wskazówek dotyczących Query Store w usłudze Azure SQL Database używa zaimportowanej bazy danych z użyciem pliku BACPAC (.bacpac). Dowiedz się, jak zaimportować nową bazę danych na serwer usługi Azure SQL Database, zobacz Szybki start: importowanie pliku bacpac do bazy danych w usłudze Azure SQL Database lub Azure SQL Managed Instance.

-- ************************************************************************ --
-- Query Store hints demo

-- Demo uses "PropertyMLS" database which can be imported from BACPAC here:
-- https://github.com/microsoft/sql-server-samples/tree/master/samples/features/query-store

-- Email QSHintsFeedback@microsoft.com for questions\feedback
-- ************************************************************************ --

/*
    Demo prep, connect to the PropertyMLS database
*/

ALTER DATABASE [PropertyMLS] SET QUERY_STORE CLEAR;
ALTER DATABASE CURRENT SET QUERY_STORE = ON;
ALTER DATABASE CURRENT SET QUERY_STORE  (QUERY_CAPTURE_MODE = ALL);
GO

-- Should be READ_WRITE
SELECT actual_state_desc
FROM sys.database_query_store_options;
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints.
    Checking if any already exist (should be none).
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
     The PropertySearchByAgent stored procedure has a parameter
     used to filter AgentId.  Looking at the statistics for AgentId,
     you will see that there is a big skew for AgentId 101.
*/
SELECT	hist.range_high_key AS [AgentId],
        hist.equal_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE s.[name] = N'NCI_Property_AgentId';


-- Show actual query execution plan to see plan compiled.
-- Agent with many properties will have a scan with parallelism.
EXEC [dbo].[PropertySearchByAgent] 101;

-- Agents with few properties still re-use this plan (assuming no recent plan eviction).
EXEC [dbo].[PropertySearchByAgent] 4;


/*
    Now let's find the query_id associated with this query.
*/
SELECT query_sql_text, q.query_id
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON
    qt.query_text_id = q.query_text_id
WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%' and query_sql_text not like N'%query_store%';
GO

/*
     We can set the hint associated with the query_id returned in the previous result set, as below.
     Note, we can designate one or more query hints
*/
EXEC sp_query_store_set_hints @query_id=5, @value = N'OPTION(RECOMPILE)';
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see two different plans, one for AgentId 101 and one for AgentId 4.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
    We can remove the hint using sp_query_store_clear_query_hints
*/
EXEC sp_query_store_clear_hints @query_id = 10;
GO

/*
    That Query Store Hint is now removed
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see one plan again.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

B. Identyfikacja zapytania w magazynie zapytań

Poniższe przykładowe zapytania sys.query_store_query_text i sys.query_store_query zwracają query_id dla wykonanego fragmentu tekstu zapytania.

W tym pokazie zapytanie, które próbujemy dostroić, znajduje się w przykładowej SalesLT bazie danych.

SELECT * FROM SalesLT.Address as A
INNER JOIN SalesLT.CustomerAddress as CA
on A.AddressID = CA.AddressID
WHERE PostalCode = '98052' ORDER BY A.ModifiedDate DESC;

Magazyn zapytań nie odzwierciedla natychmiast danych zapytania w widokach systemowych.

Zidentyfikuj zapytanie w widokach wykazu systemu magazynu zapytań:

SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON
    qt.query_text_id = q.query_text_id
WHERE query_sql_text like N'%PostalCode =%'
  AND query_sql_text not like N'%query_store%';
GO

W poniższych przykładach poprzedni przykład zapytania w SalesLT bazie danych został zidentyfikowany jako query_id 39.

Po zidentyfikowaniu zastosuj wskazówkę, aby wymusić maksymalny rozmiar przydziału pamięci w procentach skonfigurowanego limitu pamięci do :query_id

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(MAX_GRANT_PERCENT=10)';

Można również zastosować wskazówki dotyczące zapytań przy użyciu następującej składni, na przykład opcję wymuszania starszego narzędzia do szacowania kardynalności:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

Można zastosować wiele wskazówek dla zapytań, używając listy oddzielanej przecinkami.

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))';

Przejrzyj wskazówkę dla Store zapytań dotyczącą query_id 39:

SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason, last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc
FROM sys.query_store_query_hints
WHERE query_id = 39;

Na koniec usuń wskazówkę z query_id 39, używając sp_query_store_clear_hints.

EXEC sys.sp_query_store_clear_hints @query_id = 39;

Treści powiązane

  • Last updated on