Delen via

Prestaties bewaken met behulp van Query Store

  • Artikel

van toepassing op: SQL Server 2016 (13.x) en latere versies Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (alleen toegewezen SQL-pool)SQL-database in Microsoft Fabric

De functie Query Store biedt inzicht in de keuze en prestaties van queryplannen voor SQL Server, Azure SQL Database, Fabric SQL Database, Azure SQL Managed Instance en Azure Synapse Analytics. Query Store vereenvoudigt het oplossen van prestatieproblemen doordat u snel prestatieverschillen kunt vinden die worden veroorzaakt door wijzigingen in queryplannen. Query Store legt automatisch een geschiedenis van query's, plannen en runtimestatistieken vast en behoudt deze voor uw beoordeling. Hiermee worden gegevens gescheiden door tijdvensters, zodat u databasegebruikspatronen kunt zien en begrijpt wanneer er wijzigingen in het queryplan op de server zijn aangebracht.

U kunt Query Store configureren met behulp van de optie ALTER DATABASE SET.

Belangrijk

Als u Query Store gebruikt voor Just-In-Time-workloadinzichten in SQL Server 2016 (13.x), wilt u de oplossingen voor schaalbaarheid van prestaties zo snel mogelijk installeren in KB-4340759.

Query Store inschakelen

  • Query Store is standaard ingeschakeld voor nieuwe Azure SQL Database- en Azure SQL Managed Instance-databases.
  • Query Store is niet standaard ingeschakeld voor SQL Server 2016 (13.x), SQL Server 2017 (14.x), SQL Server 2019 (15.x). Deze is standaard ingeschakeld in de READ_WRITE-modus voor nieuwe databases vanaf SQL Server 2022 (16.x). Als u functies wilt inschakelen om de prestatiegeschiedenis beter bij te houden, problemen met betrekking tot queryplannen op te lossen en nieuwe mogelijkheden in te schakelen in SQL Server 2022 (16.x), raden we u aan Query Store in te schakelen voor alle databases.
  • Query Store is niet standaard ingeschakeld voor nieuwe Azure Synapse Analytics-databases.

De Query Store-pagina gebruiken in SQL Server Management Studio

  1. Klik in Objectverkenner met de rechtermuisknop op een database en selecteer Eigenschappen.

    Notitie

    Vereist ten minste versie 16 van Management Studio.

  2. Selecteer in het dialoogvenster Database-eigenschappen de pagina Query Store.

  3. Selecteer in de bedieningsmodus (aangevraagd) het lezen en schrijvenvak.

Gebruik Transact-SQL-verklaringen

Gebruik de ALTER DATABASE-instructie om Query Store in te schakelen voor een bepaalde database. Bijvoorbeeld:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

Opties voor het configureren van de Query Store in een Fabric SQL-database met ALTER DATABASE zijn momenteel beperkt.

Schakel in Azure Synapse Analytics de Query Store in zonder extra opties, bijvoorbeeld:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON;

Zie ALTER DATABASE SET Options (Transact-SQL)voor meer syntaxisopties die betrekking hebben op de Query Store.

Notitie

Query Store kan niet worden ingeschakeld voor de master- of tempdb-databases.

Belangrijk

Raadpleeg Best Practice met de Query Store-voor informatie over het inschakelen van Query Store en het aanpassen ervan aan uw workload.

Informatie in de Query Store

Uitvoeringsplannen voor een specifieke query in SQL Server veranderen doorgaans in de loop van de tijd vanwege een aantal verschillende redenen, zoals wijzigingen in statistieken, schemawijzigingen, het maken/verwijderen van indexen, enzovoort. In de procedurecache (waarin queryplannen in de cache worden opgeslagen) wordt alleen het meest recente uitvoeringsplan opgeslagen. Plannen worden ook verwijderd uit de plancache vanwege geheugendruk. Als gevolg hiervan kunnen regressies van queryprestaties die worden veroorzaakt door wijzigingen in het uitvoeringsplan, niet-triviaal en tijdrovend zijn om op te lossen.

Omdat Query Store meerdere uitvoeringsplannen per query bewaart, kan het beleid afdwingen om de queryprocessor te leiden om een specifiek uitvoeringsplan voor een query te gebruiken. Dit wordt aangeduid als plan afdwingen. Het afdwingen van een plan in Query Store wordt geleverd door gebruik te maken van een mechanisme dat vergelijkbaar is met de USE PLAN query-hint, maar er zijn geen wijzigingen in gebruikersapplicaties vereist. Plan afdwingen kan een regressie van queryprestaties oplossen die wordt veroorzaakt door een planwijziging in een zeer korte periode.

Notitie

Query Store verzamelt plannen voor DML-instructies, zoals SELECT, INSERT, UPDATE, DELETE, MERGEen BULK INSERT.

Query Store verzamelt standaard geen plannen voor DDL-instructies, zoals CREATE INDEX, enzovoort. Query Store legt cumulatief resourceverbruik vast door plannen voor de onderliggende DML-instructies te verzamelen. Query Store kan bijvoorbeeld de SELECT- en INSERT-instructies weergeven die intern worden uitgevoerd om een nieuwe index te vullen.

Query Store verzamelt standaard geen gegevens voor systeemeigen gecompileerde opgeslagen procedures. Gebruik sys.sp_xtp_control_query_exec_stats om gegevensverzameling in te schakelen voor systeemeigen opgeslagen procedures.

Wachtstatistieken zijn een andere bron van informatie die helpen bij het oplossen van problemen met prestaties in de Database Engine. Wachtstatistieken waren gedurende lange tijd alleen beschikbaar op exemplaarniveau, waardoor het moeilijk was om backtrack-wachttijden op een specifieke query uit te voeren. Vanaf SQL Server 2017 (14.x) en Azure SQL Database bevat Query Store een dimensie waarmee wachtstatistieken worden bijgehouden. In het volgende voorbeeld kan de Query Store wachtstatistieken verzamelen.

ALTER DATABASE <database_name>
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );

Veelvoorkomende scenario's voor het gebruik van de Query Store-functie zijn:

  • U kunt snel een planprestatieregressie vinden en oplossen door het vorige queryplan opnieuw toe te passen. Corrigeer queries die onlangs achteruit zijn gegaan in de prestaties door wijzigingen in het uitvoeringsplan.
  • Bepaal hoe vaak een query in een bepaald tijdvenster is uitgevoerd, zodat een DBA kan helpen bij het oplossen van prestatieresourceproblemen.
  • Identificeer de belangrijkste n query's (op uitvoeringstijd, geheugenverbruik, enzovoort) in de afgelopen x uur.
  • Controleer de geschiedenis van queryplannen voor een bepaalde query.
  • Analyseer de gebruikspatronen voor resources (CPU, I/O en geheugen) voor een bepaalde database.
  • Identificeer de top n-query's die wachten op middelen.
  • Inzicht in de natuur van het wachten voor een bepaalde query of een bepaald plan.

De Query Store bevat drie winkels:

  • een planopslag voor het persistent maken van de informatie over het uitvoeringsplan.
  • een opslagsysteem voor runtimestatistieken om de informatie over uitvoeringsstatistieken te bewaren.
  • een wachtstatistiekenopslag voor het opslaan van informatie over wachtstatistieken.

Het aantal unieke plannen dat kan worden opgeslagen voor een query in de planopslag, wordt beperkt door de max_plans_per_query configureeroptie. Om de prestaties te verbeteren, wordt de informatie asynchroon naar de opslagplaatsen geschreven. Om het gebruik van ruimte te minimaliseren, worden de uitvoeringsstatistieken van runtime in het archief met runtimestatistieken geaggregeerd in een vast tijdvenster. De informatie in deze winkels is zichtbaar door query's uit te voeren op de Query Store-catalogusweergaven.

De volgende query retourneert informatie over query's, hun plannen, het compileren van tijd- en uitvoeringsstatistieken uit de Query Store.

SELECT Txt.query_text_id, Txt.query_sql_text, Pln.plan_id, Qry.*, RtSt.*
FROM sys.query_store_plan AS Pln
INNER JOIN sys.query_store_query AS Qry
    ON Pln.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id
INNER JOIN sys.query_store_runtime_stats RtSt
ON Pln.plan_id = RtSt.plan_id;

Query Store voor secundaire replica's

van toepassing op: SQL Server (vanaf SQL Server 2022 (16.x))

De functie Query Store voor secundaire replica's maakt dezelfde Query Store-functionaliteit mogelijk voor secundaire replicaworkloads die beschikbaar zijn voor primaire replica's. Wanneer Query Store voor secundaire replica's is ingeschakeld, verzenden replica's de queryuitvoeringsgegevens die normaal gesproken in de Query Store worden opgeslagen naar de primaire replica. De primaire replica bewaart de gegevens vervolgens op schijf in een eigen Query Store. In wezen is er een gedeelde Query Store tussen de primaire en alle secundaire replica's. De Query Store bestaat op de primaire replica en slaat gegevens op voor alle replica's samen.

Zie Query Store voor Always On- beschikbaarheidsgroep secundaire replica'svoor volledige informatie over Query Store voor secundaire replica's.

De functie Geregresseerde queries gebruiken

Nadat u Query Store hebt ingeschakeld, vernieuwt u het databasegedeelte van het deelvenster Objectverkenner om de sectie Query Store toe te voegen.

schermopname van de rapportagestructuur van Query Store in SSMS-objectverkenner.

Notitie

Voor Azure Synapse Analytics zijn Query Store-weergaven beschikbaar onder Systeemweergaven in het databasegedeelte van het deelvenster Objectverkenner.

Selecteer geregresseerde query's om het deelvenster geregresseerde query's te openen in SQL Server Management Studio. In het deelvenster Geregresseerde query's ziet u de query's en plannen in de Query Store. Gebruik de vervolgkeuzelijsten bovenaan om query's te filteren op basis van verschillende criteria: Duur (ms) (standaard), CPU-tijd (ms), Logische leesbewerkingen (KB), Logische schrijfbewerkingen (KB), Fysieke leesbewerkingen (KB), CLR-tijd (ms), DOP, Geheugenverbruik (KB), Aantal rijen, Gebruikte logboekgeheugen (KB), Tijdelijke DB-geheugengebruik (KB) en wachttijd (ms).

Selecteer een plan om het grafische queryplan weer te geven. Knoppen zijn beschikbaar om de bronquery weer te geven, een queryplan af te dwingen en uit te schakelen, tussen raster- en grafiekindelingen te schakelen, geselecteerde plannen te vergelijken (als er meer dan één is geselecteerd) en de weergave te vernieuwen.

Schermopname van het rapport van teruggevallen SQL Server-query's in SSMS Object Explorer.

Als u een plan wilt afdwingen, selecteert u een query en een plan en selecteert u vervolgens Het plan afdwingen. U kunt alleen plannen afdwingen die zijn opgeslagen door de functie van het queryplan en die nog steeds worden bewaard in de cache van het queryplan.

Zoek naar wachtende query's

Vanaf SQL Server 2017 (14.x) en Azure SQL Database zijn wachtstatistieken per query in de loop van de tijd beschikbaar in Query Store.

Bij Query Store worden wachttypen gecombineerd in wachtcategorieën. De toewijzing van wachtcategorieën naar wachttypen is beschikbaar in sys.query_store_wait_stats (Transact-SQL).

Selecteer Querywachtstatistieken om het deelvenster Querywachtstatistieken te openen in SQL Server Management Studio 18.0 of latere versies. In het deelvenster Querywachtstatistieken ziet u een staafdiagram met de bovenste wachtcategorieën in de Query Store. Gebruik de vervolgkeuzelijst bovenaan om een statistische criteria te selecteren voor de wachttijd: gemiddeld, max, min, std dev en totaal (standaard).

schermopname van het rapport SQL Server Query Wait Statistics in SSMS Object Explorer.

Selecteer een wachtcategorie door op de balk te klikken en een detailweergave van de geselecteerde wachtcategorie weer te geven. Dit nieuwe staafdiagram bevat de query's die hebben bijgedragen aan die wachtcategorie.

schermopname van de detailweergave wachtstatistieken van SQL Server-query's in SSMS-objectverkenner.

Gebruik de vervolgkeuzelijst bovenaan om query's te filteren op basis van verschillende wachttijdcriteria voor de geselecteerde wachtcategorie: avg, max, min, std dev en totaal (standaard). Selecteer een plan om het grafische queryplan weer te geven. Knoppen zijn beschikbaar om de oorspronkelijke query weer te geven, een queryplan af te dwingen en op te heffen, en de weergave te vernieuwen.

wachtcategorieën combineren verschillende wachttypen in groepen die van nature vergelijkbaar zijn. Voor verschillende wachtcategorieën is een andere follow-upanalyse vereist om het probleem op te lossen, maar wachttypen binnen dezelfde categorie leiden tot vergelijkbare ervaringen bij het oplossen van problemen. Het aanbieden van de betrokken query boven op de wachttijden zou het ontbrekende onderdeel zijn om de meeste van dergelijke onderzoeken succesvol af te ronden.

Hier volgen enkele voorbeelden hoe u meer inzicht krijgt in uw workload voor en na het introduceren van wachtcategorieën in Query Store:

