Lezen in het Engels

Share via

Aanbevolen procedures voor het beheren van de Query Store

  • Artikel

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

In dit artikel vindt u een overzicht van het beheer van de SQL Server Query Store en de omringende functies.

Notitie

In SQL Server 2022 (16.x) is Query Store nu standaard ingeschakeld voor alle nieuw gemaakte SQL Server-databases om de prestatiegeschiedenis beter bij te houden, problemen met betrekking tot queryplannen op te lossen en nieuwe mogelijkheden voor queryprocessor in te schakelen.

Standaardinstellingen voor Query Store in Azure SQL Database

In deze sectie worden optimale configuratiestandaarden in Azure SQL Database beschreven die zijn ontworpen om een betrouwbare werking van de Query Store en afhankelijke functies te garanderen. De standaardconfiguratie is geoptimaliseerd voor het verzamelen van continue gegevens. Dit is minimale tijd die wordt besteed aan off-/READ_ONLY statussen. Zie ALTER DATABASE SET options (Transact-SQL)voor meer informatie over alle beschikbare Query Store-opties.

Configuratie Beschrijving Verstek Commentaar
MAX_STORAGE_SIZE_MB Hiermee geeft u de limiet op voor de gegevensruimte die Query Store in de klantdatabase kan opnemen 100 vóór SQL Server 2019 (15.x)
1000 vanaf SQL Server 2019 (15.x)		 Verplicht op nieuwe databases
INTERVAL_LENGTE_MINUTEN Hiermee definieert u de grootte van het tijdvenster waarin verzamelde runtimestatistieken voor queryplannen worden samengevoegd en behouden. Elk actief queryplan heeft maximaal één rij gedurende een bepaalde periode die door deze configuratie is gedefinieerd. 60 Verplicht voor nieuwe databases
VEROUDERDE_VRAAG_DREMPEL_DAGEN Op tijd gebaseerd opschoonbeleid waarmee de bewaarperiode van persistente runtimestatistieken en inactieve query's wordt bepaald 30 Afgedwongen voor nieuwe databases en databases met eerdere standaardwaarde (367)
GROOTTE_GEBASEERDE_OOPEINGS_MODUS Hiermee geeft u op of automatisch opschonen van gegevens plaatsvindt wanneer de grootte van queryopslag de limiet nadert AUTO Afgedwongen voor alle databases
QUERY_CAPTURE_MODE Hiermee geeft u op of alle query's of alleen een subset van query's worden bijgehouden AUTO Afgedwongen voor alle databases
DATA_FLUSH_INTERVAL_SECONDS Hiermee geeft u de maximale periode op waarin vastgelegde runtimestatistieken worden bewaard in het geheugen, voordat de schijf wordt leeggemaakt 900 Afgedwongen voor nieuwe databases

Belangrijk

Deze standaardwaarden worden automatisch toegepast in de laatste fase van de activering van Query Store in een Azure SQL Database. Nadat azure SQL Database is ingeschakeld, worden configuratiewaarden die door klanten zijn ingesteld, niet gewijzigd, tenzij ze een negatieve invloed hebben op de primaire workload of betrouwbare bewerkingen van de Query Store.

Notitie

Query Store kan niet worden uitgeschakeld in één database en elastische pool van Azure SQL Database. Het uitvoeren van ALTER DATABASE [database] SET QUERY_STORE = OFF zal de waarschuwing 'QUERY_STORE=OFF' is not supported in this version of SQL Server. retourneren

Als u uw aangepaste instellingen wilt behouden, gebruikt u ALTER DATABASE met Query Store-opties om de configuratie terug te zetten naar de vorige status. Bekijk beste praktijken met de Query Store om te leren hoe je optimale configuratieparameters kunt kiezen.

De optimale Opnamemodus voor Query Store instellen

Behoud de meest relevante gegevens in Query Store. In de volgende tabel worden typische scenario's beschreven voor elke Opnamemodus van Query Store:

Opnamemodus van Query Store Scenario
Alle Analyseer uw werklast grondig met betrekking tot de vormen van alle query's, hun uitvoeringsfrequenties en andere statistieken.

Identificeer nieuwe query's in uw workload.

Detecteren of ad-hocqueries worden gebruikt om kansen te identificeren voor gebruikers- of automatische parameterisatie.

