Praktik terbaik untuk mengelola Penyimpanan Kueri

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance

Artikel ini menguraikan manajemen Penyimpanan Kueri SQL Server dan fitur di sekitarnya.

Untuk informasi selengkapnya tentang mengonfigurasi dan mengelola dengan Penyimpanan Kueri, lihat Memantau performa dengan menggunakan Penyimpanan Kueri.

Catatan

Di SQL Server 2022 (16.x), Penyimpanan Kueri sekarang diaktifkan secara default untuk semua database SQL Server yang baru dibuat untuk membantu melacak riwayat performa dengan lebih baik, memecahkan masalah terkait rencana kueri, dan mengaktifkan kemampuan prosesor kueri baru.

Pengaturan Default Query Store di Azure SQL Database

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

Konfigurasi	Deskripsi	Bawaan	Komentar
UKURAN_PENYIMPANAN_MAKSIMAL_MB	Menetapkan batas untuk ruang data yang dapat digunakan Query Store di dalam database pelanggan.	100 sebelum SQL Server 2019 (15.x) 1000 dimulai dengan SQL Server 2019 (15.x)	Diberlakukan untuk database baru
PANJANG_INTERVAL_MENIT	Menentukan ukuran jendela waktu di mana statistik runtime yang dikumpulkan untuk rencana kueri dikumpulkan dan dipertahankan. Setiap paket kueri aktif memiliki paling banyak satu baris untuk jangka waktu yang ditentukan dengan konfigurasi ini	60	Diberlakukan untuk database baru
BATAS_HARI_QUERY_KADALUARSA	Kebijakan pembersihan berbasis waktu yang mengontrol periode retensi statistik runtime yang dipertahankan dan kueri tidak aktif	30	Diberlakukan untuk database baru dan database dengan default sebelumnya (367)
Mode Pembersihan Berdasarkan Ukuran	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 subset kueri yang dilacak	AUTO	Diberlakukan untuk semua database
INTERVAL_PEMBERSIHAN_DATA_DETIK	Menentukan periode maksimum di mana statistik runtime yang diambil disimpan dalam memori, sebelum disimpan ke disk	900	Diberlakukan untuk database baru

Penting

Default ini secara otomatis diterapkan dalam tahap akhir aktivasi Penyimpanan Kueri di 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

Query Store tidak dapat dinonaktifkan pada Azure SQL Database single database dan Elastic Pool. Menjalankan ALTER DATABASE [database] SET QUERY_STORE = OFF akan mengembalikan peringatan 'QUERY_STORE=OFF' is not supported in this version of SQL Server.

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

Atur Mode Penangkapan Query Store yang optimal

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

Mode Pengambilan Penyimpanan Kueri	Skenario
Semua	Analisis beban kerja Anda secara menyeluruh dalam hal semua bentuk kueri dan frekuensi eksekusinya dan statistik lainnya. Identifikasi pertanyaan baru dalam beban kerja Anda. Deteksi apakah kueri ad hoc digunakan untuk mengidentifikasi peluang untuk pengguna atau parameterisasi otomatis. Catatan: Ini adalah mode pengambilan default di 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: Di SQL Server 2019 (15.x) dan versi yang lebih baru, ini adalah mode pengambilan default.
Tidak	Anda telah mengambil 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. Pengaturan "None" ini juga sesuai untuk vendor perangkat lunak yang menyertakan konfigurasi Penyimpanan Kueri yang dikonfigurasi untuk memantau beban kerja aplikasi mereka. Penggunaan "None" harus dilakukan dengan hati-hati karena Anda bisa saja melewatkan kesempatan untuk melacak dan mengoptimalkan kueri baru yang 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 . Meskipun Otomatis adalah pengaturan default dan direkomendasikan, jika masih ada kekhawatiran tentang overhead yang mungkin diperkenalkan oleh Penyimpanan Kueri, administrator database dapat menggunakan kebijakan penangkapan yang disesuaikan untuk menyetel lebih lanjut perilaku penangkapan Penyimpanan Kueri. Untuk informasi dan rekomendasi selengkapnya, lihat kebijakan penangkapan kustom pada bagian selanjutnya dari artikel ini. Untuk informasi selengkapnya tentang sintaks ini, lihat ALTER DATABASE SET Options.

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.

Simpan 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.
Saring kueri yang tidak relevan.	Konfigurasikan Mode Pengambilan Query Store ke Mode Otomatis.
Hapus kueri yang kurang relevan saat ukuran maksimum tercapai.	Aktifkan kebijakan pembersihan berbasis ukuran.

Kebijakan pengambilan kustom

Saat Mode Pengambilan Penyimpanan Kueri KUSTOM diaktifkan, konfigurasi Penyimpanan Kueri tambahan tersedia di bawah pengaturan kebijakan penangkapan Penyimpanan Kueri baru untuk menyempurnakan pengumpulan data di server tertentu.

Pengaturan kustom baru menentukan apa yang terjadi pada ambang waktu kebijakan penangkapan internal. Ini adalah batas waktu tertentu di mana kondisi yang dapat dikonfigurasi dievaluasi dan, jika ada yang benar, kueri dapat ditangkap oleh Penyimpanan Kueri.

Mode Penangkapan Penyimpanan Kueri menentukan kebijakan penangkapan kueri untuk Penyimpanan Kueri.

Semua: Mengambil semua kueri. Opsi ini adalah default di SQL Server 2016 (13.x) dan SQL Server 2017 (14.x).
Otomatis: Kueri yang jarang dilakukan dan memiliki durasi kompilasi serta eksekusi yang tidak signifikan akan diabaikan. Ambang batas untuk jumlah eksekusi, kompilasi, dan durasi runtime ditentukan secara internal. Dimulai dengan SQL Server 2019 (15.x), ini adalah opsi default.
None: Query Store berhenti menangkap kueri baru.
Kustom: Memungkinkan kontrol tambahan dan kemampuan untuk menyempurnakan kebijakan pengumpulan data. Pengaturan khusus baru menentukan apa yang terjadi selama batas waktu kebijakan penangkapan internal. Ini adalah periode waktu di mana kondisi yang dapat dikonfigurasi dievaluasi, dan jika ada yang benar, kueri memenuhi syarat untuk ditangkap oleh Penyimpanan Kueri.

Menyetel kebijakan penangkapan kustom yang sesuai untuk lingkungan Anda harus dipertimbangkan saat:

Database sangat besar.
Database memiliki sejumlah besar kueri ad hoc unik.
Database memiliki batasan ukuran atau pertumbuhan tertentu.

SSMS
T-SQL

Unduh versi terbaru SQL Server Management Studio (SSMS)

Untuk melihat pengaturan saat ini di Management Studio:

Di SQL Server Management Studio Object Explorer, klik kanan pada database.
Pilih Properti.
Pilih Query Store. Pada halaman Query Store, verifikasi bahwa Mode Operasi (Diminta) adalah Baca tulis.
Ubah Modus Pengambilan Penyimpanan Kueri menjadi Kustom.
Perhatikan keempat bidang kebijakan pengambilan yang ada di bawah Kebijakan Pengambilan Penyimpanan Kueri sekarang diaktifkan dan dapat dikonfigurasi.

Contoh kebijakan penangkapan kustom

Contoh berikut mengatur QUERY_CAPTURE_MODE ke OTOMATIS dan mengatur mode pengambilan kustom. Masing-masing berikut menetapkan kebijakan penangkapan kustom ke nilai defaultnya di SQL Server 2022 (16.x). Pertimbangkan untuk menyesuaikan nilai-nilai ini untuk mengurangi jumlah kueri yang diambil, dan oleh karena itu kurangi jejak pada disk Penyimpanan Kueri. Disarankan untuk mengubah nilai-nilai ini secara bertahap dengan kenaikan kecil.

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,
      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
      )
    );

Contoh kueri berikut mengubah Penyimpanan Kueri yang ada dengan menggunakan kebijakan penangkapan kustom yang menggantikan pengaturan default untuk EXECUTION_COUNT dan TOTAL_COMPILE_CPU_TIME_MS.

ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE = ON
    (
      QUERY_CAPTURE_MODE = CUSTOM,
      QUERY_CAPTURE_POLICY = (
        EXECUTION_COUNT = 100,
        TOTAL_COMPILE_CPU_TIME_MS = 10000
      )
    );

Ukuran maksimal Penyimpanan Kueri

