Praktik terbaik dengan Query Store

Berlaku untuk: SQL Server (semua versi yang didukung) Azure SQL Database Azure SQL Managed Instance

Artikel ini menguraikan praktik terbaik untuk menggunakan SQL Server Query Store dengan beban kerja Anda.

• Untuk informasi selengkapnya tentang mengonfigurasi dan mengelola dengan Penyimpanan Kueri, lihat Memantau performa dengan menggunakan Penyimpanan Kueri.
• Untuk informasi tentang menemukan informasi yang dapat ditindaklanjuti dan menyetel performa dengan Penyimpanan Kueri, lihat Menyetel performa dengan menggunakan Penyimpanan Kueri.
• Untuk informasi tentang mengoperasikan Penyimpanan Kueri di Azure SQL Database, lihat Mengoperasikan Penyimpanan Kueri di Azure SQL Database.

Catatan

Di Azure Synapse Analytics, opsi konfigurasi untuk Penyimpanan Kueri tidak didukung.

Gunakan SQL Server Management Studio terbaru

SQL Server Management Studio memiliki sekumpulan antarmuka pengguna yang dirancang untuk mengonfigurasi Penyimpanan Kueri dan untuk menggunakan data yang dikumpulkan tentang beban kerja Anda. Unduh versi terbaru SQL Server Management Studio.

Untuk deskripsi singkat tentang cara menggunakan Penyimpanan Kueri dalam skenario pemecahan masalah, lihat Query Store Azure blogs.

Menggunakan Wawasan Performa Kueri di Azure SQL Database

Jika Anda menjalankan Penyimpanan Kueri di Azure SQL Database, Anda bisa menggunakan Wawasan Performa Kueri untuk menganalisis konsumsi sumber daya dari waktu ke waktu. Meskipun Anda dapat menggunakan Management Studio dan Azure Data Studio untuk mendapatkan konsumsi sumber daya terperinci untuk semua kueri Anda, seperti CPU, memori, dan I/O, Wawasan Performa Kueri memberi Anda cara yang cepat dan efisien untuk menentukan dampaknya pada konsumsi DTU secara keseluruhan untuk database Anda. Untuk informasi selengkapnya, lihat Azure SQL Database Wawasan Performa Kueri.

Default Penyimpanan Kueri di Azure SQL Database

Bagian ini menjelaskan default konfigurasi optimal dalam Azure SQL Database yang dirancang untuk memastikan pengoperasian Penyimpanan Kueri dan fitur dependen yang andal. Konfigurasi default dioptimalkan untuk pengumpulan data berkelanjutan, yaitu waktu minimal yang dihabiskan dalam status OFF/READ_ONLY. Untuk informasi selengkapnya tentang semua opsi Penyimpanan Kueri yang tersedia, lihat OPSI UBAH KUMPULAN DATABASE (SQL Transact).

Untuk menyesuaikan opsi ini saat beban kerja Anda bertambah, lihat Menjaga Penyimpanan Kueri tetap disesuaikan dengan beban kerja Anda nanti di artikel ini.

Konfigurasi	Deskripsi	Default	Komentar
MAX_STORAGE_SIZE_MB	Menentukan batas untuk ruang data yang bisa diambil Penyimpanan Kueri di dalam database pelanggan	100 sebelum SQL Server 2019 1024 dimulai dengan SQL Server 2019	Diberlakukan untuk database baru
INTERVAL_LENGTH_MINUTES	Menentukan ukuran jendela waktu di mana statistik runtime yang dikumpulkan untuk rencana kueri dikumpulkan dan dipertahankan. Setiap rencana kueri aktif memiliki paling banyak satu baris untuk jangka waktu yang ditentukan dengan konfigurasi ini	60	Diberlakukan untuk database baru
STALE_QUERY_THRESHOLD_DAYS	Kebijakan pembersihan berbasis waktu yang mengontrol periode retensi statistik runtime yang bertahan dan kueri yang tidak aktif	30	Diberlakukan untuk database dan database baru dengan default sebelumnya (367)
SIZE_BASED_CLEANUP_MODE	Menentukan apakah pembersihan data otomatis terjadi saat ukuran data Penyimpanan Kueri mendekati batas	AUTO	Diberlakukan untuk semua database
QUERY_CAPTURE_MODE	Menentukan apakah semua kueri atau hanya subkumpulan kueri yang dilacak	AUTO	Diberlakukan untuk semua database
DATA_FLUSH_INTERVAL_SECONDS	Menentukan periode maksimum di mana statistik runtime yang diambil disimpan dalam memori, sebelum memerah ke disk	900	Diberlakukan untuk database baru

Penting

Default ini secara otomatis diterapkan dalam tahap akhir aktivasi Penyimpanan Kueri dalam Azure SQL Database. Setelah diaktifkan, Azure SQL Database tidak akan mengubah nilai konfigurasi yang ditetapkan oleh pelanggan, kecuali mereka berdampak negatif pada beban kerja utama atau operasi yang andal dari Penyimpanan Kueri.

Catatan

Penyimpanan Kueri tidak dapat dinonaktifkan dalam database tunggal Azure SQL Database dan Kumpulan Elastis. ALTER DATABASE [database] SET QUERY_STORE = OFF Menjalankan akan mengembalikan peringatan'QUERY_STORE=OFF' is not supported in this version of SQL Server.

Jika Anda ingin tetap menggunakan pengaturan kustom Anda, gunakan opsi UBAH DATABASE dengan Penyimpanan Kueri untuk mengembalikan konfigurasi ke status sebelumnya. Lihat Praktik Terbaik dengan Penyimpanan Kueri untuk mempelajari cara memilih parameter konfigurasi yang optimal.

