Problemen oplossen met trage prestaties of weinig geheugen die worden veroorzaakt door geheugentoekenningen in SQL Server

Artikel
07/30/2024

Wat zijn geheugentoelagen?

Geheugen verleent, ook wel QE-reserveringen (Query Execution) genoemd, Query Execution Memory, Workspace Memory en Memory Reservations, beschrijven het gebruik van geheugen tijdens de uitvoering van query's. SQL Server wijst dit geheugen toe tijdens het uitvoeren van de query voor een of meer van de volgende doeleinden:

Bewerkingen sorteren
Hashbewerkingen
Bewerkingen voor bulksgewijs kopiëren (geen veelvoorkomend probleem)
Index maken, inclusief invoegen in COLUMNSTORE-indexen omdat hashwoordenlijsten/tabellen tijdens runtime worden gebruikt voor het bouwen van indexen (geen veelvoorkomend probleem)

Om bepaalde contexten te bieden, kan een query tijdens de levensduur geheugen aanvragen van verschillende geheugentoewijzingen of bedienden, afhankelijk van wat deze moet doen. Wanneer een query bijvoorbeeld in eerste instantie wordt geparseerd en gecompileerd, wordt compilatiegeheugen verbruikt. Zodra de query is gecompileerd, wordt dat geheugen vrijgegeven en wordt het resulterende queryplan opgeslagen in het geheugen van de plancache. Zodra een plan in de cache is opgeslagen, is de query gereed voor uitvoering. Als de query sorteerbewerkingen, hash-overeenkomstbewerkingen (JOIN of aggregaties) of invoegingen uitvoert in een COLUMNSTORE-index, wordt geheugen gebruikt van de allocator voor het uitvoeren van query's. In eerste instantie vraagt de query om dat uitvoeringsgeheugen en later als dit geheugen wordt verleend, gebruikt de query alle of een deel van het geheugen voor het sorteren van resultaten of hash-buckets. Dit geheugen dat is toegewezen tijdens het uitvoeren van query's, wordt aangeduid als geheugentoelagen. Zoals u zich kunt voorstellen, wordt de geheugentoekenning na voltooiing van de uitvoering van de query teruggezet naar SQL Server voor gebruik voor ander werk. Daarom zijn toewijzingen van geheugentoekenningen tijdelijk van aard, maar kunnen ze nog lang duren. Als een queryuitvoering bijvoorbeeld een sorteerbewerking uitvoert op een zeer grote rijenset in het geheugen, kan het sorteren veel seconden of minuten duren en wordt het toegewezen geheugen gebruikt voor de levensduur van de query.

Voorbeeld van een query met een geheugentoe kennen

Hier volgt een voorbeeld van een query die gebruikmaakt van het uitvoeringsgeheugen en het bijbehorende queryplan met de toekenning:

SELECT * 
FROM sys.messages
ORDER BY message_id

Deze query selecteert een rijenset van meer dan 300.000 rijen en sorteert deze. De sorteerbewerking veroorzaakt een aanvraag voor het verlenen van geheugen. Als u deze query uitvoert in SSMS, kunt u het queryplan bekijken. Wanneer u de meest linkse SELECT operator van het queryplan selecteert, kunt u de geheugentoezeggingsgegevens voor de query weergeven (druk op F4 om eigenschappen weer te geven):

Als u ook met de rechtermuisknop op de witruimte in het queryplan klikt, kunt u XML van het uitvoeringsplan weergeven kiezen ... en een XML-element zoeken waarin dezelfde geheugentoezelingsinformatie wordt weergegeven.

 <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" />

Hier hebben verschillende termen uitleg nodig. Een query vereist mogelijk een bepaalde hoeveelheid uitvoeringsgeheugen (DesiredMemory) en vraagt meestal om die hoeveelheid (RequestedMemory). Tijdens runtime verleent SQL Server alle of een deel van het aangevraagde geheugen, afhankelijk van de beschikbaarheid (GrantedMemory). Uiteindelijk kan de query meer of minder van het oorspronkelijk aangevraagde geheugen (MaxUsedMemory) gebruiken. Als de queryoptimalisatie de benodigde hoeveelheid geheugen heeft overschat, gebruikt deze minder dan de aangevraagde grootte. Maar dat geheugen wordt verspild omdat het door een andere aanvraag kan worden gebruikt. Als de optimizer daarentegen de benodigde hoeveelheid geheugen heeft onderschat, kunnen de overtollige rijen naar de schijf worden gemorst om het werk op het moment van uitvoering uit te voeren. In plaats van meer geheugen toe te wijzen dan de oorspronkelijk aangevraagde grootte, pusht SQL Server de extra rijen naar de schijf en gebruikt deze als een tijdelijke werkruimte. Zie Workfiles en Worktables in Overwegingen voor geheugentoekenningen voor meer informatie.

Terminologie

Laten we eens kijken naar de verschillende termen die u kunt tegenkomen met betrekking tot deze geheugenconsumer. Ook hier worden concepten beschreven die betrekking hebben op dezelfde geheugentoewijzingen.

QE-geheugen (Query Execution Memory): deze term wordt gebruikt om te benadrukken dat tijdens het uitvoeren van een query sorteer- of hashgeheugen wordt gebruikt. QE-geheugen is meestal de grootste consument van het geheugen tijdens de levensduur van een query.
QE-reserveringen (Query Execution) of Geheugenreserveringen: Wanneer een query geheugen nodig heeft voor sorteer- of hashbewerkingen, wordt er een reserveringsaanvraag voor het geheugen ingediend. Deze reserveringsaanvraag wordt berekend tijdens het compileren op basis van de geschatte kardinaliteit. Later, wanneer de query wordt uitgevoerd, verleent SQL Server die aanvraag gedeeltelijk of volledig, afhankelijk van de beschikbaarheid van het geheugen. Uiteindelijk kan de query een percentage van het toegewezen geheugen gebruiken. Er is een geheugenbediende (accountant van geheugen) met de naam 'MEMORYCLERK_SQLQERESERVATIONS' die deze geheugentoewijzingen bijhoudt (bekijk DBCC MEMORYSTATUS of sys.dm_os_memory_clerks).
Geheugentoekenning: Wanneer SQL Server het aangevraagde geheugen verleent aan een uitvoeringsquery, wordt gezegd dat er een geheugentoekenning is opgetreden. Er zijn enkele prestatiemeteritems die de term 'grant' gebruiken. Deze tellers Memory Grants Outstanding en Memory Grants Pendinggeven het aantal geheugentoekenningen aan of wachten. Ze maken geen rekening met de grootte van de geheugentoe kennen. Eén query alleen kan bijvoorbeeld 4 GB geheugen hebben verbruikt om een sortering uit te voeren, maar dat wordt niet weerspiegeld in een van deze tellers.
Werkruimtegeheugen is een andere term die hetzelfde geheugen beschrijft. Vaak ziet u deze term in de Prestatiemeteritem Granted Workspace Memory (KB), die overeenkomt met de totale hoeveelheid geheugen die momenteel wordt gebruikt voor bewerkingen voor sorteren, hash, bulkkopie en index maken, uitgedrukt in KB. Het Maximum Workspace Memory (KB), een andere teller, is een account voor de maximale hoeveelheid werkruimtegeheugen die beschikbaar is voor aanvragen die dergelijke hash-, sorteer-, bulkkopie- en indexbewerkingen moeten uitvoeren. De term Werkruimtegeheugen wordt onregelmatig aangetroffen buiten deze twee tellers.