Nilai ukuran maksimum default Penyimpanan Kueri adalah 1000 MB, dimulai di SQL Server 2019 (15.x). Di versi sebelumnya, defaultnya adalah 100 MB. Meningkatkan batas ukuran maksimum Penyimpanan Kueri tepat dilakukan untuk database yang sibuk dengan banyak rencana kueri unik. Menyesuaikan kebijakan pengambilan (lihat bagian sebelumnya) adalah pertimbangan yang lebih penting untuk membatasi ukuran Query Store di disk dan untuk mencegah Query Store memasuki mode READ_ONLY. Sementara Penyimpanan Kueri mengumpulkan kueri, rencana eksekusi, dan statistik, ukurannya di dalam basis data bertambah hingga batas tersebut tercapai. Ketika itu terjadi, Penyimpanan Kueri secara otomatis mengubah mode operasi menjadi READ_ONLY dan berhenti mengumpulkan data baru, yang berarti bahwa analisis performa Anda tidak lagi akurat.

Di SQL Server dan Azure SQL Managed Instance, MAX_STORAGE_SIZE_MB batasan tidak diberlakukan secara ketat.
Di Azure SQL Database, nilai maksimum yang diizinkan MAX_STORAGE_SIZE_MB adalah 10.240 MB.

Ukuran penyimpanan diperiksa hanya saat Penyimpanan Kueri menulis data ke disk. Interval ini diatur oleh opsi DATA_FLUSH_INTERVAL_SECONDS atau opsi dialog Interval Pembilasan Data Penyimpanan Kueri di Management Studio.

Nilai default interval adalah 900 detik (atau 15 menit).
Jika Penyimpanan Kueri telah melanggar MAX_STORAGE_SIZE_MB batas antara pemeriksaan ukuran penyimpanan, penyimpanan akan beralih ke mode baca-saja.
Jika SIZE_BASED_CLEANUP_MODE diaktifkan, mekanisme pembersihan juga dipicu untuk memberlakukan batas MAX_STORAGE_SIZE_MB.
- Setelah cukup ruang dibersihkan, mode Penyimpanan Kueri akan secara otomatis beralih kembali ke mode READ_WRITE.

Untuk informasi selengkapnya, lihat ALTER DATABASE SET OPTION MAX_STORAGE_SIZE_MB.

Interval Pembilasan Data (menit)

"Interval Pembilasan Data menentukan frekuensi ketika statistik runtime yang dikumpulkan disimpan ke disk." Di SQL Server Management Studio, nilainya dalam hitungan menit, tetapi dalam Transact-SQL dinyatakan dalam hitungan detik. Defaultnya adalah 15 menit (900 detik).

Meningkatkan interval flush data dapat mengurangi dampak I/O penyimpanan pada Query Store secara keseluruhan, tetapi menyebabkan beban kerja I/O penyimpanan menjadi lebih tidak teratur, dengan dampak yang lebih jarang tetapi lebih berat terhadap pemanfaatan disk. 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 database dimatikan.
Mengurangi interval flush data mengurangi jumlah data Penyimpanan Kueri yang akan hilang jika terjadi pematian, kehilangan daya, atau failover. Ini juga dapat menghaluskan dampak I/O penyimpanan akibat Query Store dengan menulis ke disk lebih sering, tetapi dengan lebih sedikit data.

Catatan

Menggunakan bendera pelacakan 7745 mencegah data Penyimpanan Kueri ditulis ke disk jika terjadi kegagalan atau perintah matikan. Untuk informasi selengkapnya, lihat Gunakan Penyimpanan Kueri di server misi penting.

Mengubah pengaturan bawaan Penyimpanan Kueri

Konfigurasikan Query Store berdasarkan beban kerja dan kebutuhan pemecahan masalah kinerja Anda. Parameter default cukup baik untuk memulai, tetapi Anda harus memantau bagaimana Query Store bersifat dari waktu ke waktu dan menyesuaikan konfigurasinya.

Menampilkan pengaturan Penyimpanan Kueri saat ini

Lihat pengaturan Penyimpanan Kueri saat ini di SQL Server Management Studio (SSMS) atau T-SQL.

SSMS
T-SQL

Unduh versi terbaru SQL Server Management Studio (SSMS)

Untuk melihat pengaturan saat ini di Management Studio:

Di SQL Server Management Studio Object Explorer, klik kanan pada database.
Pilih Properti.
Pilih Penyimpanan Kueri.

Jalankan skrip T-SQL 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);

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

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

