Memantau beban kerja kumpulan SQL khusus Azure Synapse Analytics Anda menggunakan DMV

Artikel
06/01/2023

Artikel ini menjelaskan cara menggunakan Tampilan Manajemen Dinamis (DMV) untuk memantau beban kerja Anda termasuk menyelidiki eksekusi kueri di kumpulan SQL khusus.

Izin

Untuk mengkueri DMV dalam artikel ini, Anda memerlukan izin TAMPILKAN STATUS DATABASE atau KONTROL. Biasanya, memberikan TAMPILKAN STATUS DATABASE adalah izin yang lebih disukai karena jauh lebih ketat.

GRANT VIEW DATABASE STATE TO myuser;

Memantau koneksi

Semua login ke gudang data Anda dicatat ke sys.dm_pdw_exec_sessions. DMV ini berisi 10.000 login terakhir. session_id adalah kunci primer dan ditetapkan secara berurutan untuk setiap login baru.

-- Other Active Connections
SELECT * FROM sys.dm_pdw_exec_sessions where status <> 'Closed' and session_id <> session_id();

Memantau eksekusi kueri

Semua kueri yang dijalankan pada kumpulan SQL dicatat ke sys.dm_pdw_exec_requests. DMV ini berisi 10.000 kueri terakhir yang dijalankan. Secara request_id unik mengidentifikasi setiap kueri dan merupakan kunci utama untuk DMV ini. request_id ditetapkan secara berurutan untuk setiap kueri baru dan diawali dengan QID, yang merupakan singkatan dari ID kueri. Mengkueri DMV ini untuk menunjukkan session_id semua kueri untuk login tertentu.

Catatan

Prosedur yang disimpan menggunakan beberapa ID Permintaan. ID Permintaan ditetapkan secara berurutan.

Berikut ini langkah-langkah yang harus diikuti untuk menyelidiki rencana dan waktu eksekusi kueri untuk kueri tertentu.

Langkah 1: Identifikasi kueri yang ingin Anda selidiki

-- Monitor active queries
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE status not in ('Completed','Failed','Cancelled')
  AND session_id <> session_id()
ORDER BY submit_time DESC;

-- Find top 10 queries longest running queries
SELECT TOP 10 *
FROM sys.dm_pdw_exec_requests
ORDER BY total_elapsed_time DESC;

Dari hasil kueri sebelumnya, perhatikan ID Permintaan kueri yang ingin Anda selidiki.

Kueri dalam status Ditangguhkan dapat dimasukkan dalam antrean karena adanya kueri yang aktif berjalan dalam jumlah besar. Kueri ini juga muncul di sys.dm_pdw_waits. Dalam hal ini, cari menunggu seperti UserConcurrencyResourceType. Untuk informasi tentang batas konkurensi, lihat Batas memori dan konkurensi atau Kelas sumber daya untuk manajemen beban kerja. Kueri juga dapat menunggu alasan lain seperti untuk kunci objek. Jika kueri Anda sedang menunggu sumber daya, lihat Menyelidiki kueri yang menunggu sumber daya lebih lanjut di artikel ini.

Untuk menyederhanakan pencarian kueri dalam tabel sys.dm_pdw_exec_requests , gunakan LABEL untuk menetapkan komentar ke kueri Anda, yang bisa dicari dalam sys.dm_pdw_exec_requests tampilan.

-- Query with Label
SELECT *
FROM sys.tables
OPTION (LABEL = 'My Query')
;

-- Find a query with the Label 'My Query'
-- Use brackets when querying the label column, as it is a key word
SELECT  *
FROM    sys.dm_pdw_exec_requests
WHERE   [label] = 'My Query';

Langkah 2: Menyelidiki rencana kueri

Gunakan ID Permintaan untuk mengambil rencana SQL terdistribusi (DSQL) kueri dari sys.dm_pdw_request_steps

-- Find the distributed query plan steps for a specific query.
-- Replace request_id with value from Step 1.

SELECT * FROM sys.dm_pdw_request_steps
WHERE request_id = 'QID####'
ORDER BY step_index;

Saat rencana DSQL memakan waktu lebih lama dari yang diharapkan, penyebabnya bisa rencana yang kompleks dengan banyak langkah DSQL atau hanya satu langkah yang membutuhkan waktu lama. Jika rencana tersebut memiliki banyak langkah dengan beberapa operasi pemindahan, pertimbangkan untuk mengoptimalkan distribusi tabel Anda untuk mengurangi pemindahan data. Artikel Distribusi tabel menjelaskan alasan data harus dipindahkan untuk menyelesaikan kueri. Artikel ini juga menjelaskan beberapa strategi distribusi untuk meminimalkan pergerakan data.

Untuk menyelidiki detail lebih lanjut tentang satu langkah, periksa operation_type kolom langkah kueri yang berjalan lama dan perhatikan Indeks Langkah:

Untuk operasi SQL (OnOperation, RemoteOperation, ReturnOperation), lanjutkan dengan LANGKAH 3
Untuk operasi Pergerakan Data (ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation), lanjutkan dengan LANGKAH 4.

Langkah 3: Selidiki SQL pada database terdistribusi

Gunakan ID Permintaan dan Indeks Langkah untuk mengambil detail dari sys.dm_pdw_sql_requests, yang berisi informasi eksekusi langkah kueri pada semua database terdistribusi.

-- Find the distribution run times for a SQL step.
-- Replace request_id and step_index with values from Step 1 and 3.

SELECT * FROM sys.dm_pdw_sql_requests
WHERE request_id = 'QID####' AND step_index = 2;

Saat langkah kueri berjalan, DBCC PDW_SHOWEXECUTIONPLAN dapat digunakan untuk mengambil rencana estimasi SQL Server dari cache rencana SQL Server untuk langkah yang berjalan pada distribusi tertentu.

-- Find the SQL Server execution plan for a query running on a specific SQL pool or control node.
-- Replace distribution_id and spid with values from previous query.

DBCC PDW_SHOWEXECUTIONPLAN(1, 78);

Langkah 4: Menyelidiki pergerakan data pada database terdistribusi

Gunakan ID Permintaan dan Indeks Langkah untuk mengambil informasi tentang langkah pergerakan data yang berjalan pada setiap distribusi dari sys.dm_pdw_dms_workers.

-- Find information about all the workers completing a Data Movement Step.
-- Replace request_id and step_index with values from Step 1 and 3.

SELECT * FROM sys.dm_pdw_dms_workers
WHERE request_id = 'QID####' AND step_index = 2;

total_elapsed_time Periksa kolom untuk melihat apakah distribusi tertentu membutuhkan waktu jauh lebih lama daripada yang lain untuk pergerakan data.
Untuk distribusi jangka panjang, periksa rows_processed kolom untuk melihat apakah jumlah baris yang dipindahkan dari distribusi tersebut secara signifikan lebih besar dari yang lain. Jika demikian, temuan ini mungkin menunjukkan penyimpangan data dasar Anda. Salah satu penyebab kecondongan data adalah melakukan distribusi pada kolom dengan banyak nilai NULL (yang semua barisnya akan masuk ke dalam distribusi yang sama). Cegah kueri lambat dengan menghindari distribusi pada jenis kolom ini atau memfilter kueri Anda untuk menghilangkan NULL jika memungkinkan.

Jika kueri berjalan, Anda dapat menggunakan PDW_SHOWEXECUTIONPLAN DBCC untuk mengambil rencana estimasi SQL Server dari cache rencana SQL Server untuk Langkah SQL yang sedang berjalan dalam distribusi tertentu.

-- Find the SQL Server estimated plan for a query running on a specific SQL pool Compute or control node.
-- Replace distribution_id and spid with values from previous query.

DBCC PDW_SHOWEXECUTIONPLAN(55, 238);

Memantau kueri tunggu

Jika Anda menemukan bahwa kueri Anda tidak mengalami kemajuan karena sedang menunggu sumber daya, berikut ini kueri yang menampilkan semua sumber daya yang ditunggu kueri.

-- Find queries
-- Replace request_id with value from Step 1.

SELECT waits.session_id,
      waits.request_id,
      requests.command,
      requests.status,
      requests.start_time,
      waits.type,
      waits.state,
      waits.object_type,
      waits.object_name
FROM   sys.dm_pdw_waits waits
   JOIN  sys.dm_pdw_exec_requests requests
   ON waits.request_id=requests.request_id
WHERE waits.request_id = 'QID####'
ORDER BY waits.object_name, waits.object_type, waits.state;

Jika kueri secara aktif menunggu sumber daya dari kueri lain, maka statusnya akan AcquireResources. Jika kueri memiliki semua sumber daya yang diperlukan, maka statusnya akan Diizinkan.

Memantau tempdb

Database tempdb digunakan untuk menyimpan hasil perantara selama eksekusi kueri. Pemanfaatan database yang tempdb tinggi dapat menyebabkan performa kueri yang lambat. Untuk setiap DW100c yang dikonfigurasi, ruang 399 GB tempdb dialokasikan (DW1000c akan memiliki ruang total tempdb 3,99 TB). Di bawah ini adalah tips untuk memantau tempdb penggunaan dan untuk mengurangi tempdb penggunaan dalam kueri Anda.

Memantau tempdb dengan tampilan

Untuk memantau tempdb penggunaan, pertama-tama instal tampilan microsoft.vw_sql_requests dari kumpulan Microsoft Toolkit untuk SQL. Anda kemudian dapat menjalankan kueri berikut untuk melihat tempdb penggunaan per simpul untuk semua kueri yang dijalankan:

-- Monitor tempdb
SELECT
    sr.request_id,
    ssu.session_id,
    ssu.pdw_node_id,
    sr.command,
    sr.total_elapsed_time,
    exs.login_name AS 'LoginName',
    DB_NAME(ssu.database_id) AS 'DatabaseName',
    (es.memory_usage * 8) AS 'MemoryUsage (in KB)',
    (ssu.user_objects_alloc_page_count * 8) AS 'Space Allocated For User Objects (in KB)',
    (ssu.user_objects_dealloc_page_count * 8) AS 'Space Deallocated For User Objects (in KB)',
    (ssu.internal_objects_alloc_page_count * 8) AS 'Space Allocated For Internal Objects (in KB)',
    (ssu.internal_objects_dealloc_page_count * 8) AS 'Space Deallocated For Internal Objects (in KB)',
    CASE es.is_user_process
    WHEN 1 THEN 'User Session'
    WHEN 0 THEN 'System Session'
    END AS 'SessionType',
    es.row_count AS 'RowCount'
FROM sys.dm_pdw_nodes_db_session_space_usage AS ssu
    INNER JOIN sys.dm_pdw_nodes_exec_sessions AS es ON ssu.session_id = es.session_id AND ssu.pdw_node_id = es.pdw_node_id
    INNER JOIN sys.dm_pdw_nodes_exec_connections AS er ON ssu.session_id = er.session_id AND ssu.pdw_node_id = er.pdw_node_id
    INNER JOIN microsoft.vw_sql_requests AS sr ON ssu.session_id = sr.spid AND ssu.pdw_node_id = sr.pdw_node_id
    LEFT JOIN sys.dm_pdw_exec_requests exr on exr.request_id = sr.request_id
    LEFT JOIN sys.dm_pdw_exec_sessions exs on exr.session_id = exs.session_id
WHERE DB_NAME(ssu.database_id) = 'tempdb'
    AND es.session_id <> @@SPID
    AND es.login_name <> 'sa'
ORDER BY sr.request_id;

Catatan

Pergerakan Data menggunakan tempdb. Untuk mengurangi penggunaan tempdb selama pergerakan data, pastikan tabel Anda menggunakan strategi distribusi yang mendistribusikan data secara merata. Gunakan Azure Synapse SQL Distribution Advisor untuk mendapatkan rekomendasi tentang metode distrbution yang cocok untuk beban kerja Anda. Gunakan toolkit Azure Synapse untuk memantau tempdb menggunakan kueri T-SQL.

Jika Anda memiliki kueri yang mengkonsumsi memori dalam jumlah besar atau telah menerima pesan kesalahan yang terkait dengan alokasi tempdb, itu bisa disebabkan oleh pernyataan CREATE TABLE AS SELECT (CTAS) atau INSERT SELECT yang berjalan yang gagal dalam operasi pergerakan data akhir. Ini biasanya dapat diidentifikasi sebagai operasi ShuffleMove dalam rencana kueri terdistribusi tepat sebelum INSERT SELECT terakhir. Gunakan sys.dm_pdw_request_steps untuk memantau operasi ShuffleMove.

Mitigasi yang paling umum adalah memecah pernyataan CTAS atau INSERT SELECT Anda menjadi beberapa pernyataan beban sehingga volume data tidak akan melebihi batas 399 GB per 100DWUc tempdb . Anda juga dapat menskalakan kluster Anda ke ukuran yang lebih besar untuk meningkatkan berapa banyak tempdb ruang yang Anda miliki.

Selain pernyataan CTAS dan INSERT SELECT, kueri besar dan kompleks yang berjalan dengan memori yang tidak mencukupi dapat meluap ke tempdb menyebabkan kueri gagal. Pertimbangkan untuk menjalankan dengan kelas sumber daya yang lebih besar untuk menghindari luapan ke dalam tempdb.

Memantau memori

Memori dapat menjadi akar penyebab masalah performa yang lambat dan kehabisan memori. Pertimbangkan untuk menskalakan gudang data Anda jika menemukan penggunaan memori SQL Server yang mencapai batasnya selama eksekusi kueri.

Kueri berikut menampilkan penggunaan memori SQL Server dan tekanan memori per node:

-- Memory consumption
SELECT
  pc1.cntr_value as Curr_Mem_KB,
  pc1.cntr_value/1024.0 as Curr_Mem_MB,
  (pc1.cntr_value/1048576.0) as Curr_Mem_GB,
  pc2.cntr_value as Max_Mem_KB,
  pc2.cntr_value/1024.0 as Max_Mem_MB,
  (pc2.cntr_value/1048576.0) as Max_Mem_GB,
  pc1.cntr_value * 100.0/pc2.cntr_value AS Memory_Utilization_Percentage,
  pc1.pdw_node_id
FROM
-- pc1: current memory
sys.dm_pdw_nodes_os_performance_counters AS pc1
-- pc2: total memory allowed for this SQL instance
JOIN sys.dm_pdw_nodes_os_performance_counters AS pc2
ON pc1.object_name = pc2.object_name AND pc1.pdw_node_id = pc2.pdw_node_id
WHERE
pc1.counter_name = 'Total Server Memory (KB)'
AND pc2.counter_name = 'Target Server Memory (KB)'

Memantau ukuran log transaksi

Kueri berikut menampilkan ukuran log transaksi pada setiap distribusi. Jika salah satu file log mencapai 160 GB, Anda harus mempertimbangkan untuk meningkatkan skala instans Anda atau membatasi ukuran transaksi Anda.

-- Transaction log size
SELECT
  instance_name as distribution_db,
  cntr_value*1.0/1048576 as log_file_size_used_GB,
  pdw_node_id
FROM sys.dm_pdw_nodes_os_performance_counters
WHERE
instance_name like 'Distribution_%'
AND counter_name = 'Log File(s) Used Size (KB)'

Memantau rollback log transaksi

Jika kueri Anda gagal atau membutuhkan waktu lama untuk melanjutkan, Anda dapat memeriksa dan memantau apakah Anda memiliki transaksi yang bergulir kembali.

-- Monitor rollback
SELECT
    SUM(CASE WHEN t.database_transaction_next_undo_lsn IS NOT NULL THEN 1 ELSE 0 END),
    t.pdw_node_id,
    nod.[type]
FROM sys.dm_pdw_nodes_tran_database_transactions t
JOIN sys.dm_pdw_nodes nod ON t.pdw_node_id = nod.pdw_node_id
GROUP BY t.pdw_node_id, nod.[type]

Memantau beban PolyBase

Kueri berikut ini memberikan perkiraan kemajuan beban Anda. Kueri hanya menampilkan file yang saat ini sedang diproses.

-- To track bytes and files
SELECT
    r.command,
    s.request_id,
    r.status,
    count(distinct input_name) as nbr_files,
    sum(s.bytes_processed)/1024/1024/1024 as gb_processed
FROM
    sys.dm_pdw_exec_requests r
    inner join sys.dm_pdw_dms_external_work s
        on r.request_id = s.request_id
GROUP BY
    r.command,
    s.request_id,
    r.status
ORDER BY
    nbr_files desc,
    gb_processed desc;

Memantau pemblokiran kueri

Kueri berikut ini menyediakan 500 kueri teratas yang diblokir di lingkungan.

--Collect the top blocking
SELECT
    TOP 500 waiting.request_id AS WaitingRequestId,
    waiting.object_type AS LockRequestType,
    waiting.object_name AS ObjectLockRequestName,
    waiting.request_time AS ObjectLockRequestTime,
    blocking.session_id AS BlockingSessionId,
    blocking.request_id AS BlockingRequestId
FROM
    sys.dm_pdw_waits waiting
    INNER JOIN sys.dm_pdw_waits blocking
    ON waiting.object_type = blocking.object_type
    AND waiting.object_name = blocking.object_name
WHERE
    waiting.state = 'Queued'
    AND blocking.state = 'Granted'
ORDER BY
    ObjectLockRequestTime ASC;

Mengambil teks kueri dari kueri menunggu dan memblokir

Kueri berikut menyediakan teks kueri dan pengidentifikasi untuk kueri yang menunggu dan memblokir untuk memecahkan masalah dengan mudah.

-- To retrieve query text from waiting and blocking queries

SELECT waiting.session_id AS WaitingSessionId,
       waiting.request_id AS WaitingRequestId,
       COALESCE(waiting_exec_request.command,waiting_exec_request.command2) AS WaitingExecRequestText,
       blocking.session_id AS BlockingSessionId,
       blocking.request_id AS BlockingRequestId,
       COALESCE(blocking_exec_request.command,blocking_exec_request.command2) AS BlockingExecRequestText,
       waiting.object_name AS Blocking_Object_Name,
       waiting.object_type AS Blocking_Object_Type,
       waiting.type AS Lock_Type,
       waiting.request_time AS Lock_Request_Time,
       datediff(ms, waiting.request_time, getdate())/1000.0 AS Blocking_Time_sec
FROM sys.dm_pdw_waits waiting
       INNER JOIN sys.dm_pdw_waits blocking
       ON waiting.object_type = blocking.object_type
       AND waiting.object_name = blocking.object_name
       INNER JOIN sys.dm_pdw_exec_requests blocking_exec_request
       ON blocking.request_id = blocking_exec_request.request_id
       INNER JOIN sys.dm_pdw_exec_requests waiting_exec_request
       ON waiting.request_id = waiting_exec_request.request_id
WHERE waiting.state = 'Queued'
       AND blocking.state = 'Granted'
ORDER BY Lock_Request_Time DESC;

Langkah berikutnya

Untuk informasi selengkapnya tentang DMV, lihat Tampilan sistem.

Bagikan melalui