Resource Governor ile Kaynak Kullanımını Kısıtlayın

Resource Governor SQL Server 2008 ile beraber hayatımıza girdi. Resource Governor ile kaynak kısıtlaması yapabiliyoruz. Örneğin bir rapor kullanıcısı öyle rapor sorguları çekiyorki tüm sistemdeki memory ve cpu’yu tüketiyor ve sistemki memory ve cpu anlamında darboğaz oluştuğu için diğer uygulamalarda yavaşlık oluyor. İşte tam burada Resource Governor imdadımıza yetişiyor. Resource Governor ile ilgili kullanıcıyı memory ve cpu anlamında kısıtlayarak sorunumuzu çözebiliriz.

SQL Server 2014’e kadar Resource Governor ile IO limiti yapamıyorduk. SQL Server 2014’ten itibaren volume başına MAX ve MIN IOPS kısıtlamasıyla artık disk anlamında da kısıtlama yapabiliyoruz.

Resource Governor’da anlamanız gereken 3 konsept vardır:

Resource Pool: Server’ın kaynaklarını temsil eder. Instance içinde sanal bir instance gibi düşünebilirsiniz. Default olarak 2 tane resource pool oluşur.(internal ve default)

Resource Governor kullandığımızda kısıtlama yapabilmek için kendi resource pool’larımızı oluştururuz ve aslında bu resource pool’ları kısıtlarız. Örneğin şu resource pool’un kullanabileceği maksimum cpu şu olsun gibi.

internal pool: SQL Server’ın kendi iç işleri için kullandığı pool’dur.

default pool: resource governor’da ilk tanımlanan user pool’dur.  Herhangi bir user defined resource pool’a yönlendirilmemiş connection’lar bu pool’a gelir.

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.

Classification: Classifier Function ile, gelen session’lar belirli bir filtre den geçerek belirtilen workload group’a yönlendirilir.

Resource Governor’ı daha iyi anlayabilmek için cpu, memory ve disk kısıtlamalarının hepsini içeren bir örnek yapalım.

İlk olarak SSMS üzerinden aşağıdaki gibi Resource Governor’ı aktif ediyoruz.

Daha sonra aşağıdaki sorgu yardımıyla kısıtlam yapacağımız resource pool’u oluşturuyoruz. Bu resource pool’a gelen sorguların kullanacağı maksimum cpu’nun %20’yi , maksimum memory’nin %30’u, volume başına yapılacak IOPS’un da 1000’i geçmemesini sağlamış olacağız.

CAP_CPU_PERCENT ise cpu’nun işletim sistemindeki cpu idle olsa bile maksimum %80’ini kullanabileceğini söylüyoruz.

Peki MAX_CPU_PERCENT ile bunu sağlamıyormuyduk?

Evet sağlıyorduk ama işletim sistemindeki cpu idle ise bu resource pool’lar kendileri için ayrılan max cpu limitini geçebiliyordu ve cpu’nun tamamını kullanabiliyorlardı. Diğer sorgular tekrar gelmeye başladığında pool’daki sorgular tekrar limitlerine çekiliyorlardı fakat kısa bir süre diğer sorgular sıkıntı yaşayabiliyordu. İşte CAP_CPU_PERCENT ile cpu idle olsa bile maksimum %80 cpu kullanım oranına çık diyoruz. Böylelikle sorgular gelmeye başladığında %20’lik bir cpu kullanım hakları oluyor ve pool’a gelen sorgularda diğer sorgulara sıkıntı yaşatmadan tekrar %20 cpu kullanım oranına düşüyor.

Örnekte AFFINITY SCHEDULER ile sekinci ve 12’den 16’ya kadar olan scheduler’ları kullanacağını belirtiyoruz.

CREATE RESOURCE POOL [RaporPool]
WITH(
MAX_CPU_PERCENT=20,
MAX_MEMORY_PERCENT=30,
CAP_CPU_PERCENT = 80,
MAX_IOPS_PER_VOLUME=1000,
AFFINITY SCHEDULER = (8, 12 TO 16)
)

Aşağıdaki script yardımıyla da ilgili workload group’u oluşturuyoruz.

CREATE WORKLOAD GROUP [RaporGroup]
USING [RaporPool]
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

Daha sonra da gelen sorgular’dan rapor ile ilgili olanları ilgili workload group’a yönlendirecek olan classifier function’ı oluşturmamız gerekiyor. Aşağıdaki örnekte RaporUser kullanıcısı ile gelen sorguları, Uygulama ismine göre gelen sorguları ve belirli bir sunucudan gelen sorguları RaporGroup workload group’a yönlendiriyoruz.

CREATE FUNCTION Class_Func() RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
  DECLARE @workload_group sysname;

  IF ( SUSER_SNAME() = 'RaporUser')
      SET @workload_group = 'RaporGroup';
  ELSE IF (APP_NAME() = 'Uygulama_İsmi')
      SET @workload_group = 'RaporGroup';
  ELSE IF (HOST_NAME() = 'Sunucu_İsmi')
      SET @workload_group = 'RaporGroup';

  RETURN @workload_group;
END;

Son olarak da resource governor’ın yukarda oluşturduğumuz Class_Func fonksiyonu ile çalışması için aşağıdaki script’i çalıştırıyoruz.

USE master
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.Class_Func);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;

Eğer bir resource pool’u iki workload group paylaşıyorsa aşağıdaki gibi workload group’lardan birine öncelik atayabilirsiniz. Diğer önemsiz workload group’a da High yerine Low ya da Medium diyebilirsiniz.

ALTER WORKLOAD GROUP RaporGroup
WITH (IMPORTANCE = High)

Workload Group’lar için diğer konfigürasyon ayarlarını aşağıdaki gibi yapabilirsiniz.

ALTER WORKLOAD GROUP RaporGroup
WITH
(
  --Gelen sorgunun pool'daki memory'nin maksimum yüzde kaçını kullanacağını belirler.
 REQUEST_MAX_MEMORY_GRANT_PERCENT=20,
  --CPU threshold'unun aşıldığı bilgisi 5 saniyeden sonra tetiklenir. Sorgu durdurulmaz.
 REQUEST_MAX_CPU_TIME_SEC = 5,
  --Bir sorgunun memory almak için maksimum bekleme süresini belirler.
  --Bu örnekte sorgu 5 saniye boyunca memory alamazsa timeout'a düşecektir.
 REQUEST_MEMORY_GRANT_TIMEOUT_SEC=5,
  --Bir sorgunun çalışabileceği maxdop ayarını belirler.
 MAX_DOP = 1,
  --Workload group'a aynı anda gelen sorgu sayısını limitler. Bu örnekte maksimum 100 sorgu aynı anda gelebilir.
 GROUP_MAX_REQUESTS = 100
)

Aşağıdaki sorgu ile de hangi workload group’ta hangi session’ın olduğunu görebilirsiniz.

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; 

Aşağıdaki script yardımıyla mevcut resource governor ayarlarınızı sıfırlayarak silebilirsiniz.

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL)
GO
ALTER RESOURCE GOVERNOR DISABLE
GO
DROP FUNCTION dbo.Functionİsminiz
GO
DROP WORKLOAD GROUP WorkloadGroupİsminiz
GO
DROP RESOURCE POOL ResourcePoolİsminiz
GO
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Eğer mevcut session varsa son olarak reconfigure ederken aşağıdaki hatayı alırsınız. hiç session kalmadığında tekrar reconfigure derseniz başarılı bir şekilde tamamlanacaktır.

Msg 10904, Level 16, State 2, Line 11

Resource governor configuration failed. There are active sessions in workload groups being dropped or moved to different resource pools. Disconnect all active sessions in the affected workload groups and try again.