Praktik terbaik untuk mengelola Penyimpanan Kueri

Artikel
01/08/2024

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.

Default Penyimpanan Kueri 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	Default	Komentar
MAX_STORAGE_SIZE_MB	Menentukan batas ruang data yang dapat diambil Penyimpanan Kueri di dalam database pelanggan	100 sebelum SQL Server 2019 (15.x) 1000 dimulai dengan SQL Server 2019 (15.x)	Diberlakukan untuk database baru
INTERVAL_LENGTH_MINUTES	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
STALE_QUERY_THRESHOLD_DAYS	Kebijakan pembersihan berbasis waktu yang mengontrol periode retensi statistik runtime yang dipertahankan dan kueri 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 subset 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 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

Penyimpanan Kueri tidak dapat dinonaktifkan di 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 UBAH DATABASE dengan opsi Penyimpanan Kueri untuk mengembalikan konfigurasi ke status sebelumnya. Lihat Praktik Terbaik dengan Penyimpanan Kueri untuk mempelajari cara memilih parameter konfigurasi yang optimal.

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 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. 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 yang penting. Hindari menggunakan Tidak Ada kecuali Anda memiliki skenario tertentu yang memerlukannya.
Adat	SQL Server 2019 (15.x) memperkenalkan mode pengambilan kustom di `ALTER DATABASE ... SET QUERY_STORE` bawah perintah . Meskipun Otomatis default dan direkomendasikan, jika masih ada kekhawatiran tentang Penyimpanan Kueri overhead yang mungkin diperkenalkan, administrator database dapat menggunakan kebijakan penangkapan kustom untuk menyetel perilaku penangkapan Penyimpanan Kueri lebih lanjut. Untuk informasi dan rekomendasi selengkapnya, lihat Kebijakan penangkapan kustom nanti di artikel ini. Untuk informasi selengkapnya tentang sintaks ini, lihat Mengubah Opsi SET DATABASE.

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.

Kebijakan penangkapan 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 selama ambang waktu kebijakan pengambilan internal. Ini adalah batas waktu dengan kondisi yang dapat dikonfigurasi dievaluasi dan, jika ada yang benar, kueri memenuhi syarat untuk ditangkap oleh Penyimpanan Kueri.

Mode Pengambilan Penyimpanan Kueri menentukan kebijakan pengambilan 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 dan kueri yang jarang dengan kompilasi dan durasi 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 dengan 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

Gunakan 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. Pada halaman Penyimpanan Kueri, verifikasi bahwa Mode Operasi (Diminta) adalah Baca tulis.
Ubah Mode Pengambilan Penyimpanan Kueri menjadi Kustom.
Perhatikan empat bidang kebijakan pengambilan di bawah Kebijakan Penangkapan 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 untuk menggunakan kebijakan penangkapan kustom yang mengambil alih 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 maksimum 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 sesuai dalam database sibuk dengan banyak rencana kueri unik. Menyesuaikan kebijakan pengambilan (lihat bagian sebelumnya) adalah pertimbangan yang lebih penting untuk membatasi ukuran pada disk Penyimpanan Kueri dan untuk mencegah Penyimpanan Kueri memasuki mode READ_ONLY. 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 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 dicentang hanya saat Penyimpanan Kueri menulis data ke disk. Interval ini diatur oleh DATA_FLUSH_INTERVAL_SECONDS opsi atau opsi dialog Penyimpanan Kueri Studio Manajemen Interval Flush Data.

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 untuk memberlakukan MAX_STORAGE_SIZE_MB batas juga dipicu.
- Setelah cukup ruang dibersihkan, mode Penyimpanan Kueri akan secara otomatis beralih kembali ke mode READ_WRITE.

Untuk informasi selengkapnya, lihat MENGUBAH OPSI SET DATABASE MAX_STORAGE_SIZE_MB.

Interval Flush Data (menit)

