Menyetel kinerja dengan Penyimpanan Query

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (khusus kumpulan SQL)Database SQL di Microsoft Fabric

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 cara Anda dapat menggunakan informasi yang dapat ditindaklanjuti untuk meningkatkan kinerja kueri di database Anda, termasuk cara mengidentifikasi kueri berdasarkan statistik penggunaan dan memaksakan rencana eksekusi. Anda juga bisa menggunakan 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 menggunakan 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

Query Store menyimpan riwayat kompilasi dan metrik runtime selama eksekusi kueri, memungkinkan Anda menganalisis 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 rencana

Kueri dengan lebih dari satu rencana sangat menarik, karena dapat menjadi kandidat untuk penurunan performa akibat perubahan pilihan rencana.

Kueri berikut mengidentifikasi kueri dengan jumlah rencana 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-kueri ini bersama dengan semua rencana 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;

Note

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 mengalami penurunan kinerja

Contoh kueri berikut mengembalikan semua kueri yang waktu eksekusinya berlipat ganda dalam 48 jam terakhir karena perubahan pemilihan rencana. 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 penurunan performa historis

Saat Anda ingin membandingkan eksekusi terbaru dengan eksekusi historis, kueri berikut membandingkan eksekusi kueri berdasarkan periode eksekusi. Dalam contoh khusus ini, kueri membandingkan eksekusi dalam periode terbaru (1 jam) dengan periode riwayat (hari terakhir) dan mengidentifikasi kasus yang memperkenalkan additional_duration_workload. 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 ditambahkan 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 rencana eksekusi yang berbeda, menghasilkan pemanfaatan sumber daya dan durasi yang berbeda. Dengan Penyimpanan Kueri, Anda dapat mendeteksi kapan performa kueri mengalami penurunan dan menentukan rencana optimal dalam periode yang diminati. 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 rencana, Anda dapat mengidentifikasi rencana yang cepat dan cukup optimal untuk semua atau sebagian besar nilai parameter dan menerapkan rencana tersebut. Ini menjaga performa yang dapat diprediksi untuk serangkaian skenario pengguna yang lebih luas.

Paksa rencana untuk kueri (terapkan kebijakan memaksa)

Ketika rencana dipaksakan untuk kueri tertentu, SQL Server mencoba memaksakan rencana pada pengoptimal. Jika pemaksaan rencana gagal, sebuah Extended Event dipicu 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 menetapkan rencana yang direkam oleh Penyimpanan Kueri sebagai rencana untuk kueri tersebut. Dengan kata lain, satu-satunya rencana yang tersedia untuk kueri adalah rencana yang sudah digunakan untuk mengeksekusi kueri tersebut saat Penyimpanan Kueri aktif.

Note

Memaksa rencana 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, serta Azure SQL Database (dalam semua model penyebaran), Query Store mendukung kemampuan untuk memaksakan rencana eksekusi kueri untuk kursor Transact-SQL dan API yang cepat-maju dan statis. Memaksa didukung melalui sp_query_store_force_plan atau melalui penggunaan laporan SQL Server Management Studio Query Store.

Menghapus pemaksaan rencana untuk kueri

Untuk kembali mengandalkan pengoptimal kueri SQL Server dalam menghitung rencana kueri yang optimal, gunakan sp_query_store_unforce_plan untuk melepaskan rencana yang dipilih untuk kueri.

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

Konten terkait

• Memantau Kinerja menggunakan Penyimpanan Kueri
• Praktik Terbaik dengan Query Store
• Menggunakan Toko Kueri dengan OLTP In-Memory
• Skenario Penggunaan Query Store
• Cara Query Store 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 (Transact-SQL)
• Monitor dan Selaraskan Kinerja
• Alat Penyetelan dan Pemantauan Performa
• Petunjuk Query Store
• Mengoptimalkan Database Menggunakan Beban Kerja dari Query Store dengan Database Engine Tuning Advisor