Opmerking: dit is de standaardopnamemodus in SQL Server 2016 (13.x) en SQL Server 2017 (14.x).
Automatisch Richt uw aandacht op relevante en bruikbare query's. Een voorbeeld hiervan zijn query's die regelmatig worden uitgevoerd of die een aanzienlijk resourceverbruik hebben.

Opmerking: In SQL Server 2019 (15.x) en latere versies is dit de standaardopnamemodus.
Geen U hebt de queryset die u in runtime wilt bewaken al vastgelegd en u wilt de afleiding die andere query's kunnen introduceren, voorkomen.

Geen is geschikt voor het testen en benchmarken van omgevingen.

Geen is ook geschikt voor softwareleveranciers die een Query Store-configuratie verzenden die is geconfigureerd voor het bewaken van hun toepassingsworkload.

Het gebruik van 'geen' moet voorzichtig gebeuren, omdat u mogelijk kansen mist om belangrijke nieuwe query's bij te houden en te optimaliseren. Vermijd het gebruik van Geen, tenzij u een specifiek scenario hebt waarvoor dit is vereist.
aangepaste SQL Server 2019 (15.x) heeft een aangepaste opnamemodus geïntroduceerd onder de opdracht ALTER DATABASE ... SET QUERY_STORE. Hoewel Automatisch standaard is en wordt aanbevolen, kunnen databasebeheerders aangepaste opnamebeleidsregels gebruiken om het gedrag van de Query Store-opname verder af te stemmen als er nog steeds zorgen zijn over de overhead van Query Store. Zie Aangepaste opnamebeleidsregels verderop in dit artikel voor meer informatie en aanbevelingen. Zie ALTER DATABASE SET Optionsvoor meer informatie over deze syntaxis.

Notitie

Cursors, query's in opgeslagen procedures en systeemeigen gecompileerde query's worden altijd vastgelegd wanneer de Opnamemodus van Query Store is ingesteld op Alle, Automatischof Aangepaste. Als u systeemeigen gecompileerde query's wilt vastleggen, schakelt u het verzamelen van statistieken per query in met behulp van sys.sp_xtp_control_query_exec_stats.

De meest relevante gegevens in Query Store behouden

Configureer Query Store zodanig dat deze alleen de relevante gegevens bevat, zodat deze continu wordt uitgevoerd en biedt een geweldige probleemoplossingservaring met een minimale impact op uw normale werkbelasting.

De volgende tabel bevat aanbevolen procedures:

Best practice Instelling
Beperk de bewaarde historische gegevens. Configureer op tijd gebaseerd beleid voor het activeren van automatisch ops schonen.
Filter niet-relevante vragen. Configureer Query Store Capture Mode naar Auto.
Verwijder minder relevante query's wanneer de maximale grootte is bereikt. Activeer het opschoonbeleid op basis van grootte.

Aangepaste beleidsregels voor vastleggen

Wanneer de custom Query Store Capture Mode is ingeschakeld, zijn er extra Query Store-configuraties beschikbaar onder een nieuwe instelling voor het vastleggen van Query's om de gegevensverzameling op een specifieke server nauwkeurig af te stemmen.

De nieuwe aangepaste instellingen bepalen wat er gebeurt tijdens de interne tijdsdrempel van het vastlegbeleid. Dit is een tijdsgrens waarbij de configureerbare voorwaarden worden geëvalueerd en, indien van toepassing, de query in aanmerking komt om te worden vastgelegd door Query Store.

De Query Store-opnamemodus geeft het queryopnamebeleid voor Query Store op.

  • Alle: Vangt alle queries op. Deze optie is de standaardinstelling in SQL Server 2016 (13.x) en SQL Server 2017 (14.x).
  • Automatisch: onregelmatige query's en query's met een onbelangrijke compileer- en uitvoeringsduur worden genegeerd. Drempelwaarden voor het aantal uitvoeringen, compileren en runtime worden intern bepaald. Vanaf SQL Server 2019 (15.x) is dit de standaardoptie.
  • Geen: Query Store stopt met het vastleggen van nieuwe query's.
  • Aangepaste: hiermee kunt u extra controle uitoefenen en het beleid voor gegevensverzameling nauwkeurig afstemmen. Met de nieuwe aangepaste instellingen wordt gedefinieerd wat er gebeurt tijdens de interne drempelwaarde voor het vastleggen van beleidstijd. Dit is een tijdsgrens waarbij de configureerbare voorwaarden worden geëvalueerd en, indien van toepassing, de query in aanmerking komt om te worden vastgelegd door Query Store.