Menggunakan Penyimpanan Kueri dengan database Kumpulan Elastis

Anda bisa menggunakan Penyimpanan Kueri di semua database tanpa kekhawatiran, bahkan di kumpulan yang padat sekalipun. Semua masalah yang terkait dengan penggunaan sumber daya berlebihan yang mungkin telah terjadi ketika Penyimpanan Kueri diaktifkan untuk sejumlah besar database di kumpulan elastis telah diselesaikan.

Menjaga Penyimpanan Kueri tetap disesuaikan dengan beban kerja Anda

Konfigurasikan Penyimpanan Kueri berdasarkan beban kerja dan persyaratan pemecahan masalah performa Anda. Parameter default cukup baik untuk memulai, tetapi Anda harus memantau bagaimana Query Store beraksi dari waktu ke waktu dan menyesuaikan konfigurasinya.

Berikut adalah panduan yang harus diikuti untuk menetapkan nilai parameter:

Ukuran Maks (MB): Menentukan batas untuk ruang data yang diambil Penyimpanan Kueri di dalam database Anda. Ini adalah pengaturan terpenting yang secara langsung memengaruhi mode operasi Penyimpanan Kueri.

Sementara Penyimpanan Kueri mengumpulkan kueri, rencana eksekusi, dan statistik, ukurannya dalam database bertambah hingga batas ini tercapai. Ketika itu terjadi, Penyimpanan Kueri secara otomatis mengubah mode operasi menjadi baca-saja dan berhenti mengumpulkan data baru, yang berarti bahwa analisis performa Anda tidak lagi akurat.

Nilai default dalam SQL Server 2016 (13,x) dan SQL Server 2017 (14,x) adalah 100 MB. Ukuran ini mungkin tidak cukup jika beban kerja Anda menghasilkan sejumlah besar kueri dan rencana yang berbeda atau jika Anda ingin menyimpan riwayat kueri untuk jangka waktu yang lebih lama. Dimulai dengan SQL Server 2019 (15.x), nilai defaultnya adalah 1 GB. Lacak penggunaan ruang saat ini dan tingkatkan nilai Ukuran Maks (MB) untuk mencegah Penyimpanan Kueri beralih ke mode baca-saja.

Penting

Batas Ukuran Maksimum (MB) tidak diberlakukan secara ketat. Storage ukuran hanya dicentang saat Penyimpanan Kueri menulis data ke disk. Interval ini diatur oleh opsi Interval Flush Data (Menit). Jika Penyimpanan Kueri telah melanggar batas ukuran maksimum antara pemeriksaan ukuran penyimpanan, penyimpanan akan beralih ke mode baca-saja. Jika Mode Pembersihan Berbasis Ukuran diaktifkan, mekanisme pembersihan untuk memberlakukan batas ukuran maksimum juga dipicu.

Gunakan Management Studio atau jalankan skrip berikut untuk mendapatkan informasi terbaru tentang ukuran Penyimpanan Kueri:

USE [QueryStoreDB];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
 max_storage_size_mb, readonly_reason
FROM sys.database_query_store_options;

Skrip berikut menetapkan nilai baru untuk Ukuran Maks (MB):

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);

Interval Flush Data (Menit): Ini menentukan frekuensi untuk mempertahankan statistik runtime yang dikumpulkan ke disk. Ini diekspresikan dalam beberapa menit di antarmuka pengguna grafis (GUI), tetapi dalam Transact-SQL dinyatakan dalam hitung detik. Defaultnya adalah 900 detik, yaitu 15 menit di antarmuka pengguna grafis. Pertimbangkan untuk menggunakan nilai yang lebih tinggi jika beban kerja Anda tidak menghasilkan sejumlah besar kueri dan paket yang berbeda, atau jika Anda dapat menahan waktu yang lebih lama untuk mempertahankan data sebelum penonaktifan database.

Catatan

Menggunakan bendera pelacakan 7745 mencegah data Penyimpanan Kueri ditulis ke disk jika terjadi kegagalan atau perintah matikan. Untuk informasi selengkapnya, lihat bagian Menggunakan bendera pelacakan pada server yang sangat penting .

Gunakan SQL Server Management Studio atau Transact-SQL untuk mengatur nilai yang berbeda untuk Interval Flush Data:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900);

Interval Pengumpulan Statistik: Menentukan tingkat granularitas untuk statistik runtime yang dikumpulkan, yang dinyatakan dalam hitungan menit. Defaultnya adalah 60 menit. Pertimbangkan untuk menggunakan nilai yang lebih rendah jika Anda memerlukan granularitas yang lebih halus atau lebih sedikit waktu untuk mendeteksi dan mengurangi masalah. Perlu diingat bahwa nilai secara langsung memengaruhi ukuran data Penyimpanan Kueri. Gunakan SQL Server Management Studio atau Transact-SQL untuk menetapkan nilai yang berbeda untuk Interval Pengumpulan Statistik:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60);

Ambang Kueri Kedaluarsa (Hari): Kebijakan pembersihan berbasis waktu yang mengontrol periode retensi statistik runtime yang bertahan dan kueri tidak aktif, yang dinyatakan dalam beberapa hari. Secara default, Penyimpanan Kueri dikonfigurasi untuk menyimpan data selama 30 hari, yang mungkin tidak perlu panjang untuk skenario Anda.

