SQL Server 2014 리소스 관리자 풀과 CPU 제어

SQL Server 2014 리소스 관리자 풀과 CPU 제어

 

• Version : SQL Server 2014

 

SQL Server 리소스 관리자는 혼합 워크로드 환경에서 자원을 제한하기 위해 SQL Server 2008 부터 도입 되었으며 응응 프로그램 요청이 리소스 풀에서 사용할 수 있는 CPU, 물리적 IO 및 메모리 양을 제한 할 수 있다.

 

MIN_CPU_PERCENT, MAX_CPU_PERCENT

CPU 경합이 있을 때 리소스 풀의 모든 요청에 대해 보장되는 최소 및 최대 평균 CPU 대역폭이다. 이 설정을 사용하면 각 작업의 요구 사항을 기준으로 여러 작업에 대해 예측 가능한 CPU 리소스 사용량을 설정 할 수 있다.

 

 

CAP_CPU_PERCENT

리소스 풀의 모든 요청에 대한 CPU 대역폭 상한선 제한이다. 풀에 연결된 작업은 MAX_CPU_PERCENT 값보다 크지만 CPU_CPU_PERCENT 값보다 크지 않은 CPU 용량(사용가능한 경우)을 사용할 수 있다.

 

 

MIN_MEMORY_PERCENT, MAX_MEMORY_PERCENT

리소스 풀에 예약된 최소 및 최대 메모리 양이며 다른 리소스 풀과 공유 될 수 없다. 이때 참조되는 메모리는 버퍼 풀 메모리가 아니라 쿼리 실행 부여 메모리이다. 풀의 최소 메모리 값을 설정하면 해당 리소스 풀에서 실행 될 수 있는 모든 요청에 대해 지정한 비율만큼의 메모리가 사용되도록 보장할 수 있다. 이 설정은 활성 요청이 없는 경우에도 남아 있는 메모리를 다른 풀에서 사용 할 수 없기 때문에 신중을 기해야 한다. 풀의 최대 메모리 값 설정은 풀에서 요청이 실행 될 때 전체 메모리 중 이 비율 이상은 사용 할 수 없음을 의미한다.

 

 

AFFINITY

하나 이상의 스케줄러 또는 NUMA 노드에 대해 리소스 풀의 선호도를 설정하여 CPU 리소스를 더 효과적으로 격리 할 수 있다. 즉 다른 연결에 대해 다른 CPU를 부여하여 격리되어 실행 된다.

 

 

MIN_IOPS_PER_VOLUME, MAX_IOPS_PER_VOLUME

리소스 풀에 대한 디스크 볼륨당 최소 및 최대 물리적 IOPS로 리소스 풀의 사용자 스레드에 대해 발생하는 물리적 IO를 제어 할 수 있다.

 

 

CPU 또는 메모리를 구성 할 때 모둔 풀의 MIN 값의 합은 서버 리소스의 100%를 초과할 수 없다. 또한 CPU 및 메모리를 구성할 때 MAX 및 CAP 값은 MIN과 100% 사이의 임계값으로 설정 할 수 있다.

 

풀에 0이 아닌 MIN이 정의되어 있으면 다른 풀의 유효한 MAX값이 다시 조정된다. 풀에 구성된 MAX 값의 최소값과 다른 풀의 MIN 값 합계는 100%에서 차감 된다.

 

 

[리소스 거버너 설정]

다음은 리소스 관리자에서 CPU 자원을 제어하는 방법에 대해서 알아본다. 다음 스크립트는 리소스 풀을 생성하고 CPU의 사용량을 20%로 설정 한다.

CREATE RESOURCE POOL poolAdhoc WITH (MAX_CPU_PERCENT = 20); GO

 

다음 스크립트는 워크로드 그룹을 생성한다.

CREATE WORKLOAD GROUP poolGroup     USING poolAdhoc

 

 

다음 스크립트는 분류자 사용자 정의 함수를 생성한다. 로그인 이름에 따라 워크그룹을 반환한다.

USE master; GO CREATE FUNCTION dbo.fnUserClassifier() RETURNS SYSNAME WITH SCHEMABINDINGs AS BEGIN DECLARE @GroupName SYSNAME   IF SUSER_NAME() = 'ResourceTest' BEGIN SET @GroupName = 'poolGroup' END ELSE BEGIN SET @GroupName = 'default' END RETURN @GroupName; END GO

 

 

다음 스크립트는 분류자 함수를 등록하고 메모리 내 구성을 업데이트 한다.

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

 

 

[리소스 풀, 작업 그룹 및 분류자 사용자 정의 함수 확인]

생성된 리소스 풀 및 워크로드 그룹의 확인은 다음 스크립트를 통해 확인이 가능하다.

USE master SELECT * FROM sys.resource_governor_resource_pools SELECT * FROM sys.resource_governor_workload_groups GO

 

 

 

다음 스크립트는 분류자 함수와 설정을 확인 할 수 있다.

SELECT * FROM sys.resource_governor_configuration GO   SELECT object_schema_name(classifier_function_id) AS [schema_name], object_name(classifier_function_id) AS [function_name] FROM sys.dm_resource_governor_configuration

 

 

 

다음 스크립트는 리소스 풀 및 작업 그룹에 대한 현재 런타임 데이터를 확인할 수 있다.

SELECT * FROM sys.dm_resource_governor_resource_pools SELECT * FROM sys.dm_resource_governor_workload_groups GO

 

 

 

다음 스크립트는 각 그룹에 있는 세션을 확인할 수 있다.

SELECT s.group_id, CAST(g.name as nvarchar(20)), s.session_id, s.login_time, CAST(s.host_name as nvarchar(20)), CAST(s.program_name AS nvarchar(20)) FROM sys.dm_exec_sessions s INNER JOIN sys.dm_resource_governor_workload_groups g ON g.group_id = s.group_id ORDER BY g.name GO

 

 

 

다음 스크립트는 각 그룹에 있는 요청을 확인 할 수 있다.

SELECT r.group_id, g.name, r.status, r.session_id, r.request_id, r.start_time, r.command, r.sql_handle, t.text FROM sys.dm_exec_requests r INNER JOIN sys.dm_resource_governor_workload_groups g ON g.group_id = r.group_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t ORDER BY g.name GO

 

 

 

다음 스크립트는 분류자에서 실행하는 요청을 확인할 수 있다.

SELECT s.group_id, g.name, s.session_id, s.login_time, s.host_name, s.program_name FROM sys.dm_exec_sessions s INNER JOIN sys.dm_resource_governor_workload_groups g ON g.group_id = s.group_id AND 'preconnect' = s.status ORDER BY g.name GO   SELECT r.group_id, g.name, r.status, r.session_id, r.request_id, r.start_time, r.command, r.sql_handle, t.text FROM sys.dm_exec_requests r INNER JOIN sys.dm_resource_governor_workload_groups g ON g.group_id = r.group_id AND 'preconnect' = r.status CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t ORDER BY g.name GO

 

 

[참고자료]

• 리소스 관리자 리소스 풀 : http://msdn.microsoft.com/ko-kr/library/hh510189.aspx
• 분류자 사용자 정의 함수 만들기 : http://msdn.microsoft.com/ko-kr/library/cc645892.aspx
• http://www.mssqltips.com/sqlservertip/1720/handling-workloads-on-sql-server-2008-with-resource-governor/

 

 

 

2014-05-20 / 강성욱 / http://sqlmvpkr

 

 

SQLSERVER, mssql, SQL강좌, sqlserver 2014, sql 2014, SQL 서버 리소스 거버너, 정책관리, 리소스 제어, SQL Server 리소스 제어