Menjelaskan statistik tunggu

Selesai

Pendekatan komprehensif untuk memantau performa server melibatkan evaluasi apa yang ditunggu oleh server. Statistik waktu tunggu adalah kompleks, dan SQL Server dilengkapi dengan ratusan tipe waktu tunggu yang memantau setiap utas yang berjalan serta mencatat apa yang ditunggu oleh utas tersebut.

Untuk mendeteksi dan memecahkan masalah performa SQL Server secara efektif, penting untuk memahami cara kerja statistik tunggu dan bagaimana mesin database menggunakannya saat memproses permintaan. Pengetahuan ini memungkinkan Anda menentukan hambatan dan mengoptimalkan performa secara lebih akurat.

Statistik waktu jeda dipecah menjadi tiga jenis waktu jeda: waktu jeda sumber daya, waktu jeda antrean, dan waktu jeda eksternal.

• Waktu jeda sumber daya terjadi saat alur pekerja di SQL Server meminta akses ke sumber daya yang sedang digunakan oleh alur. Contoh dari waktu jeda sumber daya adalah kunci, latch, dan waktu jeda I/O disk.
• Waktu jeda antrean terjadi saat alur pekerja diam dan menunggu pekerjaan ditetapkan. Contoh antrean tunggu adalah pemantauan kebuntuan dan pembersihan rekaman yang dihapus.
• Waktu jeda eksternal terjadi saat SQL Server menunggu proses eksternal seperti kueri server yang ditautkan untuk diselesaikan. Contoh eksternal tunggu adalah menunggu jaringan yang terkait dengan mengembalikan hasil besar ke aplikasi klien.

Anda dapat memeriksa tampilan sistem sys.dm_os_wait_stats untuk menjelajahi semua waktu jeda yang dihadapi oleh alur yang dijalankan, dan sys.dm_db_wait_stats untuk Azure SQL Database. Tampilan sistem sys.dm_exec_session_wait_stats mencantumkan sesi waktu jeda yang aktif.

Tampilan sistem ini memungkinkan Anda mendapatkan gambaran umum performa server, dan dengan mudah mengidentifikasi masalah konfigurasi atau perangkat keras. Data ini dipertahankan sejak saat pengaktifan instans, tetapi data dapat dihapus sesuai kebutuhan untuk mengidentifikasi perubahan.

Statistik tunggu dievaluasi sebagai persentase dari total tunggu di server.

Hasil kueri ini dari sys.dm_os_wait_stats menunjukkan jenis waktu jeda, dan agregasi persentase waktu jeda (kolom Persentase Waktu Jeda) dan waktu jeda rata-rata dalam detik untuk setiap jenis waktu jeda.

Dalam kasus ini, server memiliki Grup Ketersediaan AlwaysOn, seperti yang ditunjukkan oleh jenis waktu jeda REDO_THREAD_PENDING_WORK dan PARALLEL_REDO_TRAN_TURN. Persentase waktu jeda CXPACKET dan SOS_SCHEDULER_YIELD yang relatif tinggi menunjukkan bahwa server ini berada di bawah tekanan CPU.

Karena DMV menyediakan daftar jenis waktu jeda dengan akumulasi waktu tertinggi sejak pengaktifan SQL Server terakhir, mengumpulkan dan menyimpan data statistik waktu jeda secara berkala dapat membantu Anda memahami dan menghubungkan masalah performa dengan peristiwa database lainnya.

Mempertimbangkan bahwa DMV memberi Anda daftar jenis waktu jeda dengan akumulasi waktu tertinggi sejak pengaktifan SQL Server terakhir, mengumpulkan dan menyimpan statistik waktu jeda secara berkala dapat membantu Anda memahami dan menghubungkan masalah performa dengan peristiwa database lainnya.

Ada beberapa jenis waktu jeda yang tersedia di SQL Server, tetapi beberapa di antaranya umum.

• RESOURCE_SEMAPHORE—menunjukkan bahwa kueri menunggu agar memori tersedia, sering kali karena alokasi memori yang berlebihan untuk kueri tertentu. Masalah ini biasanya bermanifestasi sebagai runtime kueri yang panjang atau bahkan waktu habis. Penyebab jenis tunggu ini dapat mencakup statistik kedaluarsa, indeks yang hilang, dan konkurensi kueri tinggi.

• LCK_M_X—sering menunjukkan masalah pemblokiran. Masalah ini dapat diatasi dengan mengubah ke READ COMMITTED SNAPSHOT tingkat isolasi, mengoptimalkan pengindeksan untuk mengurangi waktu transaksi, atau meningkatkan manajemen transaksi dalam kode T-SQL.

• PAGEIOLATCH_SH—jenis tunggu ini dapat menunjukkan masalah dengan indeks atau tidak adanya indeks yang berguna, menyebabkan SQL Server memindai jumlah data yang berlebihan. Atau, jika jumlah tunggu rendah tetapi waktu tunggu tinggi, mungkin menyarankan masalah performa penyimpanan. Anda dapat mengamati perilaku ini dengan menganalisis data dalam waiting_tasks_count kolom dan wait_time_ms dalam sys.dm_os_wait_stats tampilan sistem untuk menghitung waktu tunggu rata-rata untuk jenis tunggu tertentu.

• SOS_SCHEDULER_YIELD—jenis waktu jeda ini dapat menunjukkan penggunaan CPU yang tinggi, yang berkaitan dengan jumlah pemindaian besar yang tinggi, atau indeks yang hilang, dan sering kali dengan jumlah waktu jeda CXPACKET yang tinggi.

• CXPACKET—Kemunculan tinggi jenis tunggu ini dapat menunjukkan konfigurasi yang tidak tepat. Sebelum SQL Server 2019, pengaturan default untuk tingkat paralelisme maksimum (MAXDOP) adalah menggunakan semua CPU yang tersedia untuk kueri. Selain itu, batas biaya untuk paralelisme ditetapkan pada 5, yang dapat menyebabkan kueri kecil dieksekusi secara paralel dan membatasi throughput. Untuk mengurangi jenis tunggu ini, Anda dapat menurunkan pengaturan MAXDOP dan meningkatkan ambang biaya untuk paralelisme. Namun, jenis tunggu CXPACKET juga dapat menunjukkan pemanfaatan CPU yang tinggi, yang biasanya diselesaikan melalui penyetelan indeks.

• PAGEIOLATCH_UP—Jenis tunggu pada halaman data 2:1:1 ini dapat menunjukkan pola kontensi TempDB pada halaman data Halaman Ruang Bebas (PFS). Setiap file data memiliki satu halaman PFS per 64 MB data. Penantian ini biasanya disebabkan oleh hanya memiliki satu file TempDB, karena sebelum SQL Server 2016, perilaku defaultnya adalah menggunakan satu file data untuk TempDB. Praktik terbaik untuk TempDB adalah menggunakan satu file per inti CPU, hingga delapan file. Penting juga untuk memastikan file data TempDB Anda berukuran sama dan memiliki pengaturan penambahan otomatis yang sama untuk memastikan file data TempDB digunakan secara merata. SQL Server 2016 dan yang lebih tinggi mengontrol penambahan file data TempDB untuk memastikan file bertambah secara konsisten dan simultan.

Selain DMV yang sebelumnya disebutkan, Penyimpanan Kueri juga melacak waktu tunggu yang terkait dengan kueri tertentu. Meskipun data tunggu yang dilacak oleh Query Store tidak begitu terperinci seperti data dalam DMV, data tersebut tetap memberikan gambaran umum yang berguna tentang apa yang sedang ditunggu oleh kueri.