Feedback voor geheugentoewijzing

Van toepassing op: SQL Server 2017 (14.x) en latere versies, Azure SQL Managed Instance, Azure SQL Database

Soms wordt een query uitgevoerd met een geheugenruimte die te groot of te klein is. Als de geheugenaanvraag te groot is, beperken we parallellisme op de server. Als het te klein is, kunnen we naar de schijf schrijven, wat een dure operatie is. Geheugentoewijzingsfeedback probeert de geheugenbehoeften van een eerdere uitvoering te registreren (met percentielfeedback, op basis van meerdere eerdere uitvoeringen). Op basis van deze historische querygegevens past geheugentoekenningsfeedback de toekenning aan de query aan dienovereenkomstig aan voor volgende uitvoeringen.

Deze functie is vrijgegeven in drie golven. Feedback over geheugentoewijzing in batchmodus, gevolgd door feedback over geheugentoewijzing in rijmodus, en SQL Server 2022 (16.x) introduceerde geheugentoewijzingsfeedback op schijfpersistentie met behulp van de Query Store en een verbeterd algoritme dat percentieltoewijzing wordt genoemd.

Feedback geven over geheugen in batchmodus

Van toepassing op: SQL Server (vanaf SQL Server 2017 (14.x)), Azure SQL Database, Azure SQL Managed Instance (vanaf databasecompatibiliteitsniveau 140)

Het uitvoeringsplan van een query bevat het minimaal vereiste geheugen dat nodig is voor uitvoering en de ideale geheugentoekenningsgrootte om alle rijen in het geheugen te laten passen. Prestaties lijden wanneer geheugentoekenningen onjuist zijn gedimensioneerd. Overmatige subsidies resulteren in verspild geheugen en verminderde gelijktijdigheid. Onvoldoende geheugentoekenningen veroorzaken kostbare uitstroom naar schijf. Door herhalende workloads aan te pakken, herrekent batchmodus-geheugentoekenningsfeedback het werkelijke geheugen dat nodig is voor een query en werkt vervolgens de toekenningswaarde voor het cacheplan bij. Wanneer een identieke query-instructie wordt uitgevoerd, gebruikt de query de herziene geheugentoekenningsgrootte, waardoor overmatige geheugentoekenningen worden verminderd die van invloed zijn op gelijktijdigheid en het oplossen van onderschatte geheugentoekenningen die dure overloop naar schijf veroorzaken.

In de volgende grafiek ziet u een voorbeeld van het gebruik van adaptieve geheugenfeedback in batchmodus. Voor de eerste uitvoering van de query was de duur 88 seconden vanwege hoge lekkages:

DECLARE @EndTime datetime = '2016-09-22 00:00:00.000';
DECLARE @StartTime datetime = '2016-09-15 00:00:00.000';

SELECT TOP 10 hash_unique_bigint_id
FROM dbo.TelemetryDS
WHERE Timestamp BETWEEN @StartTime AND @EndTime
GROUP BY hash_unique_bigint_id
ORDER BY MAX(max_elapsed_time_microsec) DESC;

Als feedback over geheugentoekenning is ingeschakeld, is de duur voor de tweede uitvoering 1 seconde (van 88 seconden omlaag), worden lekkages volledig verwijderd en is de toekenning hoger:

Geheugentoewijzing feedback grootte

Als voor een overmatige geheugentoekenningsvoorwaarde het verleende geheugen meer dan twee keer zo groot is als het werkelijke gebruikte geheugen, wordt de geheugentoekenning opnieuw berekend en wordt het in de cache geplaatste plan bijgewerkt. Plannen met geheugentoekenningen onder 1 MB worden niet opnieuw berekend voor overschrijdingen.

Voor een onvoldoende geheugentoekenningsvoorwaarde die resulteert in een overloop naar schijf voor batchmodusoperators, zal feedback over geheugentoekenningen een herberekening van de geheugentoekenning activeren. Overloopgebeurtenissen worden gerapporteerd aan geheugenverdelingsfeedback en kunnen worden weergegeven via het spilling_report_to_memory_grant_feedback uitgebreide event. Deze gebeurtenis retourneert het knooppunt-ID van het plan en de gespilde gegevensgrootte van dat knooppunt.

