Memantau performa dengan menggunakan Penyimpanan Kueri

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

Fitur Penyimpanan Kueri memberi Anda wawasan tentang pilihan dan performa rencana kueri untuk SQL Server, Azure SQL Database, Azure SQL Managed Instance, dan Azure Synapse Analytics. Penyimpanan Kueri menyederhanakan pemecahan masalah performa dengan membantu Anda menemukan perbedaan performa yang disebabkan oleh perubahan rencana kueri dengan cepat. Query Store secara otomatis menangkap riwayat kueri, paket, dan statistik runtime, serta mempertahankannya untuk tinjauan 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 UBAH KUMPULAN DATABASE .

• Untuk informasi tentang mengoperasikan Penyimpanan Kueri di Azure SQL Database, lihat Mengoperasikan Penyimpanan Kueri di Azure SQL Database.
• Untuk informasi tentang menemukan informasi yang dapat ditindak lanjuti dan menyetel performa dengan Penyimpanan Kueri, lihat Menyetel performa dengan Penyimpanan Kueri.
• Untuk informasi tentang membentuk rencana kueri tanpa mengubah kode aplikasi, lihat Petunjuk Penyimpanan Kueri.

Penting

Jika Anda menggunakan Query Store untuk wawasan beban kerja just in time di SQL Server 2016 (13.x), rencanakan untuk menginstal perbaikan skalabilitas performa di KB 4340759 sesegera mungkin.

Mengaktifkan Penyimpanan Kueri

• Penyimpanan Kueri diaktifkan secara default untuk database Azure SQL Database dan Azure SQL Managed Instance baru.
• Penyimpanan Kueri tidak diaktifkan secara default untuk pratinjau SQL Server 2016 (13.x), SQL Server 2017 (14.x), SQL Server 2019 (15.x), atau 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 Pratinjau SQL Server 2022 (16.x), sebaiknya aktifkan Penyimpanan Kueri pada database baru dan yang sudah ada.
• Query Store tidak diaktifkan secara default untuk database Azure Synapse Analytics baru.

Gunakan halaman Penyimpanan Kueri di SQL Server Management Studio

1. Di Object Explorer, klik kanan database, lalu pilih Properti.

   Catatan

   Membutuhkan setidaknya Management Studio versi 16.

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 tetap disesuaikan dengan beban kerja Anda, lihat Praktik Terbaik dengan Penyimpanan Kueri.

Informasi di Penyimpanan Kueri

Rencana eksekusi untuk kueri tertentu dalam 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 rencana 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 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.

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 mundur menunggu ke kueri tertentu. Dimulai dengan SQL Server 2017 (14.x) dan Azure SQL Database, Penyimpanan Kueri 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 Pratinjau 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. Saat 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.

Catatan

Set replika atau grup replika: Set replika didefinisikan sebagai semua replika yang tidak disebutkan namanya yang berbagi peran (primer, sekunder, geo sekunder, geo primer), atau sebagai replika bernama individu.

Data yang disimpan tentang kueri dapat dianalisis sebagai beban kerja berdasarkan set replika. Penyimpanan Kueri untuk replika menyediakan kemampuan untuk memantau dan menyesuaikan performa beban kerja unik dan baca-saja yang mungkin dijalankan terhadap replika sekunder.

Mengaktifkan Penyimpanan Kueri untuk replika sekunder

Sebelum menggunakan Penyimpanan Kueri untuk replika sekunder, Anda harus menyiapkan dan mengonfigurasi grup ketersediaan AlwaysOn .

Penting

BERLAKU UNTUK: SQL Server 2022 (16.x) CTP 2.x

Anda harus mengaktifkan set bendera pelacakan berikut sebelum Anda dapat mengaktifkan Penyimpanan Kueri untuk replika sekunder: 12606, 12606, 12607, 12608, 12610, T12624. Untuk mengaktifkan bendera pelacakan ini:

1. Buka konsol manajemen layanan (services.msc dari menu Jalankan ).
2. Klik kanan pada layanan SQL Server untuk SQL Server CTP 2 2022 dan pilih Properti.
3. Jika status layanan Berjalan, pilih Hentikan. Ini akan menghentikan instans yang diinstal.
4. Dalam kotak Mulai parameter , tambahkan nilai: -T12606 -T12607 -T12608 -T12610 -T12624
5. Pilih Mulai untuk memulai layanan.
6. PilihOK.

Aktifkan Penyimpanan Kueri untuk replika sekunder menggunakan opsi ALTER DATABASE SET (Transact-SQL). Contoh berikut mengaktifkan Penyimpanan Kueri pada database utama, lalu pada replika sekunder. Untuk menjalankan kode ini, sambungkan ke database pada replika utama.