Invloed van de prestaties van een groot QE-geheugengebruik

In de meeste gevallen, wanneer een thread geheugen in SQL Server aanvraagt om iets gedaan te krijgen en het geheugen niet beschikbaar is, mislukt de aanvraag met een fout met onvoldoende geheugen. Er zijn echter een aantal uitzonderingsscenario's waarbij de thread niet mislukt, maar wacht totdat het geheugen beschikbaar is. Een van deze scenario's is geheugentoelagen en de andere is het compilatiegeheugen van query's. SQL Server maakt gebruik van een threadsynchronisatieobject dat een semaphore wordt genoemd om bij te houden hoeveel geheugen is verleend voor het uitvoeren van query's. Als SQL Server de vooraf gedefinieerde QE-werkruimte niet meer heeft, in plaats van de query te laten mislukken met een fout met onvoldoende geheugen, wordt de query gewacht. Aangezien het geheugen van de werkruimte een aanzienlijk percentage van het totale SQL Server-geheugen mag aannemen, heeft het wachten op geheugen in deze ruimte ernstige gevolgen voor de prestaties. Een groot aantal gelijktijdige query's heeft uitvoeringsgeheugen aangevraagd en samen hebben ze de QE-geheugengroep uitgeput, of een paar gelijktijdige query's hebben elk zeer grote subsidies aangevraagd. In beide gevallen kunnen de resulterende prestatieproblemen de volgende symptomen hebben:

Gegevens- en indexpagina's uit een buffercache zijn waarschijnlijk leeggemaakt om ruimte te maken voor de aanvragen voor grote geheugentoekenning. Dit betekent dat de pagina wordt gelezen die afkomstig is van queryaanvragen moet worden voldaan vanaf de schijf (een aanzienlijk tragere bewerking).
Aanvragen voor andere geheugentoewijzingen kunnen mislukken met geheugenfouten omdat de resource is gekoppeld aan sorteer-, hash- of indexgebouwbewerkingen.
Aanvragen waarvoor het uitvoeringsgeheugen nodig is, wachten totdat de resource beschikbaar is en het lang duurt voordat deze is voltooid. Met andere woorden, voor de eindgebruiker zijn deze query's traag.

Als u daarom wacht op het geheugen van de uitvoering van query's in Perfmon, dynamische beheerweergaven (DMV's) of DBCC MEMORYSTATUS, moet u actie ondernemen om dit probleem op te lossen, met name als het probleem vaak optreedt. Zie Wat kan een ontwikkelaar doen met sorterings- en hashbewerkingen voor meer informatie.

Wachttijden identificeren voor het uitvoeren van query's

Er zijn meerdere manieren om te bepalen of er moet worden gewacht op QE-reserveringen. Kies degene die u het beste kunt gebruiken om het grotere plaatje op serverniveau te zien. Sommige van deze hulpprogramma's zijn mogelijk niet beschikbaar voor u (bijvoorbeeld Perfmon is niet beschikbaar in Azure SQL Database). Zodra u het probleem hebt geïdentificeerd, moet u inzoomen op het afzonderlijke queryniveau om te zien welke query's moeten worden afgestemd of herschreven.

Gebruik op serverniveau de volgende methoden:
- Zie sys.dm_exec_query_resource_semaphores voor meer informatie sys.dm_exec_query_resource_semaphores DMV van resources.
- Prestatiemeteritems Voor meer informatie raadpleegt u het SQL Server Memory Manager-object.
- DBCC MEMORYSTATUS Zie DBCC MEMORYSTATUS voor meer informatie.
- Dmv-sys.dm_os_memory_clerks geheugenbediendes zie sys.dm_os_memory_clerks voor meer informatie.
- Identificeer geheugentoekenningen met behulp van XEvents (Extended Events ) voor meer informatie.
Gebruik op het niveau van de afzonderlijke query de volgende methoden:
- Specifieke query's identificeren met sys.dm_exec_query_memory_grants: momenteel uitgevoerde query's. Zie sys.dm_exec_query_memory_grants voor meer informatie.
- Specifieke query's identificeren met sys.dm_exec_requests: momenteel uitgevoerde query's. Zie sys.dm_exec_requests voor meer informatie.
- Specifieke query's identificeren met sys.dm_exec_query_stats: Historische statistieken over query's. Zie sys.dm_exec_query_stats voor meer informatie.
- Specifieke query's identificeren met behulp van Query Store (QDS) met sys.query_store_runtime_stats: Historische statistieken over query's met QDS. Zie sys.query_store_runtime_stats voor meer informatie.

Statistische statistieken over geheugengebruik

DMV-sys.dm_exec_query_resource_semaphores van resources

Met deze DMV wordt het geheugen van de queryreservering opgesplitst per resourcegroep (intern, standaard en door de gebruiker gemaakt) en resource_semaphore (reguliere en kleine queryaanvragen). Een nuttige query kan zijn:

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

In de volgende voorbeelduitvoer ziet u dat ongeveer 900 MB aan queryuitvoeringsgeheugen wordt gebruikt door 22 aanvragen en dat er nog 3 wachten. Dit vindt plaats in de standaardgroep (pool_id = 2) en de reguliere querysemafore (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)

