Memantau kinerja melalui Penyimpanan Kueri

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

Fitur Penyimpanan Kueri memberi Anda wawasan tentang pilihan dan performa rencana kueri untuk SQL Server, Azure SQL Database, database Fabric SQL, Azure SQL Managed Instance, dan Azure Synapse Analytics. Penyimpanan Kueri menyederhanakan pemecahan masalah performa dengan membantu Anda menemukan perbedaan performa yang disebabkan oleh perubahan rencana kueri dengan cepat. Query Store secara otomatis menangkap riwayat kueri, rencana, dan statistik runtime, dan menyimpannya untuk tinjauan Anda. Ini memisahkan data menurut jendela waktu sehingga Anda dapat melihat pola penggunaan database dan memahami kapan perubahan rencana kueri terjadi di server.

Anda dapat mengonfigurasi Penyimpanan Kueri menggunakan opsi ALTER DATABASE SET .

• Untuk informasi tentang mengoperasikan Penyimpanan Kueri di Azure SQL Database, lihat Mengoperasikan Penyimpanan Kueri di Azure SQL Database.
• Untuk informasi tentang menemukan informasi yang bisa ditindaklanjuti dan mengoptimalkan performa dengan Penyimpanan Kueri, lihat Mengoptimalkan performa dengan Penyimpanan Kueri.
• Untuk informasi tentang membentuk kueri rencana tanpa mengubah kode aplikasi, lihat Query Store hints.

Penting

Jika Anda menggunakan Query Store untuk wawasan beban kerja just-in-time di SQL Server 2016 (13.x), rencanakan untuk menginstal perbaikan skalabilitas performa di KB 4340759 sesegera mungkin.

Mengaktifkan Query Store

• Penyimpanan Kueri diaktifkan secara default untuk database Azure SQL Database dan Azure SQL Managed Instance baru.
• Query Store tidak diaktifkan secara bawaan untuk SQL Server 2016 (13.x), SQL Server 2017 (14.x), SQL Server 2019 (15.x). Ini diaktifkan secara default dalam READ_WRITE mode untuk database baru yang dimulai dengan SQL Server 2022 (16.x). Untuk mengaktifkan fitur untuk melacak riwayat performa dengan lebih baik, memecahkan masalah terkait rencana kueri, dan mengaktifkan kemampuan baru di SQL Server 2022 (16.x), sebaiknya aktifkan Penyimpanan Kueri di semua database.
• Query Store tidak diaktifkan secara default untuk database Azure Synapse Analytics baru.

Menggunakan halaman Penyimpanan Kueri di SQL Server Management Studio

1. Di Object Explorer, klik kanan database, lalu pilih Properti.

   Catatan

   Memerlukan setidaknya versi 16 Dari Management Studio.

2. Dalam kotak dialog Properti Database, pilih halaman Query Store.

3. Dalam kotak Mode Operasi (Diminta) , pilih Baca Tulis.

Menggunakan pernyataan Transact-SQL

ALTER DATABASE Gunakan pernyataan untuk mengaktifkan Penyimpanan Kueri untuk database tertentu. Contohnya:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

Opsi untuk mengonfigurasi Penyimpanan Kueri di database Fabric SQL saat ini terbatas dengan ALTER DATABASE.

Di Azure Synapse Analytics, aktifkan Penyimpanan Kueri tanpa opsi tambahan, misalnya:

ALTER DATABASE <database_name>
SET QUERY_STORE = ON;

Untuk opsi sintaks lainnya yang terkait dengan Penyimpanan Kueri, lihat Mengubah Opsi SET DATABASE (Transact-SQL).

Catatan

Penyimpanan Kueri tidak dapat diaktifkan untuk master atau tempdb database.

Penting

Untuk informasi tentang mengaktifkan Penyimpanan Kueri dan menyelaraskannya dengan beban kerja Anda, lihat Praktik Terbaik dengan Penyimpanan Kueri.

Informasi di Penyimpanan Kueri

