Performans: Kendi kendini sabote etmek

Vahşi yaşamdaki Sahadaki sunucularda sağlık kontrolü ve performans iyileştirmesi gibi çalışmalar yaparken, hangi akla hizmet hiç anlam veremediğim bazı ilginç ayarlar yapıldığını, sunucularda aylardır oluşan hataların izlenmediğini ve haliyle herhangi bir müdahalede de bulunulmadığını görüyorum. Ahh, sonra bir de "SQL Server kötü" demiyorlar mı!

Bu tür durumlar, ki piyasada gerçekten çok yaygın, son model spor aracınızı el freni kalkık şekilde kullanmaya benziyor. Donanım kaynaklarına ve lisanslara onca para harcanıyor, fakat bu yatırımlardan verimli bir şekilde faydalanılamıyor.

Geçenlerde 16 işlemci çekirdekli bir sunucuda çalışma yaparken zaman zaman aşağıdaki hataların alındığını gördüm:

"New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 240 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 5%. System Idle: 93%."

Ve bu hatalarla birlikte Dump alınıyordu.

Bu hata mesajı sorguların uzun sürdüğünün, olası bağlantı hatalarının ve CPU kaynaklarının verimli kullanılmadığının işaretidir.

Bekleme tiplerini kontrol ettiğimde "Threadpool" bekleme tipinin "CXPACKET"tan hemen sonra geldiğini görüyordum. Konumuz dahilinde değil, ama "CXPACKET" bekleme tipi paralelliğin kullanıldığı tüm ortamlarda olur ve kendi başına doğrudan bir sorun anlamına gelmez. Fakat ilk 10 bekleme tipi arasında "Threadpool"u görüyorsanız ve bu da ikinci sıradaysa "Worker Thread"lerle ilgili bir sorununuz var ve buna odaklanmanız gerekir demektir.

Threadpool bekleme tipi sorununun görseli

Peki nedir bu "Threadpool" ve "Worker Thread"?

Threadpool, çok miktarda istemci sunucuya bağlandığında performansın optimize edilmesini sağlar. Kullanıcıların yaptığı talepler için, yani çalıştırdıkları sorgular için bir Thread havuzu oluşturur. Threadpool'da kullanılabilecek Worker Thread sayısını SQL Server sunucu mimarisi ve işlemci çekirdek sayısına göre Database Engine servisinin başlangıcında yapar. Bu şartlara göre oluşturulacak Worker Thread sayısını Microsoft'un dokümantasyonundan inceleyebilirsiniz.

Eğer bu hesaplamaya müdahale etmeniz gerekirse, ki şahsen henüz bunu gerektirecek bir durumla hiç karşılaşmadım, o zaman "Max Worker Thread" ayarını değiştirmeniz gerekir. Bu ayar varsayılan olarak 0'dır ve çok istisnai durumlar haricinde de 0 olarak kalması gerekmektedir.

Herhangi bir SQL Server Instance'ındaki o anki "Max Worker Thread" sayısını görmek için aşağıdaki komutu çalıştırabilirsiniz:

SELECT [max_workers_count] FROM sys.dm_os_sys_info;

"Max Worker Thread" ayarınızı görmek için de aşağıdaki komutu kullanabilirsiniz:

SELECT [value], [value_in_use] FROM sys.configurations WHERE [name] = 'max worker threads';

Eğer "Max Worker Thread" ayarınız için [value] ve [value_in_use] alanlarının değerleri 0 ise ayarınız varsayılan değerdedir ve önceden de belirttiğim gibi birçok ortam için de doğru olan değer budur. Eğer bu alanlardan herhangi biri 0 değilse, birisi bu ayarı değiştirmiştir ve eğer bunu gerçekten işini bilen birisi yapmadıysa büyük ihtimalle bu hatalı bir hamledir. Eğer bu ayarı değiştirmeniz gerekirse, değişikliği uyguladıktan sonra Database Engine servisini yeniden başlatmanız gerekir, aksi takdirde değişiklik hemen devreye girmez.

Gelelim Worker Thread'in ne olduğuna. SQL Server'da her bir işlemci çekirdeği Scheduler'la temsil edilir. SQL Server Instance'ınızda kaç tane Scheduler olduğunu, bunların kaç tanesinin kullanılabilir (Visible) olduğunu sys.dm_os_schedulers isimli DMV'yi sorgulayarak görebilirsiniz. Örneğin bazı Scheduler'lar sistem tarafından kullanılır, bazıları ise lisanslama yüzünden (bakınız) pasif durumda olabilir. Worker Thread'ler, Scheduler'lar tarafından kendine atanmış olan Task'ları (örneğin bir sorgunun çalışması veya log in işlemi gibi) çalıştırırlar. Mesela havuzda yeterince kullanılabilir Worker Thread olmazsa bu nedenle "Login failed" hataları alabilirsiniz.

Peki 16 çekirdekli bir işlemci kaynağının olduğu bu ortamda yukarıdaki hata neden alınıyordu dersiniz? Sağolsun birisi ne hikmetse bu ayarı "255" olarak değiştirmiş. Bu nedenle havuzda "704" Worker Thread olabilecekken "255" tane var. Yani aslında ortamda yeterli altyapı var; ama el freni kalkık ve SQL Server çığlık ata ata çalışıyor... Haliyle bir Blocking sorunu oluştuğunda, paralel veya uzun süren bir işlem sık ve yaygın olarak çalıştırıldığında yukarıdaki gibi hata mesajlarına rastlamak da olası oluyor.

Sistem yöneticisi, veritabanı yöneticisi, yazılımcı (evet maalesef...), SQL Server yönetimi yapan tüm arkadaşlar! Eğer ne yaptığınızdan emin değilseniz, emin olmadığınız bir ayarı değiştireceğinize lütfen varsayılan haliyle bırakın. O ayarı çok merak ediyorsanız, o ayar sizi çok rahatsız ediyorsa ve farenize hakim olamıyorsanız buyurun Türkçe Microsoft SQL Server forumlarında sorun, ben veya başka bir arkadaşım müsait olduğunda elinden geldiğince cevaplar. Bu yazıda bahsettiğim sadece bir örnek, bunun gibi daha niceleri var. Yapmak istediğiniz değişiklikleri lütfen ne yaptığınızdan emin olduktan sonra yapın.

Ekrem Önsoy
Microsoft SQL Server Danışmanı
www.ekremonsoy.com