Petunjuk penyimpanan kueri

  • Artikel

Berlaku untuk: SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed Instance

Artikel ini menguraikan cara menerapkan petunjuk kueri menggunakan Penyimpanan Kueri. Petunjuk Penyimpanan Kueri menyediakan metode yang mudah digunakan untuk membentuk rencana kueri tanpa mengubah kode aplikasi.

Petunjuk Penyimpanan Kueri tersedia di Azure SQL Database dan Azure SQL Managed Instance. Petunjuk Penyimpanan Kueri juga merupakan fitur yang diperkenalkan ke SQL Server di SQL Server 2022 (16.x).

Perhatian

Karena Pengoptimal Kueri SQL Server biasanya memilih rencana eksekusi terbaik untuk kueri, sebaiknya hanya menggunakan petunjuk sebagai upaya terakhir untuk pengembang dan administrator database berpengalaman. Untuk informasi selengkapnya, lihat Petunjuk Kueri.

Tonton video ini untuk gambaran umum petunjuk Penyimpanan Kueri:

Gambaran Umum

Idealnya, Pengoptimal Kueri memilih rencana eksekusi optimal untuk kueri.

Jika paket optimal tidak dipilih, pengembang atau DBA mungkin ingin mengoptimalkan kondisi tertentu secara manual. Petunjuk kueri ditentukan melalui OPTION klausa dan dapat digunakan untuk memengaruhi perilaku eksekusi kueri. Meskipun petunjuk kueri membantu menyediakan solusi yang dilokalkan untuk berbagai masalah terkait performa, mereka memerlukan penulisan ulang teks kueri asli. Administrator dan pengembang database mungkin tidak selalu dapat membuat perubahan langsung pada kode Transact-SQL untuk menyuntikkan petunjuk kueri. Transact-SQL mungkin dikodekan secara permanen ke dalam aplikasi atau dibuat secara otomatis oleh aplikasi. Sebelumnya, pengembang mungkin harus mengandalkan panduan rencana, yang dapat rumit untuk digunakan.

Untuk informasi tentang petunjuk kueri mana yang dapat diterapkan, lihat Petunjuk kueri yang didukung.

Kapan menggunakan petunjuk Penyimpanan Kueri

Seperti namanya, fitur ini diperluas dan bergantung pada Penyimpanan Kueri. Penyimpanan Kueri memungkinkan pengambilan kueri, rencana eksekusi, dan statistik runtime terkait. Penyimpanan Kueri sangat menyederhanakan pengalaman pelanggan penyetelan performa secara keseluruhan. SQL Server 2016 (13.x) pertama kali memperkenalkan Penyimpanan Kueri, dan sekarang diaktifkan secara default di SQL Server 2022 (16.x), Azure SQL Managed Instance, dan Azure SQL Database.

The workflow for Query Store Hints.

Contoh di mana petunjuk Penyimpanan Kueri dapat membantu masalah performa tingkat kueri:

  • Kompilasi ulang kueri pada setiap eksekusi.
  • Batasi ukuran peruntukan memori untuk operasi penyisipan massal.
  • Batasi tingkat paralelisme maksimum saat memperbarui statistik.
  • Gunakan gabungan Hash, bukan gabungan Perulangan Berlapis.
  • Gunakan tingkat kompatibilitas 110 untuk kueri tertentu sambil menyimpan semua yang lain dalam database pada tingkat kompatibilitas 150.
  • Nonaktifkan pengoptimalan tujuan baris untuk kueri SELECT TOP.

Untuk menggunakan petunjuk Penyimpanan Kueri:

  1. Identifikasi Penyimpanan query_id Kueri dari pernyataan kueri yang ingin Anda ubah. Anda dapat melakukan ini dengan berbagai cara:
    • Mengkueri tampilan katalog Penyimpanan Kueri.
    • Menggunakan laporan Penyimpanan Kueri bawaan SQL Server Management Studio.
    • Menggunakan portal Azure Query Performance Insight untuk Azure SQL Database.
  2. Jalankan sys.sp_query_store_set_hintsquery_id dengan string petunjuk kueri dan yang ingin Anda terapkan ke kueri. String ini dapat berisi satu atau beberapa petunjuk kueri. Untuk informasi selengkapnya, lihat sys.sp_query_store_set_hints.

