1

I've been trying to configure / troubleshoot the resource governor on a sql server 2014 instance. I can't get any work into anything but the default resource pool. I've been following this troubleshooting guide: http://technet.microsoft.com/en-us/library/cc627395(v=sql.105).aspx

My classification function, originally conditional, now simply returns my resource pool name in all cases in an attempt to get something to run in it.

USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[RGClassifier]    Script Date: 7/25/2014 7:26:51 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[RGClassifier] ()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN

DECLARE @GroupName SYSNAME
SET @GroupName = N'in-memory'

RETURN @GroupName

END

Results of some of the troubleshooting follow:

USE master
SELECT 
  object_schema_name(classifier_function_id) AS [schema_name],
  object_name(classifier_function_id) AS [function_name]
FROM sys.dm_resource_governor_configuration

Returns:

schema_name function_name
dbo RGClassifier

Queries of sys.dm_resource_governor_configuration and sys.resource_governor_configuration indicate it is enabled and no reconfiguration is pending.

More or less stuck in the mud at this point. If anyone has configured the resource governor successfully before I'd love some pointers.

closed as off-topic by Paul White, Mark Storey-Smith, RolandoMySQLDBA, Philᵀᴹ, Marian Aug 7 '14 at 13:49

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, Mark Storey-Smith, RolandoMySQLDBA, Philᵀᴹ, Marian
If this question can be reworded to fit the rules in the help center, please edit the question.

1

Make sure your group name returned matches (identically) to your actual workload group name. Some times, I've found, it's helpful to reconfigure even if it doesn't say it needs to be done.

use master
GO

-- Create a test DB which our CF will be based on
CREATE DATABASE RGDB;
GO

-- enable RG
ALTER RESOURCE GOVERNOR RECONFIGURE
GO

CREATE FUNCTION dbo.RGClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    IF (ORIGINAL_DB_NAME() = 'RGDB')
        RETURN N'OriginalDBWG'

    RETURN N'Default'
END
GO

-- Create a new Pool
CREATE RESOURCE POOL OriginalDBPool
WITH (
MAX_CPU_PERCENT = 10
)
GO

--Create a new WG
CREATE WORKLOAD GROUP OriginalDBWG USING OriginalDBPool
GO

ALTER RESOURCE GOVERNOR WITH ( CLASSIFIER_FUNCTION = dbo.RGClassifier)
GO

ALTER RESOURCE GOVERNOR RECONFIGURE
GO

Open a new query window after running this and check the pool name by running:

SELECT wg.name FROM sys.dm_exec_sessions s inner join sys.dm_resource_governor_workload_groups wg on s.group_id = wg.group_id where s.session_id = @@SPID

Then, change the connection and add in an additional parameter

database=RGDB;

Then run the query again. You should see the workload group name change.

  • 1
    Haha oh wow. Somehow I missed the fact that the function is to return the workload group name NOT the resource pool name. Thanks! – user41207 Jul 25 '14 at 16:03

Not the answer you're looking for? Browse other questions tagged or ask your own question.