Petunjuk Penyimpanan Kueri (pratinjau)

Berlaku untuk: SQL Server (semua versi yang didukung) Azure SQL Database Azure 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 adalah fitur pratinjau di Pratinjau SQL Server 2022 (16.x). Petunjuk Penyimpanan Kueri tersedia di Azure SQL Database dan Azure SQL Managed Instance.

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

Perhatian

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

Gambaran Umum

Idealnya Pengoptimal Kueri memilih rencana eksekusi optimal untuk kueri. Ketika ini tidak terjadi, pengembang atau DBA mungkin ingin mengoptimalkan kondisi tertentu secara manual. Petunjuk kueri ditentukan melalui klausa OPTION 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 secara langsung pada kode Transact-SQL untuk menyuntikkan petunjuk kueri. Transact-SQL mungkin dikodekan secara permanen ke dalam aplikasi atau secara otomatis dihasilkan 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. Diperkenalkan pada SQL Server 2016 (13.x) dan on-by-default di Azure SQL Database, Query Store sangat menyederhanakan pengalaman pelanggan penyetelan performa secara keseluruhan.

  First the query is executed, then captured by Query Store. Then the DBA creates a Query Store hint on a query. Thereafter, the query is executed using the Query Store hint.

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

• Kompilasi ulang kueri pada setiap eksekusi.
• Batasi ukuran peruntukan memori untuk operasi penyisipan massal.
• Batasi tingkat paralelisme maksimum untuk operasi pembaruan 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 pernyataan kueri yang ingin Anda ubah. Anda dapat melakukan ini dengan berbagai cara: 1.1. Mengkueri tampilan katalog Penyimpanan Kueri. 1.2. Menggunakan SQL Server Management Studio laporan Penyimpanan Kueri bawaan. 1.3. Menggunakan portal Azure Query Performance Insight for 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, petunjuk tidak akan memblokir eksekusi kueri dan petunjuk tidak akan diterapkan. Dalam kasus di mana petunjuk akan menyebabkan kueri gagal, petunjuk diabaikan dan detail kegagalan terbaru dapat ditampilkan di sys.query_store_query_hints.

Tonton video ini untuk gambaran umum petunjuk Penyimpanan Kueri:

Prosedur tersimpan sistem petunjuk Penyimpanan Kueri

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

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

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 elemen StmtSimple dari Rencana Eksekusi dalam format XML:

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

Catatan

Selama pratinjau petunjuk Penyimpanan Kueri, elemen XML ini hanya akan tersedia melalui output perintah Transact-SQL SET STATISTICS XML dan SET SHOWPLAN XML.

Petunjuk Penyimpanan Kueri dan interoperabilitas fitur

• Petunjuk Penyimpanan Kueri akan mengambil alih petunjuk tingkat pernyataan dan panduan rencana yang dikodekan secara permanen lainnya.
• Kueri akan selalu dijalankan di mana petunjuk Penyimpanan Kueri yang berlawanan, yang akan menyebabkan kesalahan, akan diabaikan.
• Jika petunjuk Penyimpanan Kueri bertentangan, SQL Server tidak akan memblokir eksekusi kueri dan petunjuk Penyimpanan Kueri tidak akan 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 diatur di Penyimpanan Kueri untuk kueri, SQL Server akan mengabaikan petunjuk RECOMPILE dan akan menerapkan petunjuk lain jika dimanfaatkan.
  • Selain itu, SQL Server akan mengeluarkan peringatan (kode kesalahan 12460) yang menyatakan bahwa petunjuk RECOMPILE diabaikan.
  • Untuk informasi selengkapnya tentang pertimbangan kasus penggunaan parameterisasi paksa, lihat Panduan untuk Menggunakan Parameterisasi Paksa.
• Saat ini, petunjuk Penyimpanan Kueri dapat diterapkan terhadap replika utama grup ketersediaan AlwaysOn.

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 memanfaatkan petunjuk Penyimpanan Kueri. * Misalnya, pengoptimalan Rencana Sensitif Parameter (PSP) diperkenalkan pada SQL Server 2022 (tingkat kompatibilitas 160), yang memanfaatkan beberapa rencana aktif per kueri untuk mengatasi distribusi data yang tidak seragam. Jika lingkungan Anda tidak dapat menggunakan tingkat kompatibilitas terbaru, petunjuk Penyimpanan Kueri menggunakan petunjuk RECOMPILE dapat dimanfaatkan pada tingkat kompatibilitas pendukung apa pun.
• Petunjuk Penyimpanan Kueri mengambil alih perilaku rencana kueri SQL Server. Disarankan untuk hanya memanfaatkan petunjuk Penyimpanan Kueri saat diperlukan untuk mengatasi masalah terkait performa.
• Disarankan untuk mengevaluasi kembali petunjuk Penyimpanan Kueri, petunjuk tingkat pernyataan, panduan rencana, dan rencana 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

A. 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 kueri berikut 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 di 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;

Perhatikan bahwa 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;

Lihat juga

• sys.query_store_query_hints (SQL Bertransaksi)
• sys.sp_query_store_set_hints (SQL Bertransaksi)
• sys.sp_query_store_clear_hints (SQL Bertransaksi)
• Menyimpan Rencana Eksekusi dalam Format XML
• Tampilkan dan Simpan Rencana Eksekusi
• Petunjuk (Transact-SQL) - Kueri

Langkah berikutnya

• Praktik terbaik dengan Query Store
• Praktik terbaik dengan petunjuk Penyimpanan Kueri
• Memantau performa dengan menggunakan Penyimpanan Kueri
• Mengonfigurasi tingkat paralelisme maksimum (MAXDOP) dalam Azure SQL Database