De aangepaste geheugentoekenning wordt weergegeven in het werkelijke plan (na uitvoering) via de GrantedMemory eigenschap.

U kunt deze eigenschap zien in de hoofdoperator van het grafische showplan of in de XML-uitvoer van het showplan:

<MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="10336" RequiredMemory="1024" DesiredMemory="10336" RequestedMemory="10336" GrantWaitTime="0" GrantedMemory="10336" MaxUsedMemory="9920" MaxQueryMemory="725864" />

Als u wilt dat uw workloads automatisch in aanmerking komen voor deze verbetering, schakelt u compatibiliteitsniveau 140 in voor de database.

Voorbeeld:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140;

Geheugentoewijzing feedback en parametersensitieve scenario's

Voor verschillende parameterwaarden zijn mogelijk ook verschillende queryplannen vereist om optimaal te blijven. Dit type query wordt gedefinieerd als 'parametergevoelig'.

Voor parametergevoelige plannen wordt feedback over geheugenvergunning uitgeschakeld voor een query als de geheugenvereisten instabiel zijn. De functie feedback over geheugentoekenning is uitgeschakeld na verschillende herhaalde uitvoeringen van de query. Dit kan worden waargenomen door de memory_grant_feedback_loop_disabled uitgebreide gebeurtenis te bewaken. Deze voorwaarde wordt beperkt met de persistentie- en percentielmodus voor feedback over geheugentoekenning die is geïntroduceerd in SQL Server 2022 (16.x). De persistentiefunctie van feedback over geheugentoekenning vereist dat Query Store is ingeschakeld in de database en is ingesteld op de modus 'lezen schrijven'.

Zie de architectuurhandleiding voor queryverwerking voor meer informatie over parametersniffing en parametergevoeligheid.

Cache voor geheugenverstrekkingsfeedback

Feedback kan worden opgeslagen in het gecachete plan voor een enkele uitvoering. Het zijn echter de opeenvolgende uitvoeringen van die instructie die profiteren van de aanpassingen in de feedback voor geheugentoekenningen. Deze functie is van toepassing op herhaalde uitvoering van instructies. Feedback over geheugentoewijzing zal alleen het plan in de cache aanpassen. Vóór SQL Server 2022 (16.x) zijn wijzigingen niet vastgelegd in de Query Store.

Feedback blijft niet behouden als het plan wordt verwijderd uit de cache. Feedback gaat ook verloren als er een failover is. Een instructie met OPTION (RECOMPILE) creëert een nieuw plan en slaat het niet op in de cache. Omdat het niet in de cache staat, wordt er geen feedback over geheugentoekenning gegenereerd en wordt het niet opgeslagen voor die compilatie en uitvoering. Als echter een equivalente instructie (dat wil zeggen, met dezelfde query-hash) die niet is gebruikt OPTION (RECOMPILE) , in de cache is opgeslagen en vervolgens opnieuw is uitgevoerd, kunnen de tweede en latere opeenvolgende uitvoeringen profiteren van feedback over geheugentoekenningen.

Feedbackactiviteit bij het toestaan van geheugen bijhouden

U kunt feedbackevenementen voor geheugen toekenning bijhouden met behulp van de memory_grant_updated_by_feedback uitgebreide gebeurtenis. Met deze gebeurtenis wordt de huidige geschiedenis van het aantal uitvoeringen bijgehouden, het aantal keren dat het plan is bijgewerkt door feedback over geheugentoelening, de ideale aanvullende geheugentoerekening voordat deze wordt gewijzigd en de ideale aanvullende geheugentoerekening nadat feedback over geheugentoerekening het in de cache geplaatste plan heeft gewijzigd.

Feedback over geheugentoewijzing, resource governor en query-hints

Het feitelijke geheugen dat wordt toegekend, respecteert de geheugenlimiet van de query die wordt bepaald door de resource governor of query hint.

Schakel geheugenfeedback in batchmodus uit zonder wijziging van het compatibiliteitsniveau