Hindari menyimpan data historis yang tidak Anda rencanakan untuk digunakan. Praktik ini mengurangi perubahan pada status baca-saja. Ukuran data Penyimpanan Kueri dan waktu untuk mendeteksi dan mengurangi masalah akan lebih dapat diprediksi. Gunakan Management Studio atau skrip berikut untuk mengonfigurasi kebijakan pembersihan berbasis waktu:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90));

Mode Pembersihan Berbasis Ukuran: Menentukan apakah pembersihan data otomatis terjadi saat ukuran data Penyimpanan Kueri mendekati batas. Aktifkan pembersihan berbasis ukuran untuk memastikan bahwa Penyimpanan Kueri selalu berjalan dalam mode baca-tulis dan mengumpulkan data terbaru. Perhatikan bahwa tidak ada jaminan di bawah beban kerja berat bahwa pembersihan Penyimpanan Kueri akan secara konsisten mempertahankan ukuran data di bawah batas. Dimungkinkan agar pembersihan data otomatis tertinggal dan beralih (sementara) ke mode baca-saja.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

Mode Pengambilan Penyimpanan Kueri: Menentukan kebijakan pengambilan kueri untuk Penyimpanan Kueri.

• Semua: Menangkap semua kueri. Opsi ini adalah default di SQL Server 2016 (13.x) dan SQL Server 2017 (14.x).
• Otomatis: Jarang kueri dan kueri dengan durasi kompilasi dan eksekusi yang tidak signifikan diabaikan. Ambang batas untuk jumlah eksekusi, kompilasi, dan durasi runtime ditentukan secara internal. Dimulai dengan SQL Server 2019 (15.x), ini adalah opsi default.
• Tidak ada: Penyimpanan Kueri berhenti menangkap kueri baru.
• Kustom: Memungkinkan kontrol tambahan dan kemampuan untuk menyempurnakan kebijakan pengumpulan data. Pengaturan kustom baru menentukan apa yang terjadi selama ambang waktu kebijakan pengambilan internal. Ini adalah batas waktu di mana kondisi yang dapat dikonfigurasi dievaluasi dan, jika ada yang benar, kueri memenuhi syarat untuk ditangkap oleh Penyimpanan Kueri.

Penting

Kursor, kueri di dalam prosedur tersimpan, dan kueri yang dikompilasi secara asli selalu diambil saat Mode Pengambilan Penyimpanan Kueri diatur ke Semua, Otomatis, atau Kustom. Untuk mengambil kueri yang dikompilasi secara asli, aktifkan pengumpulan statistik per kueri dengan menggunakan sys.sp_xtp_control_query_exec_stats.

Skrip berikut ini mengatur QUERY_CAPTURE_MODE ke AUTO:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);

Contoh

Contoh berikut mengatur QUERY_CAPTURE_MODE ke OTOMATIS dan mengatur opsi lain yang direkomendasikan di SQL Server 2016 (13.x):

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60
    );

Contoh berikut mengatur QUERY_CAPTURE_MODE ke AUTO dan mengatur opsi lain yang direkomendasikan di SQL Server 2017 (14.x) untuk menyertakan statistik tunggu:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      QUERY_CAPTURE_MODE = AUTO,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON
    );

Contoh berikut mengatur QUERY_CAPTURE_MODE ke OTOMATIS dan mengatur opsi lain yang direkomendasikan di SQL Server 2019 (15.x), dan secara opsional mengatur kebijakan penangkapan KUSTOM dengan defaultnya, alih-alih mode penangkapan OTOMATIS default baru:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1000,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
        EXECUTION_COUNT = 30,
        TOTAL_COMPILE_CPU_TIME_MS = 1000,
        TOTAL_EXECUTION_CPU_TIME_MS = 100
      )
    );

Mulai dengan pemecahan masalah performa kueri

Alur kerja pemecahan masalah dengan Penyimpanan Kueri sederhana, seperti yang diperlihatkan dalam diagram berikut:

Aktifkan Penyimpanan Kueri dengan menggunakan Management Studio, seperti yang dijelaskan di bagian sebelumnya, atau jalankan pernyataan SQL Transact berikut:

ALTER DATABASE [DatabaseOne] SET QUERY_STORE = ON;

Diperlukan waktu hingga Query Store mengumpulkan himpunan data yang secara akurat mewakili beban kerja Anda. Biasanya, satu hari sudah cukup bahkan untuk beban kerja yang sangat kompleks. Namun, Anda dapat mulai menjelajahi data dan mengidentifikasi kueri yang memerlukan perhatian segera setelah mengaktifkan fitur tersebut. Buka subfolder Penyimpanan Kueri di bawah simpul database di Object Explorer Management Studio untuk membuka tampilan pemecahan masalah untuk skenario tertentu.

Management Studio tampilan Penyimpanan Kueri beroperasi dengan serangkaian metrik eksekusi, masing-masing dinyatakan sebagai salah satu fungsi statistik berikut:

Versi SQL Server	Metrik eksekusi	Fungsi statistik
SQL Server 2016 (13.x)	Waktu CPU, Durasi, Jumlah eksekusi, Pembacaan logis, Penulisan logis, Konsumsi memori, Pembacaan fisik, waktu CLR, Tingkat paralelisme (DOP), dan Jumlah baris	Rata-rata, Maksimum, Minimum, SimpangSimpangan Standar, Total
SQL Server 2017 (14.x)	Waktu CPU, Durasi, Jumlah eksekusi, Pembacaan logis, Penulisan logis, Konsumsi memori, Pembacaan fisik, waktu CLR, Tingkat paralelisme, Jumlah baris, Memori log, memori TempDB, dan Waktu tunggu	Rata-rata, Maksimum, Minimum, SimpangSimpangan Standar, Total

