Evaluasi kinerja kueri dengan rencana eksekusi dan Tampilan Manajemen Dinamis (DMV)

Saat kueri berjalan lebih lambat dari yang diharapkan, langkah pertama adalah memahami cara mesin database menjalankannya. Rencana eksekusi menunjukkan kepada Anda operator yang tepat, metode akses data, dan biaya sumber daya yang dipilih pengoptimal untuk kueri. Tampilan manajemen dinamis (DMV) mendukung hal ini dengan mengekspos data performa runtime dari semua kueri dalam database, sehingga Anda dapat menemukan kueri yang paling mahal sebelum menganalisis rencana tunggal apa pun.

Membaca rencana eksekusi

Rencana eksekusi adalah serangkaian instruksi yang dihasilkan pengoptimal kueri untuk mengambil dan memproses data. Ini menentukan tabel mana yang akan diakses terlebih dahulu, apakah akan menggunakan indeks atau memindai tabel, dan cara menggabungkan, memfilter, mengurutkan, dan menggabungkan hasil. Pengoptimal mengevaluasi beberapa rencana kandidat dan memilih salah satu dengan perkiraan biaya terendah.

Ada dua jenis rencana eksekusi:

• Estimasi rencana eksekusi: Dihasilkan tanpa menjalankan kueri. Ini menunjukkan operator yang direncanakan dan perkiraan jumlah baris berdasarkan statistik. Gunakan perkiraan rencana untuk analisis cepat tanpa memengaruhi database.
• Rencana eksekusi aktual: Diambil selama eksekusi kueri. Ini termasuk rencana perkiraan, jumlah baris sebenarnya, waktu pelaksanaan aktual, alokasi memori, serta peringatan. Rencana aktual mengungkapkan perbedaan antara apa yang diharapkan pengoptimal dan apa yang sebenarnya terjadi.

Untuk menampilkan perkiraan rencana, jalankan SET SHOWPLAN_XML ON sebelum kueri atau pilih Tampilkan Perkiraan Rencana Eksekusi di SQL Server Management Studio (SSMS). Untuk mengambil rencana aktual, jalankan SET STATISTICS XML ON atau pilih Sertakan Rencana Eksekusi Aktual di SSMS sebelum menjalankan kueri.

Meskipun perkiraan dan rencana aktual terlihat mirip, metrik runtime rencana aktual sangat penting untuk mendiagnosis masalah performa. Misalnya, jika jumlah baris yang diestimasi untuk pemindaian tabel adalah 100 tetapi jumlah baris sebenarnya adalah 10.000, itu dapat menunjukkan statistik yang kedaluwarsa yang dapat menyebabkan rencana yang buruk. Pengoptimal mengkompilasi rencana berdasarkan statistik saat pertama kali menemukan kueri. Jika statistik tersebut tidak mencerminkan distribusi data saat ini, rencana tersebut mungkin berkinerja buruk.

Mengidentifikasi masalah umum dalam rencana eksekusi

Rencana eksekusi dibaca dari kiri ke kanan, atas ke bawah. Operator pertama mengakses tabel dasar, dan operator akhir menghasilkan hasil kueri. Perhatikan masalah umum berikut:

Jenis operator memberi tahu Anda cara mesin mengakses data. Ada banyak jenis operator, masing-masing mewakili metode yang berbeda untuk mengambil atau memproses data. Misalnya, operator Pencarian Indeks mewakili metode yang sangat efisien yang menargetkan baris tertentu menggunakan kunci indeks. Operator Pemindaian Tabel atau Pemindaian Indeks di sisi lain, mewakili metode yang kurang efisien yang membaca setiap baris. Jika Anda melihat pemindaian pada tabel besar, Anda mungkin memerlukan indeks. Misalnya, jika aplikasi e-niaga meminta pesanan berdasarkan tanggal dan paket menunjukkan Pemindaian Indeks Berkluster pada Orders tabel, menambahkan indeks non-kluster pada OrderDate kolom dapat mengubah pemindaian tersebut menjadi pencarian. Harap dicatat bahwa tidak semua pemindaian itu buruk. Jika tabel kecil, atau kondisi pencarian mengembalikan sebagian besar baris dalam tabel, pemindaian mungkin merupakan metode akses yang paling efisien. Selalu pertimbangkan konteks kueri dan ukuran data. Ketahui data Anda dan gunakan rencana eksekusi untuk mengonfirmasi apakah metode akses masuk akal.

