resource governor in SQL Server 2012

좀 아쉽다.....

아직도 Buffer Pool 용량제한, I/O 성능등은 제어하지 못한다.

2014 에서는 가능해 보인다.

 

하지만

2012 에서 20개의 pool 에서 62개로 pool 이 늘어나고

경쟁방식의 맥스 cpu 사용량과 더불어 hard cap 이 가능한 것은 좋아 보이기도 한다.

또한 BP 을 제외한 모든 메모리 영역은 제어 가능하다.

 

아직도 시끄러운 이웃은......이사가는게(다른 인스턴스로) 답인듯 하다.........

RG의 timeout 설정은 재미있는 설정인듯 하다.

 

Resource Governor in SQL Server 2012.docx

 

USE [MASTER]
GO

DROP DATABASE MB500
DROP DATABASE MB1000
DROP DATABASE MB200
GO

/****** OBJECT:  DATABASE [READAHEAD]    SCRIPT DATE: 2013-07-07 오후 3:23:18 ******/
CREATE DATABASE MB500
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'MB500', FILENAME = N'F:\MSSQL\MB500.MDF' , SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB )
 LOG ON
( NAME = N'MB500_LOG', FILENAME = N'F:\MSSQL\MB500.LDF' , SIZE = 1GB , MAXSIZE = 2048GB , FILEGROWTH = 1048576KB )
GO
CREATE DATABASE MB1000
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'MB1000', FILENAME = N'F:\MSSQL\MB1000.MDF' , SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB )
 LOG ON
( NAME = N'MB1000_LOG', FILENAME = N'F:\MSSQL\MB1000.LDF' , SIZE = 1GB , MAXSIZE = 2048GB , FILEGROWTH = 1048576KB )
GO
CREATE DATABASE MB200
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'MB200', FILENAME = N'F:\MSSQL\MB200.MDF' , SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB )
 LOG ON
( NAME = N'MB200_LOG', FILENAME = N'F:\MSSQL\MB200.LDF' , SIZE = 1GB , MAXSIZE = 2048GB , FILEGROWTH = 1048576KB )
GO

USE MB500
GO

SELECT TOP 125000 ROW_NUMBER() OVER ( ORDER BY (SELECT NULL)) IDXINT
  , CAST('A' AS CHAR(3000)) CHAR3000
  , CAST(ABS(CHECKSUM(NEWID())) % 1000000 AS INT) CINT3
  INTO MB500
  FROM SYSOBJECTS A1
     , SYSOBJECTS A2
     , SYSOBJECTS A3
     , SYSOBJECTS A4
     , SYSOBJECTS A5
GO
USE MB1000
GO
SELECT TOP (125000*2) ROW_NUMBER() OVER ( ORDER BY (SELECT NULL)) IDXINT
  , CAST('A' AS CHAR(3000)) CHAR3000
  , CAST(ABS(CHECKSUM(NEWID())) % 1000000 AS INT) CINT3
  INTO MB1000
  FROM SYSOBJECTS A1
     , SYSOBJECTS A2
     , SYSOBJECTS A3
     , SYSOBJECTS A4
     , SYSOBJECTS A5
GO
USE MB200
GO
SELECT TOP (125000/5*2) ROW_NUMBER() OVER ( ORDER BY (SELECT NULL)) IDXINT
  , CAST('A' AS CHAR(3000)) CHAR3000
  , CAST(ABS(CHECKSUM(NEWID())) % 1000000 AS INT) CINT3
  INTO MB200
  FROM SYSOBJECTS A1
     , SYSOBJECTS A2
     , SYSOBJECTS A3
     , SYSOBJECTS A4
     , SYSOBJECTS A5
GO

-- 테스트 쿼리
SELECT COUNT(*) FROM MB200.DBO.MB200
SELECT COUNT(*) FROM MB500.DBO.MB500
SELECT COUNT(*) FROM MB1000.DBO.MB1000
GO

-- BP 용량 조회
SELECT TYPE, SUM(PAGES_KB) PAGES_KB
  FROM  SYS.DM_OS_MEMORY_CLERKS
 GROUP BY TYPE
 ORDER BY PAGES_KB DESC
GO

SELECT LEFT(CASE DATABASE_ID WHEN 32767 THEN 'RESOURCEDB' ELSE DB_NAME(DATABASE_ID) END, 20) AS DATABASE_NAME
  , COUNT(*)AS BUFFERED_PAGE_COUNT
  , COUNT(*) * 8192 / (1024 * 1024) AS BUFFER_POOL_MB
  FROM SYS.DM_OS_BUFFER_DESCRIPTORS
 GROUP BY DB_NAME(DATABASE_ID) ,DATABASE_ID
HAVING LEFT(CASE DATABASE_ID WHEN 32767 THEN 'RESOURCEDB' ELSE DB_NAME(DATABASE_ID) END, 20) LIKE 'MB%'
 ORDER BY 1
GO

USE MB200
GO

CREATE CLUSTERED INDEX CL_MB200 ON MB200 (IDXINT)
GO

USE MB500
GO

CREATE CLUSTERED INDEX CL_MB500 ON MB500 (IDXINT)
GO

USE MB1000
GO

CREATE CLUSTERED INDEX CL_MB1000 ON MB1000 (IDXINT)
GO

CREATE LOGIN MB1000 WITH PASSWORD = 'MB1000', CHECK_POLICY = OFF
CREATE LOGIN MB500 WITH PASSWORD = 'MB500', CHECK_POLICY = OFF
CREATE LOGIN MB200 WITH PASSWORD = 'MB1000', CHECK_POLICY = OFF

USE MB1000
GO
CREATE USER MB1000 FOR LOGIN MB1000;
CREATE USER MB500 FOR LOGIN MB500;
CREATE USER MB200 FOR LOGIN MB200;
GO

USE MB500
GO
CREATE USER MB1000 FOR LOGIN MB1000;
CREATE USER MB500 FOR LOGIN MB500;
CREATE USER MB200 FOR LOGIN MB200;
GO

USE MB200
GO
CREATE USER MB1000 FOR LOGIN MB1000;
CREATE USER MB500 FOR LOGIN MB500;
CREATE USER MB200 FOR LOGIN MB200;
GO

CREATE RESOURCE POOL MB1000POOL
CREATE RESOURCE POOL MB500POOL
CREATE RESOURCE POOL MB200POOL

CREATE WORKLOAD GROUP MB1000GROUP USING MB1000POOL
CREATE WORKLOAD GROUP MB500GROUP USING MB500POOL
CREATE WORKLOAD GROUP MB200GROUP USING MB200POOL
GO

ALTER RESOURCE GOVERNOR RECONFIGURE
GO

USE MASTER
GO

CREATE FUNCTION CLASSIFIER_V1()
RETURNS SYSNAME WITH SCHEMABINDING
BEGIN
 DECLARE @val varchar(32)
 SET @val = 'default';
 IF 'MB1000' = SUSER_SNAME()
  SET @val = 'MB1000GROUP';
 ELSE IF 'MB500' = SUSER_SNAME()
  SET @val = 'MB500GROUP';
 ELSE IF 'MB200' = SUSER_SNAME()
  SET @val = 'MB200GROUP';
 RETURN @val;
END
GO

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

SELECT * FROM sys.dm_resource_governor_configuration
SELECT * FROM sys.dm_resource_governor_resource_pools
SELECT * FROM sys.dm_resource_governor_workload_groups