Problemen met trage prestaties of weinig geheugen oplossen die worden veroorzaakt door geheugentoelagen in SQL Server
Wat zijn geheugensubsidies?
Geheugentoekenningen, ook wel QE-reserveringen (queryuitvoering), geheugen voor queryuitvoering, werkruimtegeheugen en geheugenreservering genoemd, beschrijven het gebruik van geheugen tijdens het uitvoeren van query's. SQL Server wijst dit geheugen toe tijdens het uitvoeren van de query voor een of meer van de volgende doeleinden:
- Sorteerbewerkingen
- Hash-bewerkingen
- Bulk kopieerbewerkingen (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 enige context te bieden, kan een query tijdens de levensduur geheugen aanvragen bij verschillende geheugentoewijzingen of -medewerkers, afhankelijk van wat er moet gebeuren. Wanneer een query bijvoorbeeld in eerste instantie wordt geparseerd en gecompileerd, verbruikt deze compilatiegeheugen. Zodra de query is gecompileerd, wordt dat geheugen vrijgegeven en wordt het resulterende queryplan opgeslagen in het cachegeheugen van het plan. Zodra een plan is opgeslagen in de cache, is de query gereed voor uitvoering. Als de query sorteerbewerkingen, hash-overeenkomstbewerkingen (JOIN of aggregaties) of invoegingen uitvoert in een COLUMNSTORE-indexen, wordt geheugen uit de queryuitvoerings allocator gebruikt. In eerste instantie vraagt de query om dat uitvoeringsgeheugen en later als dit geheugen wordt verleend, gebruikt de query het hele geheugen 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 ook wel geheugentoelagen genoemd. U kunt zich voorstellen dat zodra de uitvoeringsbewerking van de query is voltooid, de geheugentoekending wordt vrijgegeven aan SQL Server voor gebruik voor ander werk. Daarom zijn toewijzingen voor geheugentoekenningen tijdelijk van aard, maar kunnen ze nog lang duren. Als een query bijvoorbeeld een sorteerbewerking uitvoert op een zeer grote rijenset in het geheugen, kan het sorteren vele seconden of minuten duren en wordt het toegewezen geheugen gebruikt voor de levensduur van de query.
Voorbeeld van een query met een geheugentoestemming
Hier volgt een voorbeeld van een query die gebruikmaakt van uitvoeringsgeheugen en het bijbehorende queryplan met de toekenning:
SELECT *
FROM sys.messages
ORDER BY message_id
Met deze query wordt een rijenset van meer dan 300.000 rijen geselecteerd en gesorteerd. De sorteerbewerking induceert een aanvraag voor geheugentoekenning. Als u deze query uitvoert in SSMS, kunt u het queryplan bekijken. Wanneer u de meest SELECT linkse operator van het queryplan selecteert, kunt u de informatie over het verlenen van geheugen voor de query bekijken (druk op F4 om Eigenschappen weer te geven):
Als u ook met de rechtermuisknop in de witruimte in het queryplan klikt, kunt u XML-uitvoeringsplan weergeven... kiezen en een XML-element zoeken met dezelfde gegevens over geheugentoestemming.
<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" />
Verschillende termen moeten hier worden uitgelegd. Een query kan een bepaalde hoeveelheid uitvoeringsgeheugen nodig hebben (DesiredMemory) en zou meestal die hoeveelheid aanvragen (RequestedMemory). Tijdens runtime verleent SQL Server het aangevraagde geheugen geheel of gedeeltelijk, afhankelijk van de beschikbaarheid (GrantedMemory). Uiteindelijk kan de query meer of minder van het oorspronkelijk aangevraagde geheugen (MaxUsedMemory) gebruiken. Als het queryoptimalisatieprogramma de benodigde hoeveelheid geheugen heeft overschat, gebruikt deze minder dan de aangevraagde grootte. Maar dat geheugen wordt verspild, omdat het kan zijn gebruikt door een andere aanvraag. Aan de andere kant, als het optimalisatieprogramma de benodigde geheugengrootte heeft onderschat, kunnen de overtollige rijen naar de schijf worden gemorst om het werk tijdens de 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 and Worktables in Memory Grant Considerations (Werkbestanden en werktabellen 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.
Geheugen voor queryuitvoering (QE-geheugen): Deze term wordt gebruikt om het feit te markeren dat sorteer- of hashgeheugen wordt gebruikt tijdens het uitvoeren van een query. QE-geheugen is meestal de grootste consument van 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 geheugen gemaakt. Die reserveringsaanvraag wordt berekend tijdens het compileren op basis van de geschatte kardinaliteit. Wanneer de query later wordt uitgevoerd, verleent SQL Server die aanvraag gedeeltelijk of volledig, afhankelijk van de geheugenbeschikbaarheid. Uiteindelijk kan de query een percentage van het toegewezen geheugen gebruiken. Er is een geheugenmedewerker (accountant van geheugen) met de naam 'MEMORYCLERK_SQLQERESERVATIONS' die deze geheugentoewijzingen bijhoudt (raadpleeg DBCC MEMORYSTATUS of sys.dm_os_memory_clerks).
Geheugentoelagen: Wanneer SQL Server het aangevraagde geheugen verleent aan een uitvoerende query, wordt gezegd dat er een geheugentoekenning is opgetreden. Er zijn enkele prestatiemeteritems die de term 'verlenen' gebruiken. Met deze tellers,
Memory Grants OutstandingenMemory Grants Pending, wordt het aantal geheugentoekenningen weergegeven dat is voldaan of wacht. Ze houden geen rekening met de grootte van de geheugentoestemming. 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 mogelijk in de Perfmon-teller
Granted Workspace Memory (KB), die de totale hoeveelheid geheugen weergeeft die momenteel wordt gebruikt voor bewerkingen voor sorteren, hash, bulksgewijs kopiëren en indexen maken, uitgedrukt in kB. DeMaximum Workspace Memory (KB), een andere teller, controleert de maximale hoeveelheid werkruimtegeheugen dat beschikbaar is voor aanvragen die dergelijke hash-, sorteer-, bulkkopie- en indexbewerkingen moeten uitvoeren. De term Werkruimtegeheugen wordt af en toe buiten deze twee tellers aangetroffen.
Impact op prestaties van groot QE-geheugengebruik
In de meeste gevallen, wanneer een thread geheugen binnen SQL Server vraagt om iets gedaan te krijgen en het geheugen niet beschikbaar is, mislukt de aanvraag met een fout over onvoldoende geheugen. Er zijn echter een aantal uitzonderingsscenario's waarbij de thread niet mislukt, maar wacht totdat er geheugen beschikbaar is. Een van deze scenario's is geheugentoelagen en de andere is het geheugen voor querycompilatie. SQL Server gebruikt een threadsynchronisatieobject dat een semafoor wordt genoemd om bij te houden hoeveel geheugen is verleend voor het uitvoeren van query's. Als SQL Server de vooraf gedefinieerde QE-werkruimte opraakt, in plaats van dat de query mislukt met een fout over onvoldoende geheugen, zorgt dit ervoor dat de query wacht. Aangezien werkruimtegeheugen een aanzienlijk percentage van het totale SQL Server geheugen mag nemen, 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 grote aanvragen voor geheugentoekending. Dit betekent dat pagina-leesbewerkingen die afkomstig zijn van queryaanvragen vanaf de schijf moeten worden voldaan (een aanzienlijk tragere bewerking).
- Aanvragen voor andere geheugentoewijzingen kunnen mislukken met geheugenfouten omdat de resource is gekoppeld aan sorteer-, hash- of indexopbouwbewerkingen.
- Aanvragen waarvoor uitvoeringsgeheugen nodig is, wachten totdat de resource beschikbaar is en het voltooien ervan duurt lang. Met andere woorden, voor de eindgebruiker zijn deze query's traag.
Daarom moet u actie ondernemen om dit probleem op te lossen als er wachttijden zijn op het uitvoergeheugen 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 regelmatig optreedt. Zie Wat kan een ontwikkelaar doen met sorteer- en hashbewerkingen voor meer informatie.
Wachttijden voor het uitvoeren van query's identificeren
Er zijn meerdere manieren om de wachttijden voor QE-reserveringen te bepalen. Kies degene die het beste bij u past 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 niveau van de afzonderlijke query's om te zien welke query's moeten worden afgesteld of herschreven.
Gebruik op serverniveau de volgende methoden:
- Resource semafoor DMV sys.dm_exec_query_resource_semaphores Zie sys.dm_exec_query_resource_semaphores voor meer informatie.
- Prestatiemeteritems Zie SQL Server Memory Manager-object voor meer informatie.
- DBCC MEMORYSTATUS Zie DBCC MEMORYSTATUS voor meer informatie.
- DMV-sys.dm_os_memory_clerks voor geheugenmedewerkers Zie sys.dm_os_memory_clerks voor meer informatie.
- Geheugentoekenningen identificeren met behulp van uitgebreide gebeurtenissen (XEvents) Zie Uitgebreide gebeurtenissen (XEvents) 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 worden query's uitgevoerd. Zie sys.dm_exec_query_memory_grants voor meer informatie.
- Specifieke query's identificeren met sys.dm_exec_requests: momenteel worden query's uitgevoerd. 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.
- Identificeer specifieke query's 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 geheugengebruiksstatistieken
Resource semafoor DMV sys.dm_exec_query_resource_semaphores
Met deze DMV wordt het geheugen van de queryreservering opgesplitst per resourcegroep (intern, standaard en door de gebruiker gemaakt) en resource_semaphore (normale 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 er ongeveer 900 MB aan geheugen voor queryuitvoering wordt gebruikt door 22 aanvragen en dat er nog drie wachten. Dit vindt plaats in de standaardgroep (pool_id = 2) en de reguliere querymaafoor (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 wachtende toekenningsaanvragen (Memory Grants Pending) en de hoeveelheid geheugen kunt bekijken die wordt gebruikt door geheugentoekenningen (Granted Workspace Memory (KB)). In de volgende afbeelding zijn de openstaande subsidies 18, de openstaande subsidies 2 en het toegekende werkruimtegeheugen is 828.288 KB. De Memory Grants Pending Perfmon-teller met een niet-nulwaarde geeft aan dat het geheugen is uitgeput.
Zie SQL Server Memory Manager-object voor meer informatie.
- SQLServer, Memory Manager: Maximaal werkruimtegeheugen (KB)
- SQLServer, Memory Manager: Memory Grants Outstanding
- SQLServer, Memory Manager: Geheugentoelagen in behandeling
- SQLServer, Memory Manager: Toegewezen werkruimtegeheugen (KB)
DBCC MEMORYSTATUS
Een andere plaats waar u details over het geheugen van queryreservering kunt zien, is DBCC MEMORYSTATUS (sectie Querygeheugenobjecten). U kunt de Query Memory Objects (default) uitvoer voor gebruikersquery's bekijken. Als u bijvoorbeeld Resource Governor hebt ingeschakeld met een resourcegroep met de naam PoolAdmin, kunt u zowel als Query Memory Objects (default)Query Memory Objects (PoolAdmin)bekijken.
Hier volgt een voorbeeld van uitvoer van een systeem waar aan 18 aanvragen geheugen voor queryuitvoering is toegekend en 2 aanvragen wachten op geheugen. De beschikbare teller is nul, wat aangeeft dat er geen werkruimtegeheugen meer beschikbaar is. Dit feit verklaart de twee wachtaanvragen. De Wait Time toont de verstreken tijd in milliseconden sinds een aanvraag in de wachtrij is geplaatst. Zie Geheugenobjecten opvragen 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 geheugenmedewerker 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
DMV-sys.dm_os_memory_clerks van geheugenmedewerkers
Als u meer van een tabellaire resultatenset nodig hebt, afgezien van de op secties gebaseerde DBCC MEMORYSTATUS, kunt u sys.dm_os_memory_clerks gebruiken voor vergelijkbare informatie. Zoek de MEMORYCLERK_SQLQERESERVATIONS geheugenmedewerker. 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 uitgebreide gebeurtenissen (XEvents)
Er zijn meerdere uitgebreide gebeurtenissen die informatie over geheugentoekenningen bieden en waarmee u deze informatie kunt vastleggen via een tracering:
- sqlserver.additional_memory_grant: treedt op wanneer een query meer geheugentoestemming probeert op te halen tijdens de uitvoering. Als u deze extra geheugentoestemming niet kunt ophalen, kan dit leiden tot een vertraging van de query.
- sqlserver.query_memory_grant_blocking: treedt op wanneer een query andere query's blokkeert tijdens het wachten op een geheugentoekending.
- sqlserver.query_memory_grant_info_sampling: treedt op aan het einde van de willekeurig bemonsterde query's die informatie over geheugentoekending bevatten (deze kan bijvoorbeeld worden gebruikt voor telemetrie).
- sqlserver.query_memory_grant_resource_semaphores: vindt plaats met tussenpozen van vijf minuten voor elke resource governor-resourcegroep.
- sqlserver.query_memory_grant_usage: treedt op aan het einde van de queryverwerking voor query's met geheugentoekenningen van meer dan 5 MB om gebruikers op de hoogte te stellen van onnauwkeurigheden voor geheugentoekenningen.
- sqlserver.query_memory_grants: vindt plaats met tussenpozen van vijf minuten voor elke query met een geheugentoestemming.
Uitgebreide gebeurtenissen voor geheugentoestemming
Zie Feedback over geheugentoedeling voor meer informatie over het verwerken van feedbackfuncties voor geheugentoestemming.
- sqlserver.memory_grant_feedback_loop_disabled: treedt op wanneer de feedbacklus voor geheugentoestemming is uitgeschakeld.
- sqlserver.memory_grant_updated_by_feedback: treedt op wanneer geheugentoestemming wordt bijgewerkt door feedback.
Waarschuwingen voor het uitvoeren van query's 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 geheugentoekending of wanneer de eerste poging om geheugen op te halen mislukt. Gebruik deze gebeurtenis in combinatie met gebeurtenissen die wachttijden identificeren om conflictproblemen op te lossen die van invloed zijn op de prestaties.
- sqlserver.hash_spill_details: treedt op aan het einde van de 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_showplanof-gebeurtenissenquery_post_execution_showplanom 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 is 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_showplangebeurtenissen ofquery_post_execution_showplanom te bepalen welke bewerking in het gegenereerde plan de hashwaarschuwing veroorzaakt. - sqlserver.sort_warning: treedt op wanneer de sorteerbewerking voor een uitgevoerde query 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 Byin eenSelectinstructie.) Gebruik deze gebeurtenis om query's te identificeren die langzaam worden uitgevoerd vanwege de sorteerbewerking, met name wanneer dewarning_type= 2 aangeeft dat meerdere passaties over de gegevens zijn vereist om te sorteren.
Plannen voor het genereren van gebeurtenissen die geheugentoekenningsgegevens bevatten
Het volgende queryplan waarmee uitgebreide gebeurtenissen worden gegenereerd, bevatten 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
Index bouwen voor kolomarchief
Een van de gebieden die via XEvents worden behandeld, 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 verkleind. Er zijn hier verschillende kolommen van belang.
- sqlserver.column_store_index_build_memory_trace: Traceer het geheugengebruik tijdens de indexbuild.
- sqlserver.column_store_index_build_memory_usage_scale_down: De opslagengine is omlaag geschaald.
- sqlserver.column_store_index_memory_estimation: geeft het geheugenschattingsresultaat weer tijdens de columnstore-build van de rijgroep.
Specifieke query's identificeren
Er zijn twee soorten query's die u kunt vinden bij het bekijken van het afzonderlijke aanvraagniveau. De query's die een grote hoeveelheid geheugen voor queryuitvoering verbruiken en query's die op hetzelfde geheugen wachten. De laatste groep kan bestaan uit aanvragen met een bescheiden behoefte aan geheugentoelagen, en als dat het geval is, kunt u uw aandacht ergens anders richten. Maar ze kunnen ook de boosdoeners zijn als ze enorme geheugengrootten aanvragen. Richt u op hen als u dat het geval vindt. Het kan gebruikelijk zijn om te ontdekken dat één bepaalde query de dader is, maar veel exemplaren ervan worden voortgebracht. De exemplaren die de geheugentoekenningen ontvangen, 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 toegewezen, kunt u een query uitvoeren op de sys.dm_exec_query_memory_grants dynamische beheerweergave. Deze DMV geeft informatie weer over het uitvoeren van query's, niet over historische informatie.
Met de volgende instructie worden gegevens opgehaald uit de DMV en worden ook de querytekst en het queryplan als resultaat opgehaald:
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 actief QE-geheugenverbruik. Voor de meeste query's is het geheugen toegewezen, zoals wordt weergegeven door granted_memory_kb en used_memory_kb niet-NULL-numerieke waarden zijn. De query's waarvoor de 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 ongeveer 37 seconden wachten aan. Sessie 72 is de volgende in de rij 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. U kunt dit wachttype zien in sys.dm_exec_requests voor afzonderlijke aanvragen. Deze laatste DMV is het beste uitgangspunt om te bepalen welke query's het slachtoffer zijn van onvoldoende geheugen voor toekenning. U kunt de RESOURCE_SEMAPHORE wachttijd in sys.dm_os_wait_stats ook bekijken als geaggregeerde gegevenspunten op SQL Server niveau. Dit wachttype wordt weergegeven wanneer een aanvraag voor querygeheugen niet kan worden verleend omdat andere gelijktijdige query's het geheugen hebben verbruikt. Een groot aantal wachtaanvragen en lange wachttijden duiden op een overmatig aantal gelijktijdige query's met uitvoeringsgeheugen of grote geheugenaanvragen.
Opmerking
De wachttijd voor geheugentoekenning is eindig. Na een overmatige wachttijd (bijvoorbeeld meer dan 20 minuten), SQL Server keer de query uit en treedt fout 8645 op: 'Er is een time-out opgetreden tijdens het wachten op geheugenresources om de query uit te voeren. Voer de query opnieuw uit. Mogelijk ziet u de time-outwaarde die is ingesteld op serverniveau door te kijken timeout_sec in sys.dm_exec_query_memory_grants. De time-outwaarde kan enigszins variëren tussen SQL Server versies.
Met behulp van sys.dm_exec_requestskunt u zien welke query's geheugen hebben gekregen 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 die u zowel de verleende als de wachtaanvragen laat zien:
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'
Een voorbeelduitvoer toont twee aanvragen die geheugen hebben gekregen en twee dozijn andere wachten op subsidies. De granted_query_memory kolom rapporteert de grootte op pagina's van 8 kB. Een waarde van 34.709 betekent bijvoorbeeld 34.709 * 8 kB = 277.672 kB aan toegewezen geheugen.
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 geheugentoekenning op dit moment niet optreedt, maar u de aangevallen query'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 wordt verwijderd uit de plancache, worden de bijbehorende rijen verwijderd uit deze weergave. Met andere woorden, de DMV bewaart statistieken in het geheugen die niet behouden blijven na een SQL Server opnieuw opstarten of nadat geheugendruk een release van de plancache veroorzaakt. Dat gezegd hebbende, kunt u de informatie hier waardevol vinden, met name voor statistische querystatistieken. Iemand heeft onlangs gemeld dat er grote geheugentoelagen van query's zijn, maar wanneer u de serverworkload bekijkt, ontdekt u mogelijk dat het probleem is verdwenen. In deze situatie sys.dm_exec_query_stats kan het inzicht bieden dat andere dvd's niet kunnen. Hier volgt een voorbeeldquery waarmee u de top 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 aparte rij van SELECT Name FROM t1 JOIN t2 ON t1.Id = t2.Id WHERE t1.Id = 100 (alleen de waarde van het filterpredicaat varieert). De query haalt de top 20-instructies op met een maximale toekenningsgrootte van meer 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 te kijken naar de query's die zijn geaggregeerd door query_hash. In dit voorbeeld ziet u hoe u de gemiddelde, maximale en minimale toekenningsgrootte voor een query-instructie voor alle exemplaren kunt vinden sinds 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)
De Sample_Statement_Text kolom toont 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 bevat, kunt WHERE Id = 5u deze lezen in de meer algemene 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 u Query Store hebt ingeschakeld, kunt u profiteren van de persistente historische statistieken. In tegenstelling tot gegevens van sys.dm_exec_query_stats, overleven deze statistieken een SQL Server opnieuw opstarten of geheugenbelasting omdat ze zijn opgeslagen in een database. QDS heeft ook groottelimieten en een bewaarbeleid. Zie de secties De optimale opnamemodus voor Query Store instellen en De meest relevante gegevens behouden in Query Store inAanbevolen procedures voor het beheren van de Query Store voor meer informatie.
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 = 1Voer 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 naar query's in het bereik van deze database kijkt, niet naar de hele SQL Server. Mogelijk moet u dus weten in welke database een bepaalde aanvraag voor geheugentoestemming is uitgevoerd. Anders voert u deze diagnostische query uit in meerdere databases totdat u de aanzienlijke geheugentoelagen hebt gevonden.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 waarmee gegevens uit meerdere weergaven worden gecombineerd, waaronder 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 van sys.dm_exec_query_resource_semaphores.
Opmerking
Deze query retourneert twee rijen per sessie vanwege het gebruik van sys.dm_exec_query_resource_semaphores (één rij voor de reguliere resourcemaafoor en een andere rij voor de resourcemaafoor met kleine query's).
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 )
Opmerking
De LOOP JOIN hint wordt gebruikt in deze diagnostische query om een geheugentoekending door de query zelf te voorkomen en er wordt geen ORDER BY component gebruikt. Als de diagnostische query zelf op een toekenning wacht, zou het doel van het diagnosticeren van geheugentoelagen worden uitgeschakeld. 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 laat duidelijk zien hoe een query die is verzonden door session_id = 60, de gevraagde 9 MB geheugentoekending heeft gekregen, maar slechts 7 MB is vereist om de uitvoering van de query 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 geheugentoelagen, dus de RESOURCE_SEMAPHOREwait_type. De wachttijd is meer dan 1300 seconden (21 minuten) en de granted_memory_mb wachttijd is NULL.
Deze diagnostische query is een voorbeeld, dus u kunt deze op elke manier aanpassen die aan uw behoeften voldoet. Een versie van deze query wordt ook gebruikt in diagnostische hulpprogramma's die door Microsoft SQL Server worden gebruikt.
Diagnostische hulpprogramma's
Er zijn diagnostische hulpprogramma's die de technische ondersteuning van Microsoft SQL Server gebruikt om logboeken te verzamelen en problemen efficiënter 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 van SQL LogScout of PSSDIAG te importeren in een SQL Server-database. SQL Nexus maakt twee tabellen, tbl_dm_exec_query_resource_semaphores en tbl_dm_exec_query_memory_grants, die de informatie bevatten die nodig is om geheugentoelagen te diagnosticeren. 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 zijn geheugentoelagen belangrijk voor een ontwikkelaar of DBA
Op basis van de ondersteuningservaring van Microsoft zijn problemen met geheugentoestemming meestal een van de meest voorkomende problemen met betrekking tot geheugen. Toepassingen voeren vaak schijnbaar eenvoudige query's uit die uiteindelijk prestatieproblemen kunnen veroorzaken op de SQL Server vanwege grote sorteer- 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, wat het prestatieknelpunt is.
Met behulp van de hulpprogramma's die hier worden beschreven (DMV's, Perfmon-tellers en werkelijke queryplannen) kunt u bepalen welke query's grote gebruikers 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 sorteer- en hashbewerkingen
Zodra u specifieke query's hebt geïdentificeerd die een grote hoeveelheid queryreserveringsgeheugen verbruiken, kunt u stappen ondernemen om de geheugentoekenningen te verminderen door deze query's opnieuw te ontwerpen.
Wat veroorzaakt sorteer- en hashbewerkingen in query's
De eerste stap is om u ervan bewust te worden welke bewerkingen in een query kunnen leiden tot geheugentoelagen.
Redenen waarom een query een SORTEER-operator zou gebruiken:
ORDER BY (T-SQL) leidt ertoe dat rijen worden gesorteerd voordat ze als eindresultaat worden gestreamd.
GROUP BY (T-SQL) kan een sorteeroperator introduceren in een queryplan voorafgaand aan groepering als er geen onderliggende index aanwezig is waarin de gegroepeerde kolommen worden gerangschikt.
DISTINCT (T-SQL) gedraagt zich op dezelfde manier als
GROUP BY. Als u afzonderlijke rijen wilt identificeren, worden de tussenliggende resultaten geordend en worden duplicaten verwijderd. Het optimalisatieprogramma gebruikt eenSortoperator vóór deze operator als de gegevens nog niet zijn gesorteerd vanwege een geordende indexzoekfunctie of -scan.De operator Samenvoegen, wanneer geselecteerd door het queryoptimalisatieprogramma, vereist dat beide gekoppelde invoer worden gesorteerd. SQL Server kan een sortering activeren als er geen geclusterde index beschikbaar is in de joinkolom in een van de tabellen.
Redenen waarom een query gebruikmaakt van een HASH-queryplanoperator:
Deze lijst is niet volledig, maar bevat de meest voorkomende redenen voor hashbewerkingen. Analyseer het queryplan om de hash-overeenkomstbewerkingen te identificeren.
JOIN (T-SQL): bij het samenvoegen van tabellen heeft SQL Server de keuze uit drie fysieke operators,
Nested Loop,Merge JoinenHash Join. Als SQL Server uiteindelijk een Hash Join kiest, heeft het QE-geheugen nodig om tussenliggende resultaten op te slaan en te verwerken. Meestal kan een gebrek aan goede indexen leiden tot deze meest resource-dure join-operator,Hash Join. Als u het queryplan wilt onderzoeken om te identificerenHash Match, raadpleegt u Logische en fysieke operators naslaginformatie.DISTINCT (T-SQL): een
Hash Aggregateoperator kan worden gebruikt om dubbele waarden in een rijenset te elimineren. Als u wilt zoeken naar eenHash Match(Aggregate) in het queryplan, raadpleegt u Naslaginformatie over logische en fysieke operators.UNION (T-SQL): dit is vergelijkbaar met
DISTINCT. EenHash Aggregatekan worden gebruikt om de duplicaten voor deze operator te verwijderen.SOM/AVG/MAX/MIN (T-SQL): elke aggregatiebewerking kan mogelijk worden uitgevoerd als een
Hash Aggregate. Als u wilt zoeken naar eenHash Match(Aggregate) in het queryplan, raadpleegt u Naslaginformatie over logische en fysieke operators.
Als u deze veelvoorkomende redenen kent, kunt u de grote aanvragen voor geheugentoestemming die naar SQL Server komen, zo veel mogelijk elimineren.
Manieren om sorteer- en hashbewerkingen of de toekenningsgrootte te verminderen
- Statistieken up-to-date houden. Deze fundamentele stap, die de prestaties voor query's op veel niveaus verbetert, zorgt ervoor dat het queryoptimalisatieprogramma de meest nauwkeurige informatie heeft bij het selecteren van queryplannen. SQL Server bepaalt welke grootte moet worden aangevraagd voor de geheugentoestemming op basis van statistieken. Verouderde statistieken kunnen leiden tot overschatting of onderschatting van de toekenningsaanvraag en dus leiden tot een onnodig hoge toekenningsaanvraag of tot het overlopen van resultaten naar de schijf. Zorg ervoor dat statistieken voor automatisch bijwerken zijn ingeschakeld in uw databases en/of statische gegevens bijgewerkt houden met STATISTIEKEN BIJWERKEN 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, kan een volgende sortering in het queryplan een kleinere resultatenset ordenen of aggregeren. Voor een kleinere tussenliggende resultatenset is minder werkgeheugen vereist. Dit is een algemene regel die ontwikkelaars niet alleen kunnen volgen voor het opslaan van werksetgeheugen, maar ook voor het verminderen van CPU en I/O (deze stap is niet altijd mogelijk). Als er al goed geschreven en resource-efficiënte query's zijn uitgevoerd, is aan deze richtlijn voldaan.
- Maak indexen voor joinkolommen om samenvoegingen te helpen. De tussenliggende bewerkingen in een queryplan worden beïnvloed door de indexen in de onderliggende tabel. Als een tabel bijvoorbeeld geen index bevat voor een joinkolom en een samenvoeging de meest rendabele joinoperator blijkt te zijn, moeten alle rijen uit die tabel worden gesorteerd voordat de join wordt uitgevoerd. Als er in plaats daarvan een index op de kolom bestaat, kan een sorteerbewerking worden geëlimineerd.
- Maak indexen om hashbewerkingen te voorkomen. Meestal begint het afstemmen van eenvoudige query's 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 voor het verwerken van grote, niet-gesorteerde en niet-geïndexeerde invoer. Het maken van indexen kan deze optimalisatiestrategie wijzigen en het ophalen van gegevens versnellen. Zie Database Engine Tuning Advisor en Niet-geclusterde indexen afstemmen met ontbrekende indexsuggesties voor hulp bij het maken van indexen.
- Gebruik WAAR nodig COLUMNSTORE-indexen voor aggregatiequery's die gebruikmaken van
GROUP BY. Analysequery's die zeer grote rijensets verwerken 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 er automatisch een sortering geïntroduceerd in het queryplan. Een soort zeer groot resultaat kan leiden tot een dure geheugentoestemming. - Verwijder de
ORDER BYals u deze niet nodig hebt. In gevallen waarin resultaten worden gestreamd naar een toepassing die de resultaten op een eigen manier sorteert of waarmee de gebruiker de volgorde van de weergegeven gegevens kan wijzigen, hoeft u geen sortering uit te voeren aan de SQL Server kant. 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. Deze techniek kan hash- of samenvoegingskoppelingen voorkomen die gebruikmaken van geheugentoelagen. Deze optie wordt echter alleen voorgesteld als laatste redmiddel, omdat het afdwingen van een join kan leiden tot een aanzienlijk langzamere query. Stresstest uw workload om ervoor te zorgen dat dit een optie is. In sommige gevallen is een geneste luskoppeling mogelijk niet eens een optie. In dit geval kan SQL Server mislukken met de fout MSSQLSERVER_8622: 'Queryprocessor kan geen queryplan produceren vanwege de hints die in deze query zijn gedefinieerd'.
Geheugentoestemming voor queryhint
Sinds SQL Server 2012 SP3 bestaat er een queryhint waarmee u de grootte van uw geheugentoestemming per query kunt bepalen. 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 stress test om overeen te komen 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 instellingen voor MIN_MEMORY_PERCENT en MAX_MEMORY_PERCENT worden gebruikt. Zodra u query's hebt geïdentificeerd die grote geheugentoelagen veroorzaken, kunt u het geheugen beperken dat wordt gebruikt door sessies of toepassingen. Het is vermeldenswaardig dat de default workloadgroep toestaat dat een query maximaal 25% van het geheugen in beslag neemt die kan worden verleend op een SQL Server-exemplaar. Zie resourcegroepen Resource Governor en WORKLOADGROEP MAKEN voor meer informatie.
Adaptieve queryverwerking en feedback over geheugentoedeling
SQL Server 2017 is de feedbackfunctie voor geheugentoekenning geïntroduceerd. Hiermee kan de engine voor queryuitvoering de toekenning aan de query aanpassen op basis van eerdere geschiedenis. Het doel is om de toekenning zo mogelijk te verkleinen of te vergroten wanneer er meer geheugen nodig is. Deze functie is uitgebracht in drie golven:
- Feedback over geheugentoestemming in batchmodus in SQL Server 2017
- Rijmodus geheugen feedback verlenen in SQL Server 2019
- Feedback over geheugentoestemming op schijfpersistentie met behulp van queryopslag en percentieltoestemming in SQL Server 2022
Zie Feedback over geheugentoe kennen voor meer informatie. De functie geheugentoekenning kan de grootte van de geheugentoelagen voor query's tijdens de uitvoering verminderen en zo de problemen verminderen die voortvloeien uit grote toekenningsaanvragen. Nu deze functie is geïmplementeerd, met name in SQL Server 2019 en latere versies, waar adaptieve verwerking in rijmodus beschikbaar is, ziet u mogelijk niet eens geheugenproblemen die afkomstig zijn van het uitvoeren van query's. Als u deze functie echter hebt ingesteld (standaard ingeschakeld) en nog steeds een 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 ondernomen om onnodige geheugentoekenningen voor uw query's te verminderen, heeft de workload waarschijnlijk meer geheugen nodig als u nog steeds problemen ondervindt met betrekking tot weinig geheugen. Overweeg daarom om 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 overlaten van ongeveer 25% van het geheugen voor het besturingssysteem en andere behoeften. Zie Configuratieopties voor servergeheugen voor meer informatie. Als er onvoldoende geheugen beschikbaar is op het systeem, kunt u overwegen om fysiek RAM-geheugen toe te voegen. Als het een virtuele machine is, verhoogt u het toegewezen RAM-geheugen voor uw VM.
Interne geheugentoekenningen
Zie het blogbericht Over het verlenen van sql-servergeheugen voor meer informatie over enkele interne gegevens over het geheugen van query's.
Een prestatiescenario maken met intensief geheugentoestemmingsgebruik
Ten slotte laat het volgende voorbeeld zien hoe u een groot verbruik van het geheugen voor queryuitvoering simuleert en query's introduceert die wachten op 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 is bedoeld om u te helpen het concept te begrijpen en u te helpen het beter te leren kennen.
Installeer RML-hulpprogramma's en SQL Server op een testserver.
Gebruik een clienttoepassing zoals SQL Server Management Studio om de instelling voor maximaal servergeheugen van uw SQL Server te verlagen tot 1500 MB:
EXEC sp_configure 'max server memory', 1500 RECONFIGUREOpen een opdrachtprompt en wijzig de map in de map RML-hulpprogramma's:
cd C:\Program Files\Microsoft Corporation\RMLUtilsGebruik 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)" -n30Gebruik de diagnostische hulpprogramma's die eerder zijn beschreven om de problemen met geheugentoestemming te identificeren.
Overzicht van manieren om met grote geheugentoelagen om te gaan
- Query's herschrijven.
- Statistieken bijwerken en regelmatig bijgewerkt houden.
- Maak de juiste indexen voor de geïdentificeerde query of query's. Indexen kunnen het grote aantal verwerkte rijen verminderen, waardoor de
JOINalgoritmen worden gewijzigd en de subsidiegrootte wordt verkleind of volledig wordt geëlimineerd. - Gebruik de
OPTIONhint (min_grant_percent = XX, max_grant_percent = XX). - Gebruik Resource Governor.
- SQL Server 2017 en 2019 maken gebruik van adaptieve queryverwerking, zodat het mechanisme voor geheugentoestemming tijdens runtime de grootte van de geheugentoedeling dynamisch kan aanpassen. Deze functie kan problemen met geheugentoekenning in de eerste plaats voorkomen.
- Vergroot het geheugen van SQL Server of het besturingssysteem.
Feedback
Binnenkort beschikbaar: In de loop van 2024 zullen we GitHub-problemen geleidelijk uitfaseren als het feedbackmechanisme voor inhoud en deze vervangen door een nieuw feedbacksysteem. Zie voor meer informatie: https://aka.ms/ContentUserFeedback.
Feedback verzenden en weergeven voor