Het afstemmen van een geschikt aangepast opnamebeleid voor uw omgeving moet worden overwogen wanneer:

  • De database is erg groot.
  • De database heeft een groot aantal unieke ad hoc-zoekopdrachten.
  • De database heeft specifieke grootte- of groeibeperkingen.

De nieuwste versie van SSMS (SQL Server Management Studio) downloaden

Huidige instellingen weergeven in Management Studio:

  1. Klik in SQL Server Management Studio Object Explorer met de rechtermuisknop op de database.
  2. Selecteer eigenschappen.
  3. Selecteer Query Store-. Controleer op de pagina Query Store of de Operation Mode (Aangevraagd) is lees-schrijf.
  4. Wijzig de Query Store-opnamemodus naar aangepaste .
  5. Let op de vier capturebeleidsvelden onder Query Store Capture Policy zijn nu ingeschakeld en configureerbaar.

Voorbeeld van aangepast beleid voor vastleggen

In het volgende voorbeeld wordt QUERY_CAPTURE_MODE ingesteld op AUTO en wordt een aangepaste opnamemodus ingesteld. Elk van de volgende stelt het aangepaste capture-beleid in op de standaardwaarde in SQL Server 2022 (16.x). Overweeg deze waarden aan te passen om het aantal vastgelegde query's te verminderen en daarom de footprint op schijf van de Query Store te verminderen. Het wordt aanbevolen deze waarden geleidelijk te wijzigen met kleine stappen.

SQL 
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

Met de volgende voorbeeldquery wordt een bestaande Query Store gewijzigd om een aangepast capture-beleid te gebruiken waarmee de standaardinstellingen voor EXECUTION_COUNT en TOTAL_COMPILE_CPU_TIME_MSworden overschreven.

SQL 
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        EXECUTION_COUNT = 100,
        TOTAL_COMPILE_CPU_TIME_MS = 10000
      )
    );

Maximale grootte van Query Store

De standaardwaarde voor maximale grootte van de Query Store is 1000 MB, te beginnen in SQL Server 2019 (15.x). In eerdere versies was de standaardwaarde 100 MB. Het verhogen van de maximale grootte van de Query Store is geschikt in een drukke database met veel unieke queryplannen. Het aanpassen van het opnamebeleid (zie vorige sectie) is een belangrijkere overweging om de grootte van de Query Store op schijf te beperken en om te voorkomen dat de Query Store READ_ONLY modus invoert. Query Store verzamelt query's, uitvoeringsplannen en statistieken, maar de grootte ervan in de database groeit totdat deze limiet is bereikt. Wanneer dat gebeurt, wijzigt Query Store automatisch de bewerkingsmodus in READ_ONLY en stopt het verzamelen van nieuwe gegevens, wat betekent dat uw prestatieanalyse niet meer nauwkeurig is.

  • In SQL Server en Azure SQL Managed Instance wordt MAX_STORAGE_SIZE_MB limiet niet strikt afgedwongen.
  • In Azure SQL Database is de maximaal toegestane MAX_STORAGE_SIZE_MB waarde 10.240 MB.

De opslaggrootte wordt alleen gecontroleerd wanneer Query Store gegevens naar schijf schrijft. Dit interval wordt ingesteld door de optie DATA_FLUSH_INTERVAL_SECONDS of de dialoogvensteroptie Query Store van Management Studio interval voor het leegmaken van gegevens.

  • De standaardwaarde voor het interval is 900 seconden (of 15 minuten).
  • Als de Query Store de MAX_STORAGE_SIZE_MB limiet tussen controles van de opslaggrootte heeft overschreden, wordt deze overgezet naar de modus Alleen-lezen.
  • Als SIZE_BASED_CLEANUP_MODE is ingeschakeld, wordt het opschoonmechanisme voor het afdwingen van de MAX_STORAGE_SIZE_MB limiet ook geactiveerd.
    • Zodra er voldoende ruimte is gewist, schakelt de Query Store-modus automatisch terug naar READ_WRITE modus.

