Müşteride bazı kontroller yaparken en masraflı sorguları da kontrol ettim. Bu çalışmayı yaparken kullandığım sorgulardan birisi şöyle (yer açısından kod örneğini kısalttım):
SELECT TOP(20)
[qs].[execution_count],
[qs].[total_worker_time],
[qs].[total_worker_time] / [qs].[execution_count] AS [avg_worker_time],
[qs].[total_elapsed_time] / [qs].[execution_count] AS [avg_elapsed_time],
SUBSTRING([qt].[text], ([qs].[statement_start_offset] / 2) + 1,
((CASE qs.[statement_end_offset]
WHEN -1 THEN DATALENGTH([qt].[text])
ELSE [qs].[statement_end_offset] END
- [qs].[statement_start_offset]) / 2) + 1) AS [problematic_statement_text]
FROM sys.dm_exec_query_stats AS [qs] WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text([qs].[plan_handle]) AS [qt]
ORDER BY qs.total_worker_time DESC OPTION (RECOMPILE);
İnternette de birçok çeşitli örneğini bulabileceğiniz bir kod örneği, özel bir şey yok. Bu kodu çalıştırdıktan sonra oluşan manzaraya ait ekran görüntüsünü de aşağıda paylaşıyorum.
 |
| Ekran görüntüsünü büyütmek için üstüne tıklayın. |
CPU açısından en masraflı sorgulara ait istatistikleri incelemek için yukarıdaki kodu çalıştırdıktan sonra karşılaştığım görüntüde kırmızı dikdörtgen ile işaretlediğim çok ilginç bir sorgu dikkatimi çekti. Bu ilginç sorguyu da aşağıda paylaşıyorum:
SELECT A.request_session_id, A.request_request_id, A.resource_type, A.resource_associated_entity_id, A.resource_database_id, A.resource_subtype, convert(nvarchar(100),substring(A.resource_description,1,100)) AS resource_description FROM sys.dm_tran_locks AS A WITH ( NOLOCK ) WHERE A.request_status = 'WAIT'
Not: Bu da kesinlikle özel veya müşterimin kendi yazılımına ait bir sorgu değil.
Sorgunun neden çok ilginç olduğunu şöyle izah edeyim:
1- Sorgu, yukarıda da belirttiğim gibi müşterimin kendi uygulamalarına ait değil,
2- Müşterimin uygulamalarından üretilen sorguların en yüksek CPU masraflısının masrafından 8 kat daha masraflı!
Daha fazla inceleyince, sorgunun 3. parti ve piyasada gayet bilinen ve yaygın olarak kullanılan bir performans izleme uygulamasından geldiğini gördüm. Bu, akıllardaki sorulardan sadece birini cevaplıyor. Bir diğer soru ise neden bu kadar sade olan ve bir Dynamic Management View (DMV)'ü sorgulayan bir sorgunun bu kadar masraflı olduğu.
Sorgunun masrafları hakkında biraz ayrıntı vereyim. Sorgu her çalıştığında 2,6~ saniye sürüyor, neredeyse hiç Read (diskten okuma) yapmıyor, fakat 2,5~ saniye CPU zamanı tüketiyordu. Ayrıca sorgu sürekli olarak çalıştırılıyordu, sanki gerçek zamanlı izleme yapılan bir ekrandan çağrılıyor gibi.
Bu noktada ilk aklıma gelenler:
- Böyle ünlü bir firma, nasıl olur da bu kadar masraflı bir sorguyu bu sıklıkta çalıştırır ve izleyenin izlenenin durumunu bu kadar dramatik olarak değiştirmesine neden olur? (Elbette her izlemenin bir maliyeti vardır, ama bu kadar da değil!)
- Acaba bu ortamdaki SQL Server versiyonuna has bir durum, davranış veya ürün arızası mı söz konusu?
Hemen aynı kodu hiç yük olmayan bir test ortamımdaki SQL Server 2016 ve 2017'de denedim. Sorgu 0ms'de tamamlanıyordu. İlginç! Başka, ama bu sefer canlı/üretim ortamı olan SQL Server 2005, 2008R2, 2012, 2014 ve 2016'larda denedim ve bu noktada sorgunun maliyetinin SQL Server'ın versiyonuna göre değil, kodun çalıştırıldığı ortamdaki işlem yoğunluğuna göre değiştiğini fark ettim. Örneğin hiçbir işlem olmayan test / geliştirme ortamlarında 0ms CPU zamanı tüketen sorgu, ortalama işlem hacmine sahip canlı/üretim ortamlarında 150 - 350ms arasında CPU zamanı tüketiyordu.
Sonuç itibariyle ortaya çıktı ki sorgunun çok CPU zamanı tüketmesinin nedeni SQL Server'ın versiyonuyla ilgili değildi, kodun çalıştırıldığı ortamdaki işlem yoğunluğuyla ilgiliydi. Bununla birlikte ne olursa olsun, bir izleme uygulamasının sistem üstünde bu derecede yük oluşturması bence kabul edilemez. Müşteriye de bu durumu ayrıntılarıyla izah ettim.
Her türlü kritik ortamınızı 7/24 izlemek durumundasınız. Olabildiğince kesintisiz, güvenli, performanslı ve stabil bir hizmet verebilmek için, işinizi riske atmamak için bu bir seçenek değil, zorunluluktur. Fakat sisteminizi izlerken yanlışlıkla performansını kötü etkilemediğinizden, doğru kontrolleri düzgün ve yeteri kadar uyguladığınızdan emin olmanızda büyük fayda var.
Ekrem Önsoy
Microsoft SQL Server Danışmanı
Tel: +90 530 976 93 59
