sys.dm_exec_query_memory_grants (T-SQL)

  • Artikel

Berlaku untuk:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)

Mengembalikan informasi tentang semua kueri yang telah diminta dan sedang menunggu pemberian memori atau telah diberi peruntukan memori. Kueri yang tidak memerlukan peruntukan memori tidak akan muncul dalam tampilan ini. Misalnya, operasi pengurutan dan gabungan hash memiliki hibah memori untuk eksekusi kueri, sementara kueri tanpa ORDER BY klausul tidak akan memiliki peruntukan memori.

Di Azure SQL Database, tampilan manajemen dinamis tidak dapat mengekspos informasi yang akan memengaruhi penahanan database atau mengekspos informasi tentang database lain yang dapat diakses pengguna. Untuk menghindari mengekspos informasi ini, setiap baris yang berisi data yang bukan milik penyewa yang tersambung difilter. Selain itu, nilai dalam kolom scheduler_id, , wait_orderpool_id, group_id difilter; nilai kolom diatur ke NULL.

Catatan

Untuk memanggil ini dari Azure Synapse Analytics atau Analytics Platform System (PDW), gunakan nama sys.dm_pdw_nodes_exec_query_memory_grants. Sintaks ini tidak didukung oleh kumpulan SQL tanpa server di Azure Synapse Analytics.

Nama kolom Jenis data Deskripsi
session_id smallint ID (SPID) sesi tempat kueri ini berjalan.
request_id int ID permintaan. Unik dalam konteks sesi.
scheduler_id int ID penjadwal yang menjadwalkan kueri ini.
Dop smallint Tingkat paralelisme kueri ini.
request_time datetime Tanggal dan waktu saat kueri ini meminta pemberian memori.
grant_time datetime Tanggal dan waktu ketika memori diberikan untuk kueri ini. NULL jika memori belum diberikan.
requested_memory_kb bigint Total jumlah memori yang diminta dalam kilobyte.
granted_memory_kb bigint Jumlah total memori yang sebenarnya diberikan dalam kilobyte. Dapat berupa NULL jika memori belum diberikan. Untuk situasi umum, nilai ini harus sama dengan requested_memory_kb. Untuk pembuatan indeks, server dapat mengizinkan memori sesuai permintaan tambahan di luar memori yang awalnya diberikan.
required_memory_kb bigint Memori minimum yang diperlukan untuk menjalankan kueri ini dalam kilobyte. requested_memory_kb sama atau lebih besar dari jumlah ini.
used_memory_kb bigint Memori fisik yang digunakan saat ini dalam kilobyte.
max_used_memory_kb bigint Memori fisik maksimum yang digunakan hingga saat ini dalam kilobyte.
query_cost float Estimasi biaya kueri.
timeout_sec int Waktu habis dalam detik sebelum kueri ini menyerahkan permintaan pemberian memori.
resource_semaphore_id smallint ID tidak unik dari semaphore sumber daya tempat kueri ini menunggu.

Catatan: ID ini unik dalam versi SQL Server yang lebih lama dari SQL Server 2008 (10.0.x). Perubahan ini dapat memengaruhi pemecahan masalah eksekusi kueri. Untuk informasi selengkapnya, lihat bagian "Komentar" nanti di artikel ini.
queue_id smallint ID antrean menunggu di mana kueri ini menunggu peruntukan memori. NULL jika memori sudah diberikan.
wait_order int Urutan berurutan kueri tunggu dalam yang ditentukan queue_id. Nilai ini dapat berubah untuk kueri tertentu jika kueri lain mendapatkan peruntukan memori atau waktu habis. NULL jika memori sudah diberikan.
is_next_candidate bit Kandidat untuk pemberian memori berikutnya.

1 = Ya

0 = Tidak