Interval Flush Data menentukan frekuensi sebelum statistik runtime yang dikumpulkan dipertahankan 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 Penyimpanan Kueri secara keseluruhan, tetapi menyebabkan beban kerja I/O penyimpanan lebih lonjakan, dengan dampak yang lebih sedikit 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 pematian database.
Mengurangi interval flush data mengurangi jumlah data Penyimpanan Kueri yang akan hilang jika terjadi pematian, kehilangan daya, atau failover. Ini juga dapat memperlancar dampak I/O penyimpanan dari Penyimpanan Kueri 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 Menggunakan Penyimpanan Kueri di server misi penting.

Mengubah default Penyimpanan Kueri

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

Gunakan 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 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 dipertahankan dan kueri tidak aktif, yang dinyatakan dalam hari. Secara default, Penyimpanan Kueri dikonfigurasi untuk menyimpan data selama 30 hari, yang mungkin tidak perlu 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 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. 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: Mengambil semua kueri. Opsi ini adalah default di SQL Server 2016 (13.x) dan SQL Server 2017 (14.x).
Otomatis: Kueri dan kueri yang jarang dengan kompilasi dan durasi 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 dengan kondisi yang dapat dikonfigurasi dievaluasi dan, jika ada yang benar, kueri memenuhi syarat untuk ditangkap oleh Penyimpanan Kueri.

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

Bagian ini menyediakan beberapa panduan tentang mengelola fitur Penyimpanan Kueri itu sendiri.

Status Penyimpanan Kueri

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

Kueri sys.database_query_store_options untuk menentukan apakah Penyimpanan Kueri saat ini aktif, dan apakah saat ini mengumpulkan statistik runtime atau tidak.

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 actual_state kolom. 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 mengikuti dalam database pengguna.

SELECT * FROM sys.database_query_store_options;

Mengatur interval Penyimpanan Kueri

Anda dapat mengambil alih interval untuk menggabungkan statistik runtime 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 Penyimpanan Kueri, seperti yang ditunjukkan di bagian ini, tidak didukung.

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 Penyimpanan Kueri penuh, gunakan pernyataan berikut untuk memperluas penyimpanan.

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

Mengatur opsi Penyimpanan Kueri

Anda bisa mengatur beberapa opsi Penyimpanan Kueri sekaligus 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 hanya ingin menghapus data kueri ad hoc, karena kurang relevan untuk pengoptimalan kueri dan analisis rencana tetapi hanya membutuhkan banyak ruang.

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

Menghapus kueri ad hoc

Ini menghapus menyeluruh kueri ad hoc dan internal dari Penyimpanan Kueri sehingga Penyimpanan Kueri 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 menghapus statistik runtime untuk paket tertentu.
Gunakan sp_query_store_remove_plan untuk menghapus satu paket.

Praktik terbaik untuk mengelola Penyimpanan Kueri

Default Penyimpanan Kueri di Azure SQL Database

Mengatur Mode Pengambilan Penyimpanan Kueri yang optimal

Menyimpan data yang paling relevan di Penyimpanan Kueri

Kebijakan penangkapan kustom

Contoh kebijakan penangkapan kustom

Ukuran maksimum Penyimpanan Kueri

Interval Flush Data (menit)

Mengubah default Penyimpanan Kueri

Menampilkan pengaturan Penyimpanan Kueri saat ini

Contoh

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

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan:

Praktik terbaik untuk mengelola Penyimpanan Kueri

Default Penyimpanan Kueri di Azure SQL Database

Mengatur Mode Pengambilan Penyimpanan Kueri yang optimal

Menyimpan data yang paling relevan di Penyimpanan Kueri

Kebijakan penangkapan kustom

Contoh kebijakan penangkapan kustom

Ukuran maksimum Penyimpanan Kueri

Interval Flush Data (menit)

Mengubah default Penyimpanan Kueri

Menampilkan pengaturan Penyimpanan Kueri saat ini

Contoh

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

Saran dan Komentar

Sumber Daya Tambahan: