Pokyny k Query Store

platí pro: SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed InstanceSQL Database v Microsoft Fabric

Tento článek popisuje, jak použít nápovědy k dotazům pomocí úložiště dotazů. Rady úložiště dotazů poskytují snadno použitelnou metodu pro tvarování plánů dotazů beze změny kódu aplikace.

Nápovědy k úložišti dotazů jsou k dispozici ve službě Azure SQL Database a ve službě Azure SQL Managed Instance. Nápovědy k úložišti dotazů jsou také funkcí zavedenou pro SQL Server v SQL Serveru 2022 (16.x).

• Další informace o konfiguraci a správě pomocí úložiště dotazů najdete v tématu Monitorování výkonu pomocí úložiště dotazů.
• Informace o zjišťování informací umožňujících akci a ladění výkonu pomocí úložiště dotazů najdete v tématu Ladění výkonu pomocí úložiště dotazů.
• Informace o provozu úložiště dotazů ve službě Azure SQL Database najdete v tématu Provoz úložiště dotazů ve službě Azure SQL Database.

Opatrnost

Vzhledem k tomu, že optimalizátor dotazů SQL Serveru obvykle vybírá nejlepší plán provádění dotazu, doporučujeme jako poslední možnost použít pouze rady pro zkušené vývojáře a správce databází. Další informace najdete v části Rady k dotazům.

Podívejte se na toto video pro přehled návodů k Úložišti dotazů.

Přehled

Optimalizátor dotazů v ideálním případě vybere optimální plán provádění dotazu.

Pokud není vybraný optimální plán, vývojář nebo dba by možná chtěli ručně optimalizovat konkrétní podmínky. Nápovědy dotazu se zadají prostřednictvím klauzule OPTION a dají se použít k ovlivnění chování při provádění dotazu. Nápovědy k dotazům sice pomáhají poskytovat lokalizovaná řešení různých problémů souvisejících s výkonem, ale vyžadují přepsání původního textu dotazu. Správci databází a vývojáři nemusí vždy moct provádět změny přímo v kódu Transact-SQL a vložit nápovědu k dotazu. Transact-SQL může být pevně zakódovaná do aplikace nebo automaticky vygenerovaná aplikací. Dříve se vývojář mohl spoléhat na průvodci plánováním, což může být složité.

Informace o tom, které dotazové nápovědy lze použít, naleznete v tématu Podporované dotazové nápovědy.

Kdy použít pokyny k úložišti dotazů

Jak název napovídá, tato funkce rozšiřuje svoji funkčnost a závisí na úložišti dotazů. Úložiště dotazů umožňuje zachytávání dotazů, plánů spouštění a přidružených statistik modulu runtime. Úložiště dotazů výrazně zjednodušuje celkové možnosti ladění výkonu pro zákazníky. SQL Server 2016 (13.x) poprvé zavedl úložiště dotazů a teď je ve výchozím nastavení povolený v SQL Serveru 2022 (16.x), Azure SQL Managed Instance a Azure SQL Database.

Nejprve se dotaz spustí a pak zachytí úložištěm dotazů. Pak DBA vytvoří příznak úložiště dotazů pro dotaz. Poté se dotaz spustí pomocí tipu Query Store.

Příklady, kdy vám mohou pomoci nápovědy Úložiště dotazů při řešení problémů s výkonem dotazů:

• Překompilujte dotaz při každém spuštění.
• Omezte velikost přidělení paměti pro hromadnou operaci vložení.
• Omezte maximální stupeň paralelismu při aktualizaci statistik.
• Místo spojení Nested Loops použijte hash spojení.
• Pro konkrétní dotaz použijte úroveň kompatibility 110 a přitom ponechte všechno ostatní v databázi na úrovni kompatibility 150.
• Zakázání optimalizace cíle řádku pro dotaz SELECT TOP.

Jak používat tipy pro úložiště dotazů:

1. Identifikujte úložiště dotazů query_id příkazu dotazu, který chcete upravit. Můžete to udělat různými způsoby:
   • Dotazování katalogových zobrazení úložiště dotazů .
   • Použití vestavěných sestav Úložiště dotazů v aplikaci SQL Server Management Studio.
   • Použití nástroje Azure Portal Query Performance Insight pro Azure SQL Database
2. Spusťte sys.sp_query_store_set_hints s query_id a návrhovým řetězcem dotazu, který chcete pro dotaz použít. Tento řetězec může obsahovat jeden nebo více tipů pro dotazy. Pro úplné informace se podívejte na sys.sp_query_store_set_hints.

Po vytvoření zůstanou pokyny Úložiště dotazů zachovány a přežijí restartování a převzetí služeb při selhání. Nápovědy k úložišti dotazů přepisují pevně zakódované rady na úrovni příkazů a existující rady průvodce plánem.

Pokud nápověda dotazu odporuje tomu, co je možné pro optimalizaci dotazů, spuštění dotazu není blokováno a nápověda se nepoužije. V případech, kdy by nápověda způsobila selhání dotazu, je tip ignorován a nejnovější podrobnosti o selhání lze zobrazit v sys.query_store_query_hints.

Systémové uložené procedury v úložišti dotazů

Chcete-li vytvořit nebo aktualizovat nápovědy, použijte sys.sp_query_store_set_hints. Rady jsou zadány v platném řetězcovém formátu N'OPTION (...)'.

• Pokud vytváříte nápovědu k úložišti dotazů a neexistuje žádná nápověda k úložišti dotazů pro konkrétní query_id, vytvoří se nová nápověda k úložišti dotazů.
• Pokud při vytváření nebo aktualizaci nápovědy úložiště dotazů již pro konkrétní query_idexistuje nápověda úložiště dotazů, poslední zadaná hodnota přepíše hodnoty dříve zadané pro přidružený dotaz.
• Pokud query_id neexistuje, vyvolá se chyba.

Poznámka

Úplný seznam podporovaných tipů najdete v tématu sys.sp_query_store_set_hints.

Pro odebrání nápověd spojených s query_idpoužijte sys.sp_query_store_clear_hints.

Atributy XML plánu provádění

Při použití nápovědy se v elementu StmtSimple plánu provádění objeví následující sada výsledků ve formátu XML .

atribut	popis
QueryStoreStatementHintText	Skutečné nápovědy úložiště dotazů použité pro dotaz
QueryStoreStatementHintId	Jedinečný identifikátor nápovědy dotazu
QueryStoreStatementHintSource	Zdroj tipu k úložišti dotazů (např. "Uživatel")

Poznámka

Tyto elementy XML jsou k dispozici prostřednictvím výstupu příkazů Transact-SQL SET STATISTICS XML a SET SHOWPLAN XML.

Nápovědy k úložišti dotazů a interoperabilita funkcí

• Pokyny úložiště dotazů přebijí ostatní pevně zakódované pokyny úrovně příkazů a průvodce plánu.
• Dotazy se vždy vykonají. Protichůdné hinty úložiště dotazů, které by jinak způsobily chybu, jsou ignorovány.
• Pokud jsou nápovědy úložiště dotazů v rozporu, SQL Server neblokuje provádění dotazů a nápověda úložiště dotazů se nepoužije.
• Jednoduchá parametrizace – Pokyny úložiště dotazů nejsou podporovány pro příkazy, které mají nárok na jednoduchou parametrizaci.
• Vynucené parametrizace – Nápověda RECOMPILE není kompatibilní s vynucenou parametrizací nastavenou na úrovni databáze. Pokud má databáze nastavenou vynucenou parametrizaci a tip RECOMPILE je součástí řetězce nápovědy nastaveného v úložišti dotazů pro dotaz, SQL Server ignoruje nápovědu RECOMPILE a použije všechny další rady, pokud jsou použity.
  • SQL Server navíc vydá upozornění (kód chyby 12461), který hlásí, že byl ignorován tip rekompilu.
  • Další informace o aspektech použití vynucené parametrizace naleznete v tématu Pokyny pro použití vynucené parametrizace.
• Ručně vytvořené nápovědy Query Store nejsou předmětem vyčištění. Nápověda a dotaz nebudou vyčištěny z úložiště dotazů automatickým dodržováním zásady zachytávání.
  • Dotazy můžou ručně odebrat uživatelé, což by také odebralo související nápovědu k úložišti dotazů.
  • Nápovědy k úložišti dotazů automaticky generované zpětné vazby CE se můžou vyčistit automatickým uchováváním zásad zachycení.
  • zpětná vazba DOP a zpětná vazba udělování paměti formují chování dotazu bez použití hintů z úložiště dotazů. Když dotazy vyčistíte automatickým uchováváním zásad zachytávání, vyčistí se také zpětná vazba DOP a data o udělení paměti.
  • Můžete ručně vytvořit stejný tip úložiště dotazů, který implementoval zpětná vazba CE, a pak dotaz s nápovědou už nebude podléhat vyčištění pomocí automatického uchovávání zásad zachytávání.

Query Store dotazy a skupiny dostupnosti

Nápovědy úložiště dotazů nemají žádný vliv na sekundární repliky, pokud není povolené úložiště dotazů pro sekundární repliky. Další informace najdete v tématu Úložiště dotazů pro sekundární repliky.

• Před SQL Serverem 2022 (16.x) je možné použít nápovědy úložiště dotazů pro primární repliku skupiny dostupnosti.
• Počínaje SQL Serverem 2022 (16.x), když je pro sekundární repliky ve skupinách dostupnosti povoleno Query Store, jsou i nápovědy Query Store přizpůsobeny sekundárním replikám.
• Do úložiště dotazů můžete přidat nápovědu pro konkrétní repliku nebo sadu replik, pokud máte povolené úložiště dotazů pro sekundární repliky. V sys.sp_query_store_set_query_hintsje nastaven parametrem @query_hint_scope, který byl zaveden v SYSTÉMU SQL Server 2022 (16.x).
• Vyhledejte sady replik, které jsou dostupné, pomocí dotazu na sys.query_store_replicas.
• Najděte plány vynucené na sekundárních replikách pomocí sys.query_store_plan_forcing_locations.

Osvědčené postupy pro úložiště dotazů

• Před vyhodnocením dotazů pro potenciální nové rady úložiště dotazů dokončete údržbu indexů a statistik.
• Před použitím nápovědy k úložišti dotazů otestujte svou aplikační databázi na nejnovější úrovni kompatibility .
  • Například optimalizace plánu PSP (Parameter Sensitive Plan) byla zavedena v SYSTÉMU SQL Server 2022 (16.x) (úroveň kompatibility 160), která používá více aktivních plánů na dotaz k řešení neuniformních distribucí dat. Pokud vaše prostředí nemůže použít nejnovější úroveň kompatibility, dají se nápovědy k úložišti dotazů pomocí nápovědy REKOMPIL použít na jakékoli podpůrné úrovni kompatibility.
• Nápovědy k úložišti dotazů přepisují chování plánu dotazů SQL Serveru. Doporučuje se používat nápovědy úložiště dotazů pouze tehdy, je-li to nutné k řešení problémů souvisejících s výkonem.
• Doporučujeme, aby se přehodnotily tipy úložiště dotazů, tipy na úrovni příkazů, příručky plánu a vynucené plány v úložišti dotazů kdykoli dojde ke změně distribuce dat nebo během projektů migrace databází. Změny v distribuci dat můžou způsobit, že doporučení úložiště dotazů generují méně optimální plány provádění.

Příklady

A. Ukázka použití nápověd pro Query Store

Následující návod k použití nápověd pro úložiště dotazů ve službě Azure SQL Database používá importovanou databázi prostřednictvím souboru BACPAC (.bacpac). Informace o importu nové databáze na server Azure SQL Database najdete v tématu Rychlý start: Import souboru BACPAC do databáze.

-- ************************************************************************ --
-- 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. Identifikace dotazu v úložišti dotazů

Následující příklad dotazu sys.query_store_query_text a sys.query_store_query je použitý k vrácení query_id pro fragment textu vykonaného dotazu.

V této ukázce je dotaz, který se pokoušíme vyladit, v ukázkové databázi SalesLT:

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;

Úložiště dotazů okamžitě neodráží data dotazů do systémových zobrazení.

Identifikujte dotaz v zobrazeních systémového katalogu úložiště dotazů:

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

V následujících ukázkách byl předchozí příklad dotazu v databázi SalesLT identifikován jako query_id 39.

Jakmile je identifikováno, použijte nápovědu k vynucení maximální velikosti přidělení paměti, které představuje procento nakonfigurovaného limitu paměti pro query_id.

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

Můžete také použít nápovědy pro dotazy s následující syntaxí, například možnost vynutit starší verzi nástroje pro posouzení kardinality:

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

S čárkami odděleným seznamem můžete použít několik tipů pro dotazy:

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

Zkontrolujte doporučení Query Store pro 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;

Nakonec odeberte nápovědu z query_id 39 pomocí sp_query_store_clear_hints.

EXEC sys.sp_query_store_clear_hints @query_id = 39;

Související obsah

• sys.query_store_query_hints (Transact-SQL)
• sys.sp_query_store_set_hints (Transact-SQL)
• sys.sp_query_store_clear_hints (Transact-SQL)
• uložení plánu provádění ve formátu XML
• zobrazení a uložení plánů provádění
• Rady (Transact-SQL) - Dotaz
• osvědčené postupy pro monitorování úloh pomocí úložiště dotazů
• osvědčené postupy pro úložiště dotazů
• Monitorování výkonu pomocí úložiště dotazů
• Konfigurace maximálního stupně paralelismu (MAXDOP) ve službě Azure SQL Database