Resource Governor is not running correctly

Question

I am using Resource Governor on my production database for restriction to maximum CPU usage. But it couldnt work efficiently. I am using same structure on different servers and it work correctly. Only difference between servers is CPU count. My OS version is Windows Server 2012 R2 and my MS SQL Server version is "Microsoft SQL Server 2014 - 12.0.2495.0 (X64) Mar 31 2015 09:47:37 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor) " My classifier function script is below:

USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[rgclassifier_MAX_CPU]    Script Date: 28.7.2015 13:24:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[rgclassifier_MAX_CPU]() RETURNS sysname 
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @workload_group_name AS sysname
      IF (ORIGINAL_DB_NAME() = 'DeFacto' )
          SET @workload_group_name = 'gMAX_CPU_PERCENT_60'
    RETURN @workload_group_name
END;

My resource pool script is below:

USE [master]
GO

/****** Object:  ResourcePool [pMAX_CPU_PERCENT_60]    Script Date: 28.7.2015 13:24:21 ******/
CREATE RESOURCE POOL [pMAX_CPU_PERCENT_60] WITH(min_cpu_percent=0, 
        max_cpu_percent=60, 
        min_memory_percent=0, 
        max_memory_percent=100, 
        cap_cpu_percent=100, 
        AFFINITY SCHEDULER = AUTO
, 
        min_iops_per_volume=0, 
        max_iops_per_volume=0)

GO

My final description script is below:

USE [master]
GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [dbo].[rgclassifier_MAX_CPU]);
GO

ALTER RESOURCE GOVERNOR WITH (MAX_OUTSTANDING_IO_PER_VOLUME = DEFAULT);
GO

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

İt doesnt restrict max CPU Usage. I set default max_cpu_percent is %60 but sometimes it use %100 percent. Thus, we can not access the system properly. This is the error message from my event log: "Failed to run resource governor classifier user-defined function 'dbo.rgclassifier_MAX_CPU'. Last error 8525, state 1. See previous errors in SQL Server error log from session ID 538 for details. Classifier elapsed time: 0 ms." Could you help me?

Answer 1

Note that the maximum CPU percentage is an opportunistic maximum. If there is available CPU capacity, the workload uses it up to 100 percent. The maximum value only applies when there is contention for CPU resources.

Here is full description