Bagikan melalui

Jenis penyempitan performa kueri yang dapat dideteksi di SQL Server dan Azure SQL Managed Instance

  • Artikel

Berlaku untuk:SQL Server Azure SQL Managed Instance

Saat mencoba mengatasi penyempitan performa, mulailah dengan menentukan apakah penyempitan terjadi saat kueri dalam status berjalan atau status menunggu. Resolusi yang berbeda berlaku tergantung pada penentuan ini. Masalah dan resolusi yang berkaitan dengan setiap jenis masalah dibahas dalam artikel ini.

Anda dapat menggunakan DMV SQL Server untuk mendeteksi jenis hambatan performa ini.

Masalah terkait status berjalan: Masalah terkait status berjalan umumnya berhubungan dengan masalah kompilasi yang mengakibatkan rencana kueri suboptimal atau masalah eksekusi yang berhubungan dengan sumber daya yang tidak mencukupi atau terlalu sering digunakan. Masalah terkait status menunggu: Masalah terkait status menunggu umumnya berkaitan dengan:

  • Penguncian (pemblokiran)
  • I/O
  • Ketidakcocokan terkait dengan tempdb penggunaan
  • Waktu tunggu peruntukan memori

Artikel ini berisi tentang SQL Server dan Azure SQL Managed Instance, lihat juga Jenis penyempitan performa kueri yang dapat dideteksi di Azure SQL Database.

Masalah kompilasi yang menghasilkan rencana kueri suboptimal

Rencana suboptimal yang dihasilkan oleh Pengoptimalan Kueri SQL mungkin menjadi penyebab performa kueri yang lambat. Pengoptimalan Kueri SQL mungkin menghasilkan rencana suboptimal karena indeks yang hilang, statistik usang, estimasi yang salah dari jumlah baris yang akan diproses, atau estimasi yang tidak akurat dari memori yang diperlukan. Jika Anda tahu kueri dijalankan lebih cepat di masa lalu atau pada instans lain, bandingkan rencana eksekusi aktual untuk melihat apakah ada perbedaan.

Contoh penyetelan dan petunjuk kueri dalam artikel Menyetel aplikasi dan database untuk performa menunjukkan dampak rencana kueri suboptimal karena kueri berparameter, cara mendeteksi kondisi ini, dan cara menggunakan petunjuk kueri untuk mengatasinya.

Mengatasi kueri dengan paket eksekusi kueri suboptimal

Bagian berikut ini membahas cara mengatasi kueri dengan rencana eksekusi kueri suboptimal.

Kueri-kueri yang memiliki masalah rencana sensitif parameter (PSP)

Masalah rencana sensitif parameter (PSP) terjadi ketika pengoptimal kueri menghasilkan rencana eksekusi kueri yang hanya optimal untuk nilai parameter tertentu (atau kumpulan nilai) dan rencana yang di-cache kemudian tidak optimal untuk nilai parameter yang digunakan dalam eksekusi berturut-turut. Rencana yang tidak optimal kemudian dapat menyebabkan masalah performa kueri dan menurunkan throughput beban kerja secara keseluruhan.

Untuk mengetahui informasi selengkapnya tentang pengendusan parameter dan pemrosesan kueri, lihat Panduan arsitektur pemrosesan kueri.

Beberapa solusi dapat mengurangi masalah PSP. Setiap solusi memiliki kompromi dan kelemahan terkait:

  • Fitur baru yang diperkenalkan dengan SQL Server 2022 (16.x) adalah pengoptimalan Rencana Sensitif Parameter, yang mencoba mengurangi sebagian besar rencana kueri suboptimal yang disebabkan oleh sensitivitas parameter. Ini diaktifkan dengan tingkat kompatibilitas database 160.
  • Gunakan petunjuk kueri RECOMPILE pada setiap eksekusi kueri. Solusi ini mengorbankan waktu kompilasi dan peningkatan CPU untuk kualitas rencana yang lebih baik. Opsi RECOMPILE seringkali tidak mungkin untuk beban kerja yang membutuhkan throughput tinggi.
  • Gunakan petunjuk kueri OPTION (OPTIMIZE FOR...) untuk mengganti nilai parameter aktual dengan nilai parameter umum yang menghasilkan rencana yang cukup baik untuk sebagian besar kemungkinan nilai parameter. Opsi ini membutuhkan pemahaman yang baik tentang nilai parameter optimal dan karakteristik rencana terkait.
  • Gunakan petunjuk kueri OPTION (OPTIMIZE FOR UNKNOWN) untuk mengganti nilai parameter aktual dan sebaliknya menggunakan rata-rata vektor kepadatan. Anda juga dapat melakukan ini dengan menangkap nilai parameter masuk dalam variabel lokal dan kemudian menggunakan variabel lokal dalam predikat alih-alih menggunakan parameter itu sendiri. Untuk perbaikan ini, kepadatan rata-rata harus cukup baik.
  • Nonaktifkan pengendusan parameter sepenuhnya dengan menggunakan petunjuk kueri DISABLE_PARAMETER_SNIFFING.
  • Gunakan petunjuk kueri KEEPFIXEDPLAN untuk mencegah kompilasi ulang dalam cache. Solusi ini mengasumsikan bahwa rencana umum yang cukup baik adalah yang sudah ada di cache. Anda juga dapat menonaktifkan pembaruan statistik otomatis untuk mengurangi kemungkinan bahwa rencana yang baik akan dikeluarkan dan rencana buruk baru akan dikompilasi.
  • Paksa rencana dengan secara eksplisit menggunakan petunjuk kueri USE PLAN dengan menulis ulang kueri dan menambahkan petunjuk dalam teks kueri. Atau atur rencana tertentu dengan menggunakan Query Store atau dengan mengaktifkan penyetelan otomatis.
  • Ganti prosedur tunggal dengan serangkaian prosedur berlapis yang masing-masing dapat digunakan berdasarkan logika bersyarat dan nilai parameter terkait.
  • Buat alternatif eksekusi string dinamis untuk definisi prosedur statis.

Untuk menerapkan petunjuk kueri, ubah kueri, atau gunakan petunjuk Penyimpanan Kueri untuk menerapkan petunjuk tanpa membuat perubahan kode. Dalam versi SQL Server sebelum SQL Server 2022, gunakan panduan paket.

Untuk informasi selengkapnya tentang cara mengatasi masalah PSP, baca posting blog ini:

Aktivitas kompilasi yang disebabkan oleh parameterisasi yang tidak tepat

Saat kueri memiliki literal, antara mesin database secara otomatis membuat parameter pada pernyataan atau pengguna secara eksplisit membuat parameter pada pernyataan untuk mengurangi jumlah kompilasi. Sejumlah besar kompilasi untuk kueri yang menggunakan pola yang sama tetapi nilai literal yang berbeda dapat menghasilkan penggunaan CPU yang tinggi. Demikian pula, jika Anda hanya membuat parameter pada sebagian kueri yang terus memiliki literal, mesin database tidak membuat parameter pada kueri lebih lanjut.

Berikut ini contoh kueri berparameter parsial:

SELECT *
FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c1 = @p1 AND t2.c2 = '961C3970-0E54-4E8E-82B6-5545BE897F8F';

Dalam contoh ini, t1.c1 menggunakan @p1, tetapi t2.c2 terus menggunakan GUID sebagai literal. Dalam hal ini, jika Anda mengubah nilai untuk c2, kueri akan diperlakukan sebagai kueri yang berbeda dan kompilasi baru akan terjadi. Untuk mengurangi kompilasi dalam contoh ini, Anda juga akan membuat parameter pada GUID.

Kueri berikut ini memperlihatkan jumlah kueri menurut hash kueri untuk menentukan apakah kueri diberikan parameter dengan benar:

SELECT TOP 10
  q.query_hash
  , count (distinct p.query_id ) AS number_of_distinct_query_ids
  , min(qt.query_sql_text) AS sampled_query_text
FROM sys.query_store_query_text AS qt
  JOIN sys.query_store_query AS q
     ON qt.query_text_id = q.query_text_id
  JOIN sys.query_store_plan AS p
     ON q.query_id = p.query_id
  JOIN sys.query_store_runtime_stats AS rs
     ON rs.plan_id = p.plan_id
  JOIN sys.query_store_runtime_stats_interval AS rsi
     ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE
  rsi.start_time >= DATEADD(hour, -2, GETUTCDATE())
  AND query_parameterization_type_desc IN ('User', 'None')
GROUP BY q.query_hash
ORDER BY count (distinct p.query_id) DESC;

Faktor-faktor yang memengaruhi perubahan rencana kueri

Kompilasi ulang rencana eksekusi kueri mungkin memunculkan hasil rencana kueri yang berbeda dari rencana cache asli. Rencana asli yang ada mungkin secara otomatis dikompilasi ulang karena berbagai alasan:

  • Perubahan dalam skema direferensikan oleh kueri
  • Perubahan data pada tabel direferensikan oleh kueri
  • Opsi konteks kueri diubah

Rencana yang dikompilasi mungkin dikeluarkan dari cache karena berbagai alasan seperti:

  • Penghidupan ulang instans
  • Perubahan konfigurasi yang dicakup database
  • Tekanan memori
  • Permintaan eksplisit untuk membersihkan cache

Jika Anda menggunakan petunjuk RECOMPILE, rencana tidak akan di-cache.

Kompilasi ulang (atau kompilasi baru setelah pengeluaran cache) masih dapat menghasilkan pembuatan rencana eksekusi kueri yang identik dengan aslinya. Saat rencana berubah dari paket sebelumnya atau awal, penjelasan ini kemungkinan:

  • Desain fisik yang berubah: Misalnya, indeks yang baru dibuat lebih efektif mencakup persyaratan kueri. Indeks baru mungkin digunakan pada kompilasi baru jika pengoptimal kueri memutuskan bahwa menggunakan indeks baru tersebut lebih optimal daripada menggunakan struktur data yang awalnya dipilih untuk versi pertama eksekusi kueri. Setiap perubahan fisik pada objek yang direferensikan dapat mengakibatkan pilihan rencana baru pada waktu kompilasi.

  • Perbedaan sumber daya server: Saat rencana dalam satu sistem berbeda dari rencana di sistem lain, ketersediaan sumber daya, seperti jumlah prosesor yang tersedia, dapat memengaruhi rencana mana yang dihasilkan. Misalnya, jika satu sistem memiliki lebih banyak prosesor, rencana paralel mungkin dipilih. Untuk informasi selengkapnya tentang paralelisme, lihat Mengonfigurasi tingkat maksimum Opsi Konfigurasi Server paralelisme.

  • Statistik yang berbeda: Statistik yang terkait objek yang direferensikan mungkin telah berubah atau mungkin berbeda secara material dari statistik sistem asli. Jika statistik berubah dan kompilasi ulang terjadi, pengoptimal kueri akan menggunakan statistik mulai dari waktu perubahan terjadi. Distribusi dan frekuensi data statistik yang direvisi mungkin berbeda dari kompilasi asli. Perubahan ini digunakan untuk menciptakan estimasi kardinalitas. (Estimasi kardinalitas adalah jumlah baris yang diharapkan mengalir melalui pohon kueri logis.) Perubahan pada estimasi kardinalitas dapat menyebabkan Anda memilih operator fisik dan perintah operasi terkait yang berbeda. Bahkan perubahan kecil pada statistik dapat mengakibatkan perubahan pada rencana eksekusi kueri.

  • Perubahan tingkat kompatibilitas database atau versi estimator kardinalitas: Perubahan pada tingkat kompatibilitas database dapat mengaktifkan strategi dan fitur baru yang mungkin menghasilkan rencana eksekusi kueri yang berbeda. Di luar tingkat kompatibilitas database, bendera pelacakan 4199 yang dinonaktifkan atau diaktifkan atau perubahan pada status QUERY_OPTIMIZER_HOTFIXES konfigurasi yang dicakup database dapat memengaruhi pilihan rencana eksekusi kueri pada waktu kompilasi. Bendera pelacakan 9481 (memaksa CE legasi) dan 2312 (memaksa CE default) juga memengaruhi rencana.

