Memecahkan masalah kehabisan memori dengan Azure SQL Database

  • Artikel

Berlaku untuk:Azure SQL Database

Anda mungkin melihat pesan kesalahan ketika mesin database SQL gagal mengalokasikan memori yang memadai untuk menjalankan kueri. Hal ini dapat disebabkan oleh berbagai alasan termasuk batas tujuan layanan yang dipilih, permintaan memori beban kerja agregat, dan permintaan memori oleh kueri. Untuk informasi selengkapnya tentang batas sumber daya memori untuk Azure SQL Databases, lihat Manajemen sumber daya di Azure SQL Database.

Catatan

Artikel ini berfokus pada Azure SQL Database. Untuk informasi selengkapnya tentang pemecahan masalah kehabisan memori di SQL Server, lihat MSSQLSERVER_701.

Coba cara penyelidikan berikut sebagai respons terhadap:

  • Kode galat 701 dengan pesan kesalahan "Memori sistem tidak mencukupi di pusat sumber daya '%ls' untuk menjalankan kueri ini."
  • Kode galat 802 dengan pesan kesalahan "Memori tidak tersedia dengan cukup di kumpulan buffer."

Melihat peristiwa memori habis

Jika Anda menemukan kesalahan memori habis, tinjau sys.dm_os_out_of_memory_events. Tampilan ini mencakup informasi tentang penyebab memori yang diprediksi kehabisan memori, ditentukan oleh algoritma heuristik dan diberikan tingkat keyakinan yang terbatas.

SELECT * FROM sys.dm_os_out_of_memory_events ORDER BY event_time DESC;

Selidiki alokasi memori

Jika kesalahan kehabisan memori tetap ada di Azure SQL Database, pertimbangkan setidaknya untuk sementara meningkatkan tujuan tingkat layanan database di portal Microsoft Azure. Jika kesalahan kehabisan memori berlanjut, gunakan kueri berikut untuk mencari peruntukan memori kueri yang luar biasa tinggi yang mungkin berkontribusi pada kondisi memori yang tidak memadai. Jalankan kueri contoh berikut di database yang mengalami kesalahan (bukan di database master server logis Azure SQL).

Menggunakan DMV untuk melihat peristiwa memori habis

memungkinkan sys.dm_os_out_of_memory_events visibilitas ke peristiwa dan penyebab peristiwa kehabisan memori (OOM) di Azure SQL Database. Peristiwa summarized_oom_snapshot yang diperluas adalah bagian dari sesi peristiwa yang ada system_health untuk menyederhanakan deteksi. Untuk informasi selengkapnya, lihat sys.dm_os_out_of_memory_events dan Blog: Cara baru untuk memecahkan masalah kesalahan di luar memori di mesin database.

Gunakan DMV untuk melihat memory clerk

Mulailah dengan penyelidikan yang luas, jika kesalahan kehabisan memori terjadi baru-baru ini, dengan melihat alokasi memori ke memory clerk. Memory clerk bersifat internal ke mesin database untuk Azure SQL Database ini. Memory clerk teratas mengenai halaman yang dialokasikan mungkin dapat memberikan informasi tentang jenis kueri atau fitur SQL Server mana yang paling banyak menghabiskan memori.

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;
  • Beberapa memory clerk umum, seperti MEMORYCLERK_SQLQERESERVATIONS, paling baik diselesaikan dengan mengidentifikasi kueri dengan peruntukan memori besar dan meningkatkan performanya dengan pengindeksan dan penyetelan indeks yang lebih baik.
  • Meskipun OBJECTSTORE_LOCK_MANAGER tidak terkait dengan peruntukan memori, OBJECTSTORE_LOCK_MANAGER diharapkan menjadi tinggi ketika kueri mengklaim banyak kunci, misalnya, karena eskalaksi penguncian yang dinonaktifkan atau transaksi yang sangat besar.
  • Beberapa clerk diharapkan menjadi pemanfaatan tertinggi: MEMORYCLERK_SQLBUFFERPOOL hampir selalu menjadi clerk teratas, sementara CACHESTORE_COLUMNSTOREOBJECTPOOL akan menjadi tinggi ketika indeks penyimpan kolom digunakan. Pemanfaatan tertinggi oleh clerk ini diharapkan.

Untuk informasi selengkapnya tentang jenis memory clerk, lihat sys.dm_os_memory_clerks.

Gunakan DMV untuk menyelidiki kueri aktif

Dalam kebanyakan kasus, kueri yang gagal bukanlah penyebab kesalahan ini.

Contoh kueri berikut untuk Azure SQL Database menampilkan informasi penting tentang transaksi yang sedang ditahan atau menunggu peruntukan memori. Targetkan kueri teratas yang diidentifikasi untuk pemeriksaan dan penyetelan performa, dan evaluasi apakah kueri tersebut dijalankan seperti yang diharapkan atau tidak. Pertimbangkan waktu kueri pelaporan atau operasi pemeliharaan yang membutuhkan banyak memori.

--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;

Anda mungkin memutuskan untuk menggunakan pernyataan KILL untuk menghentikan kueri yang sedang dijalankan yang menahan atau menunggu peruntukan memori besar. Gunakan pernyataan ini dengan hati-hati, terutama ketika proses kritis berjalan. Untuk informasi selengkapnya, lihat Pernyataan KILL (T-SQL).

Gunakan Penyimpanan Kueri untuk menyelidiki penggunaan memori kueri sebelumnya

Sementara kueri sampel sebelumnya hanya melaporkan hasil kueri langsung, kueri berikut menggunakan Penyimpanan Kueri untuk menampilkan informasi tentang eksekusi kueri sebelumnya. Hal ini dapat membantu dalam menyelidiki kesalahan kehabisan memori yang terjadi di masa lalu.

Kueri sampel berikut untuk Azure SQL Database menampilkan informasi penting tentang eksekusi kueri yang direkam oleh Penyimpanan Kueri. Targetkan kueri teratas yang diidentifikasi untuk pemeriksaan dan penyetelan performa, dan evaluasi apakah kueri tersebut dijalankan seperti yang diharapkan atau tidak. Perhatikan filter waktu pada qsp.last_execution_time untuk membatasi hasil pada riwayat terkini. Anda dapat menyesuaikan klausul TOP untuk menghasilkan lebih banyak atau lebih sedikit hasil bergantung pada lingkungan.

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;

Aktivitas yang Diperluas

Selain informasi sebelumnya, dapat membantu untuk mengambil jejak aktivitas di server untuk menyelidiki masalah kehabisan memori di Azure SQL Database.

Ada dua cara untuk memperoleh jejak di SQL Server; Extended Events (XEvents) dan Profiler Traces. Namun, SQL Server Profiler adalah teknologi pelacakan yang tidak didukung untuk Azure SQL Database. Extended Events adalah teknologi pelacakan yang lebih baru yang memungkinkan lebih banyak fleksibilitas dan lebih sedikit dampak ke sistem yang diamati, dan antarmukanya diintegrasikan ke dalam SQL Server Management Studio (SSMS). Untuk informasi selengkapnya tentang mengkueri peristiwa yang diperluas di Azure SQL Database, lihat Peristiwa yang diperluas di Azure SQL Database.

Lihat dokumen yang menjelaskan cara menggunakan Extended Events New Session Wizard di SQL Server Management Studio. Namun, untuk database Azure SQL, SQL Server Management Studio menyediakan subfolder Extended Events di bawah setiap database di Object Explorer. Gunakan sesi Peristiwa yang Diperluas untuk mengambil peristiwa yang berguna ini, dan mengidentifikasi kueri yang menghasilkannya:

  • Kesalahan Kategori:

    • error_reported
    • exchange_spill
    • hash_spill_details

  • Kategori Eksekusi:

    • excessive_non_grant_memory_used

  • Memori Kategori:

    • query_memory_grant_blocking
    • query_memory_grant_usage

  • summarized_oom_snapshot

    Pengambilan blok peruntukan memori, peluapan peruntukan memori, atau peruntukan memori yang berlebihan dapat menjadi petunjuk potensial untuk kueri yang tiba-tiba menggunakan lebih banyak memori daripada sebelumnya, dan penjelasan potensial untuk kesalahan memori yang muncul dalam beban kerja yang ada. Peristiwa summarized_oom_snapshot yang diperluas adalah bagian dari sesi peristiwa yang ada system_health untuk menyederhanakan deteksi. Untuk informasi selengkapnya, lihat Blog: Cara baru untuk memecahkan masalah kesalahan di luar memori di mesin database.

OLTP dalam memori kehabisan memori

Anda mungkin mengalami Error code 41805: There is insufficient memory in the resource pool '%ls' to run this operation jika menggunakan OLTP Dalam Memori. Kurangi jumlah data dalam tabel yang dioptimalkan memori dan parameter bernilai tabel yang dioptimalkan memori, atau tingkatkan database ke tujuan layanan yang lebih tinggi untuk memiliki lebih banyak memori. Untuk informasi selengkapnya tentang masalah kehabisan memori dengan OLTP Dalam Memori SQL Server, lihat Menyelesaikan masalah kehabisan memori.

Mendapatkan dukungan Azure SQL Database

Jika kesalahan kehabisan memori tetap ada di Azure SQL Database, ajukan permintaan dukungan Azure dengan memilih Dapatkan Dukungan di situs Dukungan Azure.

Konten terkait