Perkiraan jumlah baris versus aktual mengungkapkan apakah asumsi pengoptimal cocok dengan realitas. Pengoptimal mendasarkan rencananya pada statistik, metadata yang menjelaskan distribusi dan kepadatan data dalam tabel Anda. Jika statistik tersebut kedaluarsa, perkiraan dan jumlah baris aktual berbeda. Ketika pengoptimal meremehkan jumlah baris, pengoptimal mungkin memilih gabungan perulangan berlapis (yang memproses satu baris pada satu waktu dari tabel dalam gabungan) ketika gabungan hash (yang membangun tabel hash dalam memori untuk pencarian cepat) akan lebih cepat, atau mengalokasikan terlalu sedikit memori untuk operasi pengurutan. Statistik dapat menjadi basi setelah perubahan data yang signifikan, sehingga memperbarui statistik dengan UPDATE STATISTICS atau mengaktifkan pembaruan statistik otomatis dapat membantu pengoptimal membuat keputusan yang lebih baik.

Operator Pencarian Kunci muncul ketika mesin menemukan baris melalui indeks non-kluster tetapi membutuhkan kolom tambahan dari indeks berkluster. Untuk setiap baris yang cocok, mesin melakukan akses berulang ke indeks berkluster untuk mengambil kolom-kolom tersebut. Jika filter mengembalikan banyak baris, pencarian tambahan tersebut akan bertambah dengan cepat. Misalnya, jika aplikasi e-niaga memfilter pesanan menurut CustomerID tetapi juga memilih OrderDate, TotalAmount, dan ShippingAddress, dan indeks nonclustered pada CustomerID tidak menyertakan kolom tersebut, rencana menunjukkan Pencarian Kunci untuk setiap pesanan yang sesuai. Anda dapat menghilangkan Pencarian Kunci dengan menambahkan kolom yang hilang sebagai kolom yang disertakan dalam indeks. Perlu diingat bahwa kolom yang disertakan meningkatkan ukuran indeks, yang dapat memperlambat penulisan, jadi mempertimbangkan manfaat performa baca terhadap overhead tulis.

Panah tebal antar operator mewakili jumlah baris yang mengalir di antaranya. Panah tebal yang tidak terduga di awal rencana (membaca dari kiri ke kanan, atas ke bawah) sering berarti filter atau indeks yang hilang membiarkan terlalu banyak baris.

Saran indeks hilang muncul sebagai teks berwarna hijau yang disorot di bagian atas rencana eksekusi grafis di SSMS. Ketika pengoptimal mendeteksi bahwa indeks dapat secara signifikan mengurangi biaya kueri, pengoptimal menampilkan rekomendasi langsung dalam rencana. Klik kanan pada saran dan pilih Detail Indeks yang Hilang untuk menghasilkan pernyataan CREATE INDEX yang dapat Anda tinjau dan jalankan. Saran ini adalah salah satu kemenangan termudah yang bisa Anda dapatkan dari membaca rencana eksekusi.

Peringatan muncul sebagai segitiga kuning dengan tanda seru (⚠) pada operator yang terpengaruh. Setiap peringatan menunjuk ke peluang pengoptimalan. Peringatan umum meliputi:

• Statistik yang hilang: Pengoptimal tidak dapat menemukan statistik untuk kolom, sehingga ditebak pada jumlah baris alih-alih menggunakan distribusi data aktual. Untuk memperbaiki masalah ini, buat statistik pada kolom yang digunakan dalam kueri Anda atau perbarui statistik yang ada jika sudah kedaluarsa.
• Pemberian memori yang berlebihan: Kueri meminta lebih banyak memori daripada yang diperlukan, membuang-buang sumber daya yang dapat digunakan kueri lain. Masalah ini sering terjadi ketika pengoptimal memperkirakan jumlah baris secara berlebihan. Memperbarui statistik atau menulis ulang kueri untuk memfilter baris sebelumnya dapat membantu mengurangi peruntukan memori.
• Tanpa Predikat Gabungan: Dua tabel digabungkan tanpa kondisi yang tepat, menghasilkan produk Kartesius yang mengembalikan setiap kemungkinan kombinasi baris. Periksa kueri Anda untuk memastikan tidak ada klausa yang hilang atau salah pada ON.
• Konversi implisit: Jenis data tidak cocok memaksa mesin untuk mengonversi nilai pada runtime, yang dapat mengubah pencarian indeks menjadi pemindaian. Misalnya, jika WHERE klausul membandingkan nvarchar parameter dengan varchar kolom, mesin mengonversi setiap baris di kolom menjadi nvarchar sebelum membandingkan. Untuk memperbaiki konversi implisit, cocokkan jenis data dalam parameter kueri Anda dengan definisi kolom.
• Sort atau Hash melimpahkan: Operasi pengurutan atau hash kehabisan memori yang dialokasikan dan melimpahkan hasil sementara ke tempdb. Operasi ini adalah penyebab penggunaan CPU tinggi yang paling umum kedua setelah pemindaian. Jika Anda melihat peringatan tumpahan memori, kemungkinan pengoptimal membuat perkiraan jumlah baris yang lebih rendah dan meminta memori yang terlalu sedikit. Menjalankan UPDATE STATISTICS untuk menyegarkan statistik tabel atau menulis ulang kueri untuk mengurangi jumlah baris sebelum pengurutan sering dapat menghilangkan tumpahan.

Rencana eksekusi adalah alat yang ampuh untuk memahami performa kueri. Mereka menunjukkan kepada Anda dengan tepat bagaimana mesin menjalankan kueri dan di mana hambatan berada. Dengan belajar membaca rencana eksekusi secara efektif, Anda dapat dengan cepat mengidentifikasi dan memperbaiki masalah performa dalam kueri database Anda.

Lakukan kueri pada DMV untuk data kinerja runtime

DMVs memperlihatkan data performa waktu nyata dan akumulasi dari mesin database. Azure SQL Database memerlukan izin VIEW DATABASE STATE untuk melakukan kueri terhadapnya. Meskipun rencana eksekusi menunjukkan kepada Anda bagaimana satu kueri berjalan, Dynamic Management Views (DMVs) menunjukkan apa yang terjadi di semua kueri. Ini membantu Anda menemukan kueri yang paling mahal terlebih dahulu.

Temukan kueri termahal

Waktu CPU, pembacaan logis, dan jumlah eksekusi adalah metrik yang paling umum untuk mengidentifikasi kueri yang mahal. Waktu CPU yang tinggi atau pembacaan logis menunjukkan kueri yang menggunakan banyak sumber daya, sementara jumlah eksekusi yang tinggi berarti bahkan kueri yang tidak terlalu mahal dapat memiliki dampak besar pada kinerja keseluruhan. Mulailah dengan meninjau kueri teratas berdasarkan waktu CPU rata-rata atau pembacaan logis untuk menemukan kandidat pengoptimalan.

sys.dm_exec_query_stats mengembalikan statistik performa agregat untuk rencana kueri yang di-cache. Gabungkan dengan sys.dm_exec_sql_text untuk melihat teks kueri dan sys.dm_exec_query_plan untuk mengambil rencana eksekusi.

Kueri berikut menemukan 10 kueri teratas menurut waktu CPU rata-rata:

SELECT TOP 10
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset) / 2) + 1) AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY avg_cpu_time DESC;

Skrip ini membantu Anda mengidentifikasi kueri mana yang layak mendapatkan perhatian Anda. Tinggi relatif avg_logical_reads terhadap ukuran set hasil sering kali menunjukkan indeks yang kurang atau perencanaan yang tidak efisien. Namun, berhati-hatilah saat menafsirkan hasil ini. Kueri dengan waktu CPU rata-rata tinggi yang hanya berjalan sekali sehari mungkin kurang dari kueri sedang yang berjalan ribuan kali per jam. Selalu pertimbangkan biaya rata-rata dan jumlah eksekusi saat Anda memprioritaskan. Anda juga dapat mengurutkan berdasarkan avg_logical_reads untuk menemukan kueri yang menggunakan I/O secara intensif, yang sering menunjukkan ketiadaan indeks atau metode akses yang tidak efisien.

Periksa kueri yang sedang dijalankan

Meskipun kueri sebelumnya menunjukkan kueri bersejarah paling mahal dalam cache rencana, sys.dm_exec_requests memberi Anda cuplikan dari setiap permintaan yang saat ini berjalan. Ini termasuk kolom untuk waktu CPU, pembacaan, penulisan, jenis tunggu, waktu tunggu, dan ID sesi pemblokiran. Gunakan tampilan ini untuk melihat kueri aktif yang mengonsumsi terlalu banyak sumber daya atau terjebak menunggu pada kunci. Tampilan ini adalah salah satu DMV terpenting untuk pemantauan dan pemecahan masalah kinerja real-time.