Geheugen toewijzingsfeedback kan worden uitgeschakeld op het database- of statementbereik terwijl het compatibiliteitsniveau van de database 140 en hoger blijft. Om geheugen toekenning terugkoppeling van batchmodus uit te schakelen voor alle queryuitvoeringen die afkomstig zijn van de database, voert u de onderstaande SQL-instructies uit binnen de context van de toepasselijke database.

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

Wanneer deze instelling is ingeschakeld, wordt deze instelling weergegeven als ingeschakeld in sys.database_scoped_configurations.

Om batchmodus-geheugenaanvraag-feedback opnieuw in te schakelen voor alle queryuitvoeringen die afkomstig zijn uit de database, voert u de SQL-instructies uit binnen de context van de toepasselijke database:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

-- Azure SQL Database, SQL Server 2019 and higher
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

U kunt het geheugen van de batchmodus ook uitschakelen om feedback te geven voor een specifieke query door deze aan te wijzen DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK als hint voor de USE HINT-query. Voorbeeld:

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'));

Een USE HINT query-hint heeft voorrang op een database-scopeconfiguratie of traceervlag.

Feedback voor geheugen toekenning in rijmodus

Van toepassing op: SQL Server (vanaf SQL Server 2019 (15.x)), Azure SQL Database, Azure SQL Managed Instance (te beginnen met databasecompatibiliteitsniveau 150)

Feedback over het verlenen van geheugen in rijmodus wordt uitgebreid met de feedbackfunctie voor geheugentoekenningen in de batchmodus door geheugentoekenningen aan te passen voor operators voor batch- en rijmodus.

Om geheugen toelatingsfeedback in de rijmodus in Azure SQL Database in te schakelen, stelt u het databasecompatibiliteitsniveau in op 150 of hoger voor de database waarmee u verbonden bent tijdens het uitvoeren van de query.

Voorbeeld:

ALTER DATABASE [<database name>] SET COMPATIBILITY_LEVEL = 150;

Net als bij geheugenaanvraagfeedback in de batchmodus, is de geheugenaanvraagfeedbackactiviteit in de rijmodus zichtbaar via de XEvent memory_grant_updated_by_feedback. We introduceren ook twee nieuwe kenmerken van het queryuitvoeringsplan voor een betere zichtbaarheid van de huidige status van een geheugentoekenningsbewerking voor zowel rij- als batchmodus.

Voor feedback over geheugentoekoppeling is echter geen Query Store vereist, maar voor de persistentieverbeteringen die zijn geïntroduceerd in SQL Server 2022 (16.x) moet de Query Store worden ingeschakeld voor de database en de status Lezen/schrijven hebben. Zie Voor meer informatie over persistentie, percentiel en persistentie modus geheugen feedback geven verderop in dit artikel.

De activiteit van feedback voor geheugenverlening in rijmodus is zichtbaar via het memory_grant_updated_by_feedback uitgebreide evenement.

Vanaf feedback voor de geheugentoewijzing in rijmodus worden twee nieuwe kenmerken van het queryplan weergegeven voor werkelijke uitvoeringsplannen na afloop: IsMemoryGrantFeedbackAdjusted en LastRequestedMemory, die worden toegevoegd aan het XML-element van het MemoryGrantInfo queryplan.

• Het LastRequestedMemory kenmerk toont het toegewezen geheugen in Kilobytes (KB) van de vorige queryuitvoering.
• Met het kenmerk IsMemoryGrantFeedbackAdjusted kunt u de status van geheugentoekenningsfeedback voor de instructie controleren binnen een daadwerkelijk queryuitvoeringsplan.

Waarden die in dit kenmerk worden weergegeven, zijn als volgt:

Feedback over geheugentoewijzing met percentiel- en persistentiemodus.

Van toepassing op: SQL Server (vanaf SQL Server 2022 (16.x)), Azure SQL Database, Azure SQL Managed Instance

Deze functie is geïntroduceerd in SQL Server 2022 (16.x), maar deze prestatieverbetering is beschikbaar voor query's die werken op databasecompatibiliteitsniveau 140 (geïntroduceerd in SQL Server 2017) of hoger, of de QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n hint van 140 en hoger, en wanneer Query Store is ingeschakeld voor de database en de status Lezen schrijven heeft.

