Probleem oplossen met fouten vanwege onvoldoende geheugen in Azure SQL Database

  • Artikel

Van toepassing op: Azure SQL Database

Er worden mogelijk foutberichten weergegeven wanneer de SQL-database-engine onvoldoende geheugen heeft toegewezen om de query uit te voeren. Dit kan worden veroorzaakt door verschillende redenen, waaronder de limieten van het geselecteerde servicedoel, het samenvoegen van de geheugenvraag van de werkbelasting en de geheugeneisen van de query. Zie Resourcebeheer in Azure SQL Database voor meer informatie over de geheugenresourcelimiet voor Azure SQL Databases.

Notitie

Dit artikel is gericht op Azure SQL Database. Zie MSSQLSERVER_701 voor meer informatie over het oplossen van problemen met onvoldoende geheugen in SQL Server.

Probeer de volgende onderzoekspunten als reactie op:

  • Foutcode 701 met het foutbericht 'Er is onvoldoende systeemgeheugen in resourcegroep '%ls' om deze query uit te voeren.
  • Foutcode 802 met het foutbericht 'Er is onvoldoende geheugen beschikbaar in de buffergroep'.

Geheugengerelateerde gebeurtenissen weergeven

Als er onvoldoende geheugenfouten optreden, dan raadpleegt u sys.dm_os_out_of_memory_events. Deze weergave bevat informatie over de voorspelde oorzaak van het geheugen, bepaald door een heuristisch algoritme en wordt voorzien van een eindige mate van vertrouwen.

SELECT * FROM sys.dm_os_out_of_memory_events ORDER BY event_time DESC;

Geheugentoewijzing onderzoeken

Als er onvoldoende geheugenfouten optreden in Azure SQL Database, kunt u het serviceniveaudoelstelling van de database in Azure Portal ten minste tijdelijk verhogen. Als er onvoldoende geheugenfouten optreden, gebruikt u de volgende query's om te zoeken naar ongebruikelijk hoge querygeheugentoekenningen die kunnen bijdragen aan een onvoldoende geheugenvoorwaarde. Voer de volgende voorbeeldquery's uit in de database waarop de fout is opgetreden (niet in de master database van de logische Azure SQL-server).

DMV gebruiken om geheugengerelateerde gebeurtenissen weer te geven

Hiermee sys.dm_os_out_of_memory_events kunt u inzicht krijgen in de gebeurtenissen en oorzaken van OOM-gebeurtenissen (out of memory) in Azure SQL Database. De summarized_oom_snapshot uitgebreide gebeurtenis maakt deel uit van de bestaande system_health gebeurtenissessie om de detectie te vereenvoudigen. Zie sys.dm_os_out_of_memory_events en Blog voor meer informatie: Een nieuwe manier om geheugenfouten in de database-engine op te lossen.

DMV's gebruiken om geheugenbedienders weer te geven

Begin met een breed onderzoek, als de fout met onvoldoende geheugen onlangs is opgetreden, door de toewijzing van geheugen aan geheugenmedewerkers weer te geven. Geheugenbediendes zijn intern voor de database-engine voor deze Azure SQL Database. De belangrijkste geheugenmedewerkers in termen van toegewezen pagina's kunnen informatief zijn voor welk type query of functie van SQL Server het meeste geheugen verbruikt.

SELECT [type], [name], pages_kb, virtual_memory_committed_kb
FROM sys.dm_os_memory_clerks
WHERE memory_node_id <> 64 -- ignore Dedicated Admin Connection (DAC) node
ORDER BY pages_kb DESC;
GO
SELECT [type], [name], pages_kb, virtual_memory_committed_kb
FROM sys.dm_os_memory_clerks
WHERE memory_node_id <> 64 -- ignore Dedicated Admin Connection (DAC) node
ORDER BY virtual_memory_committed_kb DESC;
  • Sommige algemene geheugenmedewerkers, zoals MEMORYCLERK_SQLQERESERVATIONS, kunnen het beste worden opgelost door query's met grote geheugentoekenningen te identificeren en hun prestaties te verbeteren met betere indexering en indexafstemming.
  • Hoewel OBJECTSTORE_LOCK_MANAGER niet gerelateerd is aan geheugentoekenningen, is het naar verwachting hoog wanneer query's veel vergrendelingen claimen, bijvoorbeeld vanwege uitgeschakelde escalatie van vergrendelingen of zeer grote transacties.
  • Sommige bedienden zullen naar verwachting het hoogste gebruik zijn: MEMORYCLERK_SQLBUFFERPOOL is bijna altijd de topbediende, terwijl CACHESTORE_COLUMNSTOREOBJECTPOOL hoog is wanneer columnstore-indexen worden gebruikt. Het hoogste gebruik door deze bedienden wordt verwacht.

Zie sys.dm_os_memory_clerks voor meer informatie over geheugenbediende typen.

DMV's gebruiken om actieve query's te onderzoeken

In de meeste gevallen is de query die is mislukt niet de oorzaak van deze fout.

De volgende voorbeeldquery voor Azure SQL Database retourneert belangrijke informatie over transacties die momenteel worden bewaard of wachten op geheugentoekenningen. Richt u op de belangrijkste query's die zijn geïdentificeerd voor het afstemmen van onderzoeken en prestaties, en evalueer of ze naar behoren worden uitgevoerd. Overweeg de timing van geheugenintensieve rapportagequery's of onderhoudsbewerkingen.

--Active requests with memory grants
SELECT
--Session data 
  s.[session_id], s.open_transaction_count
--Memory usage
, r.granted_query_memory, mg.grant_time, mg.requested_memory_kb, mg.granted_memory_kb
, mg.required_memory_kb, mg.used_memory_kb, mg.max_used_memory_kb     
--Query 
, query_text = t.text, input_buffer = ib.event_info, query_plan_xml = qp.query_plan
, request_row_count = r.row_count, session_row_count = s.row_count
--Session history and status
, s.last_request_start_time, s.last_request_end_time, s.reads, s.writes, s.logical_reads
, session_status = s.[status], request_status = r.status
--Session connection information
, s.host_name, s.program_name, s.login_name, s.client_interface_name, s.is_user_process
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests AS r 
    ON r.[session_id] = s.[session_id]
LEFT OUTER JOIN sys.dm_exec_query_memory_grants AS mg 
    ON mg.[session_id] = s.[session_id]
OUTER APPLY sys.dm_exec_sql_text (r.[sql_handle]) AS t
OUTER APPLY sys.dm_exec_input_buffer(s.[session_id], NULL) AS ib 
OUTER APPLY sys.dm_exec_query_plan (r.[plan_handle]) AS qp 
WHERE mg.granted_memory_kb > 0
ORDER BY mg.granted_memory_kb desc, mg.requested_memory_kb desc;

U kunt besluiten om de KILL-instructie te gebruiken om te stoppen met een query die momenteel wordt uitgevoerd of wacht op een grote geheugentoekenning. Gebruik deze instructie zorgvuldig, met name wanneer kritieke processen worden uitgevoerd. Zie KILL (Transact-SQL) voor meer informatie.

Query Store gebruiken om het geheugengebruik van eerdere query's te onderzoeken

Hoewel de vorige voorbeeldquery alleen live queryresultaten rapporteert, gebruikt de volgende query de Query Store om informatie te retourneren over de uitvoering van eerdere query's. Dit kan handig zijn bij het onderzoeken van een fout in het geheugen die in het verleden is opgetreden.

De volgende voorbeeldquery voor Azure SQL Database retourneert belangrijke informatie over queryuitvoeringen die zijn vastgelegd in de Query Store. Richt u op de belangrijkste query's die zijn geïdentificeerd voor het afstemmen van onderzoeken en prestaties, en evalueer of ze naar behoren worden uitgevoerd. Let op het tijdfilter om qsp.last_execution_time de resultaten te beperken tot recente geschiedenis. U kunt de TOP-component aanpassen om meer of minder resultaten te produceren, afhankelijk van uw omgeving.

SELECT TOP 10 PERCENT --limit results
  a.plan_id, query_id, plan_group_id, query_sql_text
, query_plan = TRY_CAST(query_plan as XML)
, avg_query_max_used_memory
, min_query_max_used_memory
, max_query_max_used_memory
, last_query_max_used_memory 
, last_execution_time  
, query_count_executions
    FROM (
    SELECT 
      qsp.plan_id, qsp.query_id, qsp.plan_group_id, qsp.query_plan, qsqt.query_sql_text
    , last_execution_time = MAX(qsp.last_execution_time)
    , query_count_executions = SUM(qsrs.count_executions) 
    , avg_query_max_used_memory = AVG(qsrs.avg_query_max_used_memory)
    , min_query_max_used_memory = MIN(qsrs.min_query_max_used_memory)
    , max_query_max_used_memory = MAX(qsrs.max_query_max_used_memory)
    , last_query_max_used_memory = MAX(qsrs_latest.last_query_max_used_memory) --only from latest result
    FROM sys.query_store_plan AS qsp 
    INNER JOIN sys.query_store_query AS qsq
        ON qsp.query_id = qsq.query_id
    INNER JOIN sys.query_store_query_text AS qsqt
        ON qsq.query_text_id = qsqt.query_text_id 
    INNER JOIN sys.query_store_runtime_stats AS qsrs
        ON qsp.plan_id = qsrs.plan_id 
    INNER JOIN (SELECT plan_id
            , last_query_max_used_memory 
            , rownum = ROW_NUMBER() OVER (PARTITION BY plan_id ORDER BY last_execution_time DESC)
            FROM sys.query_store_runtime_stats qsrs) AS qsrs_latest
        ON qsrs_latest.plan_id = qsp.plan_id
        AND qsrs_latest.rownum = 1 --use latest last_query_max_used_memory per plan_id
    WHERE DATEADD(hour, -24, sysdatetime()) < qsp.last_execution_time --past 24 hours only
    AND qsrs_latest.last_query_max_used_memory > 0
    GROUP BY qsp.plan_id, qsp.query_id, qsp.plan_group_id, qsp.query_plan, qsqt.query_sql_text
    ) AS a
ORDER BY max_query_max_used_memory DESC, avg_query_max_used_memory DESC;

Uitgebreide gebeurtenissen

Naast de vorige informatie kan het handig zijn om een tracering van de activiteiten op de server vast te leggen om een probleem met onvoldoende geheugen in Azure SQL Database grondig te onderzoeken.

Er zijn twee manieren om traceringen vast te leggen in SQL Server; Uitgebreide gebeurtenissen (XEvents) en Profiler-traceringen. SQL Server Profiler wordt echter afgeschaft voor traceringstechnologie die niet wordt ondersteund voor Azure SQL Database. Uitgebreide gebeurtenissen is de nieuwere traceringstechnologie die meer veelzijdigheid en minder impact op het waargenomen systeem mogelijk maakt en de interface is geïntegreerd in SQL Server Management Studio (SSMS). Zie Uitgebreide gebeurtenissen in Azure SQL Database voor meer informatie over het uitvoeren van query's op uitgebreide gebeurtenissen in Azure SQL Database.

Raadpleeg het document waarin wordt uitgelegd hoe u de wizard Nieuwe sessie uitgebreide gebeurtenissen gebruikt in SSMS. Voor Azure SQL-databases biedt SSMS echter een submap uitgebreide gebeurtenissen onder elke database in Objectverkenner. Gebruik een sessie uitgebreide gebeurtenissen om deze nuttige gebeurtenissen vast te leggen en de query's te identificeren die deze genereren:

  • Categoriefouten:

    • error_reported
    • exchange_spill
    • hash_spill_details

  • Uitvoering van categorie:

    • excessive_non_grant_memory_used

  • Categoriegeheugen:

    • query_memory_grant_blocking
    • query_memory_grant_usage

  • summarized_oom_snapshot

    Het vastleggen van geheugentoekenningen, geheugentoekenningen of overmatige geheugentoekenningen kan een mogelijke aanwijzing zijn voor een query die plotseling meer geheugen in beslag neemt dan in het verleden, en een mogelijke verklaring voor een opkomende geheugenfout in een bestaande workload. De summarized_oom_snapshot uitgebreide gebeurtenis maakt deel uit van de bestaande system_health gebeurtenissessie om de detectie te vereenvoudigen. Zie Blog: Een nieuwe manier om geheugenfouten in de database-engine op te lossen voor meer informatie.

In-memory OLTP onvoldoende geheugen

U kunt het tegenkomen Error code 41805: There is insufficient memory in the resource pool '%ls' to run this operation als u OLTP in het geheugen gebruikt. Verminder de hoeveelheid gegevens in tabellen die zijn geoptimaliseerd voor geheugen en parameters voor tabelwaarden die zijn geoptimaliseerd voor geheugen, of schaal de database omhoog naar een hogere servicedoelstelling om meer geheugen te hebben. Voor meer informatie over problemen met onvoldoende geheugen met OLTP van SQL Server in het geheugen raadpleegt u Problemen met onvoldoende geheugen oplossen.

Ondersteuning voor Azure SQL Database ophalen

Als er onvoldoende geheugenfouten optreden in Azure SQL Database, dient u een ondersteuning voor Azure aanvraag in door Ondersteuning krijgen te selecteren op de Azure-ondersteuningssite.

Gerelateerde inhoud