Grafik berikut ini memperlihatkan cara menemukan tampilan Penyimpanan Kueri:

Tabel berikut ini menjelaskan kapan harus menggunakan setiap tampilan Penyimpanan Kueri:

tampilan SQL Server Management Studio	Skenario
Kueri yang Diregresi	Menentukan kueri yang metrik eksekusinya baru-baru ini mengalami kemunculan (misalnya, berubah menjadi lebih buruk). Gunakan tampilan ini untuk menghubungkan masalah performa yang diamati di aplikasi Anda dengan kueri aktual yang perlu diperbaiki atau ditingkatkan.
Total Konsumsi Sumber Daya	Analisis total konsumsi sumber daya untuk database untuk salah satu metrik eksekusi. Gunakan tampilan ini untuk mengidentifikasi pola sumber daya (beban kerja harian vs. malam) dan mengoptimalkan konsumsi keseluruhan untuk database Anda.
Kueri yang Mengonsumsi Sumber Daya Teratas	Pilih metrik eksekusi yang menarik, dan identifikasi kueri yang memiliki nilai paling ekstrem untuk interval waktu yang disediakan. Gunakan tampilan ini untuk memfokuskan perhatian Anda pada kueri paling relevan yang memiliki dampak terbesar terhadap konsumsi sumber daya database.
Kueri Dengan Paket Paksa	Mencantumkan paket yang sebelumnya dipaksakan menggunakan Penyimpanan Kueri. Gunakan tampilan ini untuk mengakses semua paket paksa saat ini dengan cepat.
Kueri Dengan Variasi Tinggi	Analisis kueri dengan variasi eksekusi tinggi karena berkaitan dengan salah satu dimensi yang tersedia, seperti Durasi, waktu CPU, IO, dan penggunaan Memori, dalam interval waktu yang diinginkan. Gunakan tampilan ini untuk mengidentifikasi kueri dengan performa varian luas yang dapat memengaruhi pengalaman pengguna di seluruh aplikasi Anda.
Statistik Tunggu Kueri	Menganalisis kategori tunggu yang paling aktif dalam database dan kueri mana yang paling berkontribusi pada kategori tunggu yang dipilih. Gunakan tampilan ini untuk menganalisis statistik tunggu dan mengidentifikasi kueri yang mungkin memengaruhi pengalaman pengguna di seluruh aplikasi Anda. Berlaku untuk: Dimulai dengan SQL Server Management Studio v18.0 dan SQL Server 2017 (14.x).
Kueri Terlacak	Lacak eksekusi kueri terpenting secara real time. Biasanya, Anda menggunakan tampilan ini saat Anda memiliki kueri dengan paket paksa dan Anda ingin memastikan bahwa performa kueri stabil.

Tip

Untuk deskripsi terperinci tentang cara menggunakan Management Studio untuk mengidentifikasi kueri yang memakan sumber daya teratas dan memperbaiki kueri yang mengalami kemunculan karena perubahan pilihan rencana, lihat Query Store Azure Blogs.

Saat Anda mengidentifikasi kueri dengan performa suboptimal, tindakan Anda bergantung pada sifat masalah.

• Jika kueri dijalankan dengan beberapa rencana dan rencana terakhir secara signifikan lebih buruk dari rencana sebelumnya, Anda dapat menggunakan mekanisme pemaksaan rencana untuk memaksanya. SQL Server mencoba memaksa rencana di pengoptimal. Jika pemakaian rencana gagal, XEvent diaktifkan dan pengoptimal diinstruksikan untuk mengoptimalkan dengan cara normal.

  

  Catatan

  Grafik sebelumnya mungkin menampilkan bentuk yang berbeda untuk rencana kueri tertentu, dengan arti berikut untuk setiap status yang mungkin:
  

  Bentuk	Makna
  Lingkaran	Kueri selesai, yang berarti bahwa eksekusi reguler berhasil diselesaikan.
  Square	Dibatalkan, yang berarti bahwa eksekusi yang dibatalkan yang dimulai klien.
  Segitiga	Gagal, yang berarti bahwa pengecualian membatalkan eksekusi.

  Selain itu, ukuran bentuk mencerminkan jumlah eksekusi kueri dalam interval waktu yang ditentukan. Ukurannya meningkat dengan jumlah eksekusi yang lebih tinggi.

• Anda mungkin menyimpulkan bahwa kueri Anda kehilangan indeks untuk eksekusi optimal. Informasi ini muncul dalam rencana eksekusi kueri. Buat indeks yang hilang, dan periksa performa kueri dengan menggunakanQuery Store.

  

Jika Anda menjalankan beban kerja di SQL Database, daftar untuk SQL Database Index Advisor untuk menerima rekomendasi indeks secara otomatis.

• Dalam beberapa kasus, Anda mungkin memberlakukan kompilasi ulang statistik jika Anda melihat bahwa perbedaan antara perkiraan dan jumlah baris aktual dalam rencana eksekusi signifikan.
• Menulis ulang kueri yang bermasalah, misalnya, untuk memanfaatkan parameterisasi kueri atau menerapkan logika yang lebih optimal.

Tip

Di Azure SQL Database, pertimbangkan fitur Petunjuk Penyimpanan Kueri (Pratinjau) untuk memaksa petunjuk kueri pada kueri tanpa perubahan kode. Untuk informasi dan contoh selengkapnya, lihat Petunjuk Penyimpanan Kueri (Pratinjau).

Verifikasi bahwa Penyimpanan Kueri mengumpulkan data kueri terus menerus

Penyimpanan Kueri dapat mengubah mode operasi secara diam-diam. Pantau status Penyimpanan Kueri secara teratur untuk memastikan bahwa Penyimpanan Kueri beroperasi, dan mengambil tindakan untuk menghindari kegagalan karena penyebab yang dapat dicegah. Jalankan kueri berikut untuk menentukan mode operasi dan menampilkan parameter yang paling relevan:

USE [QueryStoreDB];
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Perbedaan antara actual_state_desc dan desired_state_desc menunjukkan bahwa perubahan mode operasi terjadi secara otomatis. Perubahan yang paling umum adalah agar Penyimpanan Kueri beralih secara diam-diam ke mode baca-saja. Dalam keadaan yang sangat jarang terjadi, Penyimpanan Kueri dapat berakhir dalam status ERROR karena kesalahan internal.

Saat status aktual bersifat baca-saja, gunakan readonly_reason kolom untuk menentukan akar penyebabnya. Biasanya, Anda menemukan bahwa Penyimpanan Kueri beralih ke mode baca-saja karena kuota ukuran terlampaui. Dalam hal ini, readonly_reason diatur ke 65536. Untuk alasan lain, lihat sys.database_query_store_options (Transact-SQL).

Pertimbangkan langkah-langkah berikut untuk mengalihkan Penyimpanan Kueri ke mode baca-tulis dan mengaktifkan pengumpulan data:

• Tingkatkan ukuran penyimpanan maksimum dengan menggunakan opsi MAX_STORAGE_SIZE_MB .ALTER DATABASE

• Bersihkan data Penyimpanan Kueri dengan menggunakan pernyataan berikut:

  ALTER DATABASE [QueryStoreDB] SET QUERY_STORE CLEAR;
  

Anda dapat menerapkan salah satu atau kedua langkah ini dengan menjalankan pernyataan berikut yang secara eksplisit mengubah mode operasi kembali ke baca-tulis:

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

Lakukan langkah-langkah berikut agar proaktif:

• Anda dapat mencegah perubahan mode operasi secara diam-diam dengan menerapkan praktik terbaik. Pastikan bahwa ukuran Penyimpanan Kueri selalu di bawah nilai yang diizinkan secara maksimal untuk secara dramatis mengurangi kemungkinan transisi ke mode baca-saja. Aktifkan kebijakan berbasis ukuran seperti yang dijelaskan di bagian Konfigurasi Penyimpanan Kueri sehingga Penyimpanan Kueri secara otomatis membersihkan data saat ukuran mendekati batas.
• Untuk memastikan bahwa data terbaru dipertahankan, konfigurasikan kebijakan berbasis waktu untuk menghapus informasi kedaluwarsa secara teratur.
• Terakhir, pertimbangkan untuk mengatur Mode Pengambilan Penyimpanan Kueri ke Otomatis karena memfilter kueri yang biasanya kurang relevan untuk beban kerja Anda.

Status KESALAHAN

Untuk memulihkan Penyimpanan Kueri, coba atur mode baca-tulis secara eksplisit dan periksa status aktual lagi.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Jika masalah berlanjut, itu menunjukkan bahwa kerusakan data Penyimpanan Kueri tetap ada di disk.

Dimulai dengan SQL Server 2017 (14.x), Penyimpanan Kueri dapat dipulihkan dengan menjalankan prosedur tersimpan sys.sp_query_store_consistency_check dalam database yang terpengaruh. Penyimpanan Kueri harus dinonaktifkan sebelum Anda mencoba operasi pemulihan. Berikut adalah contoh kueri untuk digunakan atau dimodifikasi untuk menyelesaikan pemeriksaan konsistensi dan pemulihan QDS:

IF EXISTS (SELECT * FROM sys.database_query_store_options WHERE actual_state=3) 
BEGIN
  BEGIN TRY
    ALTER DATABASE [QDS] SET QUERY_STORE = OFF
    Exec [QDS].dbo.sp_query_store_consistency_check
    ALTER DATABASE [QDS] SET QUERY_STORE = ON
    ALTER DATABASE [QDS] SET QUERY_STORE (OPERATION_MODE = READ_WRITE)
  END TRY
 
  BEGIN CATCH 
    SELECT  
      ERROR_NUMBER() AS ErrorNumber  
      ,ERROR_SEVERITY() AS ErrorSeverity  
      ,ERROR_STATE() AS ErrorState  
      ,ERROR_PROCEDURE() AS ErrorProcedure  
      ,ERROR_LINE() AS ErrorLine  
      ,ERROR_MESSAGE() AS ErrorMessage; 
  END CATCH;   
END

Untuk SQL Server 2016 (13.x), Anda perlu menghapus data dari Penyimpanan Kueri seperti yang diperlihatkan.

Jika pemulihan tidak berhasil, Anda bisa mencoba menghapus Penyimpanan Kueri sebelum Anda mengatur mode baca-tulis.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE CLEAR;
GO

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
GO

SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
    max_storage_size_mb, readonly_reason, interval_length_minutes,
    stale_query_threshold_days, size_based_cleanup_mode_desc,
    query_capture_mode_desc
FROM sys.database_query_store_options;

Mengatur Mode Pengambilan Penyimpanan Kueri yang optimal

Simpan data yang paling relevan di Penyimpanan Kueri. Tabel berikut ini menjelaskan skenario umum untuk setiap Mode Pengambilan Penyimpanan Kueri:

Mode Pengambilan Penyimpanan Kueri	Skenario
Semua	Analisis beban kerja Anda secara menyeluruh dalam hal semua bentuk kueri dan frekuensi eksekusinya dan statistik lainnya. Identifikasi kueri baru dalam beban kerja Anda. Deteksi apakah kueri ad-hoc digunakan untuk mengidentifikasi peluang untuk parameterisasi pengguna atau otomatis. Catatan: Ini adalah mode pengambilan default dalam SQL Server 2016 (13.x) dan SQL Server 2017 (14.x).
Auto	Fokuskan perhatian Anda pada kueri yang relevan dan dapat ditindakkan. Contohnya adalah kueri yang dijalankan secara teratur atau yang memiliki konsumsi sumber daya yang signifikan. Catatan: Dimulai dengan SQL Server 2019 (15.x), ini adalah mode pengambilan default.
Tidak ada	Anda telah menangkap kumpulan kueri yang ingin Anda pantau dalam runtime dan Anda ingin menghilangkan gangguan yang mungkin diperkenalkan oleh kueri lain. Tidak ada yang cocok untuk lingkungan pengujian dan tolok ukur. Tidak ada yang juga sesuai untuk vendor perangkat lunak yang mengirim konfigurasi Penyimpanan Kueri yang dikonfigurasi untuk memantau beban kerja aplikasi mereka. Tidak ada yang harus digunakan dengan hati-hati karena Anda mungkin melewatkan kesempatan untuk melacak dan mengoptimalkan kueri baru penting. Hindari menggunakan Tidak Ada kecuali Anda memiliki skenario tertentu yang memerlukannya.
Kustom	SQL Server 2019 (15.x) memperkenalkan mode Pengambilan kustom di ALTER DATABASE SET QUERY_STORE bawah perintah . Saat diaktifkan, konfigurasi Penyimpanan Kueri tambahan tersedia di bawah pengaturan kebijakan pengambilan Penyimpanan Kueri baru untuk menyempurnakan pengumpulan data di server tertentu. Pengaturan kustom baru menentukan apa yang terjadi selama ambang waktu kebijakan pengambilan internal. Ini adalah batas waktu di mana kondisi yang dapat dikonfigurasi dievaluasi dan, jika ada yang benar, kueri memenuhi syarat untuk ditangkap oleh Penyimpanan Kueri. Untuk informasi selengkapnya, lihat MENGUBAH Opsi SET DATABASE (SQL Transact).

Catatan

Kursor, kueri di dalam prosedur tersimpan, dan kueri yang dikompilasi secara asli selalu diambil saat Mode Pengambilan Penyimpanan Kueri diatur ke Semua, Otomatis, atau Kustom. Untuk mengambil kueri yang dikompilasi secara asli, aktifkan pengumpulan statistik per kueri dengan menggunakan sys.sp_xtp_control_query_exec_stats.

Menyimpan data yang paling relevan di Penyimpanan Kueri

Konfigurasikan Penyimpanan Kueri agar hanya berisi data yang relevan sehingga berjalan terus menerus dan memberikan pengalaman pemecahan masalah yang hebat dengan dampak minimal pada beban kerja reguler Anda.

Tabel berikut ini menyediakan praktik terbaik:

Praktik terbaik	Pengaturan
Membatasi data historis yang disimpan.	Konfigurasikan kebijakan berbasis waktu untuk mengaktifkan autocleanup.
Memfilter kueri yang tidak relevan.	Konfigurasikan Mode Pengambilan Penyimpanan Kueri ke Otomatis.
Hapus kueri yang kurang relevan saat ukuran maksimum tercapai.	Aktifkan kebijakan pembersihan berbasis ukuran.

Hindari menggunakan kueri yang tidak berparameter

Menggunakan kueri yang tidak berparameter saat tidak diperlukan bukanlah praktik terbaik. Contohnya adalah dalam kasus analisis ad-hoc. Paket singgahan tidak dapat digunakan kembali, yang memaksa Pengoptimal Kueri untuk mengkompilasi kueri untuk setiap teks kueri unik. Untuk informasi selengkapnya, lihat Panduan untuk menggunakan parameterisasi paksa.

Selain itu, Penyimpanan Kueri dapat dengan cepat melebihi kuota ukuran karena sejumlah besar teks kueri yang berbeda dan akibatnya sejumlah besar rencana eksekusi yang berbeda dengan bentuk yang sama. Akibatnya, performa beban kerja Anda kurang optimal, dan Penyimpanan Kueri mungkin beralih ke mode baca-saja atau terus-menerus menghapus data untuk mencoba mengikuti kueri yang masuk.

Pertimbangkan opsi berikut:

• Parameterisasi kueri jika berlaku. Misalnya, membungkus kueri di dalam prosedur tersimpan atau sp_executesql. Untuk informasi selengkapnya, lihat Parameter dan penggunaan kembali rencana eksekusi.
• Gunakan opsi optimalkan untuk beban kerja ad hoc jika beban kerja Anda berisi banyak batch ad-hoc penggunaan tunggal dengan rencana kueri yang berbeda.
  • Bandingkan jumlah nilai query_hash yang berbeda dengan jumlah total entri dalam sys.query_store_query. Jika rasionya mendekati 1, beban kerja ad-hoc Anda menghasilkan kueri yang berbeda.
• Terapkan parameterisasi paksa untuk database atau untuk subkumpulan kueri jika jumlah rencana kueri yang berbeda tidak besar.
  • Gunakan panduan rencana untuk memaksa parameterisasi hanya untuk kueri yang dipilih.
  • Konfigurasikan parameterisasi paksa dengan menggunakan perintah opsi database parameterisasi , jika ada sejumlah kecil rencana kueri yang berbeda dalam beban kerja Anda. Contohnya adalah ketika rasio antara hitungan query_hash yang berbeda dan jumlah total entri di sys.query_store_query jauh kurang dari 1.