Zie ALTER DATABASE SET OPTION MAX_STORAGE_SIZE_MBvoor meer informatie.

Interval voor leegmaken van gegevens (minuten)

Het interval voor het leegmaken van gegevens definieert de frequentie voordat verzamelde runtimestatistieken op schijf worden bewaard. In SQL Server Management Studio is de waarde in minuten, maar in Transact-SQL wordt deze in seconden uitgedrukt. De standaardwaarde is 15 minuten (900 seconden).

  • Door het interval voor het leegmaken van gegevens te verhogen, kan het totale I/O-effect van de Query Store-opslag worden verminderd, maar de I/O-werkbelasting van de opslag wordt meer , met minder maar zwaardere gevolgen voor schijfgebruik. Overweeg om een hogere waarde te gebruiken als uw workload geen groot aantal verschillende query's en plannen genereert, of als u langere tijd kunt weerstaan om gegevens op te slaan voordat een database wordt afgesloten.
  • Als u het interval voor het leegmaken van gegevens verlaagt, neemt de hoeveelheid Query Store-gegevens af die verloren gaan in het geval van afsluiten, stroomverlies of failover. Het kan ook de I/O-impact van de opslag van Query Store vereffenen door vaker naar de schijf te schrijven, maar met minder gegevens.

Notitie

Met traceringsvlag 7745 voorkomt u dat Query Store-gegevens naar de schijf worden geschreven in het geval van een failover- of afsluitopdracht. Zie Query Store gebruiken op bedrijfskritieke serversvoor meer informatie.

Standaardinstellingen voor Query Store wijzigen

Configureer Query Store op basis van de vereisten voor het oplossen van problemen met workloads en prestaties. De standaardparameters zijn goed genoeg om te starten, maar u moet controleren hoe Query Store zich na verloop van tijd gedraagt en de configuratie dienovereenkomstig aanpassen.

Huidige instellingen van Query Store weergeven

Bekijk de huidige Query Store-instellingen in SQL Server Management Studio (SSMS) of T-SQL.

De nieuwste versie van SSMS (SQL Server Management Studio) downloaden

Huidige instellingen weergeven in Management Studio:

  1. Klik in SQL Server Management Studio Object Explorer met de rechtermuisknop op de database.
  2. Selecteer eigenschappen.
  3. Selecteer Query Store-.

Met het volgende script wordt een nieuwe waarde ingesteld voor maximale grootte (MB):

SQL 
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);

Gebruik SQL Server Management Studio of Transact-SQL om een andere waarde in te stellen voor gegevensspoelinterval:

SQL 
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);

interval voor het verzamelen van statistieken: definieert het granulariteitsniveau voor de verzamelde runtimestatistiek, uitgedrukt in minuten. De standaardwaarde is 60 minuten. Overweeg om een lagere waarde te gebruiken als u een fijnere granulariteit of minder tijd nodig hebt om problemen te detecteren en te verhelpen. Houd er rekening mee dat de waarde rechtstreeks van invloed is op de grootte van Query Store-gegevens. Gebruik SQL Server Management Studio of Transact-SQL om een andere waarde in te stellen voor verzamelingsinterval voor statistieken:

SQL 
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60);

drempel voor verouderde query's (dagen): Tijdgebaseerd opschoonbeleid dat de bewaarperiode voor persistente runtimestatistieken en inactieve query's bepaalt, uitgedrukt in dagen. Query Store is standaard geconfigureerd om de gegevens 30 dagen te bewaren, wat mogelijk onnodig lang is voor uw scenario.

Vermijd het bewaren van historische gegevens die u niet van plan bent te gebruiken. Deze procedure vermindert de wijzigingen in de status Alleen-lezen. De grootte van Query Store-gegevens en de tijd die nodig is om het probleem te detecteren en te verhelpen, is voorspelbaarder. Gebruik Management Studio of het volgende script om op tijd gebaseerde opschoonbeleid te configureren:

SQL 
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90));

Op grootte gebaseerde opschoningsmodus: Hiermee geeft u op of automatisch opschonen van gegevens plaatsvindt wanneer de grootte van Query Store de limiet nadert. Activeer op grootte gebaseerde opschoning om ervoor te zorgen dat Query Store altijd wordt uitgevoerd in de lees-/schrijfmodus en de meest recente gegevens verzamelt. Er is geen garantie dat het opschonen van de Query Store onder zware werkbelastingen, consistent de gegevensgrootte onder de limiet zal houden. Het is mogelijk dat het automatisch opschonen van gegevens achtervalt en (tijdelijk) overschakelt naar de modus Alleen-lezen.

SQL 
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

Query Store-opnamemodus: hiermee geeft u het queryopnamebeleid voor Query Store op.

  • Alle: legt alle queries vast. Deze optie is de standaardinstelling in SQL Server 2016 (13.x) en SQL Server 2017 (14.x).
  • Automatisch: onregelmatige query's en query's met een onbelangrijke compileer- en uitvoeringsduur worden genegeerd. Drempelwaarden voor het aantal uitvoeringen, compileren en runtime worden intern bepaald. Vanaf SQL Server 2019 (15.x) is dit de standaardoptie.
  • Geen: Query Store stopt met het vastleggen van nieuwe query's.
  • Aangepaste: Hiermee kun je aanvullende controle uitoefenen en het beleid voor gegevensverzameling nauwkeurig afstemmen. Met de nieuwe aangepaste instellingen wordt gedefinieerd wat er gebeurt tijdens de interne drempelwaarde voor het vastleggen van beleidstijd. Dit is een tijdsgrens waarbij de configureerbare voorwaarden worden geëvalueerd en, indien van toepassing, de query in aanmerking komt om te worden vastgelegd door Query Store.

Belangrijk

Cursors, query's in opgeslagen procedures en systeemeigen gecompileerde query's worden altijd vastgelegd wanneer de Opnamemodus van Query Store is ingesteld op Alle, Automatischof Aangepaste. Als u systeemeigen gecompileerde query's wilt vastleggen, schakelt u het verzamelen van statistieken per query in met behulp van sys.sp_xtp_control_query_exec_stats.

Met het volgende script wordt QUERY_CAPTURE_MODE ingesteld op AUTO:

SQL 
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

Voorbeelden

In het volgende voorbeeld wordt QUERY_CAPTURE_MODE ingesteld op AUTO en worden andere aanbevolen opties ingesteld in SQL Server 2016 (13.x):

SQL 
ALTER DATABASE [QueryStoreDB]
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 = 1000,
      INTERVAL_LENGTH_MINUTES = 60
    );

In het volgende voorbeeld wordt QUERY_CAPTURE_MODE ingesteld op AUTO en worden andere aanbevolen opties ingesteld in SQL Server 2017 (14.x) om wachtstatistieken op te nemen:

SQL 
ALTER DATABASE [QueryStoreDB]
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 = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON
    );

In het volgende voorbeeld wordt het aangepaste opnamebeleid ingesteld op de standaardinstellingen van SQL Server 2019 (15.x), in plaats van de nieuwe standaardmodus voor automatisch vastleggen. Zie <query_capture_policy_option_list>voor meer informatie over aangepaste opties en standaardinstellingen voor vastleggen.

SQL 
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

Query Store-onderhoud

In deze sectie vindt u enkele richtlijnen voor het beheren van de functie Query Store zelf.

Query Store-toestand

Query Store slaat zijn gegevens op in de gebruikersdatabase en daarom heeft het een groottebeperking (geconfigureerd met MAX_STORAGE_SIZE_MB). Als gegevens in Query Store de limiet bereiken, wordt de status van lezen/schrijven automatisch gewijzigd in alleen-lezen en wordt het verzamelen van nieuwe gegevens gestopt.

Query sys.database_query_store_options om te bepalen of Query Store momenteel actief is en of deze momenteel runtimestatistieken verzamelt of niet.

SQL 
SELECT actual_state, actual_state_desc, readonly_reason,
    current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

De Query Store-status wordt bepaald door de kolom actual_state. Als de status anders is dan de gewenste status, kan de kolom readonly_reason u meer informatie geven. Wanneer de grootte van Query Store het quotum overschrijdt, schakelt de functie over naar read_only modus en geeft deze een reden op. Zie sys.database_query_store_optionsvoor meer informatie over redenen.

Query Store-opties ophalen

Voer het volgende uit in een gebruikersdatabase voor gedetailleerde informatie over de status van Query Store.

SQL 
SELECT * FROM sys.database_query_store_options;

Het Query Store-interval instellen

U kunt het interval voor het aggregeren van queryruntimestatistieken overschrijven (standaard is 60 minuten). Nieuwe waarde voor interval wordt weergegeven via sys.database_query_store_options weergave.

SQL 
ALTER DATABASE <database_name>
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

Willekeurige waarden zijn niet toegestaan voor INTERVAL_LENGTH_MINUTES. Gebruik een van de volgende intervallen: 1, 5, 10, 15, 30, 60 of 1440 minuten.

Notitie

Voor Azure Synapse Analytics wordt het aanpassen van configuratieopties voor Query Store, zoals in deze sectie wordt gedemonstreerd, niet ondersteund.

Query Store-ruimtegebruik

Als u de huidige grootte van de Query Store wilt controleren en de limiet wilt beperken, voert u de volgende instructie uit in de gebruikersdatabase.

SQL 
SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Als de Query Store-opslag vol is, gebruikt u de volgende instructie om de opslag uit te breiden.

SQL 
ALTER DATABASE <database_name>
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);

Query Store-opties instellen

U kunt meerdere Query Store-opties tegelijk instellen met één ALTER DATABASE-instructie.

SQL 
ALTER DATABASE <database name>
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 3000,
    MAX_STORAGE_SIZE_MB = 500,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 1000,
    WAIT_STATS_CAPTURE_MODE = ON
);

Zie ALTER DATABASE SET Options (Transact-SQL)voor de volledige lijst met configuratieopties.

De ruimte opschonen

Interne tabellen van Query Store worden gemaakt in de primaire bestandsgroep tijdens het maken van de database en die configuratie kan later niet meer worden gewijzigd. Als u onvoldoende ruimte hebt, wilt u mogelijk oudere Query Store-gegevens wissen met behulp van de volgende instructie.

SQL 
ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

U kunt ook alleen ad-hoc querygegevens wissen, omdat deze minder relevant zijn voor queryoptimalisaties en plananalyse, maar net zoveel ruimte in beslag nemen.

In Azure Synapse Analytics is het wissen van de Query Store niet beschikbaar. Gegevens worden de afgelopen zeven dagen automatisch bewaard.

Adhocqueries verwijderen

Hiermee worden ad-hoc- en interne query's uit de Query Store verwijderd, zodat de Query Store geen ruimte meer heeft en query's verwijdert die we echt moeten bijhouden.

SQL 
SET NOCOUNT ON
-- This purges adhoc and internal queries from
-- the Query Store in the current database
-- so that the Query Store does not run out of space
-- and remove queries we really need to track

DECLARE @id int;
DECLARE adhoc_queries_cursor CURSOR
FOR
    SELECT q.query_id
    FROM sys.query_store_query_text AS qt
    JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
    JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
    JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
    WHERE q.is_internal_query = 1  -- is it an internal query then we dont care to keep track of it
       OR q.object_id = 0 -- if it does not have a valid object_id then it is an adhoc query and we don't care about keeping track of it
    GROUP BY q.query_id
    HAVING MAX(rs.last_execution_time) < DATEADD (minute, -5, GETUTCDATE())  -- if it has been more than 5 minutes since the adhoc query ran
    ORDER BY q.query_id;
OPEN adhoc_queries_cursor ;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
WHILE @@fetch_status = 0
BEGIN
    PRINT 'EXEC sp_query_store_remove_query ' + str(@id);
    EXEC sp_query_store_remove_query @id;
    FETCH NEXT FROM adhoc_queries_cursor INTO @id;
END
CLOSE adhoc_queries_cursor;
DEALLOCATE adhoc_queries_cursor;

U kunt uw eigen procedure definiëren met verschillende logica voor het wissen van gegevens die u niet meer wilt.

In het vorige voorbeeld wordt de uitgebreide opgeslagen procedure sp_query_store_remove_query gebruikt om onnodige gegevens te verwijderen. U kunt ook het volgende doen:

  • Gebruik sp_query_store_reset_exec_stats om runtimestatistieken voor een bepaald plan te wissen.
  • Gebruik sp_query_store_remove_plan om één abonnement te verwijderen.

Verwante inhoud