Interval Pengumpulan Statistik: Menentukan tingkat detail untuk statistik waktu nyata yang dikumpulkan, dinyatakan dalam 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 Usang (Hari): Kebijakan pembersihan berbasis waktu yang mengontrol periode retensi statistik runtime yang tersimpan dan kueri tidak aktif, yang dinyatakan dalam satuan hari. Secara bawaan, Penyimpanan Kueri dikonfigurasi untuk menyimpan data selama 30 hari, yang mungkin terlalu lama 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 Berdasarkan Ukuran: Menentukan apakah pembersihan data otomatis terjadi saat ukuran data pada Query Store mendekati batas. Aktifkan pembersihan berdasarkan ukuran untuk memastikan bahwa Penyimpanan Kueri selalu berjalan dalam mode baca dan tulis serta mengumpulkan data terbaru. 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 penangkapan kueri untuk Query Store.

Semua: Mencakup semua kueri. Opsi ini adalah default di SQL Server 2016 (13.x) dan SQL Server 2017 (14.x).
Otomatis: Kueri yang jarang dengan waktu 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: Query Store berhenti menangkap kueri baru.
Kustom: Memungkinkan kontrol tambahan dan kemampuan untuk menyempurnakan kebijakan pengumpulan data. Pengaturan kustom baru menentukan apa yang terjadi selama periode waktu kebijakan pengambilan data internal. Ini adalah batas waktu di mana kondisi yang dapat dikonfigurasi dievaluasi dan, jika ada yang benar, kueri memenuhi syarat untuk ditangkap oleh Query Store.

Penting

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 kebijakan penangkapan KUSTOM ke default SQL Server 2019 (15.x), bukan mode penangkapan OTOMATIS default baru. Untuk informasi selengkapnya tentang opsi dan default kebijakan pengambilan kustom, lihat <query_capture_policy_option_list>.

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

Pemeliharaan Toko Kueri

Bagian ini memberikan beberapa panduan tentang pengelolaan fitur Query Store itu sendiri.

Status Penyimpanan Kueri

Query Store menyimpan datanya di dalam database pengguna dan itulah sebabnya memiliki batas ukuran (dikonfigurasi dengan MAX_STORAGE_SIZE_MB). Jika data di Penyimpanan Kueri mencapai batas tersebut, Penyimpanan Kueri akan secara otomatis mengubah status dari baca-tulis menjadi baca-saja dan berhenti mengumpulkan data baru.

Gunakan kueri pada sys.database_query_store_options untuk memeriksa apakah Query Store saat ini aktif dan apakah sedang mengumpulkan statistik runtime.

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

Status Penyimpanan Kueri ditentukan oleh kolom actual_state. Jika berbeda dari status yang diinginkan, readonly_reason kolom dapat memberi Anda informasi lebih lanjut. Ketika ukuran Penyimpanan Kueri melebihi kuota, fitur akan beralih ke mode read_only dan memberikan alasan. Untuk informasi tentang alasannya, lihat sys.database_query_store_options.

Dapatkan opsi Penyimpanan Kueri

Untuk mengetahui informasi terperinci tentang status Penyimpanan Kueri, jalankan perintah berikut dalam database pengguna.

SELECT * FROM sys.database_query_store_options;

Mengatur interval Penyimpanan Kueri

Anda dapat mengganti interval untuk menggabungkan statistik waktu operasi kueri (defaultnya adalah 60 menit). Nilai baru untuk interval diekspos melalui sys.database_query_store_options tampilan.

ALTER DATABASE <database_name>
SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 15);

Nilai arbitrer tidak diperbolehkan untuk INTERVAL_LENGTH_MINUTES. Gunakan salah satu interval berikut: 1, 5, 10, 15, 30, 60, atau 1440 menit.

Catatan

Untuk Azure Synapse Analytics, menyesuaikan opsi konfigurasi Query Store, seperti yang ditunjukkan di bagian ini, tidak disediakan.

Penggunaan ruang Penyimpanan Kueri

Untuk memeriksa ukuran dan batas Penyimpanan Kueri saat ini, jalankan pernyataan berikut dalam database pengguna.

SELECT current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

Jika penyimpanan Query Store penuh, gunakan pernyataan berikut untuk memperluas kapasitas.

ALTER DATABASE <database_name>
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = <new_size>);

