Skenario Penggunaan Penyimpanan Kueri

Berlaku untuk: SQL ServerAzure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

Penyimpanan Kueri dapat digunakan dalam serangkaian skenario yang luas saat melacak dan memastikan performa beban kerja yang dapat diprediksi sangat penting. Berikut adalah beberapa contoh yang dapat Anda pertimbangkan:

• Menentukan dan memperbaiki kueri dengan regresi pilihan paket

• Mengidentifikasi dan menyetel kueri penggunaan sumber daya teratas

• Pengujian A/B

• Jaga stabilitas performa selama peningkatan ke SQL Server yang lebih baru

• Mengidentifikasi dan meningkatkan beban kerja ad hoc

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

Menentukan dan memperbaiki kueri dengan regresi pilihan paket

Selama eksekusi kueri regulernya, Pengoptimal Kueri dapat memutuskan untuk memilih rencana yang berbeda karena input penting menjadi berbeda: kardinalitas data telah berubah, indeks telah dibuat, diubah, atau dihilangkan, statistik telah diperbarui, dll. Biasanya, rencana baru lebih baik, atau hampir sama dari rencana yang digunakan sebelumnya. Namun, ada kasus ketika rencana baru secara signifikan lebih buruk - situasi ini disebut sebagai pilihan rencana mengubah regresi. Sebelum Penyimpanan Kueri, sulit untuk mengidentifikasi dan memperbaiki karena SQL Server tidak menyediakan penyimpanan data bawaan, bagi pengguna untuk melihat rencana eksekusi yang digunakan dari waktu ke waktu.

Dengan Penyimpanan Kueri, Anda bisa dengan cepat:

• Identifikasi semua kueri yang metrik eksekusinya telah diturunkan, dalam periode waktu yang diminati (jam terakhir, hari, minggu, dll.). Gunakan Kueri yang Diregresi di SQL Server Management Studio untuk mempercepat analisis Anda.

• Di antara kueri yang mengalami kemunduran, mudah untuk menemukan kueri yang memiliki beberapa rencana dan yang terdegradasi karena pilihan rencana yang buruk. Gunakan panel Ringkasan Paket di Kueri yang Diregresi untuk memvisualisasikan semua paket untuk kueri yang diregresi dan performa kuerinya dari waktu ke waktu.

• Paksa rencana sebelumnya dari sejarah, jika terbukti lebih baik. Gunakan tombol Paksa Paket di Kueri yang Diregresi untuk memaksa rencana yang dipilih untuk kueri.

Untuk deskripsi terperinci tentang skenario, lihat Penyimpanan Kueri: Perekam data penerbangan untuk blog database Anda.

Mengidentifikasi dan menyetel kueri penggunaan sumber daya teratas

Meskipun beban kerja Anda dapat menghasilkan ribuan kueri, biasanya hanya beberapa dari mereka yang benar-benar menggunakan sebagian besar sumber daya sistem dan karenanya memerlukan perhatian Anda. Di antara kueri yang menggunakan sumber daya teratas, Anda biasanya akan menemukan kueri yang telah diregresi atau kueri yang dapat ditingkatkan dengan penyetelan tambahan.

Cara term mudah untuk memulai eksplorasi adalah dengan membuka Kueri Penggunaan Sumber Daya Teratas di Management Studio. Antarmuka pengguna dipisahkan menjadi tiga panel: Histogram yang mewakili kueri penggunaan sumber daya teratas (kiri), ringkasan paket untuk kueri yang dipilih (kanan) dan rencana kueri visual untuk paket yang dipilih (bawah). Pilih Konfigurasikan untuk mengontrol berapa banyak kueri yang ingin Anda analisis dan interval waktu yang menarik. Selain itu, Anda dapat memilih antara dimensi konsumsi sumber daya yang berbeda (durasi, CPU, memori, IO, jumlah eksekusi) dan garis besar (Rata-rata, Min, Maks, Total, Simpangan Baku).

Lihat ringkasan rencana di sebelah kanan untuk menganalisis riwayat eksekusi dan pelajari tentang berbagai rencana dan statistik runtime mereka. Gunakan panel bawah untuk memeriksa paket yang berbeda atau untuk membandingkannya secara visual, dirender berdampingan (gunakan tombol Bandingkan).

Saat Anda mengidentifikasi kueri dengan performa suboptimal, tindakan Anda bergantung pada sifat masalah:

1. Jika kueri dijalankan dengan beberapa rencana dan paket terakhir secara signifikan lebih buruk dari rencana sebelumnya, Anda dapat menggunakan mekanisme pemakaian rencana untuk memastikan SQL Server akan menggunakan rencana optimal untuk eksekusi di masa mendatang

2. Periksa apakah pengoptimal menyarankan indeks yang hilang dalam paket XML. Jika ya, buat indeks yang hilang dan gunakan Penyimpanan Kueri untuk mengevaluasi performa kueri setelah pembuatan indeks

3. Pastikan bahwa statistik sudah diperbarui untuk tabel yang mendasari yang digunakan oleh kueri.

4. Pastikan bahwa indeks yang digunakan oleh kueri didefragmentasi.

5. Pertimbangkan untuk menulis ulang kueri mahal. Misalnya, manfaatkan parameterisasi kueri dan kurangi penggunaan SQL dinamis. Terapkan logika optimal saat membaca data (terapkan pemfilteran data di sisi database, bukan di sisi aplikasi).

Pengujian A/B

Gunakan Penyimpanan Kueri untuk membandingkan performa beban kerja sebelum dan sesudah aplikasi berubah.

Daftar berikut berisi beberapa contoh di mana Anda bisa menggunakan Penyimpanan Kueri untuk menilai dampak perubahan lingkungan atau aplikasi pada performa beban kerja:

• Meluncurkan versi aplikasi baru.

• Menambahkan perangkat keras baru ke server.

• Membuat indeks yang tidak ada di tabel yang direferensikan oleh kueri mahal.

• Menerapkan kebijakan pemfilteran untuk keamanan tingkat baris. Untuk informasi selengkapnya, lihat Mengoptimalkan Keamanan Tingkat Baris dengan Penyimpanan Kueri.

• Menambahkan penerapan versi sistem temporal ke tabel yang sering dimodifikasi oleh aplikasi OLTP Anda.

Dalam salah satu skenario ini, terapkan alur kerja berikut:

1. Jalankan beban kerja Anda dengan Penyimpanan Kueri sebelum perubahan yang direncanakan untuk menghasilkan garis besar performa.

2. Terapkan perubahan aplikasi pada saat yang dikontrol tepat waktu.

3. Lanjutkan menjalankan beban kerja cukup lama untuk menghasilkan gambar performa sistem setelah perubahan

4. Bandingkan hasil dari #1 dan #3.

   1. Buka Konsumsi Database Keseluruhan untuk menentukan dampak ke seluruh database.

   2. Buka Kueri Penggunaan Sumber Daya Teratas (atau jalankan analisis Anda sendiri menggunakan Transact-SQL) untuk menganalisis dampak perubahan ke kueri yang paling penting.

5. Putuskan apakah akan menyimpan perubahan atau melakukan rollback jika performa baru tidak dapat diterima.

Ilustrasi berikut menunjukkan analisis Penyimpanan Kueri (langkah 4) jika pembuatan indeks hilang. Buka panel Ringkasan Kueri /Rencana Penggunaan Sumber Daya Teratas untuk mendapatkan tampilan ini untuk kueri yang harus terpengaruh oleh pembuatan indeks:

Selain itu, Anda dapat membandingkan paket sebelum dan sesudah pembuatan indeks dengan merendernya secara berdampingan. (opsi toolbar "Bandingkan paket untuk kueri yang dipilih di jendela terpisah", yang ditandai dengan kotak merah pada toolbar.)

Rencanakan sebelum pembuatan indeks (plan_id = 1, di atas) memiliki petunjuk indeks yang hilang dan Anda dapat memeriksa bahwa Pemindaian Indeks Berkluster adalah operator termahal dalam kueri (persegi panjang merah).

Rencanakan setelah pembuatan indeks hilang (plan_id = 15, di bawah) sekarang memiliki Index Seek (Nonclustered) yang mengurangi biaya keseluruhan kueri dan meningkatkan performanya (persegi hijau).

Berdasarkan analisis, Anda mungkin akan mempertahankan indeks karena performa kueri telah ditingkatkan.

Jaga stabilitas performa selama peningkatan ke SQL Server yang lebih baru

Sebelum SQL Server 2014 (12.x), pengguna terkena risiko regresi performa selama peningkatan ke versi platform terbaru. Alasan untuk itu adalah fakta bahwa versi terbaru Pengoptimal Kueri menjadi aktif segera setelah bit baru diinstal.

Dimulai dengan SQL Server 2014 (12.x) semua perubahan Pengoptimal Kueri terkait dengan tingkat kompatibilitas database terbaru, sehingga paket tidak diubah tepat pada titik peningkatan melainkan ketika pengguna mengubah COMPATIBILITY_LEVEL ke yang terbaru. Kemampuan ini, dalam kombinasi dengan Penyimpanan Kueri memberi Anda tingkat kontrol yang besar atas performa kueri dalam proses peningkatan. Alur kerja pemutakhiran yang direkomendasikan diperlihatkan dalam gambar berikut:

1. Tingkatkan SQL Server tanpa mengubah tingkat kompatibilitas database. Ini tidak mengekspos perubahan Pengoptimal Kueri terbaru tetapi masih menyediakan fitur SQL Server yang lebih baru termasuk Penyimpanan Kueri.

2. Mengaktifkan Penyimpanan Kueri. Untuk informasi selengkapnya, lihat Menjaga Penyimpanan Kueri disesuaikan dengan beban kerja Anda.

3. Izinkan Penyimpanan Kueri untuk mengambil kueri dan paket, dan membuat garis besar performa dengan tingkat kompatibilitas database sumber/sebelumnya. Tinggal di langkah ini cukup lama untuk menangkap semua rencana dan mendapatkan garis besar yang stabil. Ini bisa menjadi durasi siklus bisnis biasa untuk beban kerja produksi.

4. Pindah ke tingkat kompatibilitas database terbaru: dapatkan beban kerja Anda terekspos ke Pengoptimal Kueri terbaru, untuk berpotensi membuat paket baru.

5. Gunakan Penyimpanan Kueri untuk analisis dan perbaikan regresi: biasanya, peningkatan Pengoptimal Kueri baru harus menghasilkan rencana yang lebih baik. Namun, Query Store akan memberikan cara mudah untuk mengidentifikasi regresi pilihan rencana dan memperbaikinya menggunakan mekanisme pemaksaan rencana. Dimulai dengan SQL Server 2017 (14.x), saat menggunakan fitur Koreksi Paket Otomatis, langkah ini menjadi otomatis.

   a. Untuk kasus di mana ada regresi, paksa rencana baik yang diketahui sebelumnya di Penyimpanan Kueri.

   b. Jika ada rencana kueri yang gagal dipaksakan, atau jika performa masih tidak mencukup, pertimbangkan untuk mengembalikan tingkat kompatibilitas database ke pengaturan sebelumnya, lalu melibatkan Dukungan Pelanggan Microsoft.

Tip

Gunakan tugas SQL Server Management Studio Upgrade Database untuk meningkatkan tingkat kompatibilitas database. Lihat Memutakhirkan Database dengan menggunakan Asisten Penyetelan Kueri untuk detailnya.

Mengidentifikasi dan meningkatkan beban kerja ad hoc

Beberapa beban kerja tidak memiliki kueri dominan yang dapat Anda sesuaikan untuk meningkatkan performa aplikasi secara keseluruhan. Beban kerja tersebut biasanya ditandai dengan sejumlah besar kueri yang berbeda masing-masing mengkonsumsi sebagian sumber daya sistem. Menjadi unik, kueri tersebut jarang dijalankan (biasanya hanya sekali, dengan demikian nama ad hoc), sehingga konsumsi runtime mereka tidak penting. Di sisi lain, mengingat bahwa aplikasi menghasilkan kueri baru bersih sepanjang waktu, sebagian besar sumber daya sistem dihabiskan untuk kompilasi kueri, yang tidak optimal. Ini bukan situasi yang ideal untuk Penyimpanan Kueri mengingat bahwa sejumlah besar kueri dan rencana membanjiri ruang yang telah Anda pesan yang berarti bahwa Penyimpanan Kueri kemungkinan akan berakhir dalam mode baca-saja dengan sangat cepat. Jika Anda mengaktifkan Kebijakan Pembersihan Berbasis Ukuran (sangat disarankan untuk menjaga Penyimpanan Kueri selalu aktif dan berjalan), maka proses latar belakang akan membersihkan struktur Penyimpanan Kueri sebagian besar waktu juga mengambil sumber daya sistem yang signifikan.

Tampilan Kueri Penggunaan Sumber Daya Teratas memberi Anda indikasi pertama tentang sifat ad hoc beban kerja Anda:

Gunakan metrik Jumlah Eksekusi untuk menganalisis apakah kueri teratas Anda ad hoc (ini mengharuskan Anda menjalankan Penyimpanan Kueri dengan QUERY_CAPTURE_MODE = ALL). Dari diagram di atas, Anda dapat melihat bahwa 90% Dari Kueri Konsumsi Sumber Daya Teratas Anda hanya dijalankan sekali.