Masalah batas sumber daya di Azure SQL Managed Instance

Performa kueri lambat yang tidak terkait rencana kueri suboptimal dan indeks yang hilang umumnya terkait sumber daya yang tidak mencukupi atau terlalu sering digunakan. Jika rencana kueri optimal, kueri (dan database) mungkin mencapai batas sumber daya untuk instans terkelola. Contohnya mungkin throughput penulisan log berlebih untuk tingkat layanan.

Jika Anda mengidentifikasi masalahnya sebagai sumber daya yang tidak mencukupi, Anda dapat meningkatkan sumber daya untuk menambah kapasitas database Anda untuk menyerap persyaratan CPU. Untuk mengetahui informasi tentang penskalaan instans terkelola, lihat Batas sumber daya tingkat layanan

Masalah performa yang disebabkan oleh peningkatan volume beban kerja

Peningkatan lalu lintas aplikasi dan volume beban kerja dapat menyebabkan peningkatan penggunaan CPU. Namun, Anda harus berhati-hati untuk mendiagnosis masalah ini dengan benar. Saat Anda melihat masalah CPU tinggi, jawab pertanyaan berikut untuk menentukan apakah peningkatan disebabkan oleh perubahan volume beban kerja:

  • Apakah kueri dari aplikasi menjadi penyebab masalah CPU tinggi?

  • Untuk kueri penggunaan CPU teratas yang dapat Anda identifikasi:

    • Apakah beberapa rencana eksekusi terkait dengan kueri yang sama? Jika demikian, mengapa?
    • Untuk kueri dengan rencana eksekusi yang sama, apakah waktu eksekusinya konsisten? Apakah jumlah eksekusi meningkat? Jika demikian, peningkatan beban kerja kemungkinan menyebabkan masalah performa.

Singkatnya, jika rencana eksekusi kueri tidak dijalankan secara berbeda tetapi penggunaan CPU meningkat seiring dengan jumlah eksekusi, masalah performa kemungkinan terkait peningkatan beban kerja.

Tidak selalu mudah untuk mengidentifikasi perubahan volume beban kerja yang mendorong masalah CPU. Pertimbangkan faktor-faktor ini:

  • Perubahan penggunaan sumber daya: Misalnya, pertimbangkan skenario di mana penggunaan CPU meningkat menjadi 80 persen untuk jangka waktu yang lama. Penggunaan CPU saja bukan berarti volume beban kerja berubah. Regresi dalam rencana eksekusi kueri dan perubahan distribusi data juga dapat berkontribusi pada lebih banyak penggunaan sumber daya meskipun aplikasi menjalankan beban kerja yang sama.

  • Tampilan kueri baru: Aplikasi mungkin mendorong serangkaian kueri baru pada waktu yang berbeda.

  • Peningkatan atau penurunan jumlah permintaan: Skenario ini adalah ukuran beban kerja yang paling jelas. Jumlah kueri tidak selalu sesuai dengan lebih banyak pemanfaatan sumber daya. Namun, metrik ini masih merupakan sinyal yang signifikan, dengan asumsi faktor lain tidak berubah.

  • Paralelisme: Paralelisme yang berlebihan dapat memperburuk performa beban kerja bersamaan lainnya dengan kelaparan kueri lain dari sumber daya CPU dan utas pekerja. Untuk informasi selengkapnya tentang paralelisme, lihat Mengonfigurasi tingkat maksimum Opsi Konfigurasi Server paralelisme.

Setelah Anda menghilangkan rencana suboptimal dan masalah Terkait status menunggu yang berhubungan dengan masalah eksekusi, masalah performa umumnya adalah kueri mungkin menunggu sejumlah sumber daya. Masalah terkait status menunggu dapat disebabkan oleh:

  • Pemblokiran:

    Satu kueri mungkin menahan kunci pada objek dalam database sementara yang lain mencoba mengakses objek yang sama. Anda dapat mengidentifikasi pemblokiran kueri dengan menggunakan DMV. Untuk informasi selengkapnya, lihat Memahami dan mengatasi masalah pemblokiran.

  • Masalah IO

    Kueri mungkin menunggu halaman ditulis ke data atau file log. Dalam hal ini, periksa INSTANCE_LOG_RATE_GOVERNOR, WRITE_LOG, atau PAGEIOLATCH_* tunggu statistik di DMV. Lihat menggunakan DMV untuk mengidentifikasi masalah performa IO.

  • Masalah tempdb

    Jika beban kerja menggunakan tabel sementara atau terdapat peluapan tempdb dalam rencana, kueri mungkin memiliki masalah dengan throughput tempdb. Untuk menyelidiki lebih lanjut, tinjau mengidentifikasi masalah tempdb.

  • Masalah terkait memori

    Jika beban kerja tidak memiliki cukup memori, harapan hidup halaman mungkin menurun atau kueri mungkin mendapatkan lebih sedikit memori daripada yang dibutuhkan. Dalam beberapa kasus, kecerdasan bawaan di Pengoptimalan Kueri akan memperbaiki masalah terkait memori. Lihat menggunakan DMV untuk mengidentifikasi masalah peruntukan memori. Jika Anda menemukan kesalahan memori habis, tinjau sys.dm_os_out_of_memory_events. Pertimbangkan juga tingkat seri premium memori yang dioptimalkan dari perangkat keras Azure SQL Managed Instance dengan rasio memori yang lebih tinggi ke vCores.

Metode untuk menampilkan kategori tunggu teratas

Metode ini biasanya digunakan untuk menunjukkan kategori teratas jenis tunggu:

  • Gunakan Query Store untuk menemukan statistik tunggu untuk setiap kueri dari waktu ke waktu. Di Query Store, jenis waktu tunggu digabungkan ke dalam kategori waktu tunggu. Anda dapat menemukan pemetaan kategori tunggu untuk menunggu jenis di sys.query_store_wait_stats.
  • Gunakan sys.dm_os_wait_stats untuk mengembalikan informasi tentang semua tunggu yang ditemui oleh utas yang dijalankan selama operasi kueri. Anda dapat menggunakan tampilan agregat ini untuk mendiagnosis masalah performa dengan instans Azure SQL Managed Instance atau SQL Server. Kueri bisa menunggu sumber daya, antrean menunggu, atau menunggu eksternal.
  • Gunakan sys.dm_os_waiting_tasks untuk mengembalikan informasi tentang antrean tugas yang menunggu sejumlah sumber daya.

Dalam skenario CPU tinggi, Query Store dan statistik tunggu mungkin tidak mencerminkan penggunaan CPU jika:

  • Kueri yang mengonsumsi CPU tinggi masih dieksekusi.
  • Kueri yang mengonsumsi CPU tinggi sedang berjalan ketika kegagalan terjadi.

DVD yang melacak Query Store dan statistik tunggu memperlihatkan hasil hanya untuk kueri yang berhasil diselesaikan dan kehabisan waktu. DVD tidak menampilkan data untuk pernyataan yang saat ini melakukan eksekusi sampai pernyataan selesai. Gunakan tampilan manajemen dinamis sys.dm_exec_requests untuk melacak kueri yang sedang dijalankan saat ini dan waktu pekerja terkait.

Tip

Alat tambahan:

Langkah berikutnya