Återkoppling om minnestilldelning

Gäller för: SQL Server 2017 (14.x) och senare versioner, Azure SQL Managed Instance, Azure SQL Database

Ibland körs en fråga med en minnestilldelning som är för stor eller för liten. Om minnesbidraget är för stort hämmar vi parallelliteten på servern. Om den är för liten kan vi skriva till disk, vilket är en kostsam operation. Feedback för minnesallokering försöker anpassa sig till minnesbehov för en föregående körning (med feedback med percentilvärden, flera tidigare körningar). Baserat på denna historiska frågeinformation justerar minnesbidragsfeedback det minnesbidrag som ges till frågan i enlighet med detta för framtida exekveringar.

Den här funktionen har släppts i tre vågor. Feedback om minnesbeviljande i batchläge, följt av feedback om minnesbeviljande i radläge, och SQL Server 2022 (16.x) introducerade feedback om minnesbeviljande på diskar med hjälp av Query Store och en förbättrad algoritm som kallas percentilbeviljande.

Feedback om minnestilldelning i batch-läge

Gäller för: SQL Server (från och med SQL Server 2017 (14.x)), Azure SQL Database, Azure SQL Managed Instance (från och med databaskompatibilitetsnivå 140)

Frågans körningsplan innehåller det minsta minne som krävs för att köras och den ideala minnestilldelningen så att alla rader ryms i minnet. Prestandan blir lidande när minnestilldelningsstorlekar är felaktigt beräknade. Alltför stora bidrag resulterar i bortkastat minne och minskad samtidighet. Otillräckliga minnestilldelningar orsakar dyra utflöden till disk. Genom att hantera upprepade arbetsbelastningar beräknar minnesbeviljande feedback i batchläge om det faktiska minnet som krävs för en fråga och uppdaterar sedan beviljningsvärdet för den cachelagrade planen. När en identisk frågesats körs använder frågan den ändrade storleken på minnestilldelningen, vilket minskar överflödiga minnestilldelningar som påverkar samtidigheten och åtgärdar underskattade minnestilldelningar som orsakar dyra spill till disken.

I följande diagram visas ett exempel på användning av batchläge för adaptiv feedback på minnestilldelning. För den första körningen av frågan var varaktigheten 88 sekunder på grund av höga spill:

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;

När feedback om minnesbidrag är aktiverat för den andra körningen är varaktigheten 1 sekund (ned från 88 sekunder), spill tas bort helt och beviljandet är högre:

Dimensionering av feedback för minnesbeviljande

Vid ett överdrivet minnesbeviljande, om det beviljade minnet är mer än två gånger så stort som det faktiska förbrukade minnet, kommer feedback för minnesbeviljande att beräkna om minnesbeviljandet och uppdatera den cachelagrade planen. Planer med minnesbidrag under 1 MB räknas inte om för överförbrukning.

För en otillräcklig minnesbeviljande situation som leder till att data skrivs till disk för operatorer i batch-läge, utlöser justeringar i minnesbeviljandet baserat på feedback en omberäkning av minnesbeviljandet. Spillhändelser rapporteras för feedback om minnesåtergivning och kan visas via den spilling_report_to_memory_grant_feedback utökade händelsen. Den här händelsen återger nodens ID från planeringen och den utspillda datastorleken för noden.

Den justerade minnestilldelningen visas i den faktiska planen (efter körning) genom egenskapen GrantedMemory.

Du kan se den här egenskapen i rotoperatorn för den grafiska showplanen eller i utdata för showplanens XML.

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

Om du vill att dina arbetsbelastningar automatiskt ska vara berättigade till den här förbättringen aktiverar du kompatibilitetsnivå 140 för databasen.

Exempel:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140;

Feedback om minnesbeviljande och parameterkänsliga scenarier

Olika parametervärden kan också kräva olika frågeplaner för att förbli optimala. Den här typen av fråga definieras som "parameterkänslig".

För parameterkänsliga planer avaktiveras minnesbidragsåterkoppling för en databasfråga om den har instabila minneskrav. Funktionen för återkoppling av minnesbidrag inaktiveras efter flera upprepade körningar av frågan och detta kan observeras genom övervakning av den memory_grant_feedback_loop_disabled utökade händelsen. Det här villkoret minimeras med beständighets- och percentilläget för feedback om minnesbidrag som introducerades i SQL Server 2022 (16.x). Beständighetsfunktionen i feedback om minnesåtergivning kräver att Query Store är aktiverat i databasen och är inställt på "lässkrivningsläge".