Atau, Anda dapat menjalankan skrip Transact-SQL untuk mendapatkan jumlah total teks kueri, kueri, dan paket dalam sistem dan menentukan seberapa berbeda mereka dengan membandingkan query_hash dan query_plan_hash:

--Do cardinality analysis when suspect on ad hoc workloads
SELECT COUNT(*) AS CountQueryTextRows FROM sys.query_store_query_text;  
SELECT COUNT(*) AS CountQueryRows FROM sys.query_store_query;  
SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRows FROM  sys.query_store_query;  
SELECT COUNT(*) AS CountPlanRows FROM sys.query_store_plan;  
SELECT COUNT(DISTINCT query_plan_hash) AS  CountDifferentPlanRows FROM  sys.query_store_plan;  

Ini adalah salah satu hasil potensial yang bisa Anda dapatkan jika beban kerja dengan kueri ad hoc:

Hasil kueri memperlihatkan bahwa terlepas dari banyaknya kueri dan paket di Penyimpanan Kueri dan query_hash query_plan_hash sebenarnya tidak berbeda. Rasio antara teks kueri unik dan hash kueri unik, yang jauh lebih besar dari 1, adalah indikasi bahwa beban kerja adalah kandidat yang baik untuk parameterisasi, karena satu-satunya perbedaan antara kueri adalah konstanta harfiah (parameter) yang disediakan sebagai bagian dari teks kueri.

Biasanya, situasi ini terjadi jika aplikasi Anda menghasilkan kueri (alih-alih memanggil prosedur tersimpan atau kueri berparameter) atau jika mengandalkan kerangka kerja pemetaan relasional objek yang menghasilkan kueri secara default.

Jika Anda memegang kendali atas kode aplikasi, Anda dapat mempertimbangkan untuk menulis ulang lapisan akses data untuk menggunakan prosedur tersimpan atau kueri berparameter. Namun, situasi ini juga dapat ditingkatkan secara signifikan tanpa perubahan aplikasi dengan memaksa parameterisasi kueri untuk seluruh database (semua kueri) atau untuk templat kueri individual dengan yang sama query_hash.

Pendekatan dengan templat kueri individual memerlukan pembuatan panduan rencana:

--Apply plan guide for the selected query template 
DECLARE @stmt nvarchar(max);  
DECLARE @params nvarchar(max);  
EXEC sp_get_query_template   
    N'<your query text goes here>',  
    @stmt OUTPUT,   
    @params OUTPUT;  
  
EXEC sp_create_plan_guide   
    N'TemplateGuide1',   
    @stmt,   
    N'TEMPLATE',   
    NULL,   
    @params,   
    N'OPTION (PARAMETERIZATION FORCED)';  

Solusi dengan panduan rencana lebih tepat tetapi membutuhkan lebih banyak pekerjaan.

Jika semua kueri Anda (atau mayoritasnya) adalah kandidat untuk parameterisasi otomatis, pertimbangkan untuk mengonfigurasi PARAMETERIZATION = FORCED untuk seluruh database. Untuk informasi selengkapnya, lihat Panduan untuk Menggunakan Parameterisasi Paksa.

--Apply forced parameterization for entire database  
ALTER DATABASE <database name> SET PARAMETERIZATION FORCED;  

Setelah Anda menerapkan salah satu langkah ini, Kueri Penggunaan Sumber Daya Teratas akan menunjukkan kepada Anda gambar beban kerja yang berbeda.

Dalam beberapa kasus, aplikasi Anda dapat menghasilkan banyak kueri berbeda yang bukan kandidat yang baik untuk parameterisasi otomatis. Dalam hal ini, Anda melihat sejumlah besar kueri dalam sistem tetapi rasio antara kueri unik dan unik query_hash kemungkinan mendekati 1.

Dalam hal ini, Anda mungkin ingin mengaktifkan opsi Optimalkan untuk Beban Kerja Ad hoc untuk mencegah membuang-buang memori cache pada kueri yang kemungkinan tidak akan dijalankan lagi. Untuk mencegah pengambilan kueri tersebut di Penyimpanan Kueri, atur QUERY_CAPTURE_MODE ke AUTO.

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO 
  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE CLEAR;  
ALTER DATABASE [QueryStoreTest] SET QUERY_STORE = ON   
    (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);  

Langkah berikutnya

• Memantau Performa Dengan Menggunakan Penyimpanan Kueri
• Praktik Terbaik dengan Penyimpanan Kueri
• Meningkatkan Database dengan menggunakan Asisten Penyetelan Kueri