Rencana eksekusi untuk kueri tertentu di SQL Server biasanya berkembang dari waktu ke waktu karena sejumlah alasan yang berbeda seperti perubahan statistik, perubahan skema, pembuatan/penghapusan indeks, dll. Cache prosedur (tempat paket kueri yang di-cache disimpan) hanya menyimpan rencana eksekusi terbaru. Rencana-rencana juga dikeluarkan dari cache rencana karena tekanan memori. Akibatnya, regresi performa kueri yang disebabkan oleh perubahan rencana eksekusi dapat menjadi tidak sepele dan memakan waktu untuk diselesaikan.

Karena Penyimpanan Kueri mempertahankan beberapa rencana eksekusi per kueri, Penyimpanan Kueri dapat memberlakukan kebijakan untuk mengarahkan Pemroses Kueri untuk menggunakan rencana eksekusi tertentu untuk kueri. Ini disebut sebagai pemaksaan rencana. Pemaksaan rencana di Penyimpanan Kueri disediakan dengan menggunakan mekanisme yang mirip dengan petunjuk kueri USE PLAN, tetapi tidak memerlukan perubahan apa pun pada aplikasi pengguna. Pemaksaan rencana dapat mengatasi regresi performa kueri yang disebabkan oleh perubahan rencana dalam waktu yang sangat singkat.

Catatan

Penyimpanan Kueri mengumpulkan rencana untuk Pernyataan DML seperti SELECT, INSERT, UPDATE, DELETE, MERGE, dan BULK INSERT.

Secara desain, Query Store tidak mengumpulkan rencana untuk pernyataan DDL seperti CREATE INDEX, dll. Query Store memantau konsumsi sumber daya secara kumulatif dengan mengumpulkan rencana untuk pernyataan DML yang mendasar. Misalnya, Penyimpanan Kueri mungkin menampilkan pernyataan SELECT dan INSERT yang dijalankan secara internal untuk mengisi indeks baru.

Query Store tidak mengumpulkan data untuk prosedur tersimpan yang dikompilasi secara native secara default. Gunakan sys.sp_xtp_control_query_exec_stats untuk mengaktifkan pengumpulan data untuk prosedur tersimpan yang dikompilasi secara asli.

Statistik waktu tunggu adalah sumber informasi lain yang membantu memecahkan masalah performa dalam Mesin Basis Data. Untuk waktu yang lama, statistik waktu tunggu hanya tersedia pada tingkat instans, yang membuatnya sulit untuk melacak kembali waktu tunggu ke kueri tertentu. Dimulai dengan SQL Server 2017 (14.x) dan Azure SQL Database, Query Store mencakup dimensi yang melacak statistik tunggu. Contoh berikut mengaktifkan Query Store untuk mengumpulkan statistik tunggu.

ALTER DATABASE <database_name>
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );

Skenario umum untuk menggunakan fitur Penyimpanan Kueri adalah:

• Temukan dan perbaiki regresi performa rencana dengan cepat dengan memaksa rencana kueri sebelumnya. Perbaiki kueri yang baru-baru ini mengalami penurunan performa karena perubahan rencana pelaksanaan.
• Tentukan berapa kali kueri dijalankan di jendela waktu tertentu, membantu DBA dalam memecahkan masalah sumber daya performa.
• Identifikasi kueri n teratas (berdasarkan waktu eksekusi, konsumsi memori, dll.) dalam x jam terakhir.
• Mengaudit riwayat rencana eksekusi kueri untuk kueri tertentu.
• Analisis pola penggunaan sumber daya (CPU, I/O, dan Memori) untuk database tertentu.
• Identifikasi kueri n teratas yang menunggu sumber daya.
• Pahami sifat penantian untuk kueri atau rencana tertentu.

Toko Kueri menyimpan tiga toko:

• penyimpanan rencana untuk informasi rencana eksekusi.
• penyimpanan statistik runtime untuk menyimpan informasi statistik eksekusi.
• penyimpanan statistik tunggu untuk mempertahankan informasi statistik tunggu.

Jumlah rencana unik yang dapat disimpan untuk kueri di penyimpanan rencana dibatasi oleh opsi konfigurasi max_plans_per_query. Untuk meningkatkan performa, informasi ditulis ke penyimpanan secara asinkron. Untuk meminimalkan penggunaan ruang, statistik eksekusi waktu operasi di penyimpanan statistik waktu operasi dikumpulkan selama periode waktu tetap. Informasi di dalam penyimpanan ini dapat terlihat dengan mengajukan kueri pada tampilan katalog Penyimpanan Kueri.

Kueri berikut mengembalikan informasi tentang kueri, rencana eksekusi, waktu kompilasi, dan statistik run-time dari Penyimpanan Kueri.

SELECT Txt.query_text_id, Txt.query_sql_text, Pln.plan_id, Qry.*, RtSt.*
FROM sys.query_store_plan AS Pln
INNER JOIN sys.query_store_query AS Qry
    ON Pln.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_id
INNER JOIN sys.query_store_runtime_stats RtSt
ON Pln.plan_id = RtSt.plan_id;

Penyimpanan Kueri untuk Replika Sekunder

Berlaku untuk: SQL Server (Dimulai dengan SQL Server 2022 (16.x))

Fitur Penyimpanan Kueri pada replika sekunder memungkinkan fungsionalitas Penyimpanan Kueri yang sebelumnya hanya tersedia pada replika utama dapat digunakan pada beban kerja replika sekunder. Ketika Penyimpanan Kueri untuk replika sekunder diaktifkan, replika mengirim informasi eksekusi kueri yang biasanya akan disimpan di Penyimpanan Kueri kembali ke replika utama. Replika utama kemudian menyimpan data ke disk dalam Penyimpanan Kueri-nya sendiri. Intinya, ada satu Penyimpanan Kueri yang digunakan bersama antara replika utama dan semua sekunder. Toko Kueri terdapat di replika utama dan menyimpan data untuk semua replika secara bersama.

Untuk informasi lengkap tentang Penyimpanan Kueri untuk replika sekunder, lihat Penyimpanan Kueri untuk replika sekunder dalam grup ketersediaan Always On.

Gunakan fitur Kueri yang Diregresikan

Setelah mengaktifkan Penyimpanan Kueri, segarkan bagian database panel Object Explorer untuk menambahkan bagian Penyimpanan Kueri.

Catatan

Untuk Azure Synapse Analytics, tampilan Penyimpanan Kueri tersedia di bawah Tampilan Sistem di bagian database panel Object Explorer.

Pilih Kueri Diregresi untuk membuka jendela Kueri Diregresi di SQL Server Management Studio. Panel Kueri yang Menurun menampilkan kepada Anda kueri dan rencana di Penyimpanan Kueri. Gunakan kotak daftar dropdown di bagian atas untuk memfilter kueri berdasarkan berbagai kriteria: Durasi (ms) (Default), Waktu CPU (ms), Pembacaan Logis (KB), Penulisan Logis (KB), Bacaan Fisik (KB), Waktu CLR (ms), DOP, Konsumsi Memori (KB), Jumlah Baris, Memori Log Yang Digunakan (KB), Memori Temp DB Yang Digunakan (KB), dan Waktu Tunggu (ms).

Pilih rencana untuk melihat rencana kueri grafis. Tombol tersedia untuk melihat kueri sumber, memaksa dan membatalkan pemaksaan rencana eksekusi kueri, beralih antara format kisi dan bagan, membandingkan rencana yang dipilih (jika lebih dari satu dipilih), dan memperbarui tampilan.

Untuk memaksa rencana, pilih kueri dan rencana, lalu pilih Paksa Rencana. Anda hanya bisa memaksa rencana yang disimpan oleh fitur rencana kueri dan masih ada dalam cache rencana kueri.

Menemukan kueri tunggu

Dimulai dengan SQL Server 2017 (14.x) dan Azure SQL Database, statistik tunggu untuk setiap kueri dalam jangka waktu tertentu tersedia di Penyimpanan Kueri.

Di Query Store, jenis tunggu digabungkan ke dalam kategori tunggu. Pemetaan kategori tunggu untuk jenis tunggu tersedia di sys.query_store_wait_stats (Transact-SQL).

Pilih Statistik Tunggu Kueri untuk membuka panel Statistik Tunggu Kueri di SQL Server Management Studio 18.0 atau versi yang lebih tinggi. Papan Statistik Tunggu Kueri memperlihatkan diagram batang yang berisi kategori tunggu utama di Query Store. Gunakan daftar dropdown di bagian atas untuk memilih kriteria agregat untuk waktu tunggu: rata-rata, maks, min, std dev, dan total (default).

Pilih kategori tunggu dengan memilih pada bilah dan tampilan detail pada kategori tunggu yang dipilih ditampilkan. Bagan batang baru ini berisi kueri yang berpengaruh pada kategori tunggu tersebut.

Gunakan kotak daftar dropdown di bagian atas untuk memfilter kueri berdasarkan berbagai kriteria waktu tunggu untuk kategori tunggu yang dipilih: rata-rata, maks, min, std dev, dan total (default). Pilih rencana untuk melihat rencana kueri grafikal. Tombol tersedia untuk menampilkan kueri asli, menerapkan dan membatalkan rencana kueri, serta menyegarkan tampilan.

Kategori waktu tunggu adalah penggabungan berbagai jenis waktu tunggu ke dalam kelompok yang serupa secara alami. Kategori tunggu yang berbeda memerlukan analisis lanjutan yang berbeda untuk mengatasi masalah, tetapi jenis tunggu dari kategori yang sama menimbulkan pengalaman pemecahan masalah yang sangat mirip, dan menyediakan kueri yang terpengaruh pada bagian atas daftar penantian akan menjadi elemen penting yang hilang untuk menyelesaikan sebagian besar penyelidikan tersebut dengan sukses.

Berikut adalah beberapa contoh bagaimana Anda bisa mendapatkan lebih banyak wawasan tentang beban kerja Anda sebelum dan sesudah memperkenalkan kategori tunggu di Penyimpanan Kueri:

Pengalaman sebelumnya	Pengalaman baru	Perbuatan
Penantian RESOURCE_SEMAPHORE tinggi per database	Memori Tinggi menunggu di Penyimpanan Kueri untuk kueri tertentu	Temukan kueri yang menggunakan memori terbanyak di Query Store. Kueri ini mungkin menghambat kemajuan lebih lanjut dari kueri yang terpengaruh. Pertimbangkan untuk menggunakan petunjuk kueri MAX_GRANT_PERCENT untuk kueri ini, atau untuk kueri yang terpengaruh.
Penantian LCK_M_X tinggi per database	High Lock menunggu di Query Store untuk kueri tertentu	Periksa teks kueri yang terpengaruh dan identifikasi entitas target. Lihat di Penyimpanan Kueri untuk kueri lain yang memodifikasi entitas yang sama, yang sering dijalankan dan/atau memiliki durasi tinggi. Setelah mengidentifikasi kueri ini, pertimbangkan untuk mengubah logika aplikasi untuk meningkatkan konkurensi, atau menggunakan tingkat isolasi yang kurang ketat.
Penantian PAGEIOLATCH_SH tinggi per database	IO Buffer tinggi menunggu di Query Store untuk kueri tertentu	Temukan kueri dengan jumlah pembacaan fisik yang tinggi di Query Store. Jika kueri-kueri tersebut memiliki waktu tunggu IO yang tinggi, pertimbangkan untuk menambahkan indeks pada entitas yang mendasarinya, untuk melakukan pencarian daripada pemindaian, dan dengan demikian meminimalkan beban IO dari kueri.
Waktu tunggu SOS_SCHEDULER_YIELD yang tinggi per database	Waktu tunggu CPU yang tinggi pada Penyimpanan Kueri untuk kueri tertentu	Temukan kueri penggunaan CPU teratas di Penyimpanan Kueri. Di antaranya, identifikasi kueri di mana tren CPU tinggi berkorelasi dengan waktu tunggu CPU yang tinggi pada kueri yang terpengaruh. Fokus pada pengoptimalan kueri tersebut - mungkin ada regresi rencana, atau mungkin indeks yang hilang.