Mer information om parametersniffning och parameterkänslighet finns i arkitekturguiden för frågebearbetning.

Cachelagring av minnesåtergivningsfeedback

Feedback kan lagras i den cachelagrade planen för en enda körning. Det är dock de efterföljande körningarna av den instruktionen som drar nytta av minnesåtergivningens feedbackjusteringar. Den här funktionen gäller för upprepad exekvering av kommandon. Feedback om minnesbidrag ändrar endast den cachelagrade planen. Innan SQL Server 2022 (16.x) registrerades inte ändringar i Query Store.

Feedback sparas inte om planen tas bort från cacheminnet. Feedback kommer också att gå förlorad om det sker en övergång. Ett uttryck som använder OPTION (RECOMPILE) skapar en ny plan och cacherar den inte. Eftersom den inte cachelagras skapas ingen feedback om minnestilldelning, och den sparas inte för den kompileringen och körningen. Men om en motsvarande sats (dvs. med samma frågehash) som inte använde OPTION (RECOMPILE) cachelagrades och sedan kördes om, kan andra och senare efterföljande körningar dra nytta av feedback för minnesbeviljande.

Spåra feedbackaktivitet för minnesbidrag

Du kan spåra feedback på minnestilldelning med hjälp av den memory_grant_updated_by_feedback utökade händelsen. Den här händelsen spårar den aktuella körningshistoriken, antalet gånger planen har uppdaterats av feedback om minnesbidrag, det perfekta extra minnesbidraget före ändringen och det idealiska extra minnesbidraget efter att feedback om minnesbidrag har ändrat den cachelagrade planen.

Feedback om minnesbidrag, resursguvernör och frågetips

Det faktiska minne som beviljas respekterar frågeminnesgränsen som bestäms av resursguvernören eller frågetipset.

Inaktivera minnesåtergivning för batchläge utan att ändra kompatibilitetsnivån

Feedback om minnesbeviljande kan inaktiveras i databas- eller instruktionsomfånget samtidigt som databaskompatibilitetsnivån 140 och högre bibehålls. Om du vill inaktivera minnesåtergivning i batchläge för alla frågekörningar som kommer från databasen kör du SQL-satserna nedan i kontexten för den tillämpliga databasen:

-- 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;

När den här inställningen är aktiverad visas den som aktiverad i sys.database_scoped_configurations.

Om du vill återaktivera minnesåtergivning i batchläge för alla frågekörningar som kommer från databasen kör du SQL-uttrycken i kontexten för den tillämpliga databasen:

-- 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;

Du kan också inaktivera minnesbidragsfeedback i batchläge för en specifik fråga genom att ange DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK som en USE HINT-frågetips. Till exempel:

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

Ett USE HINT frågehint har företräde framför en databasomfattande konfigurationsinställning eller spårningsflaggainställning.

Feedback för minnesbeviljande i radläge

Gäller för: SQL Server (från och med SQL Server 2019 (15.x)), Azure SQL Database, Azure SQL Managed Instance (från och med databaskompatibilitetsnivå 150)

Minnesbidragsfeedback i radläge utökar minnesbidragsfeedback-funktionen för batchläge genom att justera minnesbidragsstorlekar för både batch- och radlägesoperatörer.

Aktivera feedback om minnesåtergivning i radläge i Azure SQL Database genom att aktivera databaskompatibilitetsnivå 150 eller högre för databasen som du är ansluten till när du kör frågan.

Exempel:

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

Precis som med feedback om minnesåtergivning i batchläge är feedbackaktiviteten för minnesåtergivning i radläge synlig via XEvent. Vi inför också två nya attribut för körningsplaner för bättre insyn i det aktuella tillståndet för en återkopplingsprocess för minnesallokering, både i rad- och batchläge.

Feedback om minnesbidrag kräver inte Query Store, men de beständighetsförbättringar som introducerades i SQL Server 2022 (16.x) kräver att Query Store är aktiverat för databasen och i tillståndet "lässkrivning". Mer information om beständighet finns i Percentil- och beständighetslägets minnesåtergivningsfeedback senare i den här artikeln.