ALTER DATABASE CURRENT SET QUERY_STORE = ON;
GO

ALTER DATABASE CURRENT  
FOR SECONDARY SET QUERY_STORE = ON ( 
        OPERATION_MODE = READ_WRITE 
);
GO

Anda dapat memvalidasi bahwa Penyimpanan Kueri diaktifkan pada replika sekunder dengan menyambungkan ke database pada replika sekunder dan menjalankan transact-SQL berikut:

SELECT desired_state, desired_state_desc, actual_state, actual_state_desc, readonly_reason
FROM sys.database_query_store_options;
GO

Contoh hasil berikut dari kueri sys.database_query_store_options menunjukkan bahwa Penyimpanan Kueri berada dalam status baca/tulis untuk sekunder. readonly_reason Dari 8 menunjukkan bahwa kueri dijalankan terhadap replika sekunder. Hasil ini menunjukkan bahwa Penyimpanan Kueri telah berhasil diaktifkan pada replika sekunder.

desired_state	desired_state_desc	actual_state	actual_state_desc	readonly_reason
2	READ_WRITE	2	READ_WRITE	8

Pertimbangan performa untuk Penyimpanan Kueri untuk replika sekunder

Saluran yang digunakan oleh replika sekunder untuk mengirim informasi kueri kembali ke replika utama adalah saluran yang sama yang digunakan untuk menjaga replika sekunder tetap terbaru. Data disimpan dalam tabel yang sama pada replika utama yang digunakan Penyimpanan Kueri untuk kueri yang dijalankan pada replika utama, yang menyebabkan ukuran Penyimpanan Kueri bertambah.

Dengan demikian, ketika sistem berada di bawah beban yang signifikan, Anda mungkin melihat beberapa perlambatan karena saluran kelebihan beban. Selanjutnya, masalah pengambilan kueri adhoc yang sama yang ada untuk Penyimpanan Kueri hari ini akan berlanjut untuk beban kerja yang berjalan pada replika sekunder. Pelajari selengkapnya tentang cara Menyimpan data yang paling relevan di Penyimpanan Kueri.

Nonaktifkan Penyimpanan Kueri untuk replika sekunder

Untuk menonaktifkan Penyimpanan Kueri untuk replika sekunder, sambungkan ke database pada replika utama dan jalankan kode berikut:

ALTER DATABASE CURRENT  
FOR SECONDARY SET QUERY_STORE = OFF;
GO

Menggunakan fitur Kueri yang Diregresi

Setelah mengaktifkan Penyimpanan Kueri, refresh bagian database panel Object Explorer untuk menambahkan bagian Penyimpanan Kueri.

Catatan

Untuk Azure Synapse Analytics, tampilan Penyimpanan Kueri tersedia di bawah Tampilan Sistem di bagian database panel Object Explorer.

Pilih Kueri yang Diregresi untuk membuka panel Kueri yang Diregresi di SQL Server Management Studio. Panel Kueri yang Diregresi memperlihatkan kueri dan paket di penyimpanan kueri. Gunakan kotak drop-down 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 menampilkan kueri sumber, memaksa dan membatalkan penerapan rencana kueri, beralih antara format kisi dan bagan, membandingkan paket yang dipilih (jika lebih dari satu dipilih), dan merefresh tampilan.

Untuk memaksa rencana, pilih kueri dan rencana, lalu pilih Paksa Rencanakan. 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 menu drop-down di bagian atas untuk memilih kriteria agregat untuk waktu tunggu: rata-rata, maks, min, dev std, dan total (default).

Pilih kategori tunggu dengan mengklik bilah dan tampilan detail pada kategori tunggu yang dipilih ditampilkan. Bagan batang baru ini berisi kueri yang berkontribusi pada kategori tunggu tersebut.

Gunakan kotak drop-down di bagian atas untuk memfilter kueri berdasarkan berbagai kriteria waktu tunggu untuk kategori tunggu yang dipilih: rata-rata, maks, min, dev std, dan total (default). Pilih rencana untuk melihat rencana kueri grafis. Tombol tersedia untuk menampilkan kueri sumber, memaksa, dan menerapkan 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 Query Store:

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 Penyimpanan 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.
Waktu tunggu 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.
Waktu tunggu PAGEIOLATCH_SH tinggi per database	IO Buffer Tinggi menunggu di Penyimpanan Kueri untuk kueri tertentu	Temukan kueri dengan jumlah bacaan fisik yang tinggi di Penyimpanan Kueri. Jika mereka cocok dengan 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 kueri.
Penantian SOS_SCHEDULER_YIELD tinggi per database	CPU tinggi menunggu di Penyimpanan Kueri untuk kueri tertentu	Temukan kueri penggunaan CPU teratas di Penyimpanan Kueri. Di antaranya, identifikasi kueri yang tren CPU tinggi berkorelasi dengan CPU tinggi menunggu kueri yang terpengaruh. Fokus pada pengoptimalan kueri tersebut - mungkin ada regresi rencana, atau mungkin indeks yang hilang.