• Atur QUERY_CAPTURE_MODE ke OTOMATIS untuk memfilter kueri ad-hoc secara otomatis dengan konsumsi sumber daya kecil.

Tip

Saat menggunakan solusi pemetaan Object-Relational (ORM) seperti Entity Framework (EF), kueri aplikasi seperti pohon kueri LINQ manual atau kueri SQL mentah tertentu mungkin tidak diparameterkan, yang berdampak pada penggunaan kembali rencana dan kemampuan untuk melacak kueri di Penyimpanan Kueri. Untuk informasi selengkapnya, lihat Penembolokan dan parameterisasi Kueri EF dan Kueri SQL Mentah EF.

Menemukan kueri yang tidak berparameter di Penyimpanan Kueri

Anda bisa menemukan jumlah paket yang disimpan di Penyimpanan Kueri menggunakan kueri di bawah ini, menggunakan DMV penyimpanan kueri, di SQL Server, Azure SQL Managed Instance, atau Azure SQL Database:

SELECT count(Pl.plan_id) AS plan_count, Qry.query_hash, Txt.query_text_id, Txt.query_sql_text
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
GROUP BY Qry.query_hash, Txt.query_text_id, Txt.query_sql_text
ORDER BY plan_count desc;

Sampel berikut membuat sesi Extended Events untuk menangkap peristiwa query_store_db_diagnostics, yang dapat berguna dalam mendiagnosis konsumsi sumber daya kueri. Di SQL Server, sesi kejadian yang diperluas ini membuat file peristiwa di folder log SQL Server secara default. Misalnya, dalam penginstalan default SQL Server 2019 (15.x) pada Windows, file peristiwa (file.xel) harus dibuat di folder C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log. Untuk Azure SQL Managed Instance, tentukan lokasi Azure Blob Storage sebagai gantinya. Untuk informasi selengkapnya, lihat event_file XEvent untuk Azure SQL Managed Instance. Acara 'qds.query_store_db_diagnostics' tidak tersedia untuk Azure SQL Database.

CREATE EVENT SESSION [QueryStore_Troubleshoot] ON SERVER 
ADD EVENT qds.query_store_db_diagnostics(
      ACTION(sqlos.system_thread_id,sqlos.task_address,sqlos.task_time,sqlserver.database_id,sqlserver.database_name))
ADD TARGET package0.event_file(SET filename=N'QueryStore',max_file_size=(100))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);

Dengan data ini Anda dapat menemukan jumlah paket di Penyimpanan Kueri, dan juga banyak statistik lainnya. Cari plan_countkolom , query_count, max_stmt_hash_map_size_kb, dan max_size_mb dalam data peristiwa, untuk memahami jumlah memori yang digunakan dan jumlah rencana yang dilacak oleh Penyimpanan Kueri. Jika jumlah paket lebih tinggi dari biasanya, hal ini dapat menunjukkan peningkatan kueri yang tidak berparameter. Gunakan kueri DMV Penyimpanan Kueri di bawah ini untuk meninjau kueri berparameter dan kueri non-parameter di Penyimpanan Kueri.

Untuk kueri berparameter:

SELECT qsq.query_id, qsqt.query_sql_text
FROM sys.query_store_query AS qsq 
INNER JOIN sys.query_store_query_text AS qsqt
ON qsq.query_text_id= qsqt.query_text_id 
WHERE qsq.query_parameterization_type<>0 or qsqt.query_sql_text like '%@%';

Untuk kueri yang tidak berparameter:

SELECT qsq.query_id, qsqt.query_sql_text
FROM sys.query_store_query AS qsq 
INNER JOIN sys.query_store_query_text AS qsqt
ON qsq.query_text_id= qsqt.query_text_id 
WHERE query_parameterization_type=0;

Hindari pola DROP dan CREATE untuk memuat objek

Penyimpanan Kueri mengaitkan entri kueri dengan objek yang berisi, seperti prosedur tersimpan, fungsi, dan pemicu. Saat Anda membuat ulang objek yang berisi, entri kueri baru dibuat untuk teks kueri yang sama. Ini mencegah Anda melacak statistik performa untuk kueri tersebut dari waktu ke waktu dan menggunakan mekanisme pemakaian rencana. Untuk menghindari situasi ini, gunakan ALTER <object> proses untuk mengubah definisi objek yang berisi jika memungkinkan.

Periksa status rencana paksa secara teratur

Memaksa rencana adalah mekanisme yang nyaman untuk memperbaiki performa untuk kueri penting dan membuatnya lebih mudah diprediksi. Seperti halnya petunjuk rencana dan panduan rencana, memaksa rencana bukanlah jaminan bahwa rencana tersebut akan digunakan dalam eksekusi di masa mendatang. Biasanya, ketika skema database berubah dengan cara yang direferensikan oleh rencana eksekusi diubah atau dihilangkan, pemakaian rencana mulai gagal. Dalam hal ini, SQL Server kembali ke kompilasi ulang kueri sementara alasan kegagalan memaksa aktual muncul di sys.query_store_plan. Kueri berikut mengembalikan informasi tentang rencana paksa:

USE [QueryStoreDB];
GO

SELECT p.plan_id, p.query_id, q.object_id as containing_object_id,
    force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q on p.query_id = q.query_id
WHERE is_forced_plan = 1;