Setelah dibuat, petunjuk Penyimpanan Kueri dipertahankan dan bertahan hidupkan ulang dan failover. Petunjuk Penyimpanan Kueri mengambil alih petunjuk tingkat pernyataan yang dikodekan secara permanen dan petunjuk panduan rencana yang ada.

Jika petunjuk kueri bertentangan dengan apa yang mungkin untuk pengoptimalan kueri, eksekusi kueri tidak diblokir dan petunjuk tidak diterapkan. Dalam kasus di mana petunjuk akan menyebabkan kueri gagal, petunjuk diabaikan dan detail kegagalan terbaru dapat dilihat di sys.query_store_query_hints.

Prosedur tersimpan sistem petunjuk Penyimpanan Kueri

Untuk membuat atau memperbarui petunjuk, gunakan sys.sp_query_store_set_hints. Petunjuk ditentukan dalam format N'OPTION (...)'string yang valid.

  • Saat Anda membuat petunjuk Penyimpanan Kueri, jika tidak ada petunjuk Penyimpanan Kueri untuk petunjuk Tertentu query_id, petunjuk Penyimpanan Kueri baru dibuat.
  • Saat Anda membuat atau memperbarui petunjuk Penyimpanan Kueri, jika petunjuk Penyimpanan Kueri sudah ada untuk tertentu query_id, nilai terakhir yang disediakan mengambil alih nilai yang ditentukan sebelumnya untuk kueri terkait.
  • query_id Jika tidak ada, kesalahan akan muncul.

Catatan

Untuk daftar lengkap petunjuk yang didukung, lihat sys.sp_query_store_set_hints.

Untuk menghapus petunjuk yang terkait dengan query_id, gunakan sys.sp_query_store_clear_hints.

Atribut XML Rencana Eksekusi

Saat petunjuk diterapkan, kumpulan hasil berikut muncul di StmtSimple elemen Rencana Eksekusi dalam format XML:

Atribut Keterangan
QueryStoreStatementHintText Petunjuk Penyimpanan Kueri Aktual diterapkan ke kueri
QueryStoreStatementHintId Pengidentifikasi unik petunjuk kueri
QueryStoreStatementHintSource Petunjuk Penyimpanan Kueri Sumber (misalnya: "Pengguna")

Catatan

Elemen XML ini tersedia melalui output perintah Transact-SQL SET STATISTICS XML dan SET SHOWPLAN XML.

Petunjuk Penyimpanan Kueri dan interoperabilitas fitur

  • Petunjuk Penyimpanan Kueri mengambil alih petunjuk tingkat pernyataan dan panduan paket berkode keras lainnya.
  • Kueri selalu dijalankan. Petunjuk Penyimpanan Kueri yang berlawanan diabaikan yang akan menyebabkan kesalahan.
  • Jika petunjuk Penyimpanan Kueri bertentangan, SQL Server tidak memblokir eksekusi kueri, dan petunjuk Penyimpanan Kueri tidak diterapkan.
  • Parameterisasi sederhana - Petunjuk Penyimpanan Kueri tidak didukung untuk pernyataan yang memenuhi syarat untuk parameterisasi sederhana.
  • Parameterisasi paksa - Petunjuk RECOMPILE tidak kompatibel dengan parameterisasi paksa yang ditetapkan di tingkat database. Jika database memiliki kumpulan parameterisasi paksa, dan petunjuk RECOMPILE adalah bagian dari string petunjuk yang ditetapkan di Penyimpanan Kueri untuk kueri, SQL Server mengabaikan petunjuk RECOMPILE dan akan menerapkan petunjuk lain jika diterapkan.
    • Selain itu, SQL Server mengeluarkan peringatan (kode kesalahan 12461) yang menyatakan bahwa petunjuk RECOMPILE diabaikan.
    • Untuk informasi selengkapnya tentang pertimbangan kasus penggunaan parameterisasi paksa, lihat Panduan untuk Menggunakan Parameterisasi Paksa.
  • Petunjuk Penyimpanan Kueri yang dibuat secara manual dikecualikan dari pembersihan. Petunjuk dan kueri tidak akan dibersihkan dari Penyimpanan Kueri dengan retensi otomatis kebijakan penangkapan.
    • Kueri dapat dihapus secara manual oleh pengguna, yang juga akan menghapus petunjuk Penyimpanan Kueri terkait.
    • Petunjuk Penyimpanan Kueri yang dihasilkan secara otomatis oleh Umpan Balik CE tunduk pada pembersihan oleh retensi otomatis kebijakan penangkapan.
    • Umpan balik DOP dan memori memberikan perilaku kueri bentuk umpan balik tanpa menggunakan petunjuk Penyimpanan Kueri. Ketika kueri dibersihkan dengan retensi otomatis kebijakan penangkapan, umpan balik DOP dan data umpan balik pemberian memori juga dibersihkan.
    • Anda dapat membuat petunjuk Penyimpanan Kueri yang sama secara manual yang diterapkan umpan balik CE, lalu kueri dengan petunjuk tidak akan lagi dapat dibersihkan dengan retensi otomatis kebijakan penangkapan.

Petunjuk penyimpanan kueri dan grup ketersediaan

Untuk informasi selengkapnya, lihat Penyimpanan Kueri untuk replika sekunder.

  • Sebelum SQL Server 2022 (16.x), petunjuk Penyimpanan Kueri dapat diterapkan terhadap replika utama grup ketersediaan.
  • Dimulai dengan SQL Server 2022 (16.x), ketika Penyimpanan Kueri untuk replika sekunder diaktifkan, petunjuk Penyimpanan Kueri juga sadar replika untuk replika sekunder dalam grup ketersediaan.
  • Anda bisa menambahkan petunjuk Penyimpanan Kueri ke kumpulan replika atau replika tertentu saat Anda mengaktifkan Penyimpanan Kueri untuk replika sekunder. Dalam sys.sp_query_store_set_query_hints, ini diatur oleh @query_hint_scope parameter, yang diperkenalkan di SQL Server 2022 (16.x).
  • Temukan set replika yang tersedia dengan mengkueri sys.query_store_replicas.
  • Temukan rencana yang dipaksa pada replika sekunder dengan sys.query_store_plan_forcing_locations.

Praktik terbaik petunjuk Penyimpanan Kueri

  • Selesaikan pemeliharaan indeks dan statistik sebelum mengevaluasi kueri untuk potensi petunjuk Penyimpanan Kueri baru.
  • Uji database aplikasi Anda pada tingkat kompatibilitas terbaru sebelum menggunakan petunjuk Penyimpanan Kueri.
    • Misalnya, pengoptimalan Parameter Sensitif Plan (PSP) diperkenalkan di SQL Server 2022 (16.x) (tingkat kompatibilitas 160), yang menggunakan beberapa paket aktif per kueri untuk mengatasi distribusi data yang tidak berubah. Jika lingkungan Anda tidak dapat menggunakan tingkat kompatibilitas terbaru, petunjuk Penyimpanan Kueri menggunakan petunjuk RECOMPILE dapat digunakan pada tingkat kompatibilitas pendukung apa pun.
  • Petunjuk Penyimpanan Kueri mengambil alih perilaku rencana kueri SQL Server. Disarankan untuk hanya menggunakan petunjuk Penyimpanan Kueri saat diperlukan untuk mengatasi masalah terkait performa.
  • Disarankan untuk mengevaluasi kembali petunjuk Penyimpanan Kueri, petunjuk tingkat pernyataan, panduan rencana, dan paket paksa Penyimpanan Kueri setiap kali distribusi data berubah dan selama proyek migrasi database. Perubahan distribusi data dapat menyebabkan petunjuk Penyimpanan Kueri menghasilkan rencana eksekusi suboptimal.

Contoh

J. Demo petunjuk Penyimpanan Kueri

Panduan petunjuk Penyimpanan Kueri berikut di Azure SQL Database menggunakan database yang diimpor melalui file BACPAC (.bacpac). Pelajari cara mengimpor database baru ke server Azure SQL Database, lihat Mulai Cepat: Mengimpor file BACPAC ke database.

-- ************************************************************************ --
-- Query Store hints demo

-- Demo uses "PropertyMLS" database which can be imported from BACPAC here:
-- https://github.com/microsoft/sql-server-samples/tree/master/samples/features/query-store

-- Email QSHintsFeedback@microsoft.com for questions\feedback
-- ************************************************************************ --

