MSSQL Server Resource Governor

Bu makalede Sql Server Resource Governor konusunu ele alacağız. Sql server üzerinde bu yapı ile kullanıcı bazlı kaynak kısıtlaması yapabiliriz. Kullanıcı çekmiş olduğu rapor ile sunucu üzerinde Memory ve Cpu anlamında darboğaz yaşanmasına sebebiyet veriyor. Bu gibi durumlarda Resource Governor imdadımıza yetişiyor. Bu şekilde kullanıcının belirlemiş olduğumuz kaynak kadar kullanılmasını sağlayabiliriz.

Şöyle örnek vermek gerekirse kritik sitemlerde kullanıcıların ve sql server standart kullanıcıların birbirini etkilememesi için bu yapı kurulabilir.

SSMS’de Management sekmesi altında bulunmaktadır.

Şimdi yukarıdaki resimde görülen  Resource Governor kısımlarının ne işe yaradığına değinelim.

Resource Pool: Server’ın kaynaklarını temsil eder. Default olarak iki adet resource pool oluşur. Yukarıdaki resimde de görüldüğü gibi bunlar:

Default Pool: Resource governor’da ilk tanımlanan user pool’ dur. Herhangi bir resource pool’ a yönlendirilmemiş connection’ lar bu pool’ a yönlendirilir. 

Internal Pool: Sql server’ın kendi içi işleri için kullandığı pool’dur.

Yukarıdaki ilgili pool’lar düzenleme yapılarakta kullanılabilir. Ama tüm kullanıcılara aynı kısıtlama yapılır.

Workload Group: Aynı kritere sahip sorguların ilgili resource pool’a yönlendirilebilmesi için toplandığı bir konteynır olarak düşünebilirsiniz. Bir ya da birden fazla workload group 1 resource pool’a yönlendirilebilir.

Kısacası Resource Governor’ın ana bileşenleri:

• Resource Pool
• Workload Group
• Classification

Pasif olarak gelen Resource Governor yapımızı üzerine sağ tıklayıp Enable ediyoruz.

Aktif işleminden sonra Resource Pool oluşturma adımlarına geçiyoruz. Resource Pool yapımıza sağ tıklayıp New Resource Pool.. diyiyoruz. SSMS arayüzün den yapmayıp T-SQL kodları yardımıyla da yapılabilir. Aşağıdaki ilk örnekte sadece SSMS arayüzün den nasıl yapıldığını görmüş olacağız.

Gelen ekranda YENIPOOL adında bir Resource pool oluşturuyoruz. Bu bölümde ayarlamaları yaptıktan sonra alt kısımda bulunan workload group’u yapılandırıyoruz. Her bir workload group bir resource pool’a bağlanabilir. Daha sonra External resource pool oluşturuyoruz. Harici kaynakların bu yapı ile paylaşılması sağlanır. Bu işlemlerden sonra yukarıda script yazılan yerde işlemin script’ini alıyorum.

Yukarıdaki  ekran görüntüsünün çıktısı  şimdi yapacağımız adımların aynısı olarak karşımıza çıkmaktadır. Yukarıdaki ekran resminde birden fazla resource poll ve workload oluşturulabilir.

Oluşturulan poollar 100 üzerinden değer verilir. İnternal ise kendi default olan kendi değerini kullanır. Herhangi bir değer set edilmez. Bu değere müdahale edilmesine sql server izin vermez.

Not: Resource pool’u iki workload group paylaşıyorsa Workload grouplardan birine öncelik verilmesi gereklidir. Yukarıda dikkat ederseniz IMPORTANCE=High seçeneği ile karşılaşırız.

Şimdi T-SQL kod yardımıyla  Resource Governor yapısını oluşturalım. 2 tane resource pool oluşturup, 2 tane workload yapısına bağlamış olacağız.

1. Adım Resource Pool oluşturuyoruz. Birinci pool raporlama kullanıcıları için ayrılmış alan, ikinci pool veritabanı yöneticileri için ayrılmış alan olarak karşımıza çıkmaktadır.

-- Rapor çekecek kullanıcı için oluşturulan pool
CREATE RESOURCE POOL ReportPool

WITH

( MIN_CPU_PERCENT=0,

MAX_CPU_PERCENT=30,

MIN_MEMORY_PERCENT=0,

MAX_MEMORY_PERCENT=30)

GO

-- Admin kullanıcıları için oluşturulan pool

CREATE RESOURCE POOL AdminPool

WITH

( MIN_CPU_PERCENT=50,

MAX_CPU_PERCENT=100,

MIN_MEMORY_PERCENT=50,

MAX_MEMORY_PERCENT=100)

GO

Aşağıdaki resimde de görüldüğü gibi ilgili pool’lar oluşmuş durumda.

SELECT * FROM sys.resource_governor_resource_pools

2. Workload oluşturma işlemine geçiyoruz. Oluşturduğumuz resource pool’lar ilgili konteyner’a bağlanır. ReportPool yapımızı ReportGroup denilen konteyner’a bağlıyoruz.

CREATE WORKLOAD GROUP ReportGroup
USING ReportPool ;
GO

Adminpool yapımızı AdminGroup denilen konteyner’a bağlıyoruz

CREATE WORKLOAD GROUP AdminGroup
USING AdminPool ;
GO

Aşağıdaki resimde de görüldüğü gibi ilgili WorkLoad’lar oluşmuş durumda.

Tam system kurulduktan sonra aşağıdaki komutla tablo sonucu alınmış oldu.

SELECT * FROM sys.resource_governor_workload_groups

3. Pool ve WorkLoad oluşturup kısıtlama yaptıktan sonra, Function oluşturma işlemini geçelim. Function belirlenmiş olan kullanıcıların hangi kaynak kısıtlamasını belirlemek istiyorsak ilgili kullanıcı seçilir. In komutu ile tek bir if yapısı içirisine değerlerimiz set edilmektedir.

CREATE FUNCTION dbo.Function_Name()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN

DECLARE @WorkloadGroup AS SYSNAME

IF(SUSER_NAME() = 'Report') –Report user'ın gitmesi gerektiği konteyner
    SET @WorkloadGroup = 'ReportGroup'

ELSE IF (SUSER_NAME() = 'Yonetici')
    SET @WorkloadGroup = 'AdminGroup'

ELSE
    SET @WorkloadGroup = 'default'— şartta herhangi bir kullanıcıdan istek gelmiyorsa deafult olan WorkLoad kullanması gerektiğini söylüyor.

RETURN @WorkloadGroup
END
GO

Not: Hangi uygulamadan ve host’tan geldiğini öğrenmek istersek else-if set ifadesi eklenebilir. Aşağıdaki kod bloğu eklenebilir.

ELSE IF (APP_NAME() = 'Aplication_name')-- kısıtlama yapacağımız uygulama ismi
      SET @workload_group = 'ReportGroup';
  ELSE IF (HOST_NAME() = 'Server_name')-- Hangi sunucuda işlemin gerçekleştiği
      SET @workload_group = 'ReportGroup';

4.  Aşağıdaki ifade ile oluşturduğumuz sınıflandırıcı ile resource governor yapısını etkinleştiriyoruz. Oluşturduğumuz function yapısını değiştireceğimiz zaman aşağıdaki komutun tekrardan çalıştırılıp yeni değerin set edilmesi gerekmektedir. SSMS arayüzünden enable disable ile olabilecek bir şey değildir.

ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION=dbo.Function_Name);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

5.Oluşturduğunuz Kaynak Yöneticisini iptal etmek isterseniz, aşağıdaki komut dosyalarını kullanabilirsiniz. İlgili pool’u silmek için ilk başta workload group’un silinmesi gerekmektedir. Yoksa hata mesajıyla karşılaşırız. Bu işlem SSMS arayüzünden de yapılmaktadır.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)
GO
ALTER RESOURCE GOVERNOR DISABLE
GO
DROP FUNCTION Function_Name
GO
DROP WORKLOAD GROUP AdminGroup
GO
DROP WORKLOAD GROUP ReportGroup
GO
DROP RESOURCE POOL AdminPool
GO
DROP RESOURCE POOL ReportPool
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

İlgili konteyner içerisinde tekrar düzenleme yapmak istersek. SSMS arayüzünden düzenleme yapabiliriz. 

Yukarıdaki ekran görüntüsü üzerinde de işlemlerimizi yapabiliriz. Yukarıdaki bölümde düzenleme yaptıktan aşağıdaki kod bloğunu almış oluruz.

ALTER WORKLOAD GROUP [AdminGroup] WITH(

--gelen sorgunun pool’daki memory’in % kaçını kullanacağı

 group_max_requests=40, 

--CPU threshold'unun aşıldığı bilgisi 60 saniyeden sonra tetiklenir. Sorgu durdurulmaz.

request_max_cpu_time_sec=60, 

--Bir sorgunun memory almak için maksimum bekleme süresini belirler.

request_max_memory_grant_percent=30, 

--Sorgu 80 saniye boyunca memory alamazsa timeout'a düşecektir.

request_memory_grant_timeout_sec=80, 

--Bir sorgunun çalışabileceği maxdop ayarını belirler.

max_dop=8

GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Aşağıdaki sorgu ile de hangi workload group’ta hangi session’ın olduğunu görebilirsiniz. Bu yapı açıldıktan sonra önceki bağlantılar default olan resource yapısını kullanmaktadır.

SELECT s.session_id,s.login_name, g.name
FROM sys.dm_exec_sessions AS s 
INNER JOIN sys.dm_resource_governor_workload_groups AS g 
    ON g.group_id = s.group_id 
where session_id>60
ORDER BY g.name; 
GO

Aşağıdaki sorgu ile de hangi worklad group’ta hangi sorgu var onu öğrenebilirsiniz.

SELECT sder.group_id, wg.name, sder.status, sder.session_id, sder.request_id,
    sder.start_time, sder.command, sder.sql_handle, st.text  
FROM sys.dm_exec_requests AS sder 
INNER JOIN sys.dm_resource_governor_workload_groups AS wg 
    ON wg.group_id = sder.group_id 
CROSS APPLY sys.dm_exec_sql_text(sder.sql_handle) AS st 
where session_id>60
ORDER BY wg.name;

Bu makalede Sql Server Resource Governor yapısını ele almış olduk. Başka bir makalede görüşmek dileğiyle.

“Konumunla övünme, Firavun’a bak!“ Yûnus-91