SELECT
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    r.cpu_time,
    r.logical_reads,
    t.text AS query_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id > 50
ORDER BY r.cpu_time DESC;

Kueri ini memfilter sesi sistem dengan ID sesi 1-50 dan mengurutkan berdasarkan waktu CPU. Anda juga dapat mengurutkan berdasarkan logical_reads untuk menemukan kueri yang banyak menggunakan I/O. Kolom wait_type dan wait_time membantu Anda mengidentifikasi apakah kueri menunggu kunci, I/O, atau sumber daya lainnya.

Menemukan indeks yang hilang

Sebelumnya, kami melihat bagaimana rencana eksekusi dapat menunjukkan kepada Anda saran indeks yang hilang untuk satu kueri. Indeks yang hilang pada DMV (Dynamic Management Views) memberi Anda pandangan yang lebih luas tentang indeks mana yang akan digunakan pengoptimal kueri di semua kueri jika indeks tersebut tersedia. Tampilan ini adalah cara yang bagus untuk menemukan peluang pengoptimalan yang memengaruhi beberapa kueri. sys.dm_db_missing_index_details memperlihatkan tabel, kolom kesetaraan dan ketidaksetaraan, dan kolom yang disertakan. sys.dm_db_missing_index_group_stats menyediakan ukuran peningkatan yang menghitung pengurangan biaya.

SELECT
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.avg_total_user_cost * migs.avg_user_impact *
        (migs.user_seeks + migs.user_scans) AS improvement_measure
FROM sys.dm_db_missing_index_groups AS mig
INNER JOIN sys.dm_db_missing_index_group_stats AS migs
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mig.index_handle = mid.index_handle
ORDER BY improvement_measure DESC;

Kueri ini menghitung improvement_measure untuk setiap rekomendasi indeks yang hilang, yang merupakan produk dari biaya rata-rata kueri yang akan mendapat manfaat dari indeks, peningkatan persentase rata-rata, dan berapa kali kueri tersebut dijalankan. Mengurutkan menurut langkah ini membantu Anda memprioritaskan indeks mana yang hilang untuk dibuat terlebih dahulu. Namun, ingatlah bahwa hasil ini hanyalah rekomendasi berdasarkan kueri yang saat ini ada di cache rencana. Selalu tinjau kolom indeks yang disarankan dan uji dampaknya pada performa kueri dan biaya tulis sebelum menambahkannya ke lingkungan produksi.

Memantau sesi aktif dan tugas tunggu

sys.dm_exec_sessions memberi Anda informasi tentang semua sesi yang diautentikasi, termasuk waktu masuk, nama host, nama program, serta penggunaan CPU kumulatif dan pembacaan. Gabungkan dengan sys.dm_os_waiting_tasks untuk melihat tugas mana yang menunggu dan sumber daya apa yang mereka tunggu. Tampilan ini menjadi penting ketika Anda mendiagnosis pemblokiran dan perebutan sumber daya di unit selanjutnya.

Satukan semuanya

Rencana eksekusi dan DMV (Dynamic Management Views) memberi Anda gambaran lengkap tentang perilaku kueri. Mulailah dengan DMV untuk mengidentifikasi kueri termahal. Kemudian telusuri rencana eksekusi mereka untuk memahami mengapa mereka mahal. Apakah indeks yang hilang menyebabkan pemindaian? Statistik yang kedaluarsa menyebabkan kesalahan perkiraan baris? Pencarian Kunci yang dapat Anda hilangkan? Pendekatan sistematis ini, dari tampilan di seluruh sistem hingga analisis kueri individual, adalah cara paling efisien untuk menemukan dan memperbaiki hambatan performa.

Poin-poin Penting

Rencana eksekusi mengungkapkan strategi pengoptimal untuk kueri, dan rencana aktual termasuk metrik runtime yang mengekspos perbedaan antara perkiraan dan jumlah baris aktual. Saat Anda membaca rencana, fokus pada jenis operator (pencarian vs. pemindaian), perkiraan jumlah baris, peringatan, dan operator Key Lookup. DMV menyediakan data performa di seluruh sistem: gunakan sys.dm_exec_query_stats untuk menemukan kueri paling mahal, sys.dm_exec_requests untuk kueri yang sedang berjalan, dan DMV indeks yang hilang untuk peluang pengoptimalan. Mulailah secara luas dengan DMV untuk mengidentifikasi di mana masalah terbesar berada, lalu telusuri rencana eksekusi individu untuk memahami alasannya.