-- Create a resource pool for report users
USE master;
GO

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU=0;
GO

-- "Opportunistic" capping
CREATE RESOURCE POOL rpReportUsers
WITH
(
     CAP_CPU_PERCENT = 100,
	 MAX_CPU_PERCENT = 10
);
GO

--- Create a workload group forreport users
CREATE WORKLOAD GROUP wgReportUsers
WITH
(
     MAX_DOP = 1
) USING [rpReportUsers]
GO

-- Creating an example Reporting login/user
USE master;
GO

CREATE LOGIN [CreditRptUsr] WITH PASSWORD=N'xyz1234', 
DEFAULT_DATABASE=[Credit], CHECK_POLICY=OFF;
GO

USE Credit;
GO

CREATE USER [CreditRptUsr] FOR LOGIN [CreditRptUsr]
GO

EXEC sp_addrolemember N'db_datareader', N'CreditRptUsr'
GO


-- Create a classifier function
USE master;
GO

CREATE FUNCTION dbo.rgClassifierFunction() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN

    DECLARE @grp AS sysname
    IF (SUSER_NAME() = 'CreditRptUsr')
        SET @grp = 'wgReportUsers'
    ELSE
        SET @grp = 'default'
    RETURN @grp
END;
GO

-- Enable RG
ALTER RESOURCE GOVERNOR WITH
	(CLASSIFIER_FUNCTION=dbo.rgClassifierFunction);
GO

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

DBCC FREEPROCCACHE;
GO

-- Check that RG is enabled
SELECT classifier_function_id, is_enabled
FROM sys.resource_governor_configuration;

-- Check on resource pool
SELECT pool_id, min_cpu_percent, max_cpu_percent, cap_cpu_percent
FROM sys.resource_governor_resource_pools
WHERE name = 'rpReportUsers';

-- **
-- Launch workload and check perfmon
-- **

ALTER RESOURCE POOL rpReportUsers
WITH
(
     MAX_CPU_PERCENT = 100,
     CAP_CPU_PERCENT = 10
);
GO

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

DBCC FREEPROCCACHE;
GO

-- Check that RG is enabled
SELECT classifier_function_id, is_enabled
FROM sys.resource_governor_configuration;

-- Check on resource pool
SELECT pool_id, min_cpu_percent, max_cpu_percent, cap_cpu_percent
FROM sys.resource_governor_resource_pools
WHERE name = 'rpReportUsers';

-- **
-- Launch workload and check perfmon
-- **


-- Cleanup
USE credit;
DROP USER [CreditRptUsr];
GO

USE master;
GO

ALTER RESOURCE GOVERNOR WITH
	(CLASSIFIER_FUNCTION=NULL);
GO

ALTER RESOURCE GOVERNOR DISABLE;
GO

DROP WORKLOAD GROUP [wgReportUsers];
DROP RESOURCE POOL [rpReportUsers];
DROP FUNCTION dbo.rgClassifierFunction;

EXEC sp_configure 'max degree of parallelism', 0
RECONFIGURE

DROP LOGIN [CreditRptUsr];
GO

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU=AUTO;
GO