• Feedback over het verlenen van percentielgeheugen is standaard ingeschakeld in SQL Server 2022 (16.x), maar heeft geen effect als Query Store niet is ingeschakeld of als Query Store niet de status Lezen schrijven heeft.
• Persistentie voor geheugentoe kennen, CE en DOP-feedback is standaard ingeschakeld in SQL Server 2022 (16.x), maar heeft geen effect wanneer Query Store niet is ingeschakeld of wanneer Query Store niet de status Lezen schrijven heeft.
• Percentiel en persistentie voor feedback over geheugentoe kennen is beschikbaar in Azure SQL Database en is standaard ingeschakeld voor alle databases, zowel bestaande als nieuwe.
• Percentiel en persistentie voor feedback over geheugentoewijzing is momenteel niet beschikbaar in Azure SQL Managed Instance.

Het is raadzaam om een prestatiebasislijn voor uw workload te hebben voordat de functie is ingeschakeld voor uw database. Met de basislijnnummers kunt u bepalen of u het beoogde voordeel van de functie krijgt.

Feedback over geheugentoekenningen (MGF) is een bestaande functie waarmee de grootte wordt aangepast van het geheugen dat is toegewezen voor een query op basis van eerdere prestaties. De initiële fasen van dit project hebben echter alleen de aanpassing van de geheugentoekenning opgeslagen met het plan in de cache. Als een plan uit de cache wordt verwijderd, moet het feedbackproces opnieuw worden gestart, wat resulteert in slechte prestaties wanneer een query de eerste keer wordt uitgevoerd na verwijdering. De nieuwe oplossing is om de grant-informatie samen met de overige query-informatie in de Query Store op te slaan, zodat de voordelen blijven bestaan bij verwijderingen in de cache. Geheugentoekenning feedback persistentie en percentielwaarde(n) aanpakken bestaande beperkingen van feedback op geheugentoekenning op een niet-intrusieve manier.

Daarnaast hebben de aanpassingen van de subsidiegrootte alleen betrekking op de meest recent gebruikte subsidie. Dus als voor een geparameteriseerde query of workload bij elke uitvoering aanzienlijk verschillende geheugentoekenningen zijn vereist, kan de meest recente toekenningsinformatie onnauwkeurig zijn. Dit kan niet in lijn zijn met de werkelijke behoeften van de query die wordt uitgevoerd. Feedback over geheugentoekenning in dit scenario is niet nuttig voor prestaties, omdat we het geheugen altijd aanpassen op basis van de laatst gebruikte toekenningswaarde. In de volgende afbeelding ziet u het mogelijke gedrag met geheugentoewijzingsfeedback zonder percentielmodus en persistentiemodus.

Zoals u kunt zien, resulteert in dit ongebruikelijke maar mogelijke querygedrag, de fluctuatie tussen de werkelijke benodigde en toegewezen geheugenbedragen in verspild en onvoldoende geheugen als de uitvoering van de query zelf afwisselt in termen van de hoeveelheid geheugen. In dit scenario schakelt geheugentoekenningsfeedback zichzelf uit, omdat het meer kwaad doet dan goed.

Met behulp van een op percentiel gebaseerde berekening over de recente geschiedenis van de query, in plaats van alleen de laatste uitvoering, kunnen we de waarden voor de toekenningsgrootte gladmaken op basis van de gebruiksgeschiedenis van de uitvoering en proberen te optimaliseren voor het minimaliseren van lekkages. Bijvoorbeeld, bij dezelfde afwisselende werkbelasting zou het volgende gedrag voor geheugentoewijzing worden waargenomen:

De query-optimizer gebruikt een hoog percentiel van eerdere vereisten voor geheugentoekenningsgrootte voor de uitvoeringen van het in de cache geplaatste plan om de grootte van geheugentoekenningen te berekenen, met behulp van gegevens die in de Query Store worden bewaard. De percentielaanpassing, die de aanpassingen van de geheugentoekenning uitvoert, is gebaseerd op de recente geschiedenis van uitvoeringen. Na verloop van tijd vermindert de verstrekte geheugentoekenning overvolle geheugen en verspild geheugen.

Persistentie is ook van toepassing op DOP-feedback en CE-feedback.

