Memantau performa dengan menggunakan Penyimpanan Kueri

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

Fitur Penyimpanan Kueri memberi Anda wawasan tentang pilihan dan performa rencana kueri untuk SQL Server, Azure SQL Database, 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, paket, dan statistik runtime, serta mempertahankannya untuk peninjauan 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 dapat ditindak lanjuti dan menyetel performa dengan Penyimpanan Kueri, lihat Menyetel performa dengan Penyimpanan Kueri.
• Untuk informasi tentang membentuk rencana kueri tanpa mengubah kode aplikasi, lihat Petunjuk Penyimpanan Kueri.

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 Penyimpanan Kueri

• Penyimpanan Kueri diaktifkan secara default untuk database Azure SQL Database dan Azure SQL Managed Instance baru.
• Penyimpanan Kueri tidak diaktifkan secara default 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 Penyimpanan Kueri.

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

Menggunakan pernyataan Transact-SQL

Gunakan pernyataan ALTER DATABASE guna mengaktifkan penyimpanan kueri untuk database tertentu. Contohnya:

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

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 database atau tempdb .

Penting

Untuk informasi tentang mengaktifkan Penyimpanan Kueri dan membuatnya disesuaikan 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 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 memaksa rencana. Paket memaksa di Penyimpanan Kueri disediakan dengan menggunakan mekanisme yang mirip dengan petunjuk kueri USE PLAN , tetapi tidak memerlukan perubahan apa pun dalam aplikasi pengguna. Pemakaian 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, UPDATEDELETE, MERGE, dan BULK INSERT.

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

Penyimpanan Kueri tidak mengumpulkan data untuk prosedur tersimpan yang dikompilasi secara asli secara default. Gunakan sys.sp_xtp_control_query_exec_stats untuk mengaktifkan pengumpulan data untuk prosedur tersimpan yang dikompilasi secara asli.

Statistik tunggu adalah sumber informasi lain yang membantu memecahkan masalah performa di Mesin Database. Untuk waktu yang lama, statistik tunggu hanya tersedia pada tingkat instans, yang membuatnya sulit untuk backtrack menunggu ke kueri tertentu. Dimulai dengan SQL Server 2017 (14.x) dan Azure SQL Database, Query Store menyertakan dimensi yang melacak statistik tunggu. Contoh berikut memungkinkan Penyimpanan Kueri 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 kemunculan performa karena perubahan rencana eksekusi.
• 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 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 tunggu untuk kueri atau rencana tertentu.

Penyimpanan Kueri berisi tiga penyimpanan:

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

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

Kueri berikut mengembalikan informasi tentang kueri, paketnya, 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 untuk replika sekunder memungkinkan fungsionalitas Penyimpanan Kueri yang sama pada beban kerja replika sekunder yang tersedia untuk replika utama. 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 Kuerinya sendiri. Intinya, ada satu Penyimpanan Kueri yang dibagikan antara replika utama dan semua sekunder. Penyimpanan Kueri ada di replika utama dan menyimpan data untuk semua replika bersama-sama.

Untuk informasi lengkap tentang Penyimpanan Kueri untuk replika sekunder, lihat Penyimpanan Kueri untuk replika sekunder grup ketersediaan AlwaysOn.

Menggunakan fitur Kueri yang Diregresi

Setelah mengaktifkan Penyimpanan Kueri, refresh 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 yang Diregresi untuk membuka panel Kueri yang Diregresi di SQL Server Management Studio. Panel Kueri yang Diregresikan memperlihatkan kepada Anda kueri dan paket 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 penerapan rencana kueri, beralih antara format kisi dan bagan, membandingkan paket yang dipilih (jika lebih dari satu dipilih), dan merefresh tampilan.

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

Menemukan kueri tunggu

Dimulai dengan SQL Server 2017 (14.x) dan Azure SQL Database, statistik tunggu per kueri dari waktu ke waktu tersedia di Penyimpanan Kueri.

Di Penyimpanan Kueri, 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. Panel Statistik Tunggu Kueri memperlihatkan bagan batang yang berisi kategori tunggu teratas di Penyimpanan Kueri. 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 berkontribusi 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 grafis. Tombol tersedia untuk menampilkan kueri sumber, memaksa, dan membatalkan penerapan rencana kueri, dan me-refresh tampilan.

Kategori tunggu menggabungkan berbagai jenis tunggu ke dalam wadah yang mirip secara alami. Kategori tunggu yang berbeda memerlukan analisis tindak lanjut yang berbeda untuk menyelesaikan masalah, tetapi jenis tunggu dari kategori yang sama menyebabkan pengalaman pemecahan masalah yang sangat mirip, dan memberikan kueri yang terpengaruh di atas penantian akan menjadi bagian 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 penggunaan memori teratas di Microsoft Store Kueri. Kueri ini mungkin menunda perkembangan 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	Kunci Tinggi menunggu di Penyimpanan Kueri untuk kueri tertentu	Periksa teks kueri untuk 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 Penyimpanan Kueri untuk kueri tertentu	Temukan kueri dengan jumlah bacaan fisik yang tinggi di Microsoft Store Kueri. Jika mereka mencocokkan kueri dengan tunggu IO tinggi, pertimbangkan untuk memperkenalkan indeks pada entitas yang mendasarinya, untuk melakukan pencarian alih-alih pemindaian, dan dengan demikian meminimalkan overhead IO dari kueri.
Penantian SOS_SCHEDULER_YIELD tinggi per database	CPU tinggi menunggu di Penyimpanan Kueri untuk kueri tertentu	Temukan kueri penggunaan CPU teratas di Penyimpanan Kueri. Di antaranya, identifikasi kueri yang tren CPU tinggi berkorelasi dengan CPU tinggi menunggu 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).

sys.database_query_store_options Kueri tampilan untuk menentukan 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 membantu operasi dengan Penyimpanan Kueri.

sys.fn_stmt_sql_handle_from_sql_stmt (T-SQL)

Tampilan katalog Penyimpanan Kueri

Tampilan katalog menyajikan informasi tentang Penyimpanan Kueri.

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 tersimpan 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 Penyimpanan Kueri dapat memasukkan status KESALAHAN karena kesalahan internal. Dimulai dengan SQL Server 2017 (14.x), jika ini terjadi, Penyimpanan Kueri 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 actual_state_desc dijelaskan dalam deskripsi kolom.

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 menyelami 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 (T-SQL)
• Statistik Kueri Langsung
• Monitor Aktivitas
• Cara Penyimpanan Kueri Mengumpulkan Data
• Monitor dan Selaraskan Kinerja
• Alat Penyetelan dan Pemantauan Performa
• Menggunakan Penyimpanan Kueri dengan OLTP Dalam Memori
• Praktik Terbaik dengan Penyimpanan Kueri
• Praktik terbaik untuk mengelola Penyimpanan Kueri
• Menyetel performa dengan Penyimpanan Kueri
• Petunjuk penyimpanan kueri
• Skenario Penggunaan Penyimpanan Kueri
• Buka Monitor Aktivitas (SQL Server Management Studio)