Menyetel performa dengan Penyimpanan Kueri

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 dapat membantu dalam garis besar performa dan investigasi performa kueri Anda:

Kueri terakhir yang dijalankan pada database

Kueri n terakhir yang dijalankan pada database dalam satu jam terakhir:

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
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER 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.last_execution_time DESC;

Jumlah eksekusi

Jumlah eksekusi untuk setiap kueri dalam satu jam terakhir:

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
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
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 durasi rata-rata tertinggi dalam satu jam terakhir:

SELECT TOP 10 ROUND(CONVERT(FLOAT, SUM(rs.avg_duration * rs.count_executions)) /
        NULLIF(SUM(rs.count_executions), 0), 2) avg_duration,
    SUM(rs.count_executions) AS total_execution_count,
    qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id,
    GETUTCDATE() AS CurrentUTCTime,
    MAX(rs.last_execution_time) AS last_execution_time
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY qt.query_sql_text,
    q.query_id,
    qt.query_text_id,
    p.plan_id
ORDER BY avg_duration DESC;

Bacaan I/O fisik rata-rata tertinggi

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
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
INNER 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 dengan lebih dari satu paket sangat menarik, karena dapat menjadi kandidat untuk regresi performa karena perubahan pilihan rencana.

Kueri berikut mengidentifikasi kueri dengan jumlah paket tertinggi dalam satu jam terakhir:

SELECT q.query_id,
    object_name(object_id) AS ContainingObject,
    COUNT(*) AS QueryPlanCount,
    STRING_AGG(p.plan_id, ',') plan_ids,
    qt.query_sql_text
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
GROUP BY OBJECT_NAME(object_id),
    q.query_id,
    qt.query_sql_text
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY QueryPlanCount DESC;

Kueri berikut mengidentifikasi kueri ini bersama dengan semua paket dalam satu jam terakhir:

WITH Query_MultPlans
AS (
    SELECT COUNT(*) AS QueryPlanCount,
        q.query_id
    FROM sys.query_store_query_text AS qt
    INNER JOIN sys.query_store_query AS q
        ON qt.query_text_id = q.query_text_id
    INNER 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,
    p.plan_id,
    p.query_plan AS plan_xml,
    p.last_compile_start_time,
    p.last_execution_time
FROM Query_MultPlans AS qm
INNER JOIN sys.query_store_query AS q
    ON qm.query_id = q.query_id
INNER JOIN sys.query_store_plan AS p
    ON q.query_id = p.query_id
INNER JOIN sys.query_store_query_text qt
    ON qt.query_text_id = q.query_text_id
INNER 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 q.query_id,
    p.plan_id;

Durasi tunggu tertinggi

Kueri ini mengembalikan 10 kueri teratas dengan durasi tunggu tertinggi selama satu jam terakhir:

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
INNER JOIN sys.query_store_plan p
    ON ws.plan_id = p.plan_id
INNER JOIN sys.query_store_query q
    ON p.query_id = q.query_id
INNER JOIN sys.query_store_query_text qt
    ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_runtime_stats AS rs
    ON p.plan_id = rs.plan_id
WHERE rs.last_execution_time > DATEADD(HOUR, -1, GETUTCDATE())
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
INNER JOIN sys.query_store_query AS q
    ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan AS p1
    ON q.query_id = p1.query_id
INNER JOIN sys.query_store_runtime_stats AS rs1
    ON p1.plan_id = rs1.plan_id
INNER JOIN sys.query_store_runtime_stats_interval AS rsi1
    ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id
INNER JOIN sys.query_store_plan AS p2
    ON q.query_id = p2.query_id
INNER JOIN sys.query_store_runtime_stats AS rs2
    ON p2.plan_id = rs2.plan_id
INNER 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 semua regresi performa (tidak hanya regresi yang terkait dengan perubahan pilihan rencana), hapus kondisi AND p1.plan_id <> p2.plan_id dari kueri sebelumnya.

Kueri dengan regresi historis dalam performa

Saat Anda ingin membandingkan eksekusi terbaru dengan eksekusi historis, kueri berikut 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 mewakili berapa banyak durasi ekstra yang diperkenalkan oleh eksekusi terbaru ini, 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
    INNER 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
    INNER 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
    INNER JOIN recent
        ON hist.query_id = recent.query_id
    INNER JOIN sys.query_store_query AS q
        ON q.query_id = hist.query_id
    INNER 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 diparameterisasi 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. Ini 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, Extended Event diaktifkan dan pengoptimal diinstruksikan untuk mengoptimalkan dengan cara normal.

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

Saat Anda 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

Di SQL Server 2019 (15.x) dan versi yang lebih baru, dan Azure SQL Database (semua model penyebaran), Query Store mendukung kemampuan untuk memaksa rencana eksekusi kueri untuk kursor Transact-SQL dan API yang 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;

Konten terkait

• Memantau Performa dengan menggunakan Penyimpanan Kueri
• Praktik Terbaik dengan Penyimpanan Kueri
• Menggunakan Penyimpanan Kueri dengan OLTP Dalam Memori
• Skenario Penggunaan Penyimpanan Kueri
• Cara Penyimpanan Kueri Mengumpulkan Data
• Prosedur tersimpan Penyimpanan Kueri (Transact-SQL)
• Tampilan katalog Penyimpanan Kueri (Transact-SQL)
• Buka Monitor Aktivitas (SQL Server Management Studio)
• Statistik Kueri Langsung
• Monitor Aktivitas
• sys.database_query_store_options (T-SQL)
• Monitor dan Selaraskan Kinerja
• Alat Penyetelan dan Pemantauan Performa
• Petunjuk penyimpanan kueri
• Menyetel Database Menggunakan Beban Kerja dari Penyimpanan Kueri dengan Konsultan Penyetelan Mesin Database