Untuk mengetahui daftar lengkap alasannya, lihat sys.query_store_plan. Anda juga dapat menggunakan query_store_plan_forcing_failed XEvent untuk melacak dan memecahkan masalah kegagalan pemakaian rencana.

Tip

Di Azure SQL Database, pertimbangkan fitur Petunjuk Penyimpanan Kueri (Pratinjau) untuk memaksa petunjuk kueri pada kueri tanpa perubahan kode. Untuk informasi dan contoh selengkapnya, lihat Petunjuk Penyimpanan Kueri (Pratinjau).

Hindari mengganti nama database untuk kueri dengan rencana paksa

Rencana eksekusi mereferensikan objek dengan menggunakan nama tiga bagian seperti database.schema.object.

Jika Anda mengganti nama database, pemaksaan rencana gagal, yang menyebabkan kompilasi ulang di semua eksekusi kueri berikutnya.

Menggunakan Penyimpanan Kueri di server misi penting

Bendera pelacakan global 7745 dan 7752 dapat digunakan untuk meningkatkan ketersediaan database dengan menggunakan Penyimpanan Kueri. Untuk informasi selengkapnya, lihat Bendera pelacakan.

• Bendera pelacakan 7745 mencegah perilaku default di mana Penyimpanan Kueri menulis data ke disk sebelum SQL Server dapat dimatikan. Ini berarti bahwa data Penyimpanan Kueri yang telah dikumpulkan tetapi belum disimpan ke disk akan hilang, hingga jendela waktu yang ditentukan dengan DATA_FLUSH_INTERVAL_SECONDS.
• Bendera pelacakan 7752 memungkinkan beban asinkron Penyimpanan Kueri. Ini memungkinkan database menjadi online dan kueri dijalankan sebelum Penyimpanan Kueri telah sepenuhnya dipulihkan. Perilaku default adalah melakukan pemuatan penyimpanan kueri yang sinkron. Perilaku default mencegah kueri dieksekusi sebelum Penyimpanan Kueri dipulihkan tetapi juga mencegah kueri terlewatkan dalam pengumpulan data.

Catatan

Dimulai dengan SQL Server 2019 (15.x), perilaku ini dikendalikan oleh mesin, dan bendera pelacakan 7752 tidak berpengaruh.

Penting

Jika Anda menggunakan Penyimpanan Kueri untuk wawasan beban kerja just-in-time di SQL Server 2016 (13.x), rencanakan untuk menginstal peningkatan skalabilitas performa di SQL Server 2016 (13.x) SP2 CU2 (KB 4340759) sesegera mungkin. Tanpa peningkatan ini, ketika database berada di bawah beban kerja yang berat, ketidakcocokan spinlock dapat terjadi dan performa server mungkin menjadi lambat. Secara khusus, Anda mungkin melihat pertikaian QUERY_STORE_ASYNC_PERSIST berat pada spinlock atau SPL_QUERY_STORE_STATS_COOKIE_CACHE spinlock. Setelah penyempurnaan ini diterapkan, Penyimpanan Kueri tidak akan lagi menyebabkan ketidakcocokan spinlock.

Penting

Jika Anda menggunakan Penyimpanan Kueri untuk wawasan beban kerja just-in-time di SQL Server (SQL Server 2016 (13.x) hingga SQL Server 2017 (14.x)), rencanakan untuk menginstal peningkatan skalabilitas performa di SQL Server 2016 (13.x) SP2 CU15, SQL Server 2017 (14.x) CU23, dan SQL Server 2019 (15.x) CU9 sesegera mungkin. Tanpa peningkatan ini, ketika database berada di bawah beban kerja ad-hoc yang berat, Penyimpanan Kueri mungkin menggunakan sejumlah besar memori dan performa server mungkin menjadi lambat. Setelah peningkatan ini diterapkan, Penyimpanan Kueri memberlakukan batas internal untuk jumlah memori yang dapat digunakan berbagai komponennya, dan dapat secara otomatis mengubah mode operasi menjadi baca-saja sampai memori yang cukup telah dikembalikan ke Mesin Database. Perhatikan bahwa batas memori internal Penyimpanan Kueri tidak didokumenkan karena dapat berubah.

Menggunakan Penyimpanan Kueri di Azure SQL Database replikasi geografis aktif

Penyimpanan Kueri pada geo-replika aktif sekunder Azure SQL Database akan menjadi salinan aktivitas baca-saja pada replika utama.

Hindari tingkatan yang tidak cocok dengan replikasi geografis Azure SQL Database. Database sekunder harus berada di atau mendekati ukuran komputasi yang sama dari database utama, dan di tingkat layanan yang sama dari database utama. Cari jenis tunggu HADR_THROTTLE_LOG_RATE_MISMATCHED_SLO di sys.dm_db_wait_stats yang menunjukkan pembatasan laju log transaksi pada replika utama karena jeda sekunder.

Untuk informasi selengkapnya tentang memperkirakan dan mengonfigurasi ukuran database Azure SQL sekunder replikasi geografis aktif, lihat Mengonfigurasi database sekunder.

Lihat juga

• OPSI ALTER DATABASE SET (SQL Transact)
• Tampilan katalog Penyimpanan Kueri (SQL Transact)
• Prosedur tersimpan Penyimpanan Kueri (SQL Transact)
• Menggunakan Penyimpanan Kueri dengan In-Memory OLTP
• Panduan arsitektur pemrosesan kueri
• Petunjuk Penyimpanan Kueri

Langkah berikutnya

• Memantau Performa dengan menggunakan Penyimpanan Kueri
• Menyetel performa dengan menggunakan Penyimpanan Kueri