Memantau performa dengan menggunakan Penyimpanan Kueri

  • Artikel

Berlaku untuk: SQL Server 2016 (13.x) dan yang lebih baru Azure SQL DatabaseAzure SQL Managed InstanceAzure 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 .

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 paket 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 menangkap konsumsi sumber daya kumulatif dengan mengumpulkan rencana untuk pernyataan DML yang mendasar. Misalnya, Penyimpanan Kueri dapat menampilkan pernyataan SELECT dan INSERT yang 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 dan paket di Penyimpanan Kueri.

SELECT Txt.query_text_id, Txt.query_sql_text, Pl.plan_id, Qry.*
FROM sys.query_store_plan AS Pl
INNER JOIN sys.query_store_query AS Qry
    ON Pl.query_id = Qry.query_id
INNER JOIN sys.query_store_query_text AS Txt
    ON Qry.query_text_id = Txt.query_text_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.

Screenshot of the Query Store reporting tree in SSMS Object Explorer.

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.

Screenshot of the SQL Server Regressed Queries report in SSMS Object Explorer.

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 v18 atau 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).

Screenshot of the SQL Server Query Wait Statistics report in SSMS Object Explorer.

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.

Screenshot of the SQL Server Query Wait Statistics detail view in SSMS Object Explorer.

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 Tindakan
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.

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)1

1 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:

Baca juga

Langkah berikutnya