Poznámka:
Přístup k této stránce vyžaduje autorizaci. Můžete se zkusit přihlásit nebo změnit adresáře.
Přístup k této stránce vyžaduje autorizaci. Můžete zkusit změnit adresáře.
Platí pro: SQL Server 2017 (14.x) a novější verze, Azure SQL Managed Instance, Azure SQL Database
Někdy se dotaz spustí s přidělením paměti, které je příliš velké nebo příliš malé. Pokud je grant paměti příliš velký, inhibujeme paralelismus na serveru. Pokud je příliš malý, můžeme zapisovat na disk, což je nákladný proces. Mechanismus zpětné vazby paměti se snaží zapamatovat si požadavky na paměť z předchozího spuštění (s percentilní zpětnou vazbou, vícenásobná předchozí spuštění). Na základě těchto historických dat o dotazech systém pro zpětnou vazbu ohledně přidělení paměti upravuje grant, který je dotazu přidělen, aby optimalizoval jeho následné spuštění.
Tato funkce byla spuštěna ve třech fázích. Zpětná vazba o udělení paměti v režimu batch, následovaná zpětnou vazbou pro udělení paměti v režimu řádků a SQL Server 2022 (16.x) zavedla zpětnou vazbu na trvalost paměti na disku pomocí úložiště dotazů a vylepšený algoritmus označovaný jako udělení percentilu.
Poznámka:
Další funkce zpětné vazby dotazů viz Odhad kardinality (CE) a Stupeň paralelismu (DOP) zpětné vazby.
Zpětná vazba pro přidělení paměti v dávkovém režimu
Platí pro: SQL Server (počínaje SQL Serverem 2017 (14.x)), Azure SQL Database, Azure SQL Managed Instance (počínaje úrovní kompatibility databáze 140)
Plán provádění dotazu zahrnuje minimální požadovanou paměť potřebnou ke spuštění a ideální velikost grantu paměti tak, aby se všechny řádky vešly do paměti. Výkon trpí, když jsou velikosti přidělení paměti nesprávně nastaveny. Nadměrné granty vedou k plýtvání pamětí a snížení souběžnosti. Nedostatečná alokace paměti vede k nákladným operacím přelévání dat na disk. Když řešíte opakující se úlohy, zpětná vazba na přidělení paměti v dávkovém režimu přepočítá skutečnou paměť potřebnou pro dotaz a poté aktualizuje hodnotu přidělení paměti pro plán v mezipaměti. Když se spustí identický příkaz dotazu, použije dotaz revidovanou velikost přidělení paměti, což snižuje nadměrnou velikost paměti, což má vliv na souběžnost a opravu podceněných paměťových grantů, které způsobují nákladné přelití na disk.
Následující graf ukazuje jeden příklad použití zpětné vazby při udělování adaptivní paměti v dávkovém režimu. Doba trvání prvního spuštění dotazu byla 88 sekund kvůli vysokému přetečení.
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;
Je-li aktivována zpětná vazba udělení paměti, doba trvání je 1 sekunda (ze 88 sekund), přelévání jsou zcela odstraněna a udělení je vyšší:
Nastavení velikosti zpětné vazby udělování paměti
Pokud je udělená paměť více než dvakrát větší než velikost skutečné využité paměti, bude zpětná vazba udělení paměti přepočítávat přidělení paměti a aktualizovat plán v mezipaměti. Plány s přidělením paměti do 1 MB nebudou přepracovány kvůli překročením.
Při nedostatečně velkém přidělení paměti, které vede k zápisu na disk u operátorů dávkového režimu, zpětná vazba na přidělení paměti vyvolá přepočet grantu paměti. Události přelití se hlásí do zpětné vazby a dají se zobrazit prostřednictvím spilling_report_to_memory_grant_feedback rozšířené události. Tato událost vrátí ID uzlu z plánu a velikost přelitých dat daného uzlu.
Upravené přidělení paměti se zobrazí ve skutečném plánu (po spuštění) prostřednictvím vlastnosti GrantedMemory.
Tuto vlastnost můžete zobrazit v kořenovém operátoru grafického showplanu nebo ve výstupu XML plánu showplan:
<MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="10336" RequiredMemory="1024" DesiredMemory="10336" RequestedMemory="10336" GrantWaitTime="0" GrantedMemory="10336" MaxUsedMemory="9920" MaxQueryMemory="725864" />
Pokud chcete, aby vaše úlohy byly pro toto vylepšení automaticky způsobilé, povolte pro databázi úroveň kompatibility 140.
Příklad:
ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140;
Zpětná vazba na přidělení paměti a scénáře citlivé na parametry
Různé hodnoty parametrů můžou také vyžadovat různé plány dotazů, aby zůstaly optimální. Tento typ dotazu je definován jako "citlivý na parametr".
V případě plánů citlivých na parametry se zpětná vazba na přidělení paměti automaticky deaktivuje v dotazu, pokud má nestabilní požadavky na paměť. Funkce zpětné vazby pro udělení paměti je zakázaná po několika opakovaných spuštěních dotazu a to je možné sledovat monitorováním memory_grant_feedback_loop_disabled rozšířené události. Tato podmínka je zmírněna pomocí režimu stálosti a procentilu pro zpětnou vazbu při přiřazení paměti zavedenou v SQL Server 2022 (16.x). Funkce trvalosti zpětné vazby pro udělení paměti vyžaduje, aby úložiště dotazů bylo povolené v databázi a bylo nastaveno na režim čtení zápisu.
Další informace o zašifrování parametrů a citlivosti parametrů najdete v průvodci architekturou zpracování dotazů.
Ukládání zpětné vazby do mezipaměti přidělování paměti
Zpětná vazba může být uložená v plánu uloženém v mezipaměti pro jedno spuštění. Jedná se však o po sobě jdoucí provádění tohoto příkazu, které těží z úprav zpětné vazby v paměti. Tato funkce se vztahuje na opakované provádění příkazů. Zpětná vazba o přidělení paměti změní pouze uložený plán v mezipaměti. Před SQL Serverem 2022 (16.x) se změny nezachytávaly v úložišti dotazů.
Pokud se plán vyřadí z mezipaměti, zpětná vazba se neuchová. Při přepnutí služeb v případě selhání se ztratí i zpětná vazba. Příkaz using OPTION (RECOMPILE) vytvoří nový plán a neuloží ho v mezipaměti. Vzhledem k tomu, že není uložen v mezipaměti, nevygeneruje se žádná zpětná vazba na přidělení paměti a není uložena pro tuto kompilaci a spuštění. Pokud se však do mezipaměti vložil ekvivalentní příkaz (to znamená se stejnou hodnotou hash dotazu), který se nepoužilOPTION (RECOMPILE), a pak se znovu spustí, může mít druhý a pozdější po sobě jdoucí spuštění prospěch z zpětné vazby k udělení paměti.
Sledování aktivity zpětné vazby udělování paměti
Pomocí rozšířeného objektu memory_grant_updated_by_feedback můžete sledovat události zpětné vazby z udělení paměti. Tato událost sleduje aktuální historii počtu provedení, kolikrát byl plán aktualizován zpětnou vazbou pro přidělení paměti, ideální další přidělení paměti před úpravou a ideální další přidělení paměti poté, co zpětná vazba na přidělení paměti změnila plán uložený v mezipaměti.
Zpětná vazba k přidělení paměti, správce prostředků a hinty dotazů
Skutečná paměť udělená dodržuje limit paměti dotazu určený správcem prostředků nebo nápovědou dotazu.
Zakázat zpětnou vazbu pro dávkový režim paměti bez změny úrovně kompatibility
Zpětnou vazbu k udělení paměti lze zakázat na úrovni databáze nebo příkazu a přitom zachovat úroveň kompatibility databáze napříč úrovněmi 140 a vyššími. Pokud chcete zakázat zpětnou vazbu k udělení paměti dávkového režimu pro všechna spuštění dotazů pocházející z databáze, spusťte následující příkazy SQL v kontextu příslušné databáze:
-- 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;
Pokud je toto nastavení povolené, zobrazí se v sys.database_scoped_configurations jako povolené.
Pokud chcete znovu povolit zpětnou vazbu pro přidělení paměti v dávkovém režimu pro všechna spuštění dotazů pocházejících z databáze, spusťte příkazy SQL v kontextu příslušné databáze:
-- 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;
Pro konkrétní dotaz můžete také zakázat zpětnou vazbu přidělování paměti v režimu dávkového zpracování označením DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK jako použití nápovědy dotazu USE HINT. Například:
SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'));
USE HINT Tip dotazu má přednost před konfigurací databázového oboru nebo nastavením příznaku trasování.
Zpětná vazba k udělení paměti v režimu řádků
Platí pro: SQL Server (počínaje SQL Serverem 2019 (15.x)), Azure SQL Database, Azure SQL Managed Instance (počínaje úrovní kompatibility databáze 150)
Zpětná vazba pro přidělení paměti v režimu řádků rozšiřuje funkci zpětné vazby pro přidělení paměti v dávkovém režimu úpravou velikosti přidělené paměti pro operátory dávkového i řádkového režimu.
Pokud chcete povolit zpětnou vazbu na přidělení paměti v režimu řádkového módu ve službě Azure SQL Database, nastavte úroveň kompatibility databáze na verzi 150 nebo vyšší pro databázi, k níž jste připojeni při provádění dotazu.
Příklad:
ALTER DATABASE [<database name>] SET COMPATIBILITY_LEVEL = 150;
Stejně jako u zpětné vazby k udělení paměti v dávkovém režimu je aktivita zpětné vazby k udělení paměti v řádkovém režimu viditelná prostřednictvím XEvent memory_grant_updated_by_feedback. Zavádíme také dva nové atributy plánu spouštění dotazů, abychom mohli lépe vidět aktuální stav operace zpětné vazby pro udělení paměti pro režim řádků i dávek.
Zpětná vazba k udělení paměti nevyžaduje úložiště dotazů, ale vylepšení trvalosti zavedená v SQL Serveru 2022 (16.x) vyžadují, aby úložiště dotazů bylo povolené pro databázi a ve stavu čtení zápisu. Další informace o přetrvání naleznete v tématu Percentil a režim přetrvání paměťového grantu se zpětnou vazbou dále v tomto článku.
Aktivita zpětné vazby pro přidělování paměti v režimu řádků je viditelná prostřednictvím rozšířené události memory_grant_updated_by_feedback.
Při zahájení zpětné vazby na udělení paměti v režimu řádků se zobrazí dva atributy plánu dotazu pro skutečné plány po spuštění: IsMemoryGrantFeedbackAdjusted a LastRequestedMemory, které se přidají do elementu MemoryGrantInfo XML plánu dotazu.
- Atribut
LastRequestedMemoryzobrazuje přidělenou paměť v kilobajtech (KB) z předchozího spuštění dotazu. - Atribut
IsMemoryGrantFeedbackAdjustedumožňuje zkontrolovat stav zpětné vazby pro přidělení paměti u příkazu v rámci skutečného plánu provádění dotazu.
Hodnoty uvedené v tomto atributu jsou následující:
hodnota IsMemoryGrantFeedbackAdjusted |
Popis |
|---|---|
| Ne: První spuštění | Zpětná vazba na přidělení paměti neupravuje paměť pro první kompilaci a přidružené spuštění. |
| Ne: Přesné udělení | Pokud nedojde k zápisu na disk a příkaz použije alespoň 50% alokované paměti, nevyvolá se zpětná vazba pro alokaci paměti. |
| Ne: Zpětná vazba je zakázaná. | Pokud se zpětná vazba na udělení paměti průběžně aktivuje a kolísá mezi zvyšováním a snižováním paměti, databázový stroj deaktivuje zpětnou vazbu při udělení paměti pro daný příkaz. |
| Ano: Úprava | Byla použita zpětná vazba na přidělení paměti a při dalším spuštění může být dále upravena. |
| Ano: Úprava percentilu | Zpětná vazba na přidělení paměti je aplikována pomocí algoritmu přidělení percentilu, který sleduje historii více než jen posledního spuštění. |
| Ano: Stabilní | Byla použita zpětná vazba o udělení paměti a udělená paměť je nyní stabilní, což znamená, že to, co bylo naposledy uděleno pro předchozí spuštění, je to, co bylo uděleno pro aktuální spuštění. |
Zpětná vazba na paměťové přidělení v režimu percentilu a perzistence
Platí pro: SQL Server (počínaje SQL Serverem 2022 (16.x)), Azure SQL Database, Azure SQL Managed Instance
Tato funkce byla představena v SQL Serveru 2022 (16.x), ale tato vylepšení výkonu jsou k dispozici pro dotazy, které pracují s úrovní kompatibility databáze 140 (zavedené v SQL Serveru 2017) nebo vyšší, nebo při použití hintu 140 a vyšší, a když je úložiště dotazů povoleno pro databázi a je ve stavu zápisu/čtení.
- Zpětná vazba na udělení paměti podle percentilu je ve výchozím nastavení aktivována v SQL Serveru 2022 (16.x), ale nemá žádný vliv, pokud Query Store není povolené nebo pokud Query Store není ve stavu „čtení a zápis“.
- Trvalost udělení paměti, zpětné vazby CE a DOP jsou ve výchozím nastavení zapnuté v SQL Serveru 2022 (16.x), ale nemá žádný vliv, pokud úložiště dotazů není povolené nebo pokud úložiště dotazů není ve stavu čtení zápisu.
- Percentil a trvalost paměťové zpětné vazby jsou k dispozici ve službě Azure SQL Database a jsou ve výchozím nastavení povoleny pro všechny databáze, stávající i nové.
- Percentil a trvalost zpětné vazby k udělení paměti v současné době nejsou ve službě Azure SQL Managed Instance k dispozici.
Před povolením funkce pro vaši databázi doporučujeme mít standardní hodnoty výkonu pro vaši úlohu. Základní čísla vám pomohou určit, zda získáváte ze funkce zamýšlenou výhodu.
Zpětná vazba na přidělování paměti (MGF) je existující funkce, která upravuje velikost paměti přidělené pro dotazy na základě předchozího chování. Počáteční fáze tohoto projektu však uložily pouze úpravu přidělení paměti s plánem v mezipaměti – pokud je plán vyřazen z mezipaměti, musí se proces zpětné vazby spustit znovu, což vede k nízkému výkonu, když se dotaz spustí po vyřazení. Novým řešením je zachovat informace o oprávnění k ostatním informacím dotazu v úložišti dotazů, aby výhody přetrvávaly i po vyprázdnění mezipaměti. Trvalost a percentil zpětné vazby udělování paměti řeší stávající omezení zpětné vazby udělování paměti nerušivým způsobem.
Kromě toho se úpravy velikosti grantu zohlednily pouze u naposledy použitých grantů. Pokud tedy parametrizovaný dotaz nebo úloha vyžadují při každém spuštění výrazně různé velikosti přidělení paměti, můžou být nejnovější informace o udělení nepřesné. Mohlo by to být nevyhovující aktuálním potřebám konkrétního spuštěného dotazu. Zpětná vazba na přidělení paměti v tomto scénáři je neúčinná pro výkon, protože vždy upravujeme paměť podle poslední použité hodnoty přidělení. Následující obrázek ukazuje možné chování při zpětné vazbě k udělení paměti bez percentilu a režimu trvalosti.
Jak vidíte, v tomto neobvyklém, ale možném chování dotazu, oscilace mezi skutečnými potřebnými a udělenými objemy paměti vede k plýtvání a nedostatku paměti, pokud se samotné spuštění dotazu mění z hlediska množství paměti. V tomto scénáři se zpětná vazba na povolení paměti sama deaktivuje, protože rozpoznává, že způsobuje více škody než užitku.
Použitím výpočtu založeného na percentilu z nedávné historie dotazu, místo prostého posledního spuštění, můžeme vyhladit hodnoty alokace velikosti na základě historie využití minulých spuštění a pokusit se optimalizovat pro minimalizaci přetečení. Například stejné střídavé úlohy by vykazovalo následující chování při udělování paměti:
Optimalizátor dotazů používá vysoký percentil minulých požadavků na velikost paměťových grantů při vykonávání uloženého plánu k výpočtu velikostí paměťových grantů, za použití dat uložených v úložišti dotazů. Úprava percentilu, která provádí úpravy přidělení paměti, je založena na nedávné historii výkonu. Grant paměti v průběhu času snižuje přelití a plýtvání pamětí.
Trvalost platí také pro zpětnou vazbu DOP a zpětnou vazbu CE.
Povolení a zakázání zpětné vazby při přidělování paměti
Zakázat zpětnou vazbu paměťového přidělení režimu řádků bez změny úrovně kompatibility
Zpětnou vazbu při udělení paměti v režimu řádku lze zakázat na úrovni databáze nebo příkazu při zachování úrovně kompatibility databáze 150 a výše. Chcete-li zakázat zpětnou vazbu udělování paměti v režimu řádků pro všechna spuštění dotazů pocházející z databáze, spusťte příkazy SQL v kontextu příslušné databáze.
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;
Pokud chcete znovu povolit paměť v režimu řádků, udělte zpětnou vazbu pro všechna spuštění dotazů pocházející z databáze, spusťte v kontextu příslušné databáze následující příkaz:
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;
Můžete také zakázat zpětnou vazbu udělení paměti podle řádků pro konkrétní dotaz tím, že ho označíte DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK jako parametr USE HINT. Například:
SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'));
Tip dotazu USE HINT má přednost před nastavením příznaku trasování nebo konfigurací v oboru databáze.
Povolení trvalosti zpětné vazby a percentilu pro udělení paměti
Zpětná vazba na trvalost a percentil je ve výchozím nastavení povolená ve službě Azure SQL Database a SQL Serveru 2022 (16.x).
Pro databázi, ke které jste připojení při provádění dotazu, použijte úroveň kompatibility databáze 140 nebo vyšší. Můžete to změnit pomocí příkazu ALTER DATABASE:
ALTER DATABASE <DATABASE NAME> SET COMPATIBILITY LEVEL = 140; -- OR HIGHER
Úložiště dotazů musí být povolené pro každou databázi, ve které se používá část trvalosti této funkce.
Zakázání percentilu
Pokud chcete zakázat percentil zpětné vazby o přidělení paměti pro všechna spuštění dotazů pocházející z databáze, spusťte následující příkaz v kontextu příslušné databáze:
ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = OFF;
Výchozí nastavení MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT je ON.
Zakázat trvalost
Chcete-li zakázat trvalost zpětné vazby při přidělování paměti pro všechna provádění dotazů z databáze.
V kontextu příslušné databáze spusťte následující příkaz:
ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = OFF;
Zakázání trvalosti zpětné vazby při udělení paměti také odstraní existující shromážděnou zpětnou vazbu.
Výchozí nastavení MEMORY_GRANT_FEEDBACK_PERSISTENCE je ON.
Úvahy o zpětné vazbě při přidělování paměti
Aktuální nastavení můžete zobrazit dotazováním sys.database_scoped_configurations.
Poznámka:
Tato funkce nebude fungovat, pokud jsou oba BATCH_MODE_MEMORY_GRANT_FEEDBACK a ROW_MODE_MEMORY_GRANT_FEEDBACK nastaveny na OFF.
Vzhledem k tomu, že data zpětné vazby se teď uchovávají v úložišti dotazů, dochází k určitému zvýšení požadavků na využití úložiště dotazů.
Percentilové přidělení paměti se zaměřuje na snížení přelití. Vzhledem k tomu, že už není založená na posledním spuštění, ale na pozorování několika předchozích spuštění, může to zvýšit využití paměti pro oscilující úlohy s širokou odchylkou v požadavcích na udělení paměti mezi spuštěními.
Počínaje SQL Serverem 2022 (16.x), když je úložiště dotazů pro sekundární repliky povoleno, zpětná vazba na udělení paměti je upravena podle replik ve skupinách dostupnosti. Na primární replikě a sekundární replikě může být zpětná vazba k udělení paměti aplikována odlišně. Zpětná vazba pro udělení paměti se však neuchovává u sekundárních replik a při přepnutí po selhání se zpětná vazba z původní primární repliky použije na novou primární repliku. Veškerá zpětná vazba, která se použije na sekundární repliku, se ztratí, když se tato replika stane primární. Úložiště dotazů je k dispozici u replik sekundární skupiny dostupnosti počínaje systémem SQL Server 2025 (17.x). Další informace najdete v tématu Úložiště dotazů pro sekundární repliky.