Menyetel performa dengan Penyimpanan Kueri

Artikel
08/21/2023

Berlaku untuk: SQL Server 2016 (13.x) dan yang lebih baru Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

Fitur Penyimpanan Kueri SQL Server memberi Anda kekuatan untuk menemukan dan menyetel kueri dalam beban kerja Anda melalui antarmuka visual SQL Server Management Studio dan melalui kueri T-SQL. Artikel ini merinci bagaimana Anda bisa mengambil informasi yang dapat ditindaklanjuti untuk meningkatkan performa kueri di database Anda, termasuk cara mengidentifikasi kueri berdasarkan statistik penggunaan dan memaksa rencananya. Anda juga bisa menggunakan fitur petunjuk Penyimpanan Kueri untuk mengidentifikasi kueri dan membentuk rencana kueri mereka tanpa mengubah kode aplikasi.

Untuk informasi selengkapnya tentang cara data ini dikumpulkan, lihat Cara Penyimpanan Kueri mengumpulkan data.
Untuk informasi selengkapnya tentang mengonfigurasi dan mengelola dengan Penyimpanan Kueri, lihat Memantau performa dengan menggunakan Penyimpanan Kueri.
Untuk informasi tentang mengoperasikan Penyimpanan Kueri di Azure SQL Database, lihat Mengoperasikan Penyimpanan Kueri di Azure SQL Database.

Kueri sampel penyetelan performa

Penyimpanan Kueri menyimpan riwayat kompilasi dan metrik runtime sepanjang eksekusi kueri, memungkinkan Anda mengajukan pertanyaan tentang beban kerja Anda.

Contoh kueri berikut mungkin berguna dalam garis besar performa dan investigasi performa kueri Anda:

Kueri terakhir yang dijalankan pada database

Kueri n terakhir yang dijalankan pada database:

SELECT TOP 10 qt.query_sql_text, q.query_id,
    qt.query_text_id, p.plan_id, rs.last_execution_time
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
ORDER BY rs.last_execution_time DESC;

Jumlah eksekusi

Jumlah eksekusi untuk setiap kueri:

SELECT q.query_id, qt.query_text_id, qt.query_sql_text,
    SUM(rs.count_executions) AS total_execution_count
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
GROUP BY q.query_id, qt.query_text_id, qt.query_sql_text
ORDER BY total_execution_count DESC;

Waktu eksekusi rata-rata terpanjang

Jumlah kueri dengan waktu eksekusi rata-rata terpanjang dalam satu jam terakhir:

SELECT TOP 10 rs.avg_duration, qt.query_sql_text, q.query_id,
    qt.query_text_id, p.plan_id, GETUTCDATE() AS CurrentUTCTime,
    rs.last_execution_time
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(hour, -1, GETUTCDATE())
ORDER BY rs.avg_duration DESC;

Bacaan I/O fisik rata-rata terbesar

Jumlah kueri yang memiliki pembacaan I/O fisik rata-rata terbesar dalam 24 jam terakhir, dengan jumlah baris rata-rata dan jumlah eksekusi yang sesuai:

SELECT TOP 10 rs.avg_physical_io_reads, qt.query_sql_text,
    q.query_id, qt.query_text_id, p.plan_id, rs.runtime_stats_id,
    rsi.start_time, rsi.end_time, rs.avg_rowcount, rs.count_executions
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi
    ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rsi.start_time >= DATEADD(hour, -24, GETUTCDATE())
ORDER BY rs.avg_physical_io_reads DESC;

Kueri dengan beberapa paket

Kueri ini sangat menarik karena mereka adalah kandidat untuk regresi karena perubahan pilihan rencana. Kueri berikut mengidentifikasi kueri ini bersama dengan semua paket:

WITH Query_MultPlans
AS
(
SELECT COUNT(*) AS cnt, q.query_id
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
GROUP BY q.query_id
HAVING COUNT(distinct plan_id) > 1
)

SELECT q.query_id, object_name(object_id) AS ContainingObject,
    query_sql_text, plan_id, p.query_plan AS plan_xml,
    p.last_compile_start_time, p.last_execution_time
FROM Query_MultPlans AS qm
JOIN sys.query_store_query AS q
    ON qm.query_id = q.query_id
JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
JOIN sys.query_store_query_text qt
    ON qt.query_text_id = q.query_text_id
ORDER BY query_id, plan_id;

Durasi tunggu tertinggi

Kueri ini akan mengembalikan 10 kueri teratas dengan durasi tunggu tertinggi:

SELECT TOP 10
    qt.query_text_id,
    q.query_id,
    p.plan_id,
    sum(total_query_wait_time_ms) AS sum_total_wait_ms
FROM sys.query_store_wait_stats ws
JOIN sys.query_store_plan p ON ws.plan_id = p.plan_id
JOIN sys.query_store_query q ON p.query_id = q.query_id
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
GROUP BY qt.query_text_id, q.query_id, p.plan_id
ORDER BY sum_total_wait_ms DESC;

Catatan

Di Azure Synapse Analytics, kueri sampel Penyimpanan Kueri di bagian ini didukung dengan pengecualian statistik tunggu, yang tidak tersedia di DMV Penyimpanan Kueri Azure Synapse Analytics.

Kueri yang baru-baru ini diregresi dalam performa

Contoh kueri berikut mengembalikan semua kueri yang waktu eksekusinya digandakan dalam 48 jam terakhir karena perubahan pilihan paket. Kueri ini membandingkan semua interval statistik runtime berdampingan:

SELECT
    qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    rs1.runtime_stats_id AS runtime_stats_id_1,
    rsi1.start_time AS interval_1,
    p1.plan_id AS plan_1,
    rs1.avg_duration AS avg_duration_1,
    rs2.avg_duration AS avg_duration_2,
    p2.plan_id AS plan_2,
    rsi2.start_time AS interval_2,
    rs2.runtime_stats_id AS runtime_stats_id_2
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p1
    ON q.query_id = p1.query_id
JOIN sys.query_store_runtime_stats AS rs1
    ON p1.plan_id = rs1.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi1
    ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id
JOIN sys.query_store_plan AS p2
    ON q.query_id = p2.query_id
JOIN sys.query_store_runtime_stats AS rs2
    ON p2.plan_id = rs2.plan_id
JOIN sys.query_store_runtime_stats_interval AS rsi2
    ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id
WHERE rsi1.start_time > DATEADD(hour, -48, GETUTCDATE())
    AND rsi2.start_time > rsi1.start_time
    AND p1.plan_id <> p2.plan_id
    AND rs2.avg_duration > 2*rs1.avg_duration
ORDER BY q.query_id, rsi1.start_time, rsi2.start_time;

Jika Anda ingin melihat performa semua regresi (tidak hanya yang terkait dengan perubahan pilihan rencana), hapus kondisi AND p1.plan_id <> p2.plan_id dari kueri sebelumnya.

Kueri dengan regresi historis dalam performa

Membandingkan eksekusi terbaru dengan eksekusi historis, kueri berikutnya membandingkan eksekusi kueri berdasarkan periode eksekusi. Dalam contoh khusus ini, kueri membandingkan additional_duration_workloadeksekusi dalam periode terbaru (1 jam) vs. periode riwayat (hari terakhir) dan mengidentifikasi yang memperkenalkan . Metrik ini dihitung sebagai perbedaan antara eksekusi rata-rata terbaru dan eksekusi rata-rata riwayat dikalikan dengan jumlah eksekusi terbaru. Ini benar-benar mewakili berapa banyak durasi tambahan eksekusi terbaru yang diperkenalkan dibandingkan dengan riwayat:

--- "Recent" workload - last 1 hour
DECLARE @recent_start_time datetimeoffset;
DECLARE @recent_end_time datetimeoffset;
SET @recent_start_time = DATEADD(hour, -1, SYSUTCDATETIME());
SET @recent_end_time = SYSUTCDATETIME();

--- "History" workload
DECLARE @history_start_time datetimeoffset;
DECLARE @history_end_time datetimeoffset;
SET @history_start_time = DATEADD(hour, -24, SYSUTCDATETIME());
SET @history_end_time = SYSUTCDATETIME();

WITH
hist AS
(
    SELECT
        p.query_id query_id,
        ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,
        SUM(rs.count_executions) AS count_executions,
        COUNT(distinct p.plan_id) AS num_plans
     FROM sys.query_store_runtime_stats AS rs
        JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
    WHERE (rs.first_execution_time >= @history_start_time
               AND rs.last_execution_time < @history_end_time)
        OR (rs.first_execution_time <= @history_start_time
               AND rs.last_execution_time > @history_start_time)
        OR (rs.first_execution_time <= @history_end_time
               AND rs.last_execution_time > @history_end_time)
    GROUP BY p.query_id
),
recent AS
(
    SELECT
        p.query_id query_id,
        ROUND(ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) * 0.001, 2), 2) AS total_duration,
        SUM(rs.count_executions) AS count_executions,
        COUNT(distinct p.plan_id) AS num_plans
    FROM sys.query_store_runtime_stats AS rs
        JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
    WHERE  (rs.first_execution_time >= @recent_start_time
               AND rs.last_execution_time < @recent_end_time)
        OR (rs.first_execution_time <= @recent_start_time
               AND rs.last_execution_time > @recent_start_time)
        OR (rs.first_execution_time <= @recent_end_time
               AND rs.last_execution_time > @recent_end_time)
    GROUP BY p.query_id
)
SELECT
    results.query_id AS query_id,
    results.query_text AS query_text,
    results.additional_duration_workload AS additional_duration_workload,
    results.total_duration_recent AS total_duration_recent,
    results.total_duration_hist AS total_duration_hist,
    ISNULL(results.count_executions_recent, 0) AS count_executions_recent,
    ISNULL(results.count_executions_hist, 0) AS count_executions_hist
FROM
(
    SELECT
        hist.query_id AS query_id,
        qt.query_sql_text AS query_text,
        ROUND(CONVERT(float, recent.total_duration/
                   recent.count_executions-hist.total_duration/hist.count_executions)
               *(recent.count_executions), 2) AS additional_duration_workload,
        ROUND(recent.total_duration, 2) AS total_duration_recent,
        ROUND(hist.total_duration, 2) AS total_duration_hist,
        recent.count_executions AS count_executions_recent,
        hist.count_executions AS count_executions_hist
    FROM hist
        JOIN recent
            ON hist.query_id = recent.query_id
        JOIN sys.query_store_query AS q
            ON q.query_id = hist.query_id
        JOIN sys.query_store_query_text AS qt
            ON q.query_text_id = qt.query_text_id
) AS results
WHERE additional_duration_workload > 0
ORDER BY additional_duration_workload DESC
OPTION (MERGE JOIN);

Mempertahankan stabilitas performa kueri

Untuk kueri yang dijalankan beberapa kali, Anda mungkin melihat bahwa SQL Server menggunakan paket yang berbeda, menghasilkan pemanfaatan dan durasi sumber daya yang berbeda. Dengan Penyimpanan Kueri, Anda dapat mendeteksi kapan performa kueri mengalami kemunculan dan menentukan rencana optimal dalam jangka waktu yang diinginkan. Anda kemudian dapat memaksa rencana optimal tersebut untuk eksekusi kueri di masa mendatang.

Anda juga dapat mengidentifikasi performa kueri yang tidak konsisten untuk kueri dengan parameter (baik parameter otomatis atau diparameterkan secara manual). Di antara berbagai paket, Anda dapat mengidentifikasi rencana yang cepat dan cukup optimal untuk semua atau sebagian besar nilai parameter dan memaksa rencana tersebut, menjaga performa yang dapat diprediksi untuk serangkaian skenario pengguna yang lebih luas.

Paksa rencana untuk kueri (terapkan kebijakan memaksa)

Ketika paket dipaksa untuk kueri tertentu, SQL Server mencoba memaksa rencana di pengoptimal. Jika rencana memaksa gagal, XEvent diaktifkan dan pengoptimal diinstruksikan untuk mengoptimalkan dengan cara normal.

EXEC sp_query_store_force_plan @query_id = 48, @plan_id = 49;

Saat menggunakan sp_query_store_force_plan Anda hanya bisa memaksa paket yang direkam oleh Penyimpanan Kueri sebagai paket untuk kueri tersebut. Dengan kata lain, satu-satunya paket yang tersedia untuk kueri adalah paket yang sudah digunakan untuk menjalankan kueri tersebut saat Penyimpanan Kueri aktif.

Catatan

Memaksa paket di Penyimpanan Kueri tidak didukung di Azure Synapse Analytics.

Rencanakan dukungan untuk kursor maju cepat dan statis

Dimulai dengan SQL Server 2019 (15.x) dan Azure SQL Database (semua model penyebaran), Query Store mendukung kemampuan untuk memaksa rencana eksekusi kueri untuk kursor Transact-SQL dan API cepat ke depan dan statis. Memaksa didukung melalui sp_query_store_force_plan atau melalui laporan SQL Server Management Studio Query Store.

Menghapus paksa paket untuk kueri

Untuk mengandalkan pengoptimal kueri SQL Server lagi untuk menghitung rencana kueri yang optimal, gunakan sp_query_store_unforce_plan untuk membatalkan penerapan paket yang dipilih untuk kueri.

EXEC sp_query_store_unforce_plan @query_id = 48, @plan_id = 49;