Felsöka problem med långsamma prestanda eller minnesbrist som orsakas av minnestillskott i SQL Server
Vad är minnestillslag?
Minnestillslag, även kallade QE-reservationer (Frågekörning), frågekörningsminne, arbetsyteminne och minnesreservationer, beskriver användningen av minne vid frågekörningstid. 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)
- Skapa index, inklusive infoga i COLUMNSTORE-index eftersom hashordlistor/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 initialt förbrukar den kompileringsminne. När frågan har kompilerats frigörs 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 delar av minnet för sorteringsresultat eller hash-bucketar. Det här minnet som allokeras under frågekörningen kallas minnestillämpa. Som du kan föreställa dig släpps minnesbidraget tillbaka till SQL Server som ska användas för annat arbete när frågekörningen har slutförts. Därför är tilldelningar av minnestilldelningar 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 SELECT operatorn 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 välja Visa XML för körningsplan... och leta upp ett XML-element som visar samma information om minnestilldelning.
<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 vilja ha en viss mängd körningsminne (DesiredMemory) och begär ofta den mängden (RequestedMemory). Vid körning beviljar SQL Server hela eller delar 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. Å andra sidan, om optimeraren har underskattat storleken på det minne som behövs, kan överflödiga rader spillas 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.
Termer
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 minne. Reservationsbegäran beräknas vid kompileringstid 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).
Minnestillslag: 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 nöjda eller väntande. De tar inte hänsyn till storleken på minnesbidraget. En fråga ensam kunde ha förbrukat 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 uttryckta i KB. ,Maximum Workspace Memory (KB)en annan räknare, står för den maximala mängden arbetsyteminne som är tillgängligt 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 inuti SQL Server för att få något gjort och minnet inte är tillgängligt, misslyckas begäran med ett fel om slut på minne. 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 får frågan att vänta i stället för att misslyckas med ett minnesfel. Med tanke på att arbetsytans minne tillåts ta en betydande del av det totala SQL Server minne, 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 buffertcachen har troligen rensats ut för att ge 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 bunden med åtgärder för sortering, hash eller indexskapande.
- 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å:
- Resurs semafor DMV-sys.dm_exec_query_resource_semaphores Mer information finns i sys.dm_exec_query_resource_semaphores.
- Prestandaövervakarens räknare Mer information finns i SQL Server Memory Manager-objekt.
- DBCC MEMORYSTATUS Mer information finns i DBCC MEMORYSTATUS.
- DMV-sys.dm_os_memory_clerks för minnesbiträden Mer information finns i sys.dm_os_memory_clerks.
- Identifiera minnestillslag med hjälp av Extended Events (XEvents) Mer information finns i Extended Events (XEvents).
Använd följande metoder på enskild frågenivå:
- 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 för 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äll minnesanvändningsstatistik
Resurs semafor 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ågan semafor (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 begäranden som för närvarande har beviljats (Memory Grants Outstanding), väntande beviljandebegäranden (Memory Grants Pending) och mängden minne som används av minnestillslag (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 icke-nollvärde anger att minnet har förbrukats.
Mer information finns i SQL Server Memory Manager-objekt.
- SQLServer, Memory Manager: Maximalt arbetsyteminne (KB)
- SQLServer, Memory Manager: Minnestilldelar enastående
- SQLServer, Memory Manager: Minnestilldelar väntande
- 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 faktum förklarar de två väntande begärandena. Wait Time Visar den förflutna tiden i millisekunder sedan en begäran placerades i väntekön. Mer information om dessa räknare finns i Frågeminnesobjekt.
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
Dmv-sys.dm_os_memory_clerks för minnesbiträden
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. Leta MEMORYCLERK_SQLQERESERVATIONS efter minnestjänstemannen. Frågeminnesobjekten är dock inte tillgängliga i den här DMV:n.
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 minnestillslag med hjälp av Extended Events (XEvents)
Det finns flera utökade händelser som ger information om minnesanvändning 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 ytterligare 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 minnestillviljande (den kan till exempel användas för telemetri).
- sqlserver.query_memory_grant_resource_semaphores: Sker med fem minuters intervall för varje resursguvernörresurspool.
- sqlserver.query_memory_grant_usage: Inträffar i slutet av frågebearbetningen för frågor med minnestillslag på över 5 MB för att informera användarna om felaktig minnestilldelning.
- sqlserver.query_memory_grants: Sker med fem minuters intervall för varje fråga med en minnesbeviljande.
Utökade händelser för minnesbidragsfeedback
Information om funktioner för minnesåtergivning för frågebearbetning finns i Bevilja feedback om minne.
- sqlserver.memory_grant_feedback_loop_disabled: Inträffar när återkopplingsloopen för minnesåtergivning är inaktiverad.
- sqlserver.memory_grant_updated_by_feedback: Inträffar när minnesbidrag uppdateras med feedback.
Frågekörningsvarningar som relaterar till minnestillslag
- sqlserver.execution_warning: Inträffar när en T-SQL-instruktion eller lagrad procedur väntar mer än en sekund på en 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 hash-koppling. 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 hash-koppling. 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örd 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ör långsamt på grund av sorteringsåtgärdenwarning_type, särskilt när = 2, vilket indikerar att flera passerar ö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
Indexbyggnad 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 låg minnestillstånd och radgruppsstorleken minskades. Det finns flera intressanta kolumner här.
- sqlserver.column_store_index_build_memory_trace: Spåra minnesanvändning under indexet.
- 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 hämtar minnestillslag 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 köra frågor mot den sys.dm_exec_query_memory_grants dynamiska hanteringsvyn. Den här DMV:n visar information om hur du kör frågor för närvarande, 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 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. Den senare DMV:n ä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 anger ett överdrivet antal samtidiga frågor med hjälp av körningsminne eller stora minnesbegäranden.
Obs!
Väntetiden för minnestillslag är begränsad. Efter en överdriven väntan (till exempel över 20 minuter) SQL Server gånger frågan ut och genererar fel 8645: "En timeout uppstod 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 söka 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 felande frågorna, kan du titta på historiska frågedata via sys.dm_exec_query_stats. Datalivslängden är knuten 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. Med andra ord behåller DMV-filen statistik i minnet som inte bevaras efter en SQL Server omstart eller efter att 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 det här fallet sys.dm_exec_query_stats kan 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. Till exempel SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 5 är 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 instruktionerna 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 mer kraftfulla insikter genom att titta på frågorna aggregerade av query_hash. Det här exemplet illustrerar hur du hittar den genomsnittliga, högsta och lägsta beviljandestorleken för en frågeinstruktor för alla dess 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 exempel på utdata från 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 SQL Server omstart eller minnesbelastning eftersom de lagras i en databas. QDS har också storleksbegränsningar och en kvarhållningsprincip. Mer information finns i avsnittet Ange optimalt avbildningsläge för frågearkiv och Behåll de mest relevanta data i Query Store i Metodtips för hantering av 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 aggregerad 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 ansenliga 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. Den ger en mer ingående 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.
Obs!
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 resurssmaforen och en annan för resurssmaforen 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 )
Obs!
Tipset LOOP JOIN används i den här diagnostikfrågan för att undvika att själva frågan beviljar minne, och ingen sats ORDER BY används. Om diagnostikfrågan väntar på ett bidrag skulle dess syfte att diagnostisera minnestilldelningar besegras. Tipset LOOP JOIN kan potentiellt göra att diagnostikfrågan blir långsammare, men i det här fallet är det viktigare att hämta diagnostikresultatet.
Här är ett förkortat exempel på utdata 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. I slutändan använde frågan bara 1 MB av de 9 MB som den fick från servern. Utdata visar också att sessionerna 75 och 86 väntar på minnestillslag, alltså RESOURCE_SEMAPHOREwait_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öd använder.
Diagnosverktyg
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 tidigare beskrivna DMV:er och prestandaövervakare 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. Eller så kan du i stället för manuell undersökning 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 minnestillslag. 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 minnestillslag 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 innebär flaskhalsen för prestanda.
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 åt 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 frågorna.
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 minnestillslag.
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 det inte finns något underliggande index som beställer de grupperade kolumnerna.
DISTINCT (T-SQL) fungerar på samma sätt som
GROUP BY. För att identifiera distinkta rader sorteras mellanliggande resultat och dubbletter tas sedan bort. Optimeringen använder enSortoperator före den här operatorn om data inte redan är sorterade på grund av en ordnad indexsökning eller genomsökning.Operatorn Sammanfoga 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 grupperat 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 hashmatchningsåtgärderna.
JOIN (T-SQL): När du kopplar tabeller kan SQL Server välja 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 Join. Information 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 söker 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): Alla aggregeringsåtgärder kan potentiellt utföras som en
Hash Aggregate. Information om hur du söker 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 beviljandestorleken
- 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 dess 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. Se till att statistik för automatisk uppdatering är aktiverad i dina databaser och/eller håll statiska data uppdaterade med UPPDATERINGSSTATISTIK 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 arbetsminne. Det här är en allmän regel som utvecklare kan följa inte bara för att spara arbetsminne utan också 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. Mellanliggande åtgärder 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 anses 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. Vanligen 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 sorters eller hashåtgärder där det är möjligt. Hashkopplingar väljs ofta för att bearbeta stora, osorterade och icke-indexerade indata. Att skapa index kan ändra den här optimeringsstrategin och påskynda datahämtningen. Mer information om hur du skapar index finns i Finjusteringsverktyg för databasmotorer och Finjustera icke-klustrade 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"-aggregeringar kan behöva stora minnessegment för att få jobbet gjort. Om ett index inte är tillgängligt som ger ordnade resultat introduceras automatiskt en sortering i frågeplanen. Ett slags mycket stort resultat kan leda till ett dyrt minnesbidrag. - Ta bort om
ORDER BYdu inte behöver den. Om resultaten strömmas till ett program som sorterar resultaten 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 producerar dem och låta 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 minnestillslag. 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 detta ä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 minnesbidrag
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 minnesbidrag
SQL Server 2017 introducerade funktionen för minnesbidragsfeedback. Det gör att frågekörningsmotorn kan justera beviljandet 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:
- Feedback om minnesåtergivning i batchläge i SQL Server 2017
- Feedback om minnesåtergivning i radläge i SQL Server 2019
- Minnesbeviljande feedback 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å minnestillslagen för frågor vid körning och därmed minska de problem som härrör från stora bidragsbegäranden. Med den här funktionen på plats, särskilt på SQL Server 2019 och senare versioner, där anpassningsbar bearbetning i radläge är tillgänglig, kanske du inte ens märker några minnesproblem som kommer från frågekörning. Om du däremot har den här funktionen på plats (på som standard) och fortfarande ser en stor minnesförbrukning för QE 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 minnestillslag för dina frågor kräver arbetsbelastningen troligen mer minne om du fortfarande har 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.
Internt beviljande av minne
Mer information om några interna frågor om frågekörningsminne finns i blogginlägget Understanding SQL Server memory grant (Förstå sql-serverminnesbidrag ).
Så här skapar du ett prestandascenario med hög minnesanvändning
Slutligen illustrerar följande exempel hur du simulerar en stor förbrukning av frågekörningsminne och introducerar frågor som väntar på RESOURCE_SEMAPHORE. Du kan göra detta om du vill 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 inställningen för maximalt serverminne 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 ditt test SQL Server. 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 minnestillslag
- 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-minne.
Feedback
Kommer snart: Under hela 2024 kommer vi att fasa ut GitHub-problem som feedbackmekanism för innehåll och ersätta det med ett nytt feedbacksystem. Mer information finns i: https://aka.ms/ContentUserFeedback.
Skicka och visa feedback för