Mengatur opsi Penyimpanan Kueri

Anda dapat mengatur beberapa opsi Penyimpanan Kueri secara bersamaan dengan satu pernyataan ALTER DATABASE.

ALTER DATABASE <database name>
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 3000,
    MAX_STORAGE_SIZE_MB = 500,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 1000,
    WAIT_STATS_CAPTURE_MODE = ON
);

Untuk daftar lengkap opsi konfigurasi, lihat MENGUBAH Opsi SET DATABASE (Transact-SQL).

Bersihkan ruang

Tabel internal Penyimpanan Kueri dibuat di grup file PRIMARY selama pembuatan database dan konfigurasi tersebut tidak dapat diubah nanti. Jika Anda kehabisan ruang, Anda mungkin ingin menghapus data Penyimpanan Kueri yang lebih lama dengan menggunakan pernyataan berikut.

ALTER DATABASE <db_name> SET QUERY_STORE CLEAR;

Atau, Anda mungkin ingin menghapus data kueri ad hoc saja, karena kurang relevan untuk pengoptimalan kueri dan analisis rencana tetapi tetap memakan ruang yang sama banyak.

Di Azure Synapse Analytics, menghapus Penyimpanan Kueri tidak tersedia. Data disimpan secara otomatis selama tujuh hari terakhir.

Menghapus kueri ad hoc

Ini membersihkan kueri ad hoc dan internal dari Penyimpanan Kueri agar tidak kehabisan ruang dan menghapus kueri yang benar-benar perlu kita lacak.

SET NOCOUNT ON
-- This purges adhoc and internal queries from
-- the Query Store in the current database
-- so that the Query Store does not run out of space
-- and remove queries we really need to track

DECLARE @id int;
DECLARE adhoc_queries_cursor CURSOR
FOR
    SELECT q.query_id
    FROM sys.query_store_query_text AS qt
    JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
    JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
    JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
    WHERE q.is_internal_query = 1  -- is it an internal query then we dont care to keep track of it
       OR q.object_id = 0 -- if it does not have a valid object_id then it is an adhoc query and we don't care about keeping track of it
    GROUP BY q.query_id
    HAVING MAX(rs.last_execution_time) < DATEADD (minute, -5, GETUTCDATE())  -- if it has been more than 5 minutes since the adhoc query ran
    ORDER BY q.query_id;
OPEN adhoc_queries_cursor ;
FETCH NEXT FROM adhoc_queries_cursor INTO @id;
WHILE @@fetch_status = 0
BEGIN
    PRINT 'EXEC sp_query_store_remove_query ' + str(@id);
    EXEC sp_query_store_remove_query @id;
    FETCH NEXT FROM adhoc_queries_cursor INTO @id;
END
CLOSE adhoc_queries_cursor;
DEALLOCATE adhoc_queries_cursor;

Anda dapat menentukan prosedur Anda sendiri dengan logika yang berbeda untuk menghapus data yang tidak lagi Anda inginkan.

Contoh sebelumnya menggunakan sp_query_store_remove_query prosedur tersimpan yang diperluas untuk menghapus data yang tidak perlu. Anda juga dapat:

Gunakan sp_query_store_reset_exec_stats untuk membersihkan statistik runtime untuk rencana tertentu.
Gunakan sp_query_store_remove_plan untuk menghapus satu rencana.

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2025-03-05

Bagikan melalui

Praktik terbaik untuk mengelola Penyimpanan Kueri

Pengaturan Default Query Store di Azure SQL Database

Atur Mode Penangkapan Query Store yang optimal

Simpan data yang paling relevan di Penyimpanan Kueri

Kebijakan pengambilan kustom

Contoh kebijakan penangkapan kustom

Ukuran maksimal Penyimpanan Kueri

Interval Pembilasan Data (menit)

Mengubah pengaturan bawaan Penyimpanan Kueri

Menampilkan pengaturan Penyimpanan Kueri saat ini

Contoh

Pemeliharaan Toko Kueri

Status Penyimpanan Kueri

Dapatkan opsi Penyimpanan Kueri

Mengatur interval Penyimpanan Kueri

Penggunaan ruang Penyimpanan Kueri

Mengatur opsi Penyimpanan Kueri

Bersihkan ruang

Menghapus kueri ad hoc

Konten terkait

Saran dan Komentar

Sumber Daya Tambahan: