Bagikan melalui

Skenario Penggunaan Query Store

Berlaku untuk: SQL Server 2016 (13.x) dan versi yang lebih baru Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (khusus kumpulan SQL)Database SQL di Microsoft Fabric

Query Store dapat digunakan dalam beragam skenario saat memantau dan memastikan performa beban kerja yang dapat diprediksi sangat penting. Berikut adalah beberapa contoh yang dapat Anda pertimbangkan:

Menentukan dan memperbaiki kueri dengan regresi pada pemilihan rencana

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 regresi perubahan pilihan rencana. Sebelum adanya Query Store, sulit untuk mengidentifikasi dan memperbaiki masalah karena SQL Server tidak menyediakan penyimpanan data bawaan yang memungkinkan pengguna melihat rencana eksekusi yang telah digunakan dari waktu ke waktu.

Dengan Query Store, 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 Rencana di Kueri yang Diregres untuk memvisualisasikan semua rencana untuk kueri yang diregres dan performa kueri tersebut dari waktu ke waktu.

  • Gunakan kembali rencana sebelumnya dari sejarah, jika terbukti lebih baik. Gunakan tombol Paksa Paket di Kueri yang Diregresi untuk memaksakan paket yang telah dipilih pada kueri tersebut.

Cuplikan layar Penyimpanan Kueri menunjukkan ringkasan rencana.

Untuk deskripsi terperinci tentang skenario, lihat blog Query Store: Perekam Data Penerbangan untuk Database Anda.

Mengidentifikasi dan menyetel kueri dengan konsumsi sumber daya terbanyak

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 mengonsumsi banyak sumber daya, Anda biasanya akan menemukan kueri yang mengalami penurunan performa atau yang dapat ditingkatkan dengan penyetelan tambahan.

Cara termudah untuk memulai eksplorasi adalah dengan membuka Kueri yang Mengonsumsi Sumber Daya Terbanyak 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).

Cuplikan layar Query Store menunjukkan bahwa Anda dapat mengidentifikasi dan mengoptimalkan kueri yang mengonsumsi sumber daya terbanyak.

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, ditampilkan 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 rencana terakhir secara signifikan lebih buruk dari rencana sebelumnya, Anda dapat menggunakan mekanisme pemaksaan penggunaan rencana untuk memastikan SQL Server akan menggunakan rencana optimal untuk eksekusi di masa mendatang.

  2. Periksa apakah pengoptimal merekomendasikan adanya indeks yang hilang dalam rencana 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 yang memakan biaya tinggi. 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 Query Store untuk menilai dampak perubahan pada lingkungan atau aplikasi terhadap kinerja beban kerja.

  • Meluncurkan versi aplikasi baru.

  • Menambahkan perangkat keras baru ke server.

  • Membuat indeks yang hilang pada tabel yang dirujuk oleh kueri yang memakan biaya banyak.

  • Menerapkan kebijakan pemfilteran untuk keamanan tingkat baris. Untuk informasi lebih lanjut, lihat Mengoptimalkan Keamanan Tingkat Baris dengan Query Store.

  • 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 menggunakan Penyimpanan Kueri sebelum melakukan perubahan yang direncanakan untuk menghasilkan patokan performa.

  2. Terapkan perubahan aplikasi pada waktu yang terkendali.

  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) dalam hal pembuatan indeks yang hilang. Buka Panel Ringkasan Rencana / Kueri dengan Konsumsi Sumber Daya Teratas untuk mendapatkan tampilan ini untuk kueri yang harus terpengaruh oleh pembuatan indeks.

Cuplikan layar yang menunjukkan analisis Query Store (langkah 4) jika tidak ada pembuatan indeks.

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

Cuplikan layar memperlihatkan Query Store dan bandingkan rencana untuk kueri yang dipilih di toolbar jendela yang terpisah.

Rencana sebelum pembuatan indeks (plan_id = 1, di atas) memiliki petunjuk keberadaan indeks yang hilang dan Anda dapat memeriksa bahwa Pemindaian Indeks Kluster adalah operator paling mahal dalam kueri (ditandai dengan persegi panjang merah).

Rencana setelah pembuatan indeks yang hilang (plan_id = 15, di bawah) sekarang memiliki Index Seek (Nonclustered) yang mengurangi biaya kueri secara keseluruhan 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 pengoptimalan kueri terkait dengan tingkat kompatibilitas database terbaru, akibatnya rencana eksekusi tidak diubah setelah peningkatan melainkan ketika pengguna mengubah COMPATIBILITY_LEVEL ke yang terbaru. Fitur ini, dalam kombinasi dengan Penyimpanan Kueri, memberi Anda tingkat kendali yang tinggi atas performa kueri dalam proses peningkatan. Alur kerja pemutakhiran yang direkomendasikan diperlihatkan dalam gambar berikut:

Diagram memperlihatkan alur kerja pemutakhiran yang direkomendasikan.

  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 yang disesuaikan dengan beban kerja Anda.

  3. Izinkan Penyimpanan Kueri untuk mengambil kueri dan rencana, dan membangun garis dasar kinerja dengan tingkat kompatibilitas database sumber/sebelumnya. Tetap pada langkah ini cukup lama untuk memperoleh semua rencana dan mendapatkan patokan yang stabil. Ini bisa menjadi durasi siklus bisnis biasa untuk beban kerja produksi.

  4. Pindah ke tingkat kompatibilitas database terbaru: dapatkan beban kerja Anda dapat memanfaatkan pengoptimal kueri terbaru, yang dapat berpotensi menghasilkan rencana baru.

  5. Gunakan Query Store 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 Rencana Otomatis, langkah ini menjadi otomatis.

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

    b. Jika ada rencana kueri yang gagal dipaksakan, atau jika performanya masih tidak memadai, pertimbangkan untuk mengembalikan level kompatibilitas database ke pengaturan sebelumnya, dan menghubungi 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 berbeda, yang masing-masing menggunakan sebagian sumber daya sistem. Kueri tersebut jarang dijalankan karena sifatnya yang unik (biasanya hanya sekali, sehingga disebut ad hoc), sehingga penggunaan waktu eksekusi 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 rancangan membanjiri ruang yang Anda cadangkan, yang berarti Penyimpanan Kueri kemungkinan akan berakhir dalam mode hanya-baca 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 dan juga mengambil sumber daya sistem yang signifikan.

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

Cuplikan layar tampilan Kueri Penggunaan Sumber Daya Terbesar memperlihatkan bahwa sebagian besar kueri yang mengonsumsi sumber daya terbanyak hanya dieksekusi sekali.

Gunakan metrik Jumlah Eksekusi untuk menganalisis apakah kueri teratas Anda bersifat ad hoc (ini mengharuskan Anda menjalankan Penyimpanan Kueri dengan QUERY_CAPTURE_MODE = ALL). Dari diagram di atas, Anda dapat melihat bahwa 90% dari Kueri yang Mengkonsumsi 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 dalam situasi beban kerja dengan kueri ad hoc:

Cuplikan layar hasil potensial yang bisa Anda dapatkan untuk beban kerja yang melibatkan kueri ad hoc.

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

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 dengan Penggunaan Sumber Daya Teratas akan menunjukkan gambaran berbeda dari beban kerja Anda.

Cuplikan layar tampilan Kueri dengan Konsumsi Sumber Daya Terbesar menunjukkan gambaran yang berbeda tentang beban kerja Anda.

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

  • Last updated on