제 7 장: 리소스 관리자

MS SQL 서버는 동시에 여러 프로세스을 수행합니다. 동시에 수행되는 여러 프로세스들은 각자의 처리를 위해 제한된 자원을 가지고 경합을 벌이게 됩니다. 서버의 CPU와 메모리는 한정되어 있는 상황에서 이러한 프로세스들 끼리의 경합은 정작 중요한 프로세스가 필요한 시점에 수행되지 못하고 상대적으로 중요하지 않는 프로세스들의 수행으로 인해 예정된 시간보다 늦게 수행되는 경우가 있습니다. 예를 들어 복잡한 쿼리를 장 시간 수행해서 얻어지는 보고서 산출 시스템을 수행한 경우 갑작스런 서버 부하를 야기시켜 중요한 고객과의 응대를 위한 고객 서비스 시스템이 갑자기 느려져 업무에 방해가 될 수 있습니다.SQL Server 2008에서는 이러한 문제를 해결할 수 있는 방법으로 리소스 관리자(Resource Governor) 기능을 제공하고 있습니다. 이번 강좌에서는 리스스 관리자에 대해 살펴보도록 하겠습니다. 1. 리소스 관리자(Resource Governor)란? 리스스 관리자란 서버의 자원(CPU와 메모리를 이야기 함)을 여러 프로세스들에 대해 정해진 규칙에 따라 배정하는 리소스 관리 기능입니다. 이러한 리소스 관리를 통해서 악성 쿼리로 인해 자원이 불필요하게 낭비되는 문제를 해결할 수 있으며, 프로세스간의 중요도를 설정하여 중요한 프로세스가 우선적으로 처리되도록 할 수 있습니다. 리소스 관리는 리소스 풀, 작업 그룹, 그리고 사용자 정의 분류자 함수를 사용하여 가능하게 됩니다. 2. 리소스 풀(Resource Pool) 리소스 풀은 작업 그룹을 그룹 짓는 역할을 합니다. 리소스 풀들에 대해 각각의 리소스 풀이 사용할 수 있는 최대 CPU, 최소 CPU, 최대 메모리, 최소 메모리를 설정하게 됩니다. 자원을 많이 할당 받은 리소스 풀에 포함된 작업 그룹들은 다른 작업 그룹에 비해 상대적으로 많은 자원을 사용할 수 있게 됩니다. 리소스 풀은 기본적으로 Default 리소스 풀과 Internal 리소스 풀을 갖게 됩니다. - Default 리소스 풀 Default 작업 그룹이 포함되는 리소스 풀입니다. 또한 사용자가 정의한 작업 그룹 중에서 리소스 풀을 배정받지 못한 작업 그룹도 Default 리소스 풀에 포함됩니다. - Internal 리소스 풀 시스템 내부적인 처리릉 위한 모든 프로세스가 포함되는 Internal 작업 그룹이 포함되는 리소스 풀입니다. 아래 [화면1]에서는 리소스 관리자 부분에서 기본적으로 포함되어 있는 Default 리소스 풀과 Internal 리소스 풀을 확인한 예입니다. [화면1] 리소스 관리자는 기본적으로 사용하지 않는 상태로 되어 있습니다. 리소스 관리자를 사용하기 위해서는 아래 [화면2]에서와 같이 사용 가능을 설정해 주어야 합니다. 아니면 나중에 리소스 풀을 만들 때 사용 가능하도록 설정 할 수 도 있습니다. [화면2] 개체 탐색기에서 리소스 풀을 만드는 과정을 살펴보겠습니다. [화면3]과 같이 새 리소스 풀을 만드는 메뉴를 선택합니다. [화면3] 그러면 아래 [화면4]에서와 같이 리소스 풀을 만드는 화면이 표시됩니다. 이 화면에서 리소스 풀 이름을 설정하고 해당 리소스 풀에 대한 CPU와 메모리에 대한 제한을 설정합니다. [화면4] 아래 [화면5]에서는 Low Importance Pool 이라는 이름의 리소스 풀이 만들어진 모습을 보여줍니다. [화면5] 3. 작업 그룹(Workload Group) 실제로 처리되는 프로세스를 포함하는 것이 작업 그룹입니다. 이러한 작업 그룹이 리소스 풀에 포함됩니다. SQL Server에서 처리되는 모든 프로세스는 하나의 작업 그룹에 포함되어야 합니다. 만들 특정 작업 그룹을 할당 받지 못한 경우 Default 작업 그룹에 포함됩니다. 하나의 리소스 풀에는 여러 작업 그룹들이 포함될 수 있습니다. 하지만 하나의 작업 그룹은 하나의 리소스 풀에만 포함되어야 합니다. 하나의 리소스 풀에 포함된 여러 작업 그룹은 해당 리소스 풀에 할당된 자원을 사용하게 됩니다. 또한 같은 리소스 풀에 포함된 작업 그룹들이라 하더라도 작업 그룹에 대한 중요도를 설정하면 설정된 중요도에 따라 리소스 풀에 할당된 자원 중에 사용하게 되는 자원의 크기가 달라지게 됩니다. [화면6]에서와 같이 리소스 풀을 마우스 우측 클릭해 표시되는 단축 메뉴에서 새 작업 그룹을 만드는 메뉴를 선택하여 작업 그룹을 만들 수 있습니다. 물론 이전의 [화면4]에서 하단 부분에 작업 그룹을 바로 등록해도 됩니다. [화면6] [화면7]에서는 Low Importance Group 이라는 이름의 작업 그룹을 Low Importance Pool 리소스 풀에 만드는 과정을 보여줍니다. 이 화면에서 각 작업 그룹에 대한 중요도 등을 설정 할 수 있습니다. [화면7] [화면8]에서는 리소스 풀과 작업 그룹이 등록된 결과를 보여줍니다. [화면8] 4. 분류자 함수(Classifier Function) SQL Server에 처리를 위해 도달한 모든 프로세스는 특정 작업 그룹에 배정되도록 정확히 분류되어야 합니다. 이러한 역할을 담당하는 것이 분류자 함수입니다. 분류자 함수는 master 데이터베이스에 만들어져야 합니다. 그리고 여러 분류자 함수를 만들 수 있지만 사용될 수 있는 분류자 함수는 단 하나만 가능합니다. 분류자 함수에서는 프로세스에 사용된 로그인 계정, 응용 프로그램 이름, 호스트 이름 등등의 정보를 참조해 해당 프로세스를 어떤 작업 그룹에 포함시킬 지 결정하게 됩니다. 특정 작업 그룹에 포함되지 못한 프로세스는 Default 작업 그룹에 자동으로 포함되며, 결국 Default 리소스 풀에 포함되게 됩니다. 아래 스크립트는 fn_ClassftApps 라는 이름의 분류자 함수를 만드는 예를 보여줍니다. 응용 프로그램 이름을 가지고 작업 그룹을 결정하고 있음을 알 수 있습니다. USE master GO CREATE FUNCTION dbo.fn_ClassifyApps()   RETURNS sysname   WITH SCHEMABINDING AS   BEGIN     DECLARE @ret sysname     IF (APP_NAME() LIKE '%Low Importance Application%')         SET @ret = 'Low Importance Group'     RETURN @ret   END GO [화면9]에서는 앞에서 만든 분류자 함수를 리소스 관리자에서 사용하도록 설정하고 있는 화면입니다.분류자 함수 이름(C) 부분에 앞에서 만든 함수 이름이 표시됨을 알 수 있습니다. [화면9] 지금까지는 리소스 관리자를 사용하기 위해 리소스 풀과 작업 그룹, 그리고 분류자 함수를 만들고 이를 사용하도록 설정하는 방법을 살펴보았습니다. 이제는 실제 리소스 관리자를 사용해 프로세스들의 자원 사용을 제어하는 예를 보도록 하겠습니다. 5. 리소스 괸리자 사용 예 모든 작업은 개체 탐색기 대신 T-SQL을 사용하도록 하겠습니다. 반복적인 작업은 개체 탐색기보다 T-SQL이 더 효율적이라 생각합니다. 1) 리소스 관리자 사용 가능 설정 및 리소스 풀 만들기 먼저 리소스 관리자 사용을 설정하고 두 개의 리소스 풀을 만들겠습니다. PoolMarketing 리소스 풀은 영업 정보 관련된 작업을 포함할 것이며, PoolCustomer 리소스 풀은 고객 관련 중요 작업을 포함할 것이라 가정하겠습니다. -- 1) 리소스 관리자 사용 가능 설정 ALTER RESOURCE GOVERNOR RECONFIGURE GO -- 2) 리소스 풀 만들기 -- 모든 매개변수는 기본값을 사용함 CREATE RESOURCE POOL PoolMarketing CREATE RESOURCE POOL PoolCustomer 2) 작업 그룹 만들기 앞의 과정에서 만든 두 리소스 풀에 작업 그룹을 포함시키겠습니다. PoolMarketing 리소스 풀에는 GroupMarketing 작업 그룹과 GroupReport 작업 그룹이 포함됩니다. PoolCustomer 리소스 풀에는 GroupCustomer 작업 그룹만 포함됩니다. -- 3) 작업 그룹 만들기 -- 모든 매개변수는 기본값을 사용함 CREATE WORKLOAD GROUP GroupMarketing   USING PoolMarketing CREATE WORKLOAD GROUP GroupReport   USING PoolMarketing CREATE WORKLOAD GROUP GroupCustomer   USING PoolCustomer GO ALTER RESOURCE GOVERNOR RECONFIGURE; GO 3) 로그인 계정 만들기 여러 작업들이 SQL Server에 접근 할 때 사용할 각각의 로그인 계정을 만들겠습니다. 이들 로그인 계정을 사용해 프로세스를 구분하여 특정 작업 그룹으로 배정할 것입니다. -- 4) 로그인 계정 만들기 USE master GO -- 로그인 계정을 근거로 작업 그룹이 할당할 예정 CREATE LOGIN UserMarketing WITH PASSWORD = 'UserMarketingPwd', CHECK_POLICY = OFF CREATE LOGIN UserReport WITH PASSWORD = 'UserReportPwd', CHECK_POLICY = OFF CREATE LOGIN UserCustomer WITH PASSWORD = 'UserCustomerPwd', CHECK_POLICY = OFF GO 4) 분류자 함수 만들기 로그인 계정을 사용해 작업 그룹을 배정하는 분류자 함수를 만들고 리소스 관리자에서 사용하도록 설정하겠습니다. 분류자 함수는 필히. master 데이터베이스에 만들어야 합니다. USE master GO IF OBJECT_ID('DBO.CLASSIFIER_V1','FN') IS NOT NULL   DROP FUNCTION DBO.CLASSIFIER_V1 GO CREATE FUNCTION CLASSIFIER_V1 ()   RETURNS SYSNAME WITH SCHEMABINDING BEGIN   DECLARE @val varchar(32)   SET @val = 'default'   IF 'UserCustomer' = SUSER_SNAME()     SET @vall = 'GroupCustomer'   ELSE IF 'UserMarketing' = SUSER_SNAME()     SET @val = 'GroupMarketing'   ELSE IF 'UserReport' = SUSER_SNAME()     SET @val = 'GroupReport'   RETURN @val END GO -- 5) 분류자 함수를 리소스 관리자에 지정 ALTER RESOURCE GOVERNOR   WITH (CLASSIFIER_FUNCTION = dbo.CLASSIFIER_V1) GO -- 6) 위 설정 내용을 리소스 관리자에 반영함 ALTER RESOURCE GOVERNOR RECONFIGURE GO 5) 여러 작업 수행 이제 SQL Serve에 여러 작업을 수행해야 합니다. 이러한 작업들이 처음엔 자원들 평등하게 사용하지만 리소스 관리자를 사용해 자원 사용을 제어하면서 상황이 달라지게 됩니다. 강좌를 위해서 다음과 같은 내용의 배치 파일을 사용해 서로 다른 로그인 계정으로 작업이 수행되게 했습니다. @echo off echo "Press any key to start Marketing workload" pause start /MIN sqlcmd -S LONDON -U UserMarketing -P UserMarketingPwd -i "CPU intensive loop_01.sql" echo "Press any key to start Customer workload" pause start /MIN sqlcmd -S LONDON -U UserCustomer -P UserCustomerPwd -i "CPU intensive loop_01.sql" echo "Press any key to start Report workload" pause start /MIN sqlcmd -S LONDON -U UserReport -P UserReportPwd -i "CPU intensive loop_01.sql" 우선 처음 수행된 작업은 UserMarketing 계정으로 수행되었으므로 GroupMarketing 작업 그룹에 포함됩니다. 수행되는 작업의 모습을 Windows의 성능 모니터를 사용해 모니터링 해보았습니다. 아래 [화면10]에서 GroupMarketing 작업 그룹이 CPU 전부를 사용함을 볼 수 있습니다. [화면10] 두 번째 수행된 작업은 UserCustomer 계정으로 수행되었으므로 GroupCustomer 작업 그룹에 포함됩니다. 아래 [화면11]에서 GroupMarketing 작업 그룹과 GroupCustomer 작업 그룹이 CPU를 절반 씩 사용함을 볼 수 있습니다. [화면11] 세 번째 수행된 작업은 UserReport 계정으로 수행되었으므로 GroupReport 작업 그룹에 포함됩니다. 아래 [화면12]에서 GroupMarketing, GroupCustomer 그리고 GroupReport 작업 그룹이 CPU를 3분의 1씩 사용함을 볼 수 있습니다. [화면12] 6) 리소스풀에 대한 자원 제한 리소스 풀에 대한 CPU 제한을 변경하여 GroupCustomer 작업 그룸이 전체 CPU 50%를 사용하도록 하겠습니다. 아래 스크립트에서 PoolMarketing 리소스 그룹의 CPU 사용량을 50%로 설정하고 있습니다. -- 8) PoolMarketing 풀이 CPU를 50% 이상 사용하지 못하도록 설정 ALTER RESOURCE POOL PoolMarketing   WITH (MAX_CPU_PERCENT = 50) GO ALTER RESOURCE GOVERNOR RECONFIGURE GO 이렇게 되면 PoolMarketing 리소스 풀에 포함된 GroupMarketing 작업 그룹과 GroupReport 작업 그룹이 사용할 수 있는 최대 CPU는 50%이므로 두 작업 그룹이 25%씩 사용하게 됩니다. 결국 GroupCuatomer 작업 그룹은 이들이 사용하지 않는 CPU 50% 를 전부 사용하게 됩니다. [화면13]은 그 결과를 보여주고 있습니다. [화면13] 7) 작업 그룹에 대한 중요도 설정 이제는 PoolMarketing 리소스 풀에 포함된 GroupMarketing 작업 그룹과 GroupReport 작업 그룹의 중요도를 설정해 같은 리소스 풀에 포함된 작업 그룹의 자원 사용량이 달라지는 모습을 보겠습니다. 아래 스크립트에서는 GroupReport 작업 그룹의 중요도는 Low로, GroupMarketing 작업 그룹의 중요도는 High로 설정하고 있습니다. -- 9) 그룹에 대한 중요도 설정 -- GroupAdhoc 그룹에 대한 중요도: Low ALTER WORKLOAD GROUP GroupReport   WITH (IMPORTANCE = Low) -- GroupMarketing 그룹에 대한 중요도: High ALTER WORKLOAD GROUP GroupMarketing   WITH (IMPORTANCE = High) -- 변경 사항 반영 ALTER RESOURCE GOVERNOR RECONFIGURE GO 이렇게 되면 GroupMarketing 작업 그룹과 GroupReport 작업 그룹이 사용하는 CPU는 [화면14]에서 보는 것과 같이 달라지게 됩니다. 이들 두 작업 그룹이 사용하는 CPU 50%는 변함 없습니다. [화면14] 지금까지 리소스 관리자에 대한 내용을 간략히 살펴보았습니다. 자세한 내용을 강좌를 통해서 다루기는 힘든것 같습니다. 하지만 개념은 파악이 되었을 것이라 생각됩니다. 맺음말 리소스 관리자는 많는 전문가분들이 기다려 오던 기능입니다. SQL Server 에서 수행되는 프로세스에 대한 리소스 할당을 제어 할 수 있다는 사실이 놀랍습니다. 하지만 리소스 관리자를 사용하기 위한 리소스 풀과 작업 그룹, 그리고 분류자 함수에 대한 정확한 이해를 가지지 않고 무작정 사용하려고 하면 더 큰 문제가 발생 할 수 있습니다. 그러므로 사전에 많은 계획과 테스트가 필요하다고 생각합니다. 아뭏든 리소스 관리자는 SQL Server 2008의 새로운 기능 중 대표적이라 생각됩니다.