Training
Module
Optimalisatie van queryprestaties verkennen - Training
Optimalisatie van queryprestaties verkennen
Problemen oplossen met trage prestaties of weinig geheugen die worden veroorzaakt door geheugentoekenningen in SQL Server
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.
Hier volgt een voorbeeld van een query die gebruikmaakt van het uitvoeringsgeheugen en het bijbehorende queryplan met de toekenning:
SQL
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.
XML
<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.
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 OutstandingenMemory 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. HetMaximum 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.
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.
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.
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:
SQL
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).
Output
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)
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)
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.
Output
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.
Output
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
------------------------------------------------------------------------ -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 824640
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.
SQL
SELECT type, memory_node_id, pages_kb
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLQERESERVATIONS'
Hier volgt een voorbeelduitvoer:
Output
type memory_node_id pages_kb
----------------------------------------------- -------------- --------------
MEMORYCLERK_SQLQERESERVATIONS 0 824640
MEMORYCLERK_SQLQERESERVATIONS 64 0
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.
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.
- 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_showplanofquery_post_execution_showplangebeurtenissen 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_showplanofquery_post_execution_showplangebeurtenissen 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 Byin eenSelectinstructie.) Gebruik deze gebeurtenis om query's te identificeren die langzaam worden uitgevoerd vanwege de sorteerbewerking, met name wanneer dewarning_type= 2, waarmee wordt aangegeven dat er meerdere pass-over de gegevens zijn vereist om te sorteren.
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
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.
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.
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:
SQL
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.
Output
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
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:
SQL
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.
Output
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)
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.
SQL
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.
SQL
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:
Output
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
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:
SQLSELECT 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:
SQLSELECT 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:
Outputsample_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
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).
SQL
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.
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 .
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.
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.
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 eenSortoperator 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 LoopenMerge 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 JoinZie de naslaginformatie over logische en fysieke operators om het queryplan te onderzoeken dat u wilt identificerenHash Match.DISTINCT (T-SQL):een
Hash Aggregateoperator kan worden gebruikt om duplicaten in een rijenset te elimineren. Als u eenHash Match(Aggregate) in het queryplan wilt zoeken, raadpleegt u de 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.SUM/AVG/MAX/MIN (T-SQL): elke statistische bewerking kan mogelijk worden uitgevoerd als een
Hash Aggregate. Als u eenHash 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.
- 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 BYoptie 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'.
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:
SQL
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.
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.
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.
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.
Zie het blogbericht Over het verlenen van SQL Server-geheugen voor meer informatie over enkele interne gegevens over het geheugen van query's.
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:
SQLEXEC sp_configure 'max server memory', 1500 RECONFIGUREOpen een opdrachtprompt en wijzig de map in de map RML-hulpprogramma's:
Windows Command Promptcd 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:
Windows Command Promptostress.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 problemen met geheugentoe kennen te identificeren.
- 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
JOINalgoritmen worden gewijzigd en de grootte van subsidies wordt verkleind of volledig worden 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 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.