/*
    Demo prep, connect to the PropertyMLS database
*/

ALTER DATABASE [PropertyMLS] SET QUERY_STORE CLEAR;
ALTER DATABASE CURRENT SET QUERY_STORE = ON;
ALTER DATABASE CURRENT SET QUERY_STORE  (QUERY_CAPTURE_MODE = ALL);
GO

-- Should be READ_WRITE
SELECT actual_state_desc
FROM sys.database_query_store_options;
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints.
    Checking if any already exist (should be none).
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
     The PropertySearchByAgent stored procedure has a parameter
     used to filter AgentId.  Looking at the statistics for AgentId,
     you will see that there is a big skew for AgentId 101.
*/
SELECT	hist.range_high_key AS [AgentId],
        hist.equal_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE s.[name] = N'NCI_Property_AgentId';


-- Show actual query execution plan to see plan compiled.
-- Agent with many properties will have a scan with parallelism.
EXEC [dbo].[PropertySearchByAgent] 101;

-- Agents with few properties still re-use this plan (assuming no recent plan eviction).
EXEC [dbo].[PropertySearchByAgent] 4;


/*
    Now let's find the query_id associated with this query.
*/
SELECT query_sql_text, q.query_id
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON
    qt.query_text_id = q.query_text_id
WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%' and query_sql_text not like N'%query_store%';
GO

/*
     We can set the hint associated with the query_id returned in the previous result set, as below.
     Note, we can designate one or more query hints
*/
EXEC sp_query_store_set_hints @query_id=5, @value = N'OPTION(RECOMPILE)';
GO

/*
    You can verify Query Store Hints in sys.query_store_query_hints
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see two different plans, one for AgentId 101 and one for AgentId 4.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

/*
    We can remove the hint using sp_query_store_clear_query_hints
*/
EXEC sp_query_store_clear_hints @query_id = 10;
GO

/*
    That Query Store Hint is now removed
*/
SELECT	query_hint_id,
        query_id,
        query_hint_text,
        last_query_hint_failure_reason,
        last_query_hint_failure_reason_desc,
        query_hint_failure_count,
        source,
        source_desc
FROM sys.query_store_query_hints;
GO

-- Execute both at the same time and show actual query execution plan.
-- You should see one plan again.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO

B. Mengidentifikasi kueri di Penyimpanan Kueri

Contoh berikut mengkueri sys.query_store_query_text dan sys.query_store_query untuk mengembalikan query_id fragmen teks kueri yang dijalankan.

Dalam demo ini, kueri yang kami coba sesuaikan ada dalam SalesLT database sampel:

SELECT * FROM SalesLT.Address as A 
INNER JOIN SalesLT.CustomerAddress as CA
on A.AddressID = CA.AddressID
WHERE PostalCode = '98052' ORDER BY A.ModifiedDate DESC;

Penyimpanan Kueri tidak segera mencerminkan data kueri ke tampilan sistemnya.

Identifikasi kueri dalam tampilan katalog sistem penyimpanan kueri:

SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt 
INNER JOIN sys.query_store_query q ON 
    qt.query_text_id = q.query_text_id 
WHERE query_sql_text like N'%PostalCode =%'  
  AND query_sql_text not like N'%query_store%';
GO

Dalam sampel berikut, contoh kueri sebelumnya dalam SalesLT database diidentifikasi sebagai query_id 39.

Setelah diidentifikasi, terapkan petunjuk untuk memberlakukan ukuran peruntukan memori maksimum dalam persentase batas memori yang query_iddikonfigurasi ke :

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(MAX_GRANT_PERCENT=10)';

Anda juga dapat menerapkan petunjuk kueri dengan sintaks berikut, misalnya opsi untuk memaksa estimator kardinalitas warisan:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';

Anda bisa menerapkan beberapa petunjuk kueri dengan daftar yang dipisahkan koma:

EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))';

Tinjau petunjuk Penyimpanan Kueri di tempat untuk query_id 39:

SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason, last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc 
FROM sys.query_store_query_hints 
WHERE query_id = 39;

Terakhir, hapus petunjuk dari query_id 39, menggunakan sp_query_store_clear_hints.

EXEC sys.sp_query_store_clear_hints @query_id = 39;

Konten terkait