Prestatiemeteritems

Vergelijkbare informatie is beschikbaar via prestatiemeteritems, waar u de momenteel verleende aanvragen (Memory Grants Outstanding), de wachttoekenningen () en de hoeveelheid geheugen die wordt gebruikt door geheugentoekenningen (Memory Grants PendingGranted Workspace Memory (KB)). In de volgende afbeelding zijn de openstaande subsidies 18, de in behandeling zijnde subsidies zijn 2 en het toegewezen werkruimtegeheugen is 828.288 KB. De Memory Grants Pending Prestatiemeteritem met een niet-nulwaarde geeft aan dat het geheugen is uitgeput.

Zie het SQL Server Memory Manager-object voor meer informatie.

SQLServer, Memory Manager: Maximum aantal werkruimtegeheugen (KB)
SQLServer, Memory Manager: Memory Grant Outstanding
SQLServer, Memory Manager: Geheugentoelagen in behandeling
SQLServer, Memory Manager: Toegewezen werkruimtegeheugen (KB)

DBCC MEMORYSTATUS

Een andere plaats waar u details over het queryreserveringsgeheugen kunt zien, is DBCC MEMORYSTATUS (sectie Querygeheugenobjecten). U kunt de Query Memory Objects (default) uitvoer voor gebruikersquery's bekijken. Als u Resource Governor hebt ingeschakeld met een resourcegroep met de naam PoolAdmin, kunt u bijvoorbeeld beide Query Memory Objects (default) en Query Memory Objects (PoolAdmin).

Hier volgt een voorbeelduitvoer van een systeem waaraan 18 aanvragen zijn verleend voor het uitvoeren van query's en 2 aanvragen wachten op geheugen. De beschikbare teller is nul, wat aangeeft dat er geen werkruimtegeheugen meer beschikbaar is. In dit feit worden de twee wachtaanvragen uitgelegd. Hier Wait Time ziet u de verstreken tijd in milliseconden sinds een aanvraag in de wachtwachtrij is geplaatst. Zie Querygeheugenobjecten voor meer informatie over deze tellers.

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 geeft ook informatie weer over de geheugenbediende die het geheugen van de queryuitvoering bijhoudt. In de volgende uitvoer ziet u dat de pagina's die zijn toegewezen voor QE-reserveringen (Query Execution) groter zijn dan 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

Geheugenbediende DMV-sys.dm_os_memory_clerks

Als u meer van een tabellaire resultatenset nodig hebt, anders dan op basis DBCC MEMORYSTATUSvan secties, kunt u sys.dm_os_memory_clerks gebruiken voor vergelijkbare informatie. Zoek de MEMORYCLERK_SQLQERESERVATIONS geheugenbediende. De querygeheugenobjecten zijn echter niet beschikbaar in deze DMV.

SELECT type, memory_node_id, pages_kb 
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLQERESERVATIONS'

Hier volgt een voorbeelduitvoer:

type                                            memory_node_id pages_kb
----------------------------------------------- -------------- --------------
MEMORYCLERK_SQLQERESERVATIONS                   0              824640
MEMORYCLERK_SQLQERESERVATIONS                   64             0

Geheugentoekenningen identificeren met behulp van XEvents (Extended Events)

Er zijn meerdere uitgebreide gebeurtenissen die geheugentoekenningen bieden en waarmee u deze informatie kunt vastleggen via een tracering:

sqlserver.additional_memory_grant: treedt op wanneer een query probeert meer geheugentoe te kennen tijdens de uitvoering. Als u deze extra geheugentoetoewijzing niet krijgt, kan dit ertoe leiden dat de query wordt vertraagd.
sqlserver.query_memory_grant_blocking: treedt op wanneer een query andere query's blokkeert terwijl wordt gewacht op een geheugentoekenning.
sqlserver.query_memory_grant_info_sampling: vindt plaats aan het einde van de willekeurig steekproefquery's die informatie over geheugentoekenningen bieden (deze kan bijvoorbeeld worden gebruikt voor telemetrie).
sqlserver.query_memory_grant_resource_semaphores: vindt plaats met een interval van vijf minuten voor elke resourcegroep van resource governors.
sqlserver.query_memory_grant_usage: vindt plaats aan het einde van de queryverwerking voor query's met geheugen verleent meer dan 5 MB om gebruikers op de hoogte te stellen van onnauwkeurigheden in het geheugen.
sqlserver.query_memory_grants: vindt plaats op intervallen van vijf minuten voor elke query met een geheugentoezegebeurtenis.

Uitgebreide gebeurtenissen voor geheugentoekoppeling

Zie Feedback over geheugentoedeling voor geheugen verlenen voor informatie over queryverwerkingsfuncties voor geheugentoedeling.

sqlserver.memory_grant_feedback_loop_disabled: treedt op wanneer de feedbacklus voor geheugentoe kennen is uitgeschakeld.
sqlserver.memory_grant_updated_by_feedback: treedt op wanneer geheugentoezening wordt bijgewerkt door feedback.

Queryuitvoeringswaarschuwingen die betrekking hebben op geheugentoekenningen

sqlserver.execution_warning: treedt op wanneer een T-SQL-instructie of opgeslagen procedure meer dan één seconde wacht op een geheugentoekenning of wanneer de eerste poging om geheugen op te halen mislukt. Gebruik deze gebeurtenis in combinatie met gebeurtenissen die wachten identificeren om conflicten op te lossen die van invloed zijn op de prestaties.
sqlserver.hash_spill_details: vindt plaats aan het einde van hashverwerking als er onvoldoende geheugen is om de build-invoer van een hash-join te verwerken. Gebruik deze gebeurtenis samen met een van de query_pre_execution_showplan of query_post_execution_showplan gebeurtenissen om te bepalen welke bewerking in het gegenereerde plan de hash-overloop veroorzaakt.
sqlserver.hash_warning: treedt op wanneer er onvoldoende geheugen is om de build-invoer van een hash-join te verwerken. Dit resulteert in een hash-recursie wanneer de build-invoer wordt gepartitioneerd of een hash-bailout wanneer de partitionering van de build-invoer het maximale recursieniveau overschrijdt. Gebruik deze gebeurtenis samen met een van de query_pre_execution_showplan of query_post_execution_showplan gebeurtenissen om te bepalen welke bewerking in het gegenereerde plan de hashwaarschuwing veroorzaakt.
sqlserver.sort_warning: vindt plaats wanneer de sorteerbewerking op een uitvoerquery niet in het geheugen past. Deze gebeurtenis wordt niet gegenereerd voor sorteerbewerkingen die worden veroorzaakt door het maken van een index, alleen voor sorteerbewerkingen in een query. (Bijvoorbeeld een Order By in een Select instructie.) Gebruik deze gebeurtenis om query's te identificeren die langzaam worden uitgevoerd vanwege de sorteerbewerking, met name wanneer de warning_type = 2, waarmee wordt aangegeven dat er meerdere pass-over de gegevens zijn vereist om te sorteren.

Gebeurtenissen genereren die geheugentoekenneringsgegevens bevatten

Het volgende queryplan voor het genereren van uitgebreide gebeurtenissen bevat standaard granted_memory_kb en ideal_memory_kb velden:

sqlserver.query_plan_profile
sqlserver.query_post_execution_plan_profile
sqlserver.query_post_execution_showplan
sqlserver.query_pre_execution_showplan

Indexgebouw voor kolomarchief

Een van de gebieden die worden behandeld via XEvents is het uitvoeringsgeheugen dat wordt gebruikt tijdens het bouwen van het kolomarchief. Dit is een lijst met beschikbare gebeurtenissen:

sqlserver.column_store_index_build_low_memory: Opslagengine heeft een lage geheugenconditie gedetecteerd en de grootte van de rijgroep is verminderd. Hier zijn verschillende kolommen van belang.
sqlserver.column_store_index_build_memory_trace: Geheugengebruik traceren tijdens de indexbuild.
sqlserver.column_store_index_build_memory_usage_scale_down: Opslagengine omlaag geschaald.
sqlserver.column_store_index_memory_estimation: Geeft het resultaat van de geheugenschatting weer tijdens de build van de ROWSTORE-rijgroep.

Specifieke query's identificeren

Er zijn twee soorten query's die u kunt vinden bij het bekijken van het niveau van de afzonderlijke aanvraag. De query's die een grote hoeveelheid query-uitvoeringsgeheugen gebruiken en query's die wachten op hetzelfde geheugen. De laatste groep kan bestaan uit aanvragen met bescheiden behoefte aan geheugentoelagen, en als dat het geval is, kunt u uw aandacht ergens anders richten. Maar ze kunnen ook de schuldigen zijn als ze enorme geheugengrootten aanvragen. Richt je op hen als je merkt dat dat het geval is. Het kan gebruikelijk zijn om te vinden dat een bepaalde query de dader is, maar veel gevallen ervan worden voortgebracht. Deze instanties die de geheugentoekenningen krijgen, zorgen ervoor dat andere exemplaren van dezelfde query wachten op de toekenning. Ongeacht specifieke omstandigheden moet u uiteindelijk de query's en de grootte van het aangevraagde uitvoeringsgeheugen identificeren.

Specifieke query's identificeren met sys.dm_exec_query_memory_grants

Als u afzonderlijke aanvragen en de geheugengrootte wilt weergeven die ze hebben aangevraagd en zijn verleend, kunt u een query uitvoeren op de sys.dm_exec_query_memory_grants dynamische beheerweergave. Deze DMV toont informatie over het uitvoeren van query's, niet historische informatie.

Met de volgende instructie worden gegevens opgehaald uit de DMV en worden ook de querytekst en het queryplan opgehaald als gevolg:

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)

Hier volgt een verkorte voorbeelduitvoer van de query tijdens het actieve QE-geheugenverbruik. De meeste query's hebben hun geheugen verleend, zoals wordt weergegeven door granted_memory_kb en used_memory_kb niet-NULL-numerieke waarden zijn. De query's waarvoor hun aanvraag niet is verleend, wachten op uitvoeringsgeheugen en de granted_memory_kb = NULL. Ze worden ook in een wachtwachtrij geplaatst met een queue_id = 6. Hun wait_time_ms geeft aan dat er ongeveer 37 seconden wachten. Sessie 72 staat naast elkaar om een subsidie te krijgen, zoals aangegeven door wait_order = 1, terwijl sessie 74 erna komt met 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

Specifieke query's identificeren met sys.dm_exec_requests

Er is een wachttype in SQL Server dat aangeeft dat een query wacht op geheugentoekenning RESOURCE_SEMAPHORE. Mogelijk ziet u dit wachttype sys.dm_exec_requests voor afzonderlijke aanvragen. Deze laatste DMV is het beste startpunt om te bepalen welke query's het slachtoffer zijn van onvoldoende geheugen verlenen. U kunt ook de RESOURCE_SEMAPHORE wachttijd in sys.dm_os_wait_stats bekijken als geaggregeerde gegevenspunten op SQL Server-niveau. Dit wachttype wordt weergegeven wanneer een querygeheugenaanvraag niet kan worden verleend vanwege andere gelijktijdige query's die het geheugen hebben gebruikt. Een groot aantal wachtaanvragen en lange wachttijden geven een overmatig aantal gelijktijdige query's aan met behulp van uitvoeringsgeheugen of grote geheugenaanvraaggrootten.

Notitie

De wachttijd voor geheugentoekenning is eindig. Na een overmatige wachttijd (bijvoorbeeld meer dan 20 minuten), wordt de query door SQL Server getimed en wordt fout 8645 gegenereerd: 'Er is een time-out opgetreden tijdens het wachten tot geheugenresources de query hebben uitgevoerd. Voer de query opnieuw uit. Mogelijk ziet u de time-outwaarde die is ingesteld op serverniveau door in sys.dm_exec_query_memory_grantste kijkentimeout_sec. De time-outwaarde kan enigszins verschillen tussen SQL Server-versies.

Met het gebruik van sys.dm_exec_requests, kunt u zien welke query's zijn verleend geheugen en de grootte van die toekenning. U kunt ook bepalen welke query's momenteel wachten op een geheugentoekending door te zoeken naar het RESOURCE_SEMAPHORE wachttype. Hier volgt een query waarin zowel de verleende als de wachtaanvragen worden weergegeven:

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'