NULL = Memori sudah diberikan.
wait_time_ms bigint Waktu tunggu dalam milidetik. NULL jika memori sudah diberikan.
plan_handle varbinary(64) Pengidentifikasi untuk rencana kueri ini. Gunakan sys.dm_exec_query_plan untuk mengekstrak paket XML yang sebenarnya.
sql_handle varbinary(64) Pengidentifikasi untuk teks Transact-SQL untuk kueri ini. Gunakan sys.dm_exec_sql_text untuk mendapatkan teks Transact-SQL yang sebenarnya.
group_id int ID untuk grup beban kerja tempat kueri ini berjalan.
pool_id int ID kumpulan sumber daya tempat grup beban kerja ini berada.
is_small kecil Ketika diatur ke 1, menunjukkan bahwa pemberian ini menggunakan semaphore sumber daya kecil. Ketika diatur ke 0, menunjukkan bahwa semaphore biasa digunakan.
ideal_memory_kb bigint Ukuran, dalam kilobyte (KB), dari pemberian memori agar sesuai dengan semuanya ke dalam memori fisik. Ini didasarkan pada perkiraan kardinalitas.
pdw_node_id int Pengidentifikasi untuk simpul tempat distribusi ini aktif.

Berlaku untuk: Azure Synapse Analytics, Analytics Platform System (PDW)
reserved_worker_count bigint Jumlah utas pekerja yang dipesan.

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2016 (13.x)) dan Azure SQL Database
used_worker_count bigint Jumlah utas pekerja yang digunakan saat ini.

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2016 (13.x)) dan Azure SQL Database
max_used_worker_count bigint Jumlah maksimum utas pekerja yang digunakan hingga saat ini.

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2016 (13.x)) dan Azure SQL Database
reserved_node_bitmap bigint Bitmap simpul NUMA tempat utas pekerja dicadangkan.

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2016 (13.x)) dan Azure SQL Database

Izin

Di SQL Server, memerlukan VIEW SERVER STATE izin.
Di Azure SQL Database, memerlukan VIEW DATABASE STATE izin dalam database.

Izin untuk SQL Server 2022 dan yang lebih baru

Memerlukan izin TAMPILKAN STATUS PERFORMA SERVER pada server.

Keterangan

Kueri yang menggunakan tampilan manajemen dinamis yang menyertakan ORDER BY atau mengagregasi dapat meningkatkan konsumsi memori dan dengan demikian berkontribusi pada masalah pemecahan masalahnya.

Fitur Resource Governor memungkinkan administrator database untuk mendistribusikan sumber daya server di antara kumpulan sumber daya, hingga maksimum 64 kumpulan. Dimulai dengan SQL Server 2008 (10.0.x), setiap kumpulan bereaksi seperti instans server independen kecil dan memerlukan dua semaphores. Jumlah baris yang dikembalikan dari bisa hingga 20 kali lebih banyak dari sys.dm_exec_query_resource_semaphores baris yang dikembalikan di SQL Server 2005 (9.x).

Contoh

Skenario penelusuran kesalahan umum untuk batas waktu kueri dapat menyelidiki hal berikut:

  • Periksa status memori sistem secara keseluruhan menggunakan sys.dm_os_memory_clerks, sys.dm_os_sys_info, dan berbagai penghitung kinerja.

  • Periksa reservasi memori eksekusi kueri di sys.dm_os_memory_clerks mana type = 'MEMORYCLERK_SQLQERESERVATIONS'.

  • Periksa kueri yang menunggu1 untuk pemberian menggunakan sys.dm_exec_query_memory_grants:

    --Find all queries waiting in the memory queue  
SELECT * FROM sys.dm_exec_query_memory_grants WHERE grant_time IS NULL;

    1 Dalam skenario ini, jenis tunggu biasanya RESOURCE_SEMAPHORE. Untuk informasi selengkapnya, lihat sys.dm_os_wait_stats (Transact-SQL).

  • Cari cache untuk kueri dengan pemberian memori menggunakan sys.dm_exec_cached_plans (Transact-SQL) dan sys.dm_exec_query_plan (Transact-SQL)

    -- retrieve every query plan from the plan cache  
USE master;  
GO  
SELECT * FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);  
GO

  • Jika kueri pelarian dicurigai, periksa Showplan di query_plan kolom dari sys.dm_exec_query_plan dan batch text kueri dari sys.dm_exec_sql_text. Periksa lebih lanjut kueri intensif memori yang saat ini dijalankan, menggunakan sys.dm_exec_requests.

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

Baca juga