Återkopplingsaktiviteten för minnesåtergivning i radläge visas via den memory_grant_updated_by_feedback utökade händelsen.

Från och med feedback om minnesbeviljande i radläge visas två nya frågeplansattribut för faktiska planer efter körning: IsMemoryGrantFeedbackAdjusted och LastRequestedMemory, som läggs till i XML-elementet MemoryGrantInfo för frågeplan.

• Attributet LastRequestedMemory visar det tilldelade minnet i Kilobyte (KB) från tidigare körning av frågan.
• Med attributet IsMemoryGrantFeedbackAdjusted kan du kontrollera tillståndet för feedback om minnestilldelning för instruktionen i en aktuell frågekörningsplan.

Värden som visas i det här attributet är följande:

Feedback om minnesbeviljande i percentil- och beständighetsläge

Gäller för: SQL Server (från och med SQL Server 2022 (16.x)), Azure SQL Database, Azure SQL Managed Instance

Den här funktionen introducerades i SQL Server 2022 (16.x), men den här prestandaförbättringen är tillgänglig för frågor som körs på databaskompatibilitetsnivå 140 (introducerades i SQL Server 2017) eller senare, eller tipset QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n om 140 och senare, och när Query Store är aktiverat för databasen och är i ett "lässkrivningstillstånd".

• Feedback om att bevilja percentilminne är aktiverat som standard i SQL Server 2022 (16.x), men har ingen effekt om Query Store inte är aktiverat eller när Query Store inte är i ett "lässkrivningstillstånd".
• Beständighet för minnesbidrag, CE och DOP-feedback är aktiverat som standard i SQL Server 2022 (16.x), men har ingen effekt när Query Store inte är aktiverat eller när Query Store inte är i ett "lässkrivningstillstånd".
• Percentil och beständighet för feedback om minnesbidrag är tillgängligt i Azure SQL Database och aktiveras som standard på alla databaser, både befintliga och nya.
• Percentil och beständighet för feedback om minnesbidrag är för närvarande inte tillgängligt i Azure SQL Managed Instance.

Vi rekommenderar att du har en prestandabaslinje för din arbetsbelastning innan funktionen aktiveras för databasen. Baslinjenumren hjälper dig att avgöra om du får den avsedda fördelen med funktionen.

Feedback om minnesbidrag (MGF) är en befintlig funktion som justerar storleken på det minne som allokerats för en fråga baserat på tidigare prestanda. De inledande faserna i det här projektet lagrade dock endast minnestilldelningsjusteringen med planen i cachen – om en plan tas bort från cachen måste feedbackprocessen starta igen, vilket resulterar i dåliga prestanda de första gångerna en fråga körs efter att en fråga har avlägsnats. Den nya lösningen är att bevara tilldelningsinformationen med den andra frågeinformationen i Query Store, så att fördelarna består över cacheavhysningar. Feedback om minnesbeviljande och percentil hanterar befintliga begränsningar för feedback om minnesbeviljande på ett icke-påträngande sätt.

Dessutom justerades storleken på bidraget endast för det senast använda bidraget. Så om en parametriserad fråga eller arbetsbelastning kräver betydligt varierande minnesbidragskvantiteter vid varje körning kan den senaste informationen om minnesbeviljande vara felaktig. Det kan vara ur fas med de faktiska behoven för sökningen som utförs. Feedback om minnesbidrag i detta scenario är inte fördelaktigt för prestandan eftersom vi ständigt justerar minnet baserat på det senast använda bidragsvärdet. Nästa bild visar beteendet som är möjligt med minnestilldelningsfeedback utan percentilläge och beständighetsläge.

Som du ser resulterar oscillationen mellan de faktiska nödvändiga och beviljade minnesmängderna i det här ovanliga men möjliga frågebeteendet i bortkastat och otillräckligt minne om själva frågekörningen växlar när det gäller mängden minne. I det här scenariot avaktiverar sig minnesjusteringsfeedback självt, då det erkänner att det gör mer skada än nytta.