Vorige ervaring Nieuwe ervaring Actie
Hoge RESOURCE_SEMAPHORE wachttijden per database Hoge geheugenwachttijden in Query Store voor specifieke query's Zoek de query's die het meest veel geheugen verbruiken in Query Store. Deze query's vertragen waarschijnlijk verdere voortgang van de betrokken query's. Overweeg het gebruik van MAX_GRANT_PERCENT queryhint voor deze query's of voor de betrokken query's.
Hoge LCK_M_X wachttijden per database Lange wachttijden in Query Store voor bepaalde query's Controleer de queryteksten voor de betrokken query's en identificeer de doelentiteiten. Zoek in Query Store naar andere query's die dezelfde entiteit wijzigen, die regelmatig worden uitgevoerd en/of een hoge duur hebben. Nadat u deze query's hebt geïdentificeerd, kunt u overwegen de toepassingslogica te wijzigen om gelijktijdigheid te verbeteren of een minder beperkend isolatieniveau te gebruiken.
Hoge PAGEIOLATCH_SH wachttijd per database Io-wachttijden met hoge buffer in Query Store voor specifieke query's Zoek de queries met een groot aantal fysieke leesbewerkingen in Query Store. Als ze overeenkomen met de query's met hoge I/O-wachttijden, kunt u overwegen om een index op de onderliggende entiteit in te voeren om te zoeken in plaats van scans, en zo de IO-overhead van de query's te minimaliseren.
Hoge wachttijden voor SOS_SCHEDULER_YIELD per database Hoge CPU-wachttijden in Query Store voor specifieke query's Zoek de meest cpu-verbruikende query's in Query Store. Identificeer onder hen de query's waarvoor een hoge CPU-trend correleert met hoge CPU-wachttijden voor de betrokken query's. Richt u op het optimaliseren van deze query's: er kan een planregressie of een ontbrekende index zijn.

Configuratieopties

Zie ALTER DATABASE SET options (Transact-SQL)voor de beschikbare opties voor het configureren van Query Store-parameters.

Voer een query uit op de sys.database_query_store_options weergave om de huidige opties van de Query Store te bepalen. Zie sys.database_query_store_optionsvoor meer informatie over de waarden.

Zie Option Managementvoor voorbeelden van het instellen van configuratieopties met behulp van Transact-SQL instructies.

Notitie

Voor Azure Synapse Analytics kan Query Store worden ingeschakeld, net als op andere platforms, maar aanvullende configuratieopties worden niet ondersteund.

Query Store weergeven en beheren via Management Studio of met behulp van de volgende weergaven en procedures.

Query Store-functies

Functies helpen bewerkingen met de Query Store.

sys.fn_stmt_sql_handle_from_sql_stmt (Transact-SQL)

Catalogusweergaven van Query Store

Catalogusweergaven bevatten informatie over de Query Store.

sys.database_query_store_options (Transact-SQL)

sys.query_context_settings (Transact-SQL)

sys.query_store_plan (Transact-SQL)

sys.query_store_query (Transact-SQL)

sys.query_store_query_text (Transact-SQL)

sys.query_store_runtime_stats (Transact-SQL)

sys.query_store_wait_stats (Transact-SQL)

sys.query_store_runtime_stats_interval (Transact-SQL)

sys.database_query_store_internal_state (Transact-SQL)

Opgeslagen procedures van Query Store

Opgeslagen procedures configureren de Query Store.

sp_query_store_flush_db (Transact-SQL)

sp_query_store_reset_exec_stats (Transact-SQL)

sp_query_store_force_plan (Transact-SQL)

sp_query_store_unforce_plan (Transact-SQL)

sp_query_store_remove_plan (Transact-SQL)

sp_query_store_remove_query (Transact-SQL)

sp_query_store_clear_message_queues (Transact-SQL)

sp_query_store_consistency_check (Transact-SQL)1

1 In extreme scenario's kan Query Store een FOUT-status invoeren vanwege interne fouten. Vanaf SQL Server 2017 (14.x), als dit gebeurt, kan Query Store worden hersteld door de sp_query_store_consistency_check opgeslagen procedure in de betreffende database uit te voeren. Zie sys.database_query_store_options voor meer details zoals beschreven in de actual_state_desc kolombeschrijving.

Query Store-onderhoud

Best practices en aanbevelingen voor onderhoud en beheer van de Query Store zijn in dit artikel uitgebreid: Best practices voor beheer van de Query Store -.

Prestatiecontrole en probleemoplossing

Voor meer informatie over het afstemmen van prestaties met Query Store, zie Prestaties afstemmen met de Query Store.

Andere prestatieonderwerpen:

Verwante inhoud