SQL Server’da Wait Statistics Konusu

SQL server’da “wait statistics” performans ve performans kaynaklı sorunların analizi ve de optimizasyon  için çok önemli bir araçtır.

 SQL server’a bir veri isteği geldiğinde bu istek normal, optimum şartlarda işlemcinin hızına göre hemen gerçekleştirilebilecek bir işlem iken, kaynakların (ram, işlemci core sayısı, disk boyut ve hızı vb.) sınırlı olması ve gelen isteğin çokluğu sebebiyle işlemci bu istekleri hemen yerine getiremez, bir işlem kuyruğuna sokar ve bu istekler için bekleme sözkonusu olur. İşte SQL Server bu beklemeleri türü, süresi vb. bilgileriyle kaydeder. Çeşitli bekleme türleriyle ilgili bu kayıtlara wait statistics adı verilir. Yüzlerce wait türü vardır. Performans analizi yapabilmemiz için çok önemli göstergelerdir. Performance counters ile birlikte yorumlamak daha doğru sonuçlar üretmemizi sağlar.

Beklemeler temel olarak iki kategoriye ayrılır:

Kaynak Beklemeleri (Resource Wait) : Görev bekleme listesindedir ve suspended statüsündedir. Disk/hafıza’da yazma/okuma beklemesi ya da başka bir transaction tarafından kilitlenen page’in beklenmesi gibi.

Sinyal Beklemeleri (Signal Wait) : Görev runnable statüsündedir ancak işlemcide sıra beklemektedir

 SQL server’a bir istek(request) geldiğinde bu istek kendisine bir “worker thread” atanan bir  göreve dönüştürülür. Bir istek “worker thread” talep ettiğinde,  SQL server bu isteğe atamak için boşta bir worker thread arar. Bulamazsa ya da sunucunun maximum worker threads limitine ulaşılmışsa istek kuyruğa eklenir ve herhangi bir worker thread’in boşa çıkmasını bekler. Worker thread sayısı sunucumuzun özelliklerine göre parametrik belirlenen bir rakamdır. Örneğin 64 bit ve 4’ten fazla core içeren bir sunucuda worker thread sayısı 512+((core sayısı -4)*16) formülüyle bulunur. 16 core içeren bir sunucu için bu rakam 704 e tekabül eder. Aşağıdaki ekranda maximum worker threads ile iligili bir ayar sözkonusudur.

 Proses diskte yüklü uygulamanın çalıştırıldığı, hafızaya yüklendiği, işlemci tarafından yürütüldüğü anda aldığı isimdir. Bir proses birden fazla thread barındırabilir ve buna multithreading adı verilir.

 

  Örneğin bir select sorgusu çalıştırdık diyelim, bu sorgunun istediği bilgi için önce buffer pool’a bakılır. Burada yoksa diske gider thread, diskten yanıt beklediği sırada suspended statüsündedir ve PAGEIOLATCH wait türü oluşur. Veri diskten alınıp buffer pool’a taşındığında statüsü runnable dır ancak bu kez de CPU zamanlayıcısından sinyal beklemeye başlar. Kuyruğun sonundadır. CPU core ları başka threat lerle meşguldür. Sıra kendisine geldiğinde listenin başına alınır ve sorgu çalıştırılır. Her ne kadar bu işlemlerin bazısı eş zamanlı gerçekleşse de aşağıda bu işlemleri bir nebze sıralı olarak şematize etmeye çalıştım:

Wait istatistiklerine ait bilgi alabileceğimiz iki önemli DMV (dynamic management view) vardır:
sys.dm_os_waiting_tasks :  herhangi bir kaynağı bekleyen aktif görevleri
sys.dm_os_wait_stats :  instance seviyesinde, sql server servisinin son başlamasından bu yana toplam bekleme sürelerini gösterir.

Sunucunun son başlamasından bu yana toplanmış olan bu istatistikler aşağıdaki komutla temizlenebilir:

DBCC SQLPERF(‘sys.dm_os_wait_stats’,CLEAR);Bir de SQL Server 2016 versiyonuyla birlikte gelen sys.dm_exec_session_wait_stats view’ı vardır ve session bazında thread’lerin karşılaştığı beklemeleri gösterir.

 Yukarda bahsettiğimiz sys.dm_os_wait_stats view’ını sorguladığımızda aşağıdaki bilgileri alırız:

Wait_type : Gerçekleşen bekleme türü
Waiting_task_count : Bu bekleme türü kaç kez gerçekleşti
Wait_time_ms : Wait_type alanında belirtilen beklemenin toplam geçen zamanı. signal_wait_time_ms bekleme süresini de içerir. Runnable ve suspended statüsünde kuyrukta harcanan toplam zamanı gösterir. 
Max_wait_time_ms : Bir thread in bu bekleme türü için beklediği en fazla süre
signal_wait_time_ms : Thread’in runnable statüsünde kuyrukta CPU beklediği zaman. Yüksek değerler görüyorsak CPU darboğazı(bottleneck) olduğunu düşünürüz.

Latch, Hafızadaki veriyi korumak için kullanılan bir lock mekanizmasıdır ve kullanıcı tarafından kontrol edilemez. Bir proses latch için beklediğinde de bazı beklemeler oluşur. Latch’in üç türü vardır ve bu üç türe göre aşağıdaki isimlendirmelerde beklemeler kaydedilir: 
      – I/O Latch : PAGEIOLATCH_*
      – Buffer Latch : PAGELATCH_*
      – Non-buffer latch : LATCH_*

Yukarıdaki tanımlamalardan sonra SQL Server’ı gözlemlemek için hangi wait türlerinin takibinin önemli olduğu konusuna geçebiliriz. CPU yoğunluğu, I/O yoğunluğu, memory yoğunluğu, block/deadlock durumları gibi durumları analiz etmek için yaygın olarak gözlemlediğimiz wait türleri vardır. Yüzlerce bekleme türü var ancak SQL Server performansını gözlemlemek için yaygın olarak aşağıdakiler izlenir.

PAGEIOLATCH_* : Bir görev buffer pool’da olmayan bir Page’in diskten getirilmesini bekliyor. I/O yoğunluğu yüksek veri tabanlarında çok sık görülen bir beklemedir. Bu beklemeyi sık görüyorsak diskte I/O darboğaz(bottleneck) oluştuğundan söz edebiliriz. Disk sistemlerinizin Sql Sever’a cevap vermekte yavaş kaldığına işaret eder. Ancak bu gecikme başka sebeplerden de kaynaklanabilir. Optimize olmayan sorgular, index sorunları, güncel olmayan istatistikler, implicit conversion gibi konular da sebep olabilir. _sh shared latch , _up update latch, _ex exclusive latch için beklendiğini gösterir.

PAGELATCH : PAGELATCH beklemesi, bir görevin(task), başka bir task tarafından latch edilmiş(lock), buffer pool’daki page’e erişmek için beklediğini gösterir. İndex’lemenin gözden geçirilmesi, partitioning çalışması ve tempdb performansının iyileştirimlesi gibi çalışmalarla azaltılabilir. 

WRITELOG : Transaction commit edildiğinde ya da checkpoint işleminde diske yazılırken oluşan bekleme türüdür. Bir task transaction log bloğunun diske yazılmasını beklemektedir. Çok sık karşılaşılıyorsa transaction log’ların bulunduğu disklerde bir problem varlığından söz edilebilir. Ancak yine tek sorun diskler olmayabilir. Çok fazla transaction çok sık aralıklarla commit ediliyorsa bu da sebep olabilir. Transaction sayısı azaltılabilir. Yine gereksiz index’ler de bu beklemeye sebep olur. Bir tabloda ne kadar fazla index varsa, tablo her insert/update aldığında oluşan log miktarı da o kadar fazla olur. İndex parçalanmaları da yine bu beklemeye sebep olabilir. 

LCK_M_* : Thread veriye erişmek ve lock koymak için başka bir görevin kilitlediği kaynağı bekliyor. M harfinden sonra gelen harf lock türünü gösterir. LCK_M_S shared lock, LCK_M_U update lock, LCK_M_X exclusive lock bazı örnekleridir. 60’dan fazla türü vardır. Şu sebeplerle görülür genel olarak:
– Büyük bir tablo tarama ya da update işlemi tabloda “lock escalation” a sebep oluyor.
– Ulaşılan datadaki gereksiz shared lock’lar 

RESOURCE_SEMAPHORE : Bir worker thread bir sorgu için memory kaynağı beklerken ortaya çıkar. Çok fazla hafıza ihtiyacı olan eş zamanlı sorguların çok fazla olduğu sistemlerde görülür. Bu bekleme türünü ve memory_allocation_ext beklemesini alıyorsak veri tabanımız üzerinde memory baskısı olduğunu düşünebiliriz.

I/O_COMPLETION, ASYNC_I/O_COMNPLETION : Backup gibi uzun süren I/O işlemlerinde görülebilir. Bu bekleme türünü görüyorsak diskte bir darboğaz(bottleneck) oluştuğunu düşünebiliriz.

CXPACKET : Paralel sorgu çalıştırılması sırasında thread’lerin senkronizasyonu için beklenen zamandır. Kaynaklara dair bir darboğaz olduğunu göstermez. Veri tabanında paralel sorguların çalıştığını ve beklenenden fazla zaman aldığını gösterir. Belirli bir miktar işi on tane işçiye paylaştırdığımızı farzedelim, işçilerden bazıları işlerini erkenden bitirdi ve yeni iş bekliyorlar. İstatistikler güncellenerek azaltılabilir.

SOS_SCHEDULER_YIELD: Thread’in işlemciden zamanlayıcı(scheduler) beklediği ve runnable statüsünde olduğu süredir.

THREADPOOL : Yukarda daha önce bir isteğin kendisine “worker thread” atanması için beklediğinden bahsetmiştik. Bu bekleme o anda oluşur. İşlemci sayısına göre worker thread sayısı olduğunu gözönüne alırsak, bu beklemeyi sürekli görüyorsak sunucumuzun işlemci core sayısını artırmamız gerektiğini düşünebiliriz. Ya da maximum worker thread sayısı düşük kalmış olabilir.

ASYNC_NETWORK_IO : Network’te darboğaz oluştuğu gibi yorumlanır ama , veritabanımızı kullanan uygulamanın satır satır veri çekip işlediği durumlarda oluşur. Bir satır çeker işler, bir satır daha çeker işler böyle devam eder.

Son olarak, beklemeler tek başlarına tüm sistem performansı hakkında yeterli bilgi vermezler. Sql Server’ı barındıran işletim sisteminin performansına dair bazı bilgilere de ihtiyaç duyarız. İşletim sistemine dair bu bilgilere de performans counters adı verilen sayaçları izleyerek ulaşırız. Bu sayaçlar bize sistem kaynakları üzerindeki yoğunluğu ve beklemeleri(queue) gösterir. Wait türlerini ve kuyrukları(queue) birlikte izlemek daha iyi sonuç verecektir

HADR_WORK_QUEUE : Always On yapısına dahil olan veri tabanları için ayrılmış olan thread’lerin görev atanmasını beklediklerini gösterir. Boşta kalma bekleme süresini gösterdiği için yüksek olması umulur.

HADR_WORK_QUEUE : Always On’a dahil olan veri tabanları için ayrılmış olan thread’lerin görev için beklemeleridir. Boşta bekleme süresi olduğu için yüksek olması umulur