Delen via

Query Store hints

  • Artikel

Van toepassing op: SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed InstanceSQL-database in Microsoft Fabric

In dit artikel wordt beschreven hoe u queryhints toepast met behulp van de Query Store. Query Store-hints bieden een gebruiksvriendelijke methode voor het vormgeven van queryplannen zonder toepassingscode te wijzigen.

Query Store-hints zijn beschikbaar in Azure SQL Database en Azure SQL Managed Instance. Query Store-hints zijn ook een functie die is geïntroduceerd in SQL Server 2022 (16.x).

Voorzichtigheid

Omdat sql Server Query Optimizer doorgaans het beste uitvoeringsplan voor een query selecteert, raden we u aan alleen hints te gebruiken als laatste redmiddel voor ervaren ontwikkelaars en databasebeheerders. Zie queryhintsvoor meer informatie.

Bekijk deze video voor een overzicht van Query Store-hints:

Overzicht

In het ideale voorbeeld selecteert de Query Optimizer een optimaal uitvoeringsplan voor een query.

Als een optimaal plan niet is geselecteerd, wil een ontwikkelaar of DBA mogelijk handmatig optimaliseren voor specifieke voorwaarden. Queryhints worden opgegeven via de OPTION-component en kunnen worden gebruikt om het gedrag van de queryuitvoering te beïnvloeden. Hoewel queryhints helpen gelokaliseerde oplossingen te bieden voor verschillende prestatieproblemen, moeten ze de oorspronkelijke querytekst opnieuw schrijven. Databasebeheerders en ontwikkelaars kunnen mogelijk niet altijd rechtstreeks wijzigingen aanbrengen in Transact-SQL code om een queryhint in te voeren. De Transact-SQL kan in code worden vastgelegd in een toepassing of automatisch worden gegenereerd door de toepassing. Voorheen moest een ontwikkelaar mogelijk afhankelijk zijn van planhandleidingen, wat complex kan zijn in het gebruik.

Zie Ondersteunde queryhintsvoor informatie over welke queryhints kunnen worden toegepast.

Wanneer gebruikt u Query Store-hints

Zoals de naam al aangeeft, wordt deze functie uitgebreid en is afhankelijk van de Query Store-. Query Store maakt het vastleggen van query's, uitvoeringsplannen en bijbehorende runtimestatistieken mogelijk. Query Store vereenvoudigt het algehele proces van prestatieoptimalisatie voor de klantervaring aanzienlijk. SQL Server 2016 (13.x) heeft Query Store voor het eerst geïntroduceerd en nu is deze standaard ingeschakeld in SQL Server 2022 (16.x), Azure SQL Managed Instance en Azure SQL Database.

De werkstroom voor Query Store-hints.

Voorbeelden waarbij Query Store-hints kunnen helpen bij prestatieproblemen op queryniveau:

  • Een query opnieuw compileren voor elke uitvoering.
  • Limiteer de geheugenverleening voor een bulk-insertoperatie.
  • Beperk de maximale mate van parallelle uitvoering bij het bijwerken van statistieken.
  • Gebruik een Hash-join in plaats van een Geneste Lussen-join.
  • Gebruik compatibiliteitsniveau 110 voor een specifieke query terwijl u alle andere items in de database op compatibiliteitsniveau 150 bewaart.
  • Optimalisatie van rijdoel uitschakelen voor een SELECT TOP-query.

Hints gebruiken voor Query Store:

  1. Identificeer de Query Store query_id van de queryverklaring die u wilt wijzigen. U kunt dit op verschillende manieren doen:
    • Query's uitvoeren op de Query Store-catalogusweergaven.
    • Ingebouwde Query Store-rapporten van SQL Server Management Studio gebruiken.
    • Query Performance Insight gebruiken in Azure Portal voor Azure SQL Database.
  2. Voer sys.sp_query_store_set_hints uit met de queryhint-tekenreeks query_id die u op de query wilt toepassen. Deze tekenreeks kan een of meer query hints bevatten. Zie sys.sp_query_store_set_hintsvoor volledige informatie.

Na het maken worden Query Store-hints behouden en overleven ze herstarten en failovers. Query Store-hints overschrijven hints op basis van code op instructieniveau en bestaande hints voor planrichtlijnen.

Als een queryhint in strijd is met wat er mogelijk is voor queryoptimalisatie, wordt de uitvoering van query's niet geblokkeerd en wordt de hint niet toegepast. In de gevallen waarin een hint ertoe zou leiden dat een query mislukt, wordt de hint genegeerd en kunnen de meest recente foutdetails worden weergegeven in sys.query_store_query_hints.

Systeem opgeslagen procedures voor Query Store-hints

Gebruik sys.sp_query_store_set_hintsom hints te maken of bij te werken. Hints worden opgegeven in een geldige tekenreeksindeling N'OPTION (...)'.

  • Wanneer u een Query Store-hint maakt en er geen Query Store-hint bestaat voor een specifieke query_id, wordt er een nieuwe Query Store-hint gemaakt.
  • Wanneer u een Query Store-hint maakt of bijwerkt, als er al een Query Store-hint bestaat voor een specifieke query_id, overschrijft de laatste opgegeven waarde eerder opgegeven waarden voor de bijbehorende query.
  • Als er geen query_id bestaat, wordt er een fout gegenereerd.

Notitie

Zie sys.sp_query_store_set_hintsvoor een volledige lijst met hints die worden ondersteund.

Als u hints wilt verwijderen die zijn gekoppeld aan een query_id, gebruikt u sys.sp_query_store_clear_hints.

XML-kenmerken van uitvoeringsplan

Wanneer hints worden toegepast, verschijnt de volgende resultaatset in het StmtSimple element van het executieplan in XML-formaat.

kenmerk Beschrijving
QueryStoreStatementHintText Werkelijke Query Store-hint(s) toegepast op de query
QueryStoreStatementHintId Unieke identificatie van een query-hint
QueryStoreStatementHintSource Bron van Query Store-hint (bijvoorbeeld: 'Gebruiker')

Notitie

Deze XML-elementen zijn beschikbaar via de uitvoer van de Transact-SQL opdrachten SET STATISTICS XML en SET SHOWPLAN XML.

Hints en functie-interoperabiliteit van Query Store

  • Query Store-hints overschrijven andere hardcoded instructieniveauhints en planhandleidingen.
  • Queries worden altijd uitgevoerd. Tegengestelde Query Store-hints worden genegeerd die anders een fout zouden veroorzaken.
  • Als Query Store-hints in strijd zijn, blokkeert SQL Server de uitvoering van query's niet en wordt de Query Store-hint niet toegepast.
  • Eenvoudige parameterisatie: Query Store-hints worden niet ondersteund voor instructies die in aanmerking komen voor eenvoudige parameterisatie.
  • Geforceerde parameterisatie: de hint RECOMPILE is niet compatibel met geforceerde parameters die zijn ingesteld op databaseniveau. Als de database geforceerde parameterisering heeft en de hint RECOMPILE onderdeel is van de reeks hints die is ingesteld in Query Store voor een query, negeert SQL Server de hint RECOMPILE en zal SQL Server eventuele andere hints toepassen indien van toepassing.
  • Handmatig gemaakte Query Store-hints zijn uitgesloten van opschonen. De hint en de query worden niet opgeschoond uit Query Store door de automatische retentie van het opnamebeleid.
    • Query's kunnen handmatig worden verwijderd door gebruikers, waardoor ook de bijbehorende Query Store-hint wordt verwijderd.
    • Query Store-hints die automatisch worden gegenereerd door de CE-feedback worden opgeschoond door de automatische retentie van het opnamebeleid.
    • DOP-feedback en geheugen toewijzingsfeedback beïnvloeden het gedrag van query's zonder Query Store-hints te gebruiken. Wanneer query's worden opgeschoond door automatische retentie van het opnamebeleid, worden ook DOP-feedback en feedbackgegevens voor geheugentoekenningen opgeschoond.
    • U kunt handmatig dezelfde Query Store-hint aanmaken die door CE-feedback is ingesteld. Hierdoor zal de query met de hint niet langer onderhevig zijn aan verwijdering door het automatische retentiebeleid van het opnamebeleid.

Query Store-hints en beschikbaarheidsgroepen

Query Store-hints hebben geen effect op secundaire replica's, tenzij Query Store voor secundaire replica's is ingeschakeld. Voor meer informatie, zie Query Store voor secundaire replica's.

  • Vóór SQL Server 2022 (16.x) kunnen Query Store-hints worden toegepast op de primaire replica van een beschikbaarheidsgroep.
  • Vanaf SQL Server 2022 (16.x) wanneer Query Store voor secundaire replica's is ingeschakeld, zijn Query Store-hints ook replicabewust voor secundaire replica's in beschikbaarheidsgroepen.
  • U kunt een Query Store-hint toevoegen aan een specifieke replica of replicaset wanneer Query Store is ingeschakeld voor secundaire replica's. In sys.sp_query_store_set_query_hintswordt dit ingesteld door de parameter @query_hint_scope, die is geïntroduceerd in SQL Server 2022 (16.x).
  • Zoek de beschikbare replicasets door een query uit te voeren op sys.query_store_replicas.
  • Plannen zoeken die op secundaire replica's worden afgedwongen met sys.query_store_plan_forcing_locations.

Aanbevolen procedures voor Query Store

  • Voltooi onderhoud van indexen en statistieken voordat u query's evalueert op mogelijke nieuwe Query Store-hints.
  • Test uw toepassingsdatabase op het meest recente compatibiliteitsniveau voordat u Hints voor Query Store gebruikt.
    • Zo is de optimalisatie van parametergevoelig plan (PSP) geïntroduceerd in SQL Server 2022 (16.x) (compatibiliteitsniveau 160), waarbij meerdere actieve abonnementen per query worden gebruikt om niet-uniforme gegevensdistributies aan te pakken. Als uw omgeving het meest recente compatibiliteitsniveau niet kan gebruiken, kunnen Query Store-hints met behulp van de HINT RECOMPILE worden gebruikt op elk ondersteund compatibiliteitsniveau.
  • Query Store-hints overschrijven het gedrag van het SQL Server-queryplan. Het wordt aanbevolen om alleen Query Store-hints te gebruiken wanneer het nodig is om prestatieproblemen op te lossen.
  • Het wordt aanbevolen om hints voor Query Store opnieuw te evalueren, hints op verklaringsniveau, planhandleidingen en geforceerde Query Store plannen bij elke verandering in gegevensdistributies en tijdens database migratieprojecten. Wijzigingen in gegevensdistributie kunnen ertoe leiden dat Query Store-hints suboptimale uitvoeringsplannen genereren.

Voorbeelden

Een. Demo van hints voor Query Store

In het volgende overzicht van Query Store-hints in Azure SQL Database wordt een geïmporteerde database gebruikt via een BACPAC-bestand (.bacpac). Leer hoe je een nieuwe database kunt importeren naar een Azure SQL Database-server. Raadpleeg Quickstart: Een BACPAC-bestand importeren in een database.

-- ************************************************************************ --
-- 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. Een query identificeren in Query Store

In het volgende voorbeeld worden de query's van sys.query_store_query_text en sys.query_store_query gebruikt om de query_id voor een uitgevoerd tekstfragment van een query te retourneren.

In deze demo bevindt de query die we proberen af te stemmen zich in de SalesLT voorbeelddatabase:

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;

Query Store geeft niet onmiddellijk querygegevens weer in de systeemweergaven.

Identificeer de query in de systeemcatalogusweergaven van Query Store:

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

In de volgende voorbeelden is het vorige queryvoorbeeld in de SalesLT-database geïdentificeerd als query_id 39.

Pas na identificatie de hint toe om een maximale geheugentoekenningsgrootte af te dwingen in procent van de geconfigureerde geheugenlimiet op de query_id:

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

U kunt ook queryhints toepassen met de volgende syntaxis, bijvoorbeeld de optie om de verouderde kardinaliteitsschatter af te dwingen:

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

U kunt meerdere queryhints toepassen met een door komma's gescheiden lijst:

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

Controleer de Query Store-hint voor 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;

Verwijder ten slotte de hint uit query_id 39 met behulp van sp_query_store_clear_hints.

EXEC sys.sp_query_store_clear_hints @query_id = 39;

Verwante inhoud