Functies voor feedback over geheugen toekenning in- en uitschakelen

Geheugenfeedback voor rijmodus uitschakelen zonder het compatibiliteitsniveau te wijzigen

Feedback over geheugenverlening in rijmodus kan worden gedeactiveerd op database- of instructieniveau, terwijl het database-compatibiliteitsniveau 150 en hoger behouden blijft. Als u geheugenfeedback van de rijmodus wilt uitschakelen voor alle queryuitvoeringen vanuit de database, voert u de SQL-instructies uit binnen de context van de toepasselijke database.

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;

Als u de row mode geheugen toekenning feedback opnieuw wilt inschakelen voor alle queryuitvoeringen die door de database zijn uitgevoerd, voert u het volgende uit binnen de context van de toepasselijke database:

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;

U kunt de geheugenfeedback voor een specifieke query ook uitschakelen in de rijmodus door deze aan te wijzen DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK als hint voor een USE HINT-query. Voorbeeld:

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'));

Een USE HINT-queryhint heeft voorrang op een database-specifieke configuratie of traceringsvlaggen.

Inschakelen van feedbackpersistentie en percentiel voor geheugentoekenning

Persistentie en percentielfeedback zijn standaard ingeschakeld in Azure SQL Database en SQL Server 2022 (16.x).

Gebruik databasecompatibiliteitsniveau 140 of hoger voor de database waarmee u verbinding hebt bij het uitvoeren van de query. U kunt dit wijzigen via ALTER DATABASE:

ALTER DATABASE <DATABASE NAME> SET COMPATIBILITY LEVEL = 140; -- OR HIGHER

De Query Store moet zijn ingeschakeld voor elke database waarin het persistentiegedeelte van deze functie wordt gebruikt.

Percentiel uitschakelen

Als u het percentiel voor feedback over geheugentoekenningen wilt uitschakelen voor alle queryuitvoeringen die afkomstig zijn van de database, voert u het volgende uit binnen de context van de toepasselijke database:

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = OFF;

De standaardinstelling MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT is ON.

Persistentie uitschakelen

Om feedbackpersistentie voor geheugentoekenningen uit te schakelen voor alle queryuitvoeringen die afkomstig zijn van de database.

Voer het volgende uit binnen de context van de toepasselijke database:

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = OFF;

Als u het voortduren van geheugenfeedback uitschakelt, wordt ook de bestaande verzamelde feedback verwijderd.

De standaardinstelling MEMORY_GRANT_FEEDBACK_PERSISTENCE is ON.

Overwegingen voor feedback over geheugentoewijzing

U kunt uw huidige instellingen bekijken door een query uit te voeren op sys.database_scoped_configurations.

Gegeven dat feedbackgegevens nu worden opgeslagen in de Query Store, is er een toename in de gebruiksvereisten voor de Query Store.

Op percentiel gebaseerde geheugentoekenningen neigen ertoe om spills te verminderen. Omdat deze niet meer is gebaseerd op de laatste uitvoering, maar op een observatie van de verschillende eerdere uitvoeringen, kan dit het geheugengebruik verhogen voor schommelingen in workloads met een brede variantie in geheugentoekenningen tussen uitvoeringen.

Vanaf SQL Server 2022 (16.x) geldt dat wanneer Query Store voor secundaire replica's is ingeschakeld, feedback over geheugentoekenningen replicabewust is voor secundaire replica's in beschikbaarheidsgroepen. Geheugenverleenfeedback kan verschillend worden toegepast op een primaire replica en op een secundaire replica. Feedback over geheugentoekenningen wordt echter niet opgeslagen op secundaire replica's en bij failover wordt de feedback van de oude primaire replica toegepast op de nieuwe primaire replica. Feedback die wordt toegepast op de secundaire replica wanneer deze de primaire replica wordt, gaat verloren. Voor meer informatie, zie Query Store voor secundaire replica's.

Verwante inhoud

• Intelligent queryverwerking in SQL-databases
• Intelligente functies voor queryverwerking in detail
• opnieuw configureren (Transact-SQL)
• Prestaties bewaken en afstemmen voor betere resultaten
• ALTER DATABASE SCOPED CONFIGURATION(Transact-SQL)