Opsi konfigurasi

Untuk opsi yang tersedia untuk mengonfigurasi parameter Penyimpanan Kueri, lihat OPSI UBAH SET DATABASE (Transact-SQL).

sys.database_query_store_options Kueri tampilan untuk menentukan opsi Penyimpanan Kueri saat ini. Untuk informasi selengkapnya tentang nilai, lihat sys.database_query_store_options.

Untuk contoh tentang mengatur opsi konfigurasi menggunakan pernyataan Transact-SQL, lihat Manajemen Opsi.

Catatan

Untuk Azure Synapse Analytics, Penyimpanan Kueri dapat diaktifkan seperti pada platform lain tetapi opsi konfigurasi tambahan tidak didukung.

Tampilan, fungsi, dan prosedur terkait

Tampilkan 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 (SQL bertransaksi)

Tampilan katalog Penyimpanan Kueri

Tampilan katalog menyajikan informasi tentang Penyimpanan Kueri.

sys.database_query_store_options (SQL Bertransaksi)

sys.query_context_settings (SQL Bertransaksi)

sys.query_store_plan (SQL Bertransaksi)

sys.query_store_query (SQL Bertransaksi)

sys.query_store_query_text (SQL Bertransaksi)

sys.query_store_runtime_stats (SQL Bertransaksi)

sys.query_store_wait_stats (SQL Bertransaksi)

sys.query_store_runtime_stats_interval (SQL bertransaksi)

sys.database_query_store_internal_state (SQL Bertransaksi)

Prosedur tersimpan Penyimpanan Kueri

Prosedur tersimpan mengonfigurasi Penyimpanan Kueri.

sp_query_store_flush_db (SQL Bertransaksi)

sp_query_store_reset_exec_stats (SQL bertransaksi)

sp_query_store_force_plan (SQL Bertransaksi)

sp_query_store_unforce_plan (SQL Bertransaksi)

sp_query_store_remove_plan (SQL Bertransaksi)

sp_query_store_remove_query (SQL Bertransaksi)

sp_query_store_clear_message_queues (SQL bertransaksi)

sp_query_store_consistency_check (SQL Bertransaksi)¹

¹ Dalam skenario ekstrem Penyimpanan Kueri dapat memasukkan status ERROR 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.

Skenario penggunaan utama

Manajemen opsi

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 (SQL Bertransaksi).

Opsi Dapatkan 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 diizinkan untuk INTERVAL_LENGTH_MINUTES. Gunakan salah satu hal 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 membutuhkan ruang yang sama banyaknya.

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

Menghapus kueri ad-hoc

Ini menghapus menyeluruh kueri adhoc dan internal dari Penyimpanan Kueri sehingga Penyimpanan Kueri tidak kehabisan ruang dan menghapus kueri yang benar-benar perlu kami 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 membersihkan data yang tidak lagi Anda inginkan.

Contoh di atas 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.

Audit dan pemecahan masalah performa

Untuk informasi selengkapnya tentang menyelami penyetelan performa dengan Penyimpanan Kueri, lihat Menyetel performa dengan Penyimpanan Kueri.

Topik performa lainnya:

• Skenario Penggunaan Penyimpanan Kueri

Lihat juga

• Prosedur Tersimpan Penyimpanan Kueri (Transact-SQL)
• Tampilan Katalog Penyimpanan Kueri (SQL Bertransaksi)
• sys.database_query_store_options (SQL Bertransaksi)
• Statistik Kueri Langsung
• Monitor Aktivitas
• Cara Penyimpanan Kueri Mengumpulkan Data
• Monitor dan Selaraskan Kinerja
• Alat Penyetelan dan Pemantauan Performa
• Menggunakan Penyimpanan Kueri dengan OLTP In-Memory

Langkah berikutnya

• Praktik Terbaik dengan Penyimpanan Kueri
• Buka Monitor Aktivitas (SQL Server Management Studio)
• Menyetel performa dengan Penyimpanan Kueri