Med hjälp av en percentilbaserad beräkning över frågans senaste körningshistorik kan vi, i stället för bara den senaste körningen, jämna ut värdena på tilldelningsstorlekar baserat på tidigare användningshistorik och försöka optimera för att minimera överspill. Till exempel skulle beteendet för samma alternerande arbetsbelastning och minnestilldelning vara följande:

Frågeoptimeraren använder en hög percentil av tidigare minnesbehovsstorlekar för att beräkna minnesbeviljande storlekar för körningar av cachelagrande planer, genom att använda data som sparats i Query Store. Percentiljusteringen, som utför minnestilldelningsjusteringarna, baseras på körningshistorikens senaste data. Med tiden hjälper det minnesbidrag som ges att minska spill och bortkastat minne.

Beständighet gäller även DOP-feedback och CE-feedback.

Aktivera och inaktivera funktioner för feedback för minnestilldelning

Inaktivera minnesbidragsfeedback för radläge utan att ändra kompatibilitetsnivån

Feedback om minnesbeviljande i radläge kan inaktiveras i databas- eller instruktionsomfånget samtidigt som databaskompatibilitetsnivån 150 och högre bibehålls. Om du vill inaktivera feedback för minnesallokering i radläge för alla frågekörningar som utförs från databasen, kör SQL-kommandona i den aktuella databasens kontext.

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;

För att återaktivera feedback om minnestilldelning för radläge vid alla frågekörningar från databasen, kör följande kommando inom den tillämpliga databasens kontext:

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;

Du kan också inaktivera feedback för minnesbeviljande i radläge för en specifik fråga genom att ange DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK som ett USE HINT frågetips. Till exempel:

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

En USE HINT-frågetips har företräde framför en inställning för databasomfattningskonfiguration eller spårningsflagga .

Aktivera beständighet och percentil för minnesåtergivning

Feedback om beständighet och percentil aktiveras som standard i Azure SQL Database och SQL Server 2022 (16.x).

Använd databaskompatibilitetsnivå 140 eller högre för den databas som du är ansluten till när du kör frågan. Du kan ändra detta via ALTER DATABASE:

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

Query Store måste vara aktiverat för varje databas där beständighetsdelen av den här funktionen används.

Inaktivera percentil

Om du vill inaktivera feedbackpercentilen för minnestilldelning för alla frågekörningar som kommer från databasen, kör du följande i den aktuella databasens kontext:

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = OFF;

Standardinställningen för MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT är ON.

Inaktivera beständighet

Så här inaktiverar du beständighet för minnesåtergivning för alla frågekörningar som kommer från databasen.

Kör följande i kontexten för den tillämpliga databasen:

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = OFF;

Om du inaktiverar feedbackens varaktighet för minnestilldelning tas även befintlig insamlad feedback bort.

Standardinställningen för MEMORY_GRANT_FEEDBACK_PERSISTENCE är ON.

Överväganden för feedback om minnestilldelning

Du kan visa dina aktuella inställningar genom att fråga sys.database_scoped_configurations.

Med tanke på att feedbackdata nu sparas i Query Store ökar användningskraven för Query Store.

Percentilbaserade minnesbeviljanden tenderar att minska spill. Eftersom den inte längre baseras enbart på den senaste körningen utan på en observation av flera tidigare körningar, kan detta öka minnesanvändningen för oscillerande arbetsbelastningar med stor varians i minnesbehov mellan körningar.

Från och med SQL Server 2022 (16.x), när Query Store är aktiverat för sekundära repliker, är minnesbeviljningsfeedback medveten om sekundära repliker i tillgänglighetsgrupper. Feedback för minnesbeviljande kan tillämpas olika på en primär och en sekundär replik. Feedback om minnestillviljande sparas dock inte på sekundära repliker, och vid redundans används feedback om minnesåtergivning från den gamla primära repliken på den nya primära repliken. All feedback som tillämpas på den sekundära repliken när den blir den primära repliken går förlorad. För mer information, se Query Store för sekundära repliker.

Relaterat innehåll

• Intelligent frågebearbetning i SQL-databaser
• Intelligenta frågebearbetningsfunktioner i detalj
• OMKONFIGURERA (Transact-SQL)
• Övervaka och finjustera prestanda
• ÄNDRA DATABASOMFATTNINGSKONFIGURATION (Transact-SQL)