In een voorbeelduitvoer ziet u dat er twee aanvragen zijn toegewezen aan geheugen en dat twee dozijn andere aanvragen wachten op subsidies. De granted_query_memory kolom rapporteert de grootte in pagina's van 8 kB. Een waarde van 34.709 betekent bijvoorbeeld 34.709 * 8 KB = 277.672 KB aan geheugen dat is verleend.

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)

Specifieke query's identificeren met sys.dm_exec_query_stats

Als het probleem met het verlenen van geheugen op dit moment niet plaatsvindt, maar u de offendingquery's wilt identificeren, kunt u historische querygegevens bekijken via sys.dm_exec_query_stats. De levensduur van de gegevens is gekoppeld aan het queryplan van elke query. Wanneer een plan uit de plancache wordt verwijderd, worden de bijbehorende rijen uit deze weergave verwijderd. Met andere woorden, de DMV bewaart statistieken in het geheugen die niet behouden blijven nadat een SQL Server opnieuw is opgestart of nadat geheugendruk een plancacherelease veroorzaakt. Dat gezegd hebbende, kunt u de informatie hier waardevol vinden, met name voor statistische querystatistieken. Iemand heeft onlangs gerapporteerd dat er grote geheugentoekenningen van query's worden weergegeven, maar wanneer u de serverworkload bekijkt, is het probleem mogelijk verdwenen. In deze situatie sys.dm_exec_query_stats kan het inzicht bieden dat andere DVM's dat niet kunnen. Hier volgt een voorbeeldquery waarmee u de belangrijkste 20 instructies kunt vinden die de grootste hoeveelheden uitvoeringsgeheugen hebben verbruikt. In deze uitvoer worden afzonderlijke instructies weergegeven, zelfs als de querystructuur hetzelfde is. Is bijvoorbeeld SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 5 een afzonderlijke rij van SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 100 (alleen de filterpredicaatwaarde varieert). De query haalt de bovenste 20 instructies op met een maximale toekenningsgrootte groter dan 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

Nog krachtiger inzicht kan worden verkregen door de query's te bekijken die worden geaggregeerd door query_hash. In dit voorbeeld ziet u hoe u de gemiddelde, maximum- en minimumtoekenningen voor een query-instructie kunt vinden voor alle exemplaren, omdat het queryplan voor het eerst in de cache is opgeslagen.

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)

In Sample_Statement_Text de kolom ziet u een voorbeeld van de querystructuur die overeenkomt met de query-hash, maar deze moet worden gelezen zonder rekening te houden met specifieke waarden in de instructie. Als een instructie bijvoorbeeld bevatWHERE Id = 5, kunt u deze lezen in de algemenere vorm: WHERE Id = @any_value

Hier volgt een verkorte voorbeelduitvoer van de query, waarbij alleen geselecteerde kolommen worden weergegeven:

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

Specifieke query's identificeren met behulp van Query Store (QDS) met sys.query_store_runtime_stats

Als Query Store is ingeschakeld, kunt u profiteren van de persistente historische statistieken. In tegenstelling tot gegevens uit sys.dm_exec_query_stats, overleven deze statistieken een SQL Server-herstart of geheugenbelasting omdat ze zijn opgeslagen in een database. QDS heeft ook groottelimieten en een bewaarbeleid. Zie voor meer informatie de optimale opnamemodus voor Query Store instellen en de meest relevante gegevens in de secties Query Store behouden in aanbevolen procedures voor het beheren van de Query Store.

Bepaal of Query Store is ingeschakeld voor uw databases met behulp van deze query:
```
SELECT name, is_query_store_on 
FROM sys.databases
WHERE is_query_store_on = 1
```

Voer de volgende diagnostische query uit in de context van een specifieke database die u wilt onderzoeken:

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)

De principes hier zijn hetzelfde als sys.dm_exec_query_stats; u ziet statistische statistieken voor de instructies. Een verschil is echter dat u met QDS alleen query's bekijkt in het bereik van deze database, niet de hele SQL Server. U moet dus mogelijk weten in welke database een bepaalde aanvraag voor geheugentoekenning is uitgevoerd. Voer anders deze diagnostische query uit in meerdere databases totdat u de grote hoeveelheid geheugen verleent.

Hier volgt een verkorte voorbeelduitvoer:

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

Een aangepaste diagnostische query

Hier volgt een query waarin gegevens uit meerdere weergaven worden gecombineerd, inclusief de drie die eerder zijn vermeld. Het biedt een grondiger overzicht van de sessies en hun subsidies via sys.dm_exec_requests en sys.dm_exec_query_memory_grants, naast de statistieken op serverniveau die worden verstrekt door sys.dm_exec_query_resource_semaphores.

Notitie

Deze query retourneert twee rijen per sessie vanwege het gebruik van sys.dm_exec_query_resource_semaphores (één rij voor de reguliere resourcesemafore en een andere voor de kleine queryresourcesmafor).

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 )

Notitie

De LOOP JOIN hint wordt gebruikt in deze diagnostische query om geheugentoekenning door de query zelf te voorkomen en er wordt geen ORDER BY component gebruikt. Als de diagnostische query uiteindelijk wacht op een subsidie zelf, zou het doel van het diagnosticeren van geheugentoekennden worden verslagen. De LOOP JOIN hint kan ertoe leiden dat de diagnostische query langzamer wordt, maar in dit geval is het belangrijker om de diagnostische resultaten op te halen.

Hier volgt een verkorte voorbeelduitvoer van deze diagnostische query met alleen geselecteerde kolommen.

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

De voorbeelduitvoer illustreert duidelijk hoe een query die door session_id = 60 is ingediend, de 9 MB geheugentoekenning heeft gekregen die is aangevraagd, maar slechts 7 MB is vereist om de queryuitvoering te starten. Uiteindelijk heeft de query slechts 1 MB van de 9 MB gebruikt die deze van de server heeft ontvangen. De uitvoer laat ook zien dat sessies 75 en 86 wachten op geheugentoekenningen, dus de RESOURCE_SEMAPHORE wait_type. Hun wachttijd is meer dan 1300 seconden (21 minuten) en hun granted_memory_mb is NULL.

Deze diagnostische query is een voorbeeld, dus u kunt deze op elke gewenste manier aanpassen. Een versie van deze query wordt ook gebruikt in diagnostische hulpprogramma's die door Microsoft SQL Server worden ondersteund.

Diagnostische hulpprogramma's

Er zijn diagnostische hulpprogramma's die technische ondersteuning van Microsoft SQL Server gebruikt om logboeken te verzamelen en efficiënter problemen op te lossen. SQL LogScout en Pssdiag Configuration Manager (samen met SQLDiag) verzamelen uitvoer van de eerder beschreven DMV's en prestatiemeteritems waarmee u problemen met geheugentoekenningen kunt diagnosticeren.

Als u SQL LogScout uitvoert met LightPerf-, GeneralPerf- of DetailedPerf-scenario's, verzamelt het hulpprogramma de benodigde logboeken. Vervolgens kunt u de YourServer_PerfStats.out handmatig onderzoeken en zoeken naar -- dm_exec_query_resource_semaphores -- en -- dm_exec_query_memory_grants -- uitvoer. Of in plaats van handmatig onderzoek kunt u SQL Nexus gebruiken om de uitvoer te importeren die afkomstig is van SQL LogScout of PSSDIAG in een SQL Server-database. SQL Nexus maakt twee tabellen tbl_dm_exec_query_resource_semaphores en tbl_dm_exec_query_memory_grantsbevat de informatie die nodig is voor het diagnosticeren van geheugentoelagen. SQL LogScout en PSSDIAG verzamelen ook Perfmon-logboeken in de vorm van . BLG-bestanden , die kunnen worden gebruikt om de prestatiemeteritems te controleren die worden beschreven in de sectie Prestatiemeteritems .

Waarom worden geheugentoelagen belangrijk voor een ontwikkelaar of DBA

Op basis van de ondersteuningservaring van Microsoft zijn problemen met geheugentoe kennen vaak een aantal van de meest voorkomende problemen met betrekking tot geheugen. Toepassingen voeren vaak schijnbaar eenvoudige query's uit die mogelijk prestatieproblemen op de SQL Server veroorzaken vanwege enorme sorterings- of hashbewerkingen. Dergelijke query's verbruiken niet alleen veel SQL Server-geheugen, maar zorgen er ook voor dat andere query's wachten tot er geheugen beschikbaar is, dus het prestatieknelpunt.

Met behulp van de hulpprogramma's die hier worden beschreven (DMV's, Prestatiemeteritems en werkelijke queryplannen), kunt u bepalen welke query's grootverbruikers zijn. Vervolgens kunt u deze query's afstemmen of herschrijven om het geheugengebruik van de werkruimte op te lossen of te verminderen.

Wat kan een ontwikkelaar doen met sorterings- en hashbewerkingen

Zodra u specifieke query's identificeert die een grote hoeveelheid queryreserveringsgeheugen verbruiken, kunt u stappen ondernemen om de geheugentoekenningen te verminderen door deze query's opnieuw te ontwerpen.

Wat zorgt voor sorteer- en hashbewerkingen in query's

De eerste stap is om te weten welke bewerkingen in een query kunnen leiden tot geheugentoelagen.

Redenen waarom een query een SORT-operator zou gebruiken:

ORDER BY (T-SQL) leidt tot rijen die worden gesorteerd voordat ze als eindresultaat worden gestreamd.
GROUP BY (T-SQL) kan een sorteeroperator in een queryplan introduceren voordat u groepeert als een onderliggende index niet aanwezig is dat de gegroepeerde kolommen ordenen.
DISTINCT (T-SQL) gedraagt zich op dezelfde manier als GROUP BY. Als u afzonderlijke rijen wilt identificeren, worden de tussenliggende resultaten geordend en worden dubbele waarden verwijderd. De optimizer maakt gebruik van een Sort operator vóór deze operator als de gegevens nog niet zijn gesorteerd vanwege een geordende indexzoeking of scan.
De operator Join samenvoegen , wanneer deze is geselecteerd door de queryoptimalisatie, vereist dat beide gekoppelde invoer worden gesorteerd. SQL Server kan een sortering activeren als een geclusterde index niet beschikbaar is in de joinkolom in een van de tabellen.

Redenen waarom een query een hash-queryplanoperator zou gebruiken:

Deze lijst is niet volledig, maar bevat de meest voorkomende redenen voor Hash-bewerkingen. Analyseer het queryplan om de hash-overeenkomstbewerkingen te identificeren.

JOIN (T-SQL): Bij het samenvoegen van tabellen heeft SQL Server een keuze tussen drie fysieke operators, Nested Loopen Merge JoinHash Join. Als SQL Server uiteindelijk een Hash Join kiest, moet QE-geheugen worden opgeslagen en verwerkt voor tussenliggende resultaten. Normaal gesproken kan een gebrek aan goede indexen leiden tot deze duurste joinoperator. Hash Join Zie de naslaginformatie over logische en fysieke operators om het queryplan te onderzoeken dat u wilt identificerenHash Match.
DISTINCT (T-SQL):een Hash Aggregate operator kan worden gebruikt om duplicaten in een rijenset te elimineren. Als u een Hash Match (Aggregate) in het queryplan wilt zoeken, raadpleegt u de naslaginformatie over logische en fysieke operators.
UNION (T-SQL): dit is vergelijkbaar met DISTINCT. Een Hash Aggregate kan worden gebruikt om de duplicaten voor deze operator te verwijderen.
SUM/AVG/MAX/MIN (T-SQL): elke statistische bewerking kan mogelijk worden uitgevoerd als een Hash Aggregate. Als u een Hash Match (Aggregate) in het queryplan wilt zoeken, raadpleegt u de naslaginformatie over logische en fysieke operators.

Als u deze veelvoorkomende redenen kent, kunt u zoveel mogelijk de aanvragen voor grote geheugentoe kennen die naar SQL Server worden verzonden, elimineren.

Manieren om sorteer- en hashbewerkingen of de toekenningsgrootte te verminderen

Houd statistieken up-to-date. Deze fundamentele stap, die de prestaties voor query's op veel niveaus verbetert, zorgt ervoor dat de queryoptimalisatie de meest nauwkeurige informatie heeft bij het selecteren van queryplannen. SQL Server bepaalt welke grootte moet worden aangevraagd voor de geheugentoe kennen op basis van statistieken. Verouderde statistieken kunnen leiden tot overschatting of onderschatting van de subsidieaanvraag en leiden dus tot een onnodig hoog subsidieverzoek of tot het overlopen van resultaten op schijf. Zorg ervoor dat statistieken voor automatisch bijwerken zijn ingeschakeld in uw databases en/of dat statische gegevens bijgewerkt blijven met UPDATE STATISTICS of sp_updatestats.
Verminder het aantal rijen dat afkomstig is van tabellen. Als u een meer beperkend WHERE-filter of een JOIN gebruikt en het aantal rijen vermindert, krijgt een volgende sortering in het queryplan de volgorde of het aggregeren van een kleinere resultatenset. Voor een kleinere tussenliggende resultatenset is minder werksetgeheugen vereist. Dit is een algemene regel die ontwikkelaars niet alleen kunnen volgen voor het opslaan van werksetgeheugen, maar ook om cpu en I/O te verminderen (deze stap is niet altijd mogelijk). Als er al goed geschreven en resource-efficiënte query's aanwezig zijn, is aan deze richtlijn voldaan.
Indexen maken voor joinkolommen om samenvoegingssamenvoegingen te helpen. De tussenliggende bewerkingen in een queryplan worden beïnvloed door de indexen in de onderliggende tabel. Als een tabel bijvoorbeeld geen index heeft voor een joinkolom en een samenvoegingskolom de meest rendabele joinoperator is, moeten alle rijen uit die tabel worden gesorteerd voordat de join wordt uitgevoerd. Als er in plaats daarvan een index in de kolom bestaat, kan een sorteerbewerking worden geëlimineerd.
Maak indexen om hashbewerkingen te voorkomen. Het afstemmen van basisquery's begint meestal met het controleren of uw query's de juiste indexen hebben om leesbewerkingen te verminderen en grote sorteringen of hashbewerkingen waar mogelijk te minimaliseren of te elimineren. Hash-joins worden meestal geselecteerd om grote, niet-gesorteerde en niet-geïndexeerde invoer te verwerken. Het maken van indexen kan deze optimalisatiestrategie wijzigen en het ophalen van gegevens versnellen. Zie Database Engine Tuning Advisor en Tune niet-geclusterde indexen met ontbrekende indexsuggesties voor hulp bij het maken van indexen .
Gebruik COLUMNSTORE-indexen waar nodig voor aggregatiequery's die gebruikmaken GROUP BYvan . Analysequery's die te maken hebben met zeer grote rijensets en doorgaans groeperen op aggregaties uitvoeren, hebben mogelijk grote geheugensegmenten nodig om werk te kunnen doen. Als er geen index beschikbaar is die geordende resultaten levert, wordt automatisch een sortering geïntroduceerd in het queryplan. Een soort zeer groot resultaat kan leiden tot een dure geheugentoe kennen.
Verwijder de ORDER BY optie als u deze niet nodig hebt. In gevallen waarin resultaten worden gestreamd naar een toepassing die de resultaten op een eigen manier sorteert of de gebruiker de volgorde van de weergegeven gegevens laat wijzigen, hoeft u geen sortering uit te voeren aan de zijde van SQL Server. Stream de gegevens naar de toepassing in de volgorde waarin de server deze produceert en laat de eindgebruiker deze zelf sorteren. Rapportagetoepassingen zoals Power BI of Reporting Services zijn voorbeelden van dergelijke toepassingen waarmee eindgebruikers hun gegevens kunnen sorteren.
Overweeg, zij het voorzichtig, het gebruik van een LOOP JOIN-hint wanneer joins aanwezig zijn in een T-SQL-query. Met deze techniek voorkomt u hash- of samenvoegingsdeelnames die gebruikmaken van geheugentoekenningen. Deze optie wordt echter alleen als laatste redmiddel voorgesteld, omdat het afdwingen van een join kan leiden tot een aanzienlijk tragere query. Stresstest uw workload om ervoor te zorgen dat dit een optie is. In sommige gevallen is een geneste lusdeelname mogelijk niet eens een optie. In dit geval kan SQL Server mislukken met een fout MSSQLSERVER_8622: 'Queryprocessor kan geen queryplan produceren vanwege de hints die in deze query zijn gedefinieerd'.

Hint voor het verlenen van geheugenquery's

Sinds SQL Server 2012 SP3 bestaat er een queryhint waarmee u de grootte van uw geheugentoekenning per query kunt beheren. Hier volgt een voorbeeld van hoe u deze hint kunt gebruiken:

SELECT Column1,  Column2
FROM Table1 
ORDER BY Column1 
OPTION (MIN_GRANT_PERCENT = 3, MAX_GRANT_PERCENT = 5 )

We raden u aan hier conservatieve waarden te gebruiken, met name in de gevallen waarin u verwacht dat veel exemplaren van uw query gelijktijdig worden uitgevoerd. Zorg ervoor dat u uw workload test zodat deze overeenkomt met uw productieomgeving en bepaal welke waarden u wilt gebruiken.

Zie MAX_GRANT_PERCENT en MIN_GRANT_PERCENT voor meer informatie.

Resource Governor

QE-geheugen is het geheugen dat Resource Governor daadwerkelijk beperkt wanneer de MIN_MEMORY_PERCENT en MAX_MEMORY_PERCENT instellingen worden gebruikt. Zodra u query's identificeert die grote geheugentoekenningen veroorzaken, kunt u het geheugen beperken dat wordt gebruikt door sessies of toepassingen. Het is de moeite waard om te vermelden dat de default workloadgroep een query toestaat dat maximaal 25% van het geheugen kan worden verleend op een SQL Server-exemplaar. Zie Resource Governor-resourcegroepen en CREATE WORKLOAD GROUP voor meer informatie.

Adaptieve queryverwerking en feedback over het verlenen van geheugen

SQL Server 2017 heeft de feedbackfunctie voor geheugentoekenning geïntroduceerd. Hiermee kan de engine voor het uitvoeren van query's de toekenning aan de query aanpassen op basis van de vorige geschiedenis. Het doel is om de subsidie zo mogelijk te verkleinen of te vergroten wanneer er meer geheugen nodig is. Deze functie is uitgebracht in drie golven:

Feedback over het verlenen van geheugen in batchmodus in SQL Server 2017
Feedback over het verlenen van geheugen in rijmodus in SQL Server 2019
Feedback over geheugentoe kennen op schijfpersistentie met behulp van de Query Store en percentieltoezeiking in SQL Server 2022

Zie Feedback over geheugen verlenen voor meer informatie. De functie geheugentoekenningen kan de grootte van de geheugentoekenningen voor query's tijdens de uitvoering verminderen en zo de problemen verminderen die voortvloeien uit grote subsidieaanvragen. Met deze functie, met name op SQL Server 2019 en latere versies, waar adaptieve verwerking in rijmodus beschikbaar is, ziet u mogelijk zelfs geen geheugenproblemen die afkomstig zijn van de uitvoering van query's. Als u deze functie echter hebt ingesteld (standaard ingeschakeld) en nog steeds groot QE-geheugenverbruik ziet, past u de stappen toe die eerder zijn besproken om query's te herschrijven.

SQL Server- of besturingssysteemgeheugen vergroten

Nadat u de stappen hebt uitgevoerd om onnodige geheugentoekenningen voor uw query's te verminderen, als u nog steeds te maken hebt met problemen met weinig geheugen, vereist de workload waarschijnlijk meer geheugen. Overweeg daarom het geheugen voor SQL Server te verhogen met behulp van de max server memory instelling als er voldoende fysiek geheugen op het systeem is om dit te doen. Volg de aanbevelingen voor het verlaten van ongeveer 25% van het geheugen voor het besturingssysteem en andere behoeften. Zie Opties voor servergeheugenconfiguratie voor meer informatie. Als er onvoldoende geheugen beschikbaar is op het systeem, kunt u het fysieke RAM-geheugen toevoegen of als het een virtuele machine is, het toegewezen RAM-geheugen voor uw VIRTUELE machine verhogen.

Interne geheugentoekenningen

Zie het blogbericht Over het verlenen van SQL Server-geheugen voor meer informatie over enkele interne gegevens over het geheugen van query's.

Een prestatiescenario maken met intensief geheugentoe kennen

Ten slotte ziet u in het volgende voorbeeld hoe u een groot verbruik van het geheugen voor queryuitvoering simuleert en query's introduceert die wachten.RESOURCE_SEMAPHORE U kunt dit doen om te leren hoe u de diagnostische hulpprogramma's en technieken gebruikt die in dit artikel worden beschreven.

Waarschuwing

Gebruik dit niet in een productiesysteem. Deze simulatie wordt gegeven om u te helpen het concept te begrijpen en om u te helpen het beter te leren.

Installeer RML-hulpprogramma's en SQL Server op een testserver.
Gebruik een clienttoepassing zoals SQL Server Management Studio om de maximale servergeheugeninstelling van uw SQL Server te verlagen tot 1500 MB:
```
EXEC sp_configure 'max server memory', 1500
RECONFIGURE
```
Open een opdrachtprompt en wijzig de map in de map RML-hulpprogramma's:
```
cd C:\Program Files\Microsoft Corporation\RMLUtils   
```
Gebruik ostress.exe om meerdere gelijktijdige aanvragen uit te voeren voor uw test-SQL Server. In dit voorbeeld worden 30 gelijktijdige sessies gebruikt, maar u kunt deze waarde wijzigen:
```
ostress.exe -E -S. -Q"select * from sys.messages order by message_id option (maxdop 1)" -n30
```
Gebruik de diagnostische hulpprogramma's die eerder zijn beschreven om problemen met geheugentoe kennen te identificeren.

Overzicht van manieren om te gaan met grote geheugentoelagen

Query's herschrijven.
Werk statistieken bij en houd ze regelmatig bijgewerkt.
Maak de juiste indexen voor de geïdentificeerde query of query's. Indexen kunnen het grote aantal verwerkte rijen verminderen, waardoor de JOIN algoritmen worden gewijzigd en de grootte van subsidies wordt verkleind of volledig worden geëlimineerd.
Gebruik de OPTION hint (min_grant_percent = XX, max_grant_percent = XX).
Gebruik Resource Governor.
SQL Server 2017 en 2019 maken gebruik van adaptieve queryverwerking, zodat het feedbackmechanisme voor geheugentoedeling dynamisch kan worden aangepast tijdens runtime. Deze functie kan problemen met geheugentoekenning in de eerste plaats voorkomen.
Verhoog het GEHEUGEN van SQL Server of het besturingssysteem.

Delen via

Problemen oplossen met trage prestaties of weinig geheugen die worden veroorzaakt door geheugentoekenningen in SQL Server

Wat zijn geheugentoelagen?

Voorbeeld van een query met een geheugentoe kennen

Terminologie

Invloed van de prestaties van een groot QE-geheugengebruik

Wachttijden identificeren voor het uitvoeren van query's

Statistische statistieken over geheugengebruik

DMV-sys.dm_exec_query_resource_semaphores van resources

Prestatiemeteritems

DBCC MEMORYSTATUS

Geheugenbediende DMV-sys.dm_os_memory_clerks

Geheugentoekenningen identificeren met behulp van XEvents (Extended Events)

Uitgebreide gebeurtenissen voor geheugentoekoppeling

Queryuitvoeringswaarschuwingen die betrekking hebben op geheugentoekenningen

Gebeurtenissen genereren die geheugentoekenneringsgegevens bevatten

Indexgebouw voor kolomarchief

Specifieke query's identificeren

Specifieke query's identificeren met sys.dm_exec_query_memory_grants

Specifieke query's identificeren met sys.dm_exec_requests

Specifieke query's identificeren met sys.dm_exec_query_stats

Specifieke query's identificeren met behulp van Query Store (QDS) met sys.query_store_runtime_stats

Een aangepaste diagnostische query

Diagnostische hulpprogramma's

Waarom worden geheugentoelagen belangrijk voor een ontwikkelaar of DBA

Wat kan een ontwikkelaar doen met sorterings- en hashbewerkingen

Wat zorgt voor sorteer- en hashbewerkingen in query's

Manieren om sorteer- en hashbewerkingen of de toekenningsgrootte te verminderen

Hint voor het verlenen van geheugenquery's

Resource Governor

Adaptieve queryverwerking en feedback over het verlenen van geheugen

SQL Server- of besturingssysteemgeheugen vergroten

Interne geheugentoekenningen

Een prestatiescenario maken met intensief geheugentoe kennen

Overzicht van manieren om te gaan met grote geheugentoelagen

Feedback

Aanvullende resources