Opsi konfigurasi

Untuk opsi yang tersedia untuk mengonfigurasi parameter Penyimpanan Kueri, lihat OPSI UBAH SET DATABASE (Transact-SQL).

Kueri tampilan sys.database_query_store_options untuk menentukan opsi-opsi Penyimpanan Kueri saat ini. Untuk informasi selengkapnya tentang nilai, lihat sys.database_query_store_options.

Untuk contoh tentang mengatur opsi konfigurasi menggunakan pernyataan Transact-SQL, lihat Manajemen Opsi.

Catatan

Untuk Azure Synapse Analytics, Penyimpanan Kueri dapat diaktifkan seperti pada platform lain tetapi opsi konfigurasi tambahan tidak didukung.

Tampilan, fungsi, dan prosedur terkait

Lihat dan kelola Penyimpanan Kueri melalui Management Studio atau dengan menggunakan Tampilan dan Prosedur berikut.

Fungsi Penyimpanan Kueri

Fungsi memfasilitasi operasi pada Penyimpanan Kueri.

sys.fn_stmt_sql_handle_from_sql_stmt (T-SQL)

Tampilan katalog Query Store

Tampilan katalog menyajikan informasi tentang Query Store.

sys.database_query_store_options (T-SQL)

sys.query_context_settings (T-SQL)

sys.query_store_plan (T-SQL)

sys.query_store_query (T-SQL)

sys.query_store_query_text (T-SQL)

sys.query_store_runtime_stats (T-SQL)

sys.query_store_wait_stats (T-SQL)

sys.query_store_runtime_stats_interval (T-SQL)

sys.database_query_store_internal_state (T-SQL)

Prosedur tersimpan Penyimpanan Kueri

Prosedur yang disimpan mengonfigurasi Penyimpanan Kueri.

sp_query_store_flush_db (T-SQL)

sp_query_store_reset_exec_stats (T-SQL)

sp_query_store_force_plan (T-SQL)

sp_query_store_unforce_plan (T-SQL)

sp_query_store_remove_plan (T-SQL)

sp_query_store_remove_query (T-SQL)

sp_query_store_clear_message_queues (T-SQL)

sp_query_store_consistency_check (T-SQL)¹

¹ Dalam skenario ekstrem, Query Store dapat memasuki status KESALAHAN karena kesalahan internal. Dimulai dengan SQL Server 2017 (14.x), jika ini terjadi, Query Store dapat dipulihkan dengan menjalankan prosedur tersimpan sp_query_store_consistency_check dalam database yang terpengaruh. Lihat sys.database_query_store_options untuk detail selengkapnya yang dijelaskan dalam kolom deskripsi.

Pemeliharaan Penyimpanan Kueri

Praktik terbaik dan rekomendasi untuk pemeliharaan dan manajemen Penyimpanan Kueri telah diperluas dalam artikel ini: Praktik terbaik untuk mengelola Penyimpanan Kueri.

Audit dan pemecahan masalah performa

Untuk informasi selengkapnya tentang mendalami penyetelan performa dengan Penyimpanan Kueri, lihat Menyetel performa dengan Penyimpanan Kueri.

Topik performa lainnya:

• Skenario Penggunaan Penyimpanan Kueri

Konten terkait

• Prosedur Tersimpan Penyimpanan Kueri (Transact-SQL)
• Tampilan Katalog Penyimpanan Kueri (Transact-SQL)
• sys.database_query_store_options (Transact-SQL)
• Statistik Kueri Langsung
• Monitor Aktivitas
• Bagaimana Query Store Mengumpulkan Data
• Monitor dan Selaraskan Kinerja
• Alat Penyetelan dan Pemantauan Performa
• Menggunakan Query Store dengan In-Memory OLTP
• Praktik Terbaik dengan Penyimpanan Kueri
• Praktik terbaik untuk mengelola Query Store
• Mengatur performa dengan Penyimpanan Query
• Petunjuk Query Store
• Skenario Penggunaan Query Store
• Buka Monitor Aktivitas (SQL Server Management Studio)