Felsök problem med långsam prestanda eller minnesbrist som orsakas av minnestilldelning i SQL Server
Vad är minnesbidrag?
Minnesbidrag, även kallade frågekörningsreservationer (QE), frågekörningsminne, arbetsyteminne och minnesreservationer, beskriver användningen av minne vid frågekörning. SQL Server allokerar det här minnet under frågekörningen för ett eller flera av följande syften:
- Sorteringsåtgärder
- Hash-åtgärder
- Masskopieringsåtgärder (inte ett vanligt problem)
- Indexskapande, inklusive infogning i COLUMNSTORE-index eftersom hash-ordlistor/tabeller används vid körning för indexskapande (inte ett vanligt problem)
För att ge en viss kontext kan en fråga under sin livstid begära minne från olika minnesallokerare eller kontorister beroende på vad den behöver göra. När en fråga till exempel parsas och kompileras från början förbrukar den kompileringsminne. När frågan har kompilerats frigörs det minnet och den resulterande frågeplanen lagras i planens cacheminne. När en plan har cachelagrats är frågan redo för körning. Om frågan utför sorteringsåtgärder, hashmatchningsåtgärder (JOIN eller aggregeringar) eller infogningar i ett COLUMNSTORE-index använder den minne från frågekörningsallokeraren. Inledningsvis frågar frågan efter körningsminnet, och senare om det här minnet beviljas använder frågan hela eller en del av minnet för sorteringsresultat eller hash-bucketar. Det här minnet som allokeras under frågekörningen kallas minnestilldelare. Som du kan föreställa dig släpps minnesbidraget tillbaka till SQL Server när frågekörningen har slutförts och används för annat arbete. Minnesbeviljande allokeringar är därför tillfälliga till sin natur, men kan fortfarande pågå länge. Om en frågekörning till exempel utför en sorteringsåtgärd på en mycket stor raduppsättning i minnet kan sorteringen ta många sekunder eller minuter och det beviljade minnet används under frågans livslängd.
Exempel på en fråga med ett minnesbidrag
Här är ett exempel på en fråga som använder körningsminne och dess frågeplan som visar beviljandet:
SELECT *
FROM sys.messages
ORDER BY message_id
Den här frågan väljer en raduppsättning på över 300 000 rader och sorterar den. Sorteringsåtgärden inducerar en begäran om minnesbidrag. Om du kör den här frågan i SSMS kan du visa dess frågeplan. När du väljer den vänstra operatorn SELECT för frågeplanen kan du visa information om minnesbeviljande för frågan (tryck på F4 för att visa egenskaper):
Om du högerklickar i det tomma utrymmet i frågeplanen kan du också välja Visa XML för körningsplan... och leta upp ett XML-element som visar samma information om minnesbeviljande.
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="41232" RequiredMemory="5248" DesiredMemory="46016" RequestedMemory="46016" GrantWaitTime="0" GrantedMemory="46016" MaxUsedMemory="45816" MaxQueryMemory="277688" LastRequestedMemory="0" IsMemoryGrantFeedbackAdjusted="No: First Execution" />
Flera termer behöver förklaring här. En fråga kan önska en viss mängd körningsminne (DesiredMemory) och begär ofta den mängden (RequestedMemory). Vid körning beviljar SQL Server hela eller en del av det begärda minnet beroende på tillgänglighet (GrantedMemory). I slutändan kan frågan använda mer eller mindre av det ursprungligen begärda minnet (MaxUsedMemory). Om frågeoptimeraren har överskattat mängden minne som behövs använder den mindre än den begärda storleken. Men det minnet slösas bort eftersom det kunde ha använts av en annan begäran. Om optimeraren å andra sidan har underskattat mängden minne som behövs kan överflödiga rader spillas ut till disken för att få arbetet gjort vid körningen. I stället för att allokera mer minne än den ursprungligen begärda storleken skickar SQL Server de extra raderna till disken och använder det som en tillfällig arbetsyta. Mer information finns i Arbetsfiler och Arbetstabeller i Överväganden för minnesbidrag.
Terminologi
Nu ska vi gå igenom de olika termer som du kan stöta på när det gäller den här minneskonsumenten. Återigen beskriver alla dessa begrepp som relaterar till samma minnesallokeringar.
Frågekörningsminne (QE-minne): Den här termen används för att markera det faktum att sorterings- eller hashminne används under körningen av en fråga. Vanligtvis är QE-minne den största minneskonsumenten under en frågas livslängd.
Frågekörningsreservationer (QE) eller minnesreservationer: När en fråga behöver minne för sorterings- eller hashåtgärder gör den en reservationsbegäran för minnet. Reservationsbegäran beräknas vid kompileringstidpunkten baserat på uppskattad kardinalitet. Senare, när frågan körs, beviljar SQL Server den begäran delvis eller helt beroende på minnestillgänglighet. I slutändan kan frågan använda en procentandel av det beviljade minnet. Det finns en minnestjänsteman (minnesrevisor) med namnet "MEMORYCLERK_SQLQERESERVATIONS" som håller reda på dessa minnesallokeringar (kolla in DBCC MEMORYSTATUS eller sys.dm_os_memory_clerks).
Minnesbidrag: När SQL Server beviljar det begärda minnet till en körande fråga, sägs det att ett minnestilldelning har inträffat. Det finns några prestandaräknare som använder termen "bevilja". Dessa räknare,
Memory Grants OutstandingochMemory Grants Pending, visar antalet minnestilldelar som är uppfyllda eller väntar. De tar inte hänsyn till storleken på minnesbidraget. En fråga ensam kunde ha förbrukat till exempel 4 GB minne för att utföra en sortering, men det återspeglas inte i någon av dessa räknare.Arbetsytans minne är en annan term som beskriver samma minne. Ofta kan du se den här termen i Perfmon-räknaren
Granted Workspace Memory (KB), som återspeglar den totala mängden minne som för närvarande används för sorterings-, hash-, masskopierings- och indexskapandeåtgärder, uttryckt i KB. ,Maximum Workspace Memory (KB)en annan räknare, står för den maximala mängden ledigt arbetsyteminne för alla begäranden som kan behöva utföra sådana åtgärder för hash-, sorterings-, masskopierings- och indexskapande. Termen Arbetsyteminne påträffas sällan utanför dessa två räknare.
Prestandapåverkan av stor QE-minnesanvändning
I de flesta fall, när en tråd begär minne i SQL Server för att få något gjort och minnet inte är tillgängligt, misslyckas begäran med ett minnesfel. Det finns dock ett par undantagsscenarier där tråden inte misslyckas men väntar tills minnet blir tillgängligt. Ett av dessa scenarier är minnestillslag och det andra är frågekompileringsminne. SQL Server använder ett trådsynkroniseringsobjekt som kallas semafor för att hålla reda på hur mycket minne som har beviljats för frågekörning. Om SQL Server tar slut på den fördefinierade QE-arbetsytan, i stället för att misslyckas med frågan med ett fel om slut på minne, får frågan att vänta. Med tanke på att arbetsytans minne tillåts ta en betydande del av det totala SQL Server-minnet, har väntan på minne i det här utrymmet allvarliga prestandakonsekvenser. Ett stort antal samtidiga frågor har begärt körningsminne, och tillsammans har de förbrukat QE-minnespoolen, eller några samtidiga frågor har begärt mycket stora bidrag. Hur som helst kan de resulterande prestandaproblemen ha följande symtom:
- Data- och indexsidor från en buffertcache har troligen rensats ut för att skapa utrymme för begäranden om stora minnesbidrag. Det innebär att sidläsningar som kommer från frågebegäranden måste uppfyllas från disken (en betydligt långsammare åtgärd).
- Begäranden om andra minnesallokeringar kan misslyckas med minnesfel eftersom resursen är kopplad till sorterings-, hash- eller indexskapande åtgärder.
- Begäranden som behöver körningsminne väntar på att resursen ska bli tillgänglig och tar lång tid att slutföra. För slutanvändaren är dessa frågor med andra ord långsamma.
Om du ser väntetider på frågekörningsminnet i Perfmon, dynamiska hanteringsvyer (DMV:er) eller DBCC MEMORYSTATUSmåste du därför vidta åtgärder för att lösa det här problemet, särskilt om problemet uppstår ofta. Mer information finns i Vad kan en utvecklare göra med sorterings- och hashåtgärder.
Så här identifierar du väntetider för frågekörningsminne
Det finns flera sätt att fastställa väntetider för QE-reservationer. Välj de som passar bäst för att se den större bilden på servernivå. Vissa av dessa verktyg kanske inte är tillgängliga för dig (till exempel är Perfmon inte tillgängligt i Azure SQL Database). När du har identifierat problemet måste du öka detaljnivån på den enskilda frågenivån för att se vilka frågor som behöver justeras eller skrivas om.
Använd följande metoder på servernivå:
- Resurssemafor DMV-sys.dm_exec_query_resource_semaphores Mer information finns i sys.dm_exec_query_resource_semaphores.
- Prestandaövervakarräknare Mer information finns i SQL Server Memory Manager-objekt.
- DBCC MEMORYSTATUS Mer information finns i DBCC MEMORYSTATUS.
- Minnesbiträden DMV sys.dm_os_memory_clerks Mer information finns i sys.dm_os_memory_clerks.
- Identifiera minnesbidrag med hjälp av Extended Events (XEvents) Mer information finns i Extended Events (XEvents).
På den enskilda frågenivån använder du följande metoder:
- Identifiera specifika frågor med sys.dm_exec_query_memory_grants: Kör frågor för närvarande. Mer information finns i sys.dm_exec_query_memory_grants.
- Identifiera specifika frågor med sys.dm_exec_requests: Kör frågor för närvarande. Mer information finns i sys.dm_exec_requests.
- Identifiera specifika frågor med sys.dm_exec_query_stats: Historisk statistik om frågor. Mer information finns i sys.dm_exec_query_stats.
- Identifiera specifika frågor med hjälp av Query Store (QDS) med sys.query_store_runtime_stats: Historisk statistik för frågor med QDS. Mer information finns i sys.query_store_runtime_stats.
Sammanställd minnesanvändningsstatistik
Resurssemafor DMV-sys.dm_exec_query_resource_semaphores
Denna DMV delar upp frågereservationens minne efter resurspool (intern, standard och användarskapad) och resource_semaphore (vanliga och små frågebegäranden). En användbar fråga kan vara:
SELECT
pool_id
,total_memory_kb
,available_memory_kb
,granted_memory_kb
,used_memory_kb
,grantee_count, waiter_count
,resource_semaphore_id
FROM sys.dm_exec_query_resource_semaphores rs
Följande exempelutdata visar att cirka 900 MB frågekörningsminne används av 22 begäranden och ytterligare 3 väntar. Detta sker i standardpoolen (pool_id = 2) och den vanliga frågesemaforen (resource_semaphore_id = 0).
pool_id total_memory_kb available_memory_kb granted_memory_kb used_memory_kb grantee_count waiter_count resource_semaphore_id
------- --------------- ------------------- ----------------- -------------- ------------- ------------ ---------------------
1 30880 30880 0 0 0 0 0
1 5120 5120 0 0 0 0 1
2 907104 0 907104 898656 22 3 0
2 40960 40960 0 0 0 0 1
(4 rows affected)
Prestandaövervakarens räknare
Liknande information är tillgänglig via prestandaövervakarens räknare, där du kan observera de för närvarande beviljade begärandena (Memory Grants Outstanding), väntande beviljandebegäranden (Memory Grants Pending) och mängden minne som används av minnesbidrag (Granted Workspace Memory (KB)). I följande bild är de utestående bidragen 18, de väntande bidragen är 2 och det beviljade arbetsytans minne är 828 288 KB. Perfmon-räknaren Memory Grants Pending med ett värde som inte är noll anger att minnet har förbrukats.
Mer information finns i SQL Server Memory Manager-objektet.
- SQLServer, Memory Manager: Maximalt arbetsyteminne (KB)
- SQLServer, Memory Manager: Minnestilldelar utestående
- SQLServer, Memory Manager: Minnesbidrag väntar
- SQLServer, Memory Manager: Beviljat arbetsyteminne (KB)
DBCC MEMORYSTATUS
En annan plats där du kan se information om frågereservationens minne är DBCC MEMORYSTATUS (avsnittet Frågeminnesobjekt). Du kan titta på Query Memory Objects (default) utdata för användarfrågor. Om du har aktiverat Resource Governor med en resurspool med namnet PoolAdmin kan du till exempel titta på både Query Memory Objects (default) och Query Memory Objects (PoolAdmin).
Här är ett exempel på utdata från ett system där 18 begäranden har beviljats frågekörningsminne och 2 begäranden väntar på minne. Den tillgängliga räknaren är noll, vilket indikerar att det inte finns mer ledigt arbetsyteminne. Detta förklarar de två väntande begäranden. Visar Wait Time den förflutna tiden i millisekunder sedan en begäran placerades i väntekön. Mer information om dessa räknare finns i Fråga efter minnesobjekt.
Query Memory Objects (default) Value
------------------------------------------------------------------------ -----------
Grants 18
Waiting 2
Available 0
Current Max 103536
Future Max 97527
Physical Max 139137
Next Request 5752
Waiting For 8628
Cost 16
Timeout 401
Wait Time 2750
(11 rows affected)
Small Query Memory Objects (default) Value
------------------------------------------------------------------------ -----------
Grants 0
Waiting 0
Available 5133
Current Max 5133
Future Max 5133
DBCC MEMORYSTATUS visar också information om minnestjänstemannen som håller reda på frågekörningsminnet. Följande utdata visar att de sidor som allokerats för frågekörningsreservationer (QE) överstiger 800 MB.
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
------------------------------------------------------------------------ -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 824640
Minnes kontorister DMV sys.dm_os_memory_clerks
Om du behöver mer av en tabellresultatuppsättning, som skiljer sig från den avsnittsbaserade DBCC MEMORYSTATUS, kan du använda sys.dm_os_memory_clerks för liknande information. MEMORYCLERK_SQLQERESERVATIONS Leta efter minnestjänstemannen. Frågeminnesobjekten är dock inte tillgängliga i den här DMV:en.
SELECT type, memory_node_id, pages_kb
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLQERESERVATIONS'
Här är ett exempel på utdata:
type memory_node_id pages_kb
----------------------------------------------- -------------- --------------
MEMORYCLERK_SQLQERESERVATIONS 0 824640
MEMORYCLERK_SQLQERESERVATIONS 64 0
Identifiera minnesbidrag med hjälp av utökade händelser (XEvents)
Det finns flera utökade händelser som ger information om minnesbidrag och gör att du kan samla in den här informationen via en spårning:
- sqlserver.additional_memory_grant: Inträffar när en fråga försöker få mer minnesbidrag under körningen. Om du inte får det här extra minnesbidraget kan frågan bli långsammare.
- sqlserver.query_memory_grant_blocking: Inträffar när en fråga blockerar andra frågor i väntan på ett minnesbidrag.
- sqlserver.query_memory_grant_info_sampling: Inträffar i slutet av de slumpmässigt samplade frågorna som tillhandahåller information om minnesbidrag (den kan till exempel användas för telemetri).
- sqlserver.query_memory_grant_resource_semaphores: Inträffar med fem minuters intervall för varje resursguvernörresurspool.
- sqlserver.query_memory_grant_usage: Sker i slutet av frågebearbetningen för frågor med minnesbidrag på över 5 MB för att informera användarna om felaktig minnestilldelning.
- sqlserver.query_memory_grants: Inträffar med fem minuters intervall för varje fråga med ett minnesbeviljande.
Utökade händelser för minnesbidragsfeedback
Information om funktioner för minnesåtergivning för frågebearbetning finns i Feedback om minnesbidrag.
- sqlserver.memory_grant_feedback_loop_disabled: Inträffar när feedbackslingan för minnesåtergivning inaktiveras.
- sqlserver.memory_grant_updated_by_feedback: Inträffar när minnesbidraget uppdateras med feedback.
Varningar om frågekörning som relaterar till minnesbidrag
- sqlserver.execution_warning: Inträffar när en T-SQL-instruktion eller lagrad procedur väntar mer än en sekund på ett minnesbeviljande eller när det första försöket att hämta minne misslyckas. Använd den här händelsen i kombination med händelser som identifierar väntetider för att felsöka konkurrensproblem som påverkar prestanda.
- sqlserver.hash_spill_details: Sker i slutet av hashbearbetningen om det inte finns tillräckligt med minne för att bearbeta byggindata för en hashkoppling. Använd den här händelsen tillsammans med någon av
query_pre_execution_showplanhändelserna ellerquery_post_execution_showplanför att avgöra vilken åtgärd i den genererade planen som orsakar hash-utsläppet. - sqlserver.hash_warning: Inträffar när det inte finns tillräckligt med minne för att bearbeta byggindata för en hashkoppling. Detta resulterar antingen i en hash-rekursion när byggindata partitioneras eller en hash-räddningsaktion när partitioneringen av byggindata överskrider den maximala rekursionsnivån. Använd den här händelsen tillsammans med någon av
query_pre_execution_showplanhändelserna ellerquery_post_execution_showplanför att avgöra vilken åtgärd i den genererade planen som orsakar hashvarningen. - sqlserver.sort_warning: Inträffar när sorteringsåtgärden på en körande fråga inte får plats i minnet. Den här händelsen genereras inte för sorteringsåtgärder som orsakas av att index skapas, bara för sorteringsåtgärder i en fråga. (Till exempel en
Order Byi enSelect-instruktion.) Använd den här händelsen för att identifiera frågor som utförs långsamt på grund av sorteringsåtgärdenwarning_type, särskilt när = 2, vilket indikerar att flera pass över data krävdes för att sortera.
Planera generering av händelser som innehåller information om minnesbidrag
Följande frågeplan som genererar utökade händelser innehåller som standard granted_memory_kb och ideal_memory_kb fält:
- sqlserver.query_plan_profile
- sqlserver.query_post_execution_plan_profile
- sqlserver.query_post_execution_showplan
- sqlserver.query_pre_execution_showplan
Indexskapande för kolumnlager
Ett av de områden som täcks via XEvents är körningsminnet som används under kolumnlagringsbyggnaden. Det här är en lista över tillgängliga händelser:
- sqlserver.column_store_index_build_low_memory: Lagringsmotorn upptäckte ett minnesbristtillstånd och radgruppens storlek minskades. Det finns flera intressanta kolumner här.
- sqlserver.column_store_index_build_memory_trace: Spåra minnesanvändning under indexversionen.
- sqlserver.column_store_index_build_memory_usage_scale_down: Lagringsmotorn skalas ned.
- sqlserver.column_store_index_memory_estimation: Visar resultatet av minnesuppskattningen under columnstore-radgruppsversionen.
Identifiera specifika frågor
Det finns två typer av frågor som du kan hitta när du tittar på den enskilda begärandenivån. De frågor som förbrukar en stor mängd frågekörningsminne och de som väntar på samma minne. Den senare gruppen kan bestå av begäranden med blygsamma behov av minnesbidrag, och i så fall kan du fokusera din uppmärksamhet någon annanstans. Men de kan också vara de skyldiga om de begär enorma minnesstorlekar. Fokusera på dem om du tycker att så är fallet. Det kan vara vanligt att upptäcka att en viss fråga är gärningsmannen, men många instanser av den skapas. De instanser som får minnesbidragen gör att andra instanser av samma fråga väntar på beviljandet. Oavsett specifika omständigheter måste du i slutändan identifiera frågorna och storleken på det begärda körningsminnet.
Identifiera specifika frågor med sys.dm_exec_query_memory_grants
Om du vill visa enskilda begäranden och den minnesstorlek som de har begärt och har beviljats kan du fråga den sys.dm_exec_query_memory_grants dynamiska hanteringsvyn. Den här DMV:en visar information om hur du kör frågor, inte historisk information.
Följande instruktion hämtar data från DMV och hämtar även frågetexten och frågeplanen som ett resultat:
SELECT
session_id
,requested_memory_kb
,granted_memory_kb
,used_memory_kb
,queue_id
,wait_order
,wait_time_ms
,is_next_candidate
,pool_id
,text
,query_plan
FROM sys.dm_exec_query_memory_grants
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
Här är ett förkortat exempel på utdata från frågan under aktiv QE-minnesförbrukning. De flesta frågor har sitt minne beviljat, vilket visas av granted_memory_kb och used_memory_kb är icke-NULL numeriska värden. De frågor som inte fick sin begäran beviljad väntar på körningsminne och granted_memory_kb = NULL. Dessutom placeras de i en väntekö med = queue_id 6. Deras wait_time_ms indikerar cirka 37 sekunders väntan. Session 72 står näst på tur för att få ett bidrag enligt = wait_order 1, medan session 74 kommer efter den med wait_order = 2.
session_id requested_memory_kb granted_memory_kb used_memory_kb queue_id wait_order wait_time_ms is_next_candidate pool_id
---------- -------------------- -------------------- -------------------- -------- ----------- -------------------- ----------------- -------
80 41232 41232 40848 NULL NULL NULL NULL 2
83 41232 41232 40848 NULL NULL NULL NULL 2
84 41232 41232 40848 NULL NULL NULL NULL 2
74 41232 NULL NULL 6 2 37438 0 2
78 41232 41232 40848 NULL NULL NULL NULL 2
81 41232 41232 40848 NULL NULL NULL NULL 2
71 41232 41232 40848 NULL NULL NULL NULL 2
75 41232 NULL NULL 6 0 37438 1 2
82 41232 41232 40848 NULL NULL NULL NULL 2
76 41232 41232 40848 NULL NULL NULL NULL 2
79 41232 41232 40848 NULL NULL NULL NULL 2
85 41232 41232 40848 NULL NULL NULL NULL 2
70 41232 41232 40848 NULL NULL NULL NULL 2
55 41232 41232 40848 NULL NULL NULL NULL 2
59 41232 NULL NULL 6 3 37438 0 2
62 41232 41232 40848 NULL NULL NULL NULL 2
54 41232 41232 40848 NULL NULL NULL NULL 2
77 41232 41232 40848 NULL NULL NULL NULL 2
52 41232 41232 40848 NULL NULL NULL NULL 2
72 41232 NULL NULL 6 1 37438 0 2
69 41232 41232 40848 NULL NULL NULL NULL 2
73 41232 41232 40848 NULL NULL NULL NULL 2
66 41232 NULL NULL 6 4 37438 0 2
68 41232 41232 40848 NULL NULL NULL NULL 2
63 41232 41232 40848 NULL NULL NULL NULL 2
Identifiera specifika frågor med sys.dm_exec_requests
Det finns en väntetyp i SQL Server som anger att en fråga väntar på minnesbidrag RESOURCE_SEMAPHORE. Du kan se den här väntetypen för sys.dm_exec_requests enskilda begäranden. Denna senare DMV är den bästa utgångspunkten för att identifiera vilka frågor som är offer för otillräckligt beviljandeminne. Du kan också se väntetiden RESOURCE_SEMAPHORE i sys.dm_os_wait_stats som aggregerade datapunkter på SQL Server-nivå. Den här väntetypen visas när en frågeminnesbegäran inte kan beviljas på grund av att andra samtidiga frågor har förbrukat minnet. Ett stort antal väntande begäranden och långa väntetider indikerar ett överdrivet antal samtidiga frågor med körningsminne eller stora minnesbegärandestorlekar.
Kommentar
Väntetiden för minnesbidrag är begränsad. Efter en överdriven väntan (till exempel över 20 minuter) överskrider SQL Server frågan och genererar fel 8645: "En timeout inträffade i väntan på att minnesresurser skulle köra frågan. Kör frågan igen." Du kan se timeout-värdet som angetts på servernivå genom att titta på timeout_sec i sys.dm_exec_query_memory_grants. Tidsgränsvärdet kan variera något mellan SQL Server-versioner.
Med hjälp av sys.dm_exec_requestskan du se vilka frågor som har beviljats minne och storleken på det beviljandet. Du kan också identifiera vilka frågor som för närvarande väntar på ett minnesbidrag genom att leta efter väntetypen RESOURCE_SEMAPHORE . Här är en fråga som visar både beviljade och väntande begäranden:
SELECT session_id, wait_type, wait_time, granted_query_memory, text
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
WHERE granted_query_memory > 0
OR wait_type = 'RESOURCE_SEMAPHORE'
Ett exempel på utdata visar att två begäranden har beviljats minne och två dussin andra väntar på bidrag. Kolumnen granted_query_memory rapporterar storleken på 8 KB-sidor. Till exempel innebär värdet 34 709 34 709 * 8 KB = 277 672 kB minne beviljat.
session_id wait_type wait_time granted_query_memory text
---------- ----------------------------------- -------------------- -------------------------------------------------------------------
65 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
66 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
67 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
68 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
69 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
70 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
71 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
72 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
73 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
74 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
75 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
76 ASYNC_NETWORK_IO 11 34709 select * from sys.messages order by message_id option (maxdop 1)
77 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
78 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
79 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
80 RESOURCE_SEMAPHORE 161435 0 select * from sys.messages order by message_id option (maxdop 1)
81 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
82 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
83 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
84 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
85 ASYNC_NETWORK_IO 14 34709 select * from sys.messages order by message_id option (maxdop 1)
86 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
87 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
88 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
89 RESOURCE_SEMAPHORE 161439 0 select * from sys.messages order by message_id option (maxdop 1)
Identifiera specifika frågor med sys.dm_exec_query_stats
Om problemet med minnestilldelning inte inträffar just nu, men du vill identifiera de felaktiga frågorna, kan du titta på historiska frågedata via sys.dm_exec_query_stats. Livslängden för data är kopplad till frågeplanen för varje fråga. När en plan tas bort från plancachen tas motsvarande rader bort från den här vyn. Dmv behåller med andra ord statistik i minnet som inte bevaras efter en OMSTART av SQL Server eller efter minnesbelastning orsakar en plancacheversion. Med detta sagt kan du hitta informationen här värdefull, särskilt för sammanställd frågestatistik. Någon kanske nyligen har rapporterat att det finns stora minnesbidrag från frågor, men när du tittar på serverarbetsbelastningen kanske du upptäcker att problemet är borta. I den här situationen sys.dm_exec_query_stats kan du ge insikter som andra DVM:er inte kan. Här är en exempelfråga som kan hjälpa dig att hitta de 20 viktigaste instruktionerna som förbrukade de största mängderna körningsminne. Dessa utdata visar enskilda instruktioner även om deras frågestruktur är densamma. Är till exempel SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 5 en separat rad från SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 100 (endast filterpredikatvärdet varierar). Frågan hämtar de 20 främsta uttrycken med en maximal beviljandestorlek som är större än 5 MB.
SELECT TOP 20
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
,CONVERT(DECIMAL (10,2), max_grant_kb /1024.0) AS max_grant_mb
,CONVERT(DECIMAL (10,2), min_grant_kb /1024.0) AS min_grant_mb
,CONVERT(DECIMAL (10,2), (total_grant_kb / execution_count) /1024.0) AS avg_grant_mb
,CONVERT(DECIMAL (10,2), max_used_grant_kb /1024.0) AS max_grant_used_mb
,CONVERT(DECIMAL (10,2), min_used_grant_kb /1024.0) AS min_grant_used_mb
,CONVERT(DECIMAL (10,2), (total_used_grant_kb/ execution_count) /1024.0) AS avg_grant_used_mb
,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ execution_count) /1024.0) AS avg_ideal_grant_mb
,CONVERT(DECIMAL (10,2), (total_ideal_grant_kb/ 1024.0)) AS total_grant_for_all_executions_mb
,execution_count
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
WHERE max_grant_kb > 5120 -- greater than 5 MB
ORDER BY max_grant_kb DESC
Du kan få ännu kraftfullare insikter genom att titta på frågorna som aggregerats av query_hash. Det här exemplet illustrerar hur du hittar den genomsnittliga, högsta och minsta beviljandestorleken för en frågeinstruktor för alla sina instanser sedan frågeplanen först cachelagrades.
SELECT TOP 20
MAX(SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1)) AS sample_statement_text
,CONVERT(DECIMAL (10,2), SUM(max_grant_kb) /1024.0) AS max_grant_mb
,CONVERT(DECIMAL (10,2), SUM(min_grant_kb) /1024.0) AS min_grant_mb
,CONVERT(DECIMAL (10,2), (SUM(total_grant_kb) / SUM(execution_count)) /1024.0) AS avg_grant_mb
,CONVERT(DECIMAL (10,2), SUM(max_used_grant_kb) /1024.0) AS max_grant_used_mb
,CONVERT(DECIMAL (10,2), SUM(min_used_grant_kb) /1024.0) AS min_grant_used_mb
,CONVERT(DECIMAL (10,2), (SUM(total_used_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_grant_used_mb
,CONVERT(DECIMAL (10,2), (SUM(total_ideal_grant_kb)/ SUM(execution_count)) /1024.0) AS avg_ideal_grant_mb
,CONVERT(DECIMAL (10,2), SUM(total_grant_kb) /1024.0) AS total_grant_all_executions_mb
,SUM(execution_count) AS execution_count
,query_hash
FROM sys.dm_exec_query_stats QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
GROUP BY query_hash
HAVING SUM(max_grant_kb) > 5120 -- greater than 5 MB
ORDER BY SUM(max_grant_kb) DESC
OPTION (MAX_GRANT_PERCENT = 5)
Kolumnen Sample_Statement_Text visar ett exempel på frågestrukturen som matchar frågehashen, men den bör läsas utan hänsyn till specifika värden i -instruktionen. Om en -instruktion till exempel innehåller WHERE Id = 5kan du läsa den i dess mer allmänna form: WHERE Id = @any_value.
Här är ett förkortat exempelutdata för frågan med endast valda kolumner som visas:
sample_statement_text max_grant_mb avg_grant_mb max_grant_used_mb avg_grant_used_mb avg_ideal_grant_mb total_grant_all_executions_mb execution_count
----------------------------------------- ------------ ------------- ----------------- ----------------- ------------------ ----------------------------- ----------------
select de.ObjectName,de.CounterName,d 282.45 282.45 6.50 6.50 282.45 282.45 1
SELECT SCHEMA_NAME(udf.schema_id) AS [Sch 33.86 8.55 7.80 1.97 8.55 42.74 5
insert into #tmpCounterDateTime (CounterD 32.45 32.45 3.11 3.11 32.45 32.45 1
select db_id() dbid, db_name() dbname, * 20.80 1.30 5.75 0.36 1.30 20.80 16
SELECT SCHEMA_NAME(obj.schema_id) AS [Sch 20.55 5.19 5.13 1.28 5.19 25.93 5
SELECT xmlplan FROM (SELECT ROW_NUMBER() 19.69 1.97 1.09 0.11 1.97 19.69 10
if ( select max(cast(countervalue as floa 16.39 8.20 0.77 0.38 8.20 16.39 2
SELECT udf.name AS [Name], udf.object_id 11.36 5.08 1.66 0.83 5.08 20.33 4
select --* Database_I 10.94 5.47 1.98 0.99 5.47 10.94 2
IF (select max(cast(dat.countervalue as f 8.00 1.00 0.00 0.00 0.53 8.00 8
insert into #tmpCounterDateTime (CounterD 5.72 2.86 1.98 0.99 2.86 5.72 2
INSERT INTO #tmp (CounterDateTime, Counte 5.39 1.08 1.64 0.33 1.08 6.47 6
Identifiera specifika frågor med hjälp av Query Store (QDS) med sys.query_store_runtime_stats
Om du har Aktiverat Query Store kan du dra nytta av den bevarade historiska statistiken. I motsats till data från sys.dm_exec_query_statsöverlever den här statistiken en omstart av SQL Server eller minnesbelastning eftersom de lagras i en databas. QDS har också storleksgränser och en kvarhållningsprincip. Mer information finns i Avsnittet Ange det optimala avbildningsläget för Frågearkiv och Behåll de mest relevanta data i avsnittet Frågearkiv i Metodtips för att hantera Query Store.
Identifiera om dina databaser har Query Store aktiverat med den här frågan:
SELECT name, is_query_store_on FROM sys.databases WHERE is_query_store_on = 1Kör följande diagnostikfråga i kontexten för en specifik databas som du vill undersöka:
SELECT MAX(qtxt.query_sql_text) AS sample_sql_text ,CONVERT(DECIMAL(10,2), SUM(rts.avg_query_max_used_memory) / 128) AS avg_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.min_query_max_used_memory) / 128) AS min_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.max_query_max_used_memory) / 128) AS max_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.stdev_query_max_used_memory) / 128) AS stdev_mem_grant_used_mb ,CONVERT(DECIMAL(10,2), SUM(rts.last_query_max_used_memory) / 128) AS last_mem_grant_used_mb ,SUM(count_executions) AS count_query_executions FROM sys.query_store_runtime_stats rts JOIN sys.query_store_plan p ON p.plan_id = rts.plan_id JOIN sys.query_store_query q ON p.query_id = q.query_id LEFT OUTER JOIN sys.query_store_query_text qtxt ON q.query_text_id = qtxt.query_text_id GROUP BY q.query_hash HAVING SUM(rts.avg_query_max_used_memory) /128 > 5 -- greater than 5 MB ORDER BY SUM(avg_query_max_used_memory) DESC OPTION (MAX_GRANT_PERCENT = 5)Principerna här är desamma som
sys.dm_exec_query_stats. Du ser sammanställd statistik för -satserna. En skillnad är dock att med QDS tittar du bara på frågor i databasens omfång, inte hela SQL Server. Därför kan du behöva känna till databasen där en viss begäran om minnesbidrag kördes. Annars kör du den här diagnostikfrågan i flera databaser tills du hittar de stora minnestilldelarna.Här är ett förkortat exempelutdata:
sample_sql_text avg_mem_grant_used_mb min_mem_grant_used_mb max_mem_grant_used_mb stdev_mem_grant_used_mb last_mem_grant_used_mb count_query_executions ----------------------------------------- ---------------------- ---------------------- ---------------------- ------------------------ ----------------------- ---------------------- SELECT qtxt.query_sql_text ,CONVERT(D 550.16 550.00 550.00 0.00 550.00 1 SELECT qtxt.query_sql_text ,rts.avg_q 61.00 36.00 65.00 10.87 51.00 14 SELECT qtxt.query_sql_text ,q.* ,rts 25.46 25.00 25.00 0.00 25.00 2 insert into #tmpStats select 5 'Database 13.69 13.00 13.00 0.03 13.00 16 SELECT q.* ,rts 11.93 11.00 12.00 0.23 12.00 2 SELECT * ,rts.avg_query_max_used_memory 9.70 9.00 9.00 0.00 9.00 1 SELECT qtxt.query_sql_text ,rts.avg_q 9.32 9.00 9.00 0.00 9.00 1 select db_id() dbid, db_name() dbname, * 7.33 7.00 7.00 0.00 7.00 9 SELECT q.* ,rts.avg_query_max_used_memo 6.65 6.00 6.00 0.00 6.00 1 (@_msparam_0 nvarchar(4000),@_msparam_1 5.17 4.00 5.00 0.68 4.00 2
En anpassad diagnostikfråga
Här är en fråga som kombinerar data från flera vyer, inklusive de tre som angavs tidigare. Det ger en mer grundlig vy över sessionerna och deras bidrag via sys.dm_exec_requests och sys.dm_exec_query_memory_grants, utöver den statistik på servernivå som tillhandahålls av sys.dm_exec_query_resource_semaphores.
Kommentar
Den här frågan returnerar två rader per session på grund av användningen av sys.dm_exec_query_resource_semaphores (en rad för den vanliga resurssemaforen och en annan för resurssemafor med små frågor).
SELECT CONVERT (varchar(30), GETDATE(), 121) as runtime
, r.session_id
, r.wait_time
, r.wait_type
, mg.request_time
, mg.grant_time
, mg.requested_memory_kb
/ 1024 requested_memory_mb
, mg.granted_memory_kb
/ 1024 AS granted_memory_mb
, mg.required_memory_kb
/ 1024 AS required_memory_mb
, max_used_memory_kb
/ 1024 AS max_used_memory_mb
, rs.pool_id as resource_pool_id
, mg.query_cost
, mg.timeout_sec
, mg.resource_semaphore_id
, mg.wait_time_ms AS memory_grant_wait_time_ms
, CASE mg.is_next_candidate
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
ELSE 'Memory has been granted'
END AS 'Next Candidate for Memory Grant'
, r.command
, ltrim(rtrim(replace(replace (substring (q.text, 1, 1000), char(10), ' '), char(13), ' '))) [text]
, rs.target_memory_kb
/ 1024 AS server_target_grant_memory_mb
, rs.max_target_memory_kb
/ 1024 AS server_max_target_grant_memory_mb
, rs.total_memory_kb
/ 1024 AS server_total_resource_semaphore_memory_mb
, rs.available_memory_kb
/ 1024 AS server_available_memory_for_grants_mb
, rs.granted_memory_kb
/ 1024 AS server_total_granted_memory_mb
, rs.used_memory_kb
/ 1024 AS server_used_granted_memory_mb
, rs.grantee_count AS successful_grantee_count
, rs.waiter_count AS grant_waiters_count
, rs.timeout_error_count
, rs.forced_grant_count
, mg.dop
, r.blocking_session_id
, r.cpu_time
, r.total_elapsed_time
, r.reads
, r.writes
, r.logical_reads
, r.row_count
, s.login_time
, d.name
, s.login_name
, s.host_name
, s.nt_domain
, s.nt_user_name
, s.status
, c.client_net_address
, s.program_name
, s.client_interface_name
, s.last_request_start_time
, s.last_request_end_time
, c.connect_time
, c.last_read
, c.last_write
, qp.query_plan
FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_connections c
ON r.connection_id = c.connection_id
INNER JOIN sys.dm_exec_sessions s
ON c.session_id = s.session_id
INNER JOIN sys.databases d
ON r.database_id = d.database_id
INNER JOIN sys.dm_exec_query_memory_grants mg
ON s.session_id = mg.session_id
INNER JOIN sys.dm_exec_query_resource_semaphores rs
ON mg.resource_semaphore_id = rs.resource_semaphore_id
CROSS APPLY sys.dm_exec_sql_text (r.sql_handle ) AS q
CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) qp
OPTION (MAXDOP 1, LOOP JOIN )
Kommentar
Tipset LOOP JOIN används i den här diagnostikfrågan för att undvika minnesbidrag av själva frågan och ingen ORDER BY sats används. Om diagnostikfrågan väntar på ett beviljande i sig, skulle dess syfte med att diagnostisera minnesbidrag besegras. Tipset LOOP JOIN kan potentiellt leda till att diagnostikfrågan blir långsammare, men i det här fallet är det viktigare att få diagnostikresultatet.
Här är ett förkortat exempelutdata från den här diagnostikfrågan med endast valda kolumner.
| session_id | wait_time | wait_type | requested_memory_mb | granted_memory_mb | required_memory_mb | max_used_memory_mb | resource_pool_id |
|---|---|---|---|---|---|---|---|
| 60 | 0 | NULL | 9 | 9 | 7 | 1 | 1 |
| 60 | 0 | NULL | 9 | 9 | 7 | 1 | 2 |
| 75 | 1310085 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 1 |
| 75 | 1310085 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 2 |
| 86 | 1310129 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 1 |
| 86 | 1310129 | RESOURCE_SEMAPHORE | 40 | NULL | 0 | NULL | 2 |
Exempelutdata illustrerar tydligt hur en fråga som skickats av session_id = 60 fick det 9 MB minne som begärdes, men endast 7 MB krävdes för att starta frågekörningen. Till slut använde frågan endast 1 MB av de 9 MB som den fick från servern. Utdata visar också att sessionerna 75 och 86 väntar på minnesbidrag, alltså RESOURCE_SEMAPHORE wait_type. Väntetiden har varit över 1 300 sekunder (21 minuter) och deras granted_memory_mb är NULL.
Den här diagnostikfrågan är ett exempel, så du kan ändra den på alla sätt som passar dina behov. En version av den här frågan används också i diagnostikverktyg som Microsoft SQL Server stöder.
Diagnostikverktyg
Det finns diagnostikverktyg som microsoft SQL Server teknisk support använder för att samla in loggar och mer effektivt felsöka problem. SQL LogScout och Pssdiag Configuration Manager (tillsammans med SQLDiag) samlar in utdata från de tidigare beskrivna DMV:erna och prestandaövervakarens räknare som kan hjälpa dig att diagnostisera problem med minnesbidrag.
Om du kör SQL LogScout med LightPerf-, GeneralPerf- eller DetailedPerf-scenarier samlar verktyget in nödvändiga loggar. Du kan sedan manuellt undersöka YourServer_PerfStats.out och leta -- dm_exec_query_resource_semaphores -- efter och -- dm_exec_query_memory_grants -- utdata. I stället för manuell undersökning kan du använda SQL Nexus för att importera utdata från SQL LogScout eller PSSDIAG till en SQL Server-databas. SQL Nexus skapar två tabeller tbl_dm_exec_query_resource_semaphores och tbl_dm_exec_query_memory_grants, som innehåller den information som behövs för att diagnostisera minnesbidrag. SQL LogScout och PSSDIAG samlar också in Perfmon-loggar i form av . BLG-filer , som kan användas för att granska prestandaräknarna som beskrivs i avsnittet Prestandaövervakarens räknare.
Varför är minnesbidrag viktiga för en utvecklare eller DBA
Baserat på Microsofts supportupplevelse tenderar problem med minnesbeviljande att vara några av de vanligaste minnesrelaterade problemen. Program kör ofta till synes enkla frågor som kan orsaka prestandaproblem på SQL Server på grund av enorma sorterings- eller hashåtgärder. Sådana frågor förbrukar inte bara mycket SQL Server-minne utan gör också att andra frågor väntar på att minnet ska bli tillgängligt, vilket gör prestandaflaskhalsen.
Med hjälp av de verktyg som beskrivs här (DMV:er, Perfmon-räknare och faktiska frågeplaner) kan du identifiera vilka frågor som är konsumenter med stora bidrag. Sedan kan du finjustera eller skriva om dessa frågor för att lösa eller minska minnesanvändningen för arbetsytan.
Vad kan en utvecklare göra med sorterings- och hashåtgärder?
När du har identifierat specifika frågor som förbrukar en stor mängd frågereservationsminne kan du vidta åtgärder för att minska minnestilldelningen genom att göra om dessa frågor.
Vad orsakar sorterings- och hashåtgärder i frågor
Det första steget är att bli medveten om vilka åtgärder i en fråga som kan leda till minnesbidrag.
Orsaker till varför en fråga skulle använda en SORT-operator:
ORDER BY (T-SQL) leder till att rader sorteras innan de strömmas som ett slutligt resultat.
GROUP BY (T-SQL) kan introducera en sorteringsoperator i en frågeplan före gruppering om ett underliggande index inte finns som beställer de grupperade kolumnerna.
DISTINCT (T-SQL) beter sig på samma sätt som
GROUP BY. För att identifiera distinkta rader sorteras mellanliggande resultat och dubbletter tas bort. Optimeraren använder enSortoperator före den här operatorn om data inte redan sorteras på grund av en ordnad indexsökning eller genomsökning.Operatorn Koppla koppling , när den väljs av frågeoptimeraren, kräver att båda anslutna indata sorteras. SQL Server kan utlösa en sortering om ett klustrat index inte är tillgängligt i kopplingskolumnen i någon av tabellerna.
Orsaker till varför en fråga skulle använda en HASH-frågeplansoperator:
Den här listan är inte fullständig men innehåller de vanligaste orsakerna till Hash-åtgärder. Analysera frågeplanen för att identifiera åtgärderna för Hash-matchning.
JOIN (T-SQL): När du ansluter tabeller har SQL Server ett val mellan tre fysiska operatorer,
Nested Loop,Merge JoinochHash Join. Om SQL Server väljer en Hash-koppling behöver den QE-minne för att mellanliggande resultat ska lagras och bearbetas. Vanligtvis kan brist på bra index leda till den här resursdyra kopplingsoperatorn.Hash JoinInformation om hur du undersöker frågeplanen för att identifieraHash Matchfinns i Referens för logiska och fysiska operatorer.DISTINCT (T-SQL): En
Hash Aggregateoperator kan användas för att eliminera dubbletter i en raduppsättning. Information om hur du letar efter enHash Match(Aggregate) i frågeplanen finns i Referens för logiska och fysiska operatorer.UNION (T-SQL): Detta liknar
DISTINCT. EnHash Aggregatekan användas för att ta bort dubbletter för den här operatorn.SUM/AVG/MAX/MIN (T-SQL): En mängdåtgärd kan eventuellt utföras som en
Hash Aggregate. Information om hur du letar efter enHash Match(Aggregate) i frågeplanen finns i Referens för logiska och fysiska operatorer.
Att känna till dessa vanliga orsaker kan hjälpa dig att eliminera, så mycket som möjligt, de stora begäranden om minnesbidrag som kommer till SQL Server.
Sätt att minska sorterings- och hashåtgärder eller beviljandestorlek
- Håll statistiken uppdaterad. Det här grundläggande steget, som förbättrar prestandan för frågor på många nivåer, säkerställer att frågeoptimeraren har den mest exakta informationen när du väljer frågeplaner. SQL Server avgör vilken storlek som ska begäras för minnesbidrag baserat på statistik. Inaktuell statistik kan orsaka överskattning eller underskattning av bidragsbegäran och därmed leda till en onödigt hög bidragsbegäran eller till spillresultat till disk. Kontrollera att statistik för automatisk uppdatering är aktiverad i dina databaser och/eller håll statiska data uppdaterade med UPPDATERA STATISTIK eller sp_updatestats.
- Minska antalet rader som kommer från tabeller. Om du använder ett mer restriktivt WHERE-filter eller en JOIN och minskar antalet rader, kommer en efterföljande sortering i frågeplanen att ordna eller aggregera en mindre resultatuppsättning. En mindre mellanliggande resultatuppsättning kräver mindre minne för arbetsuppsättningar. Det här är en allmän regel som utvecklare kan följa inte bara för att spara arbetsminne utan även för att minska CPU och I/O (det här steget är inte alltid möjligt). Om välskrivna och resurseffektiva frågor redan finns på plats har den här riktlinjen uppfyllts.
- Skapa index för kopplingskolumner för att underlätta sammanslagningskopplingar. De mellanliggande åtgärderna i en frågeplan påverkas av indexen i den underliggande tabellen. Om en tabell till exempel inte har något index för en kopplingskolumn och en kopplingskoppling visar sig vara den mest kostnadseffektiva kopplingsoperatorn, måste alla rader från den tabellen sorteras innan kopplingen utförs. Om det i stället finns ett index i kolumnen kan en sorteringsåtgärd elimineras.
- Skapa index för att undvika hash-åtgärder. Vanligtvis börjar grundläggande frågejustering med att kontrollera om dina frågor har lämpliga index för att hjälpa dem att minska läsningar och minimera eller eliminera stora sorterings- eller hashåtgärder där det är möjligt. Hash-kopplingar väljs ofta för att bearbeta stora, osorterade och icke-indexerade indata. Att skapa index kan ändra den här optimerarstrategin och påskynda datahämtningen. Mer information om hur du skapar index finns i Savetnik za podešavanje mašine baze podataka och Justera icke-illustrerade index med indexförslag som saknas.
- Använd COLUMNSTORE-index där det är lämpligt för aggregeringsfrågor som använder
GROUP BY. Analysfrågor som hanterar mycket stora raduppsättningar och vanligtvis utför "gruppera efter"-sammansättningar kan behöva stora minnessegment för att få arbete gjort. Om ett index inte är tillgängligt som ger ordnade resultat introduceras en sortering automatiskt i frågeplanen. Ett slags mycket stort resultat kan leda till ett dyrt minnesbidrag. ORDER BYTa bort om du inte behöver det. Om resultaten strömmas till ett program som sorterar resultatet på sitt eget sätt eller tillåter att användaren ändrar ordningen på de data som visas, behöver du inte utföra någon sortering på SQL Server-sidan. Strömma bara ut data till programmet i den ordning servern genererar dem och låt slutanvändaren sortera dem på egen hand. Rapporteringsprogram som Power BI eller Reporting Services är exempel på sådana program som gör det möjligt för slutanvändare att sortera sina data.- Överväg, om än försiktigt, att använda ett LOOP JOIN-tips när kopplingar finns i en T-SQL-fråga. Den här tekniken kan undvika hash- eller sammanslagningskopplingar som använder minnesbidrag. Det här alternativet föreslås dock bara som en sista utväg eftersom tvingad koppling kan leda till en betydligt långsammare fråga. Stresstesta din arbetsbelastning för att säkerställa att det här är ett alternativ. I vissa fall kanske en kapslad loopkoppling inte ens är ett alternativ. I det här fallet kan SQL Server misslyckas med fel MSSQLSERVER_8622: "Frågeprocessorn kunde inte skapa en frågeplan på grund av de tips som definierats i den här frågan.".
Frågetips för minnesstipendium
Sedan SQL Server 2012 SP3 har det funnits ett frågetips som gör att du kan styra storleken på ditt minnesbidrag per fråga. Här är ett exempel på hur du kan använda det här tipset:
SELECT Column1, Column2
FROM Table1
ORDER BY Column1
OPTION (MIN_GRANT_PERCENT = 3, MAX_GRANT_PERCENT = 5 )
Vi rekommenderar att du använder konservativa värden här, särskilt i de fall där du förväntar dig att många instanser av frågan ska köras samtidigt. Se till att du stresstestar din arbetsbelastning så att den matchar produktionsmiljön och avgör vilka värden som ska användas.
Mer information finns i MAX_GRANT_PERCENT och MIN_GRANT_PERCENT.
Resource Governor
QE-minne är det minne som Resource Governor faktiskt begränsar när inställningarna för MIN_MEMORY_PERCENT och MAX_MEMORY_PERCENT används. När du har identifierat frågor som orsakar stora minnestillslag kan du begränsa det minne som används av sessioner eller program. Det är värt att nämna att default arbetsbelastningsgruppen tillåter att en fråga tar upp till 25 % av minnet som kan beviljas på en SQL Server-instans. Mer information finns i Resource Governor-resurspooler och SKAPA ARBETSBELASTNINGSGRUPP.
Adaptiv frågebearbetning och feedback om minnesåtergivning
SQL Server 2017 introducerade feedbackfunktionen för minnesbidrag. Det gör att frågekörningsmotorn kan justera det beviljande som ges till frågan baserat på tidigare historik. Målet är att minska storleken på beviljandet när det är möjligt eller öka det när mer minne behövs. Den här funktionen har släppts i tre vågor:
- Minnesåtergivning för Batch-läge i SQL Server 2017
- Minnesåtergivning för radläge i SQL Server 2019
- Feedback om minnesbeviljande på diskpersistence med hjälp av Query Store och percentilbeviljande i SQL Server 2022
Mer information finns i Feedback om minnesbidrag. Funktionen för minnestillviljande kan minska storleken på minnesbidragen för frågor vid körningen och därmed minska de problem som orsakas av stora beviljandebegäranden. Med den här funktionen på plats, särskilt i SQL Server 2019 och senare versioner, där adaptiv bearbetning i radläge är tillgänglig, kanske du inte ens märker några minnesproblem som kommer från frågekörning. Men om du har den här funktionen på plats (på som standard) och fortfarande ser stor QE-minnesförbrukning, använder du stegen som beskrevs tidigare för att skriva om frågor.
Öka SQL Server- eller OS-minne
När du har vidtagit åtgärder för att minska onödiga minnesbidrag för dina frågor kräver arbetsbelastningen troligen mer minne om du fortfarande får problem med minnesbrist. Överväg därför att öka minnet för SQL Server med hjälp av max server memory inställningen om det finns tillräckligt med fysiskt minne i systemet för att göra det. Följ rekommendationerna om att lämna cirka 25 % av minnet för operativsystemet och andra behov. Mer information finns i Konfigurationsalternativ för serverminne. Om det inte finns tillräckligt med minne i systemet kan du överväga att lägga till fysiskt RAM-minne, eller om det är en virtuell dator, öka det dedikerade RAM-minnet för den virtuella datorn.
Minnesstipendium internt
Mer information om några interna frågor om frågekörningsminne finns i blogginlägget Understanding SQL Server memory grant (Förstå SQL Server-minnesbidrag ).
Så här skapar du ett prestandascenario med hög minnesanvändning
Slutligen illustrerar följande exempel hur du simulerar stor förbrukning av frågekörningsminne och introducerar frågor som väntar på RESOURCE_SEMAPHORE. Du kan göra detta för att lära dig hur du använder diagnostikverktygen och teknikerna som beskrivs i den här artikeln.
Varning
Använd inte detta i ett produktionssystem. Den här simuleringen tillhandahålls för att hjälpa dig att förstå konceptet och hjälpa dig att lära dig det bättre.
Installera RML-verktyg och SQL Server på en testserver.
Använd ett klientprogram som SQL Server Management Studio för att sänka den maximala serverminnesinställningen för DIN SQL Server till 1 500 MB:
EXEC sp_configure 'max server memory', 1500 RECONFIGUREÖppna en kommandotolk och ändra katalogen till mappen RML-verktyg:
cd C:\Program Files\Microsoft Corporation\RMLUtilsAnvänd ostress.exe för att skapa flera samtidiga begäranden mot din SQL Server-testserver. I det här exemplet används 30 samtidiga sessioner, men du kan ändra värdet:
ostress.exe -E -S. -Q"select * from sys.messages order by message_id option (maxdop 1)" -n30Använd diagnostikverktygen som beskrevs tidigare för att identifiera problem med minnesbidrag.
Sammanfattning av sätt att hantera stora minnesbidrag
- Skriv om frågor.
- Uppdatera statistik och håll dem uppdaterade regelbundet.
- Skapa lämpliga index för den eller de frågor som identifieras. Index kan minska det stora antalet rader som bearbetas, vilket ändrar
JOINalgoritmerna och minskar storleken på bidrag eller helt eliminerar dem. - Använd tipset
OPTION(min_grant_percent = XX, max_grant_percent = XX). - Använd Resource Governor.
- SQL Server 2017 och 2019 använder anpassningsbar frågebearbetning, vilket gör att mekanismen för minnestillviljande feedback kan justera storleken på minnestillviljan dynamiskt vid körning. Den här funktionen kan förhindra problem med minnesanvändning.
- Öka SQL Server- eller OS-minnet.