Skip to main content
Perfmon added
Source Link
Ronaldo
  • 6.6k
  • 2
  • 15
  • 44

Yes, I believe you have the needed minimum configuration set.

I don't yet trust my ability to classify my users into groups

This is an example function with a few possible items you might find useful to classify your requests into Workload Groups:

CREATE FUNCTION [dbo].[RGClassifierFunction]() RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    
    RETURN (SELECT CASE
                    WHEN (APP_NAME() LIKE N'Microsoft SQL Server Management Studio%') THEN N'WorkloadGroup1'
                    WHEN (IS_MEMBER('DOMAIN\DomainGroup') = 1) THEN N'WorkloadGroup2'
                    WHEN (HOST_NAME() LIKE N'ComputerName') THEN N'WorkloadGroup3'
                    WHEN (SUSER_NAME() LIKE N'userName') THEN N'WorkloadGroup4'
                    ELSE N'default' 
            END);

END

One of the Resource governor best practices is to keep that function as simple as possible:

The classifier function extends login processing time. Avoid complex logic and long-running or resource-intensive queries in the classifier, particularly if queries use large tables. An overly complex function can cause login delays or connection timeouts.


I want to monitor how much resources each group uses

I find it very useful to monitor some counters available on the built-in Windows system tool Perfmon, like comparing the total use of CPU against the use for each Workload Group with these counters:

Perfmon Counters

Perfmon Graph


Useful material: Tutorial: Resource governor configuration examples and best practices

Yes, I believe you have the needed minimum configuration set.

I don't yet trust my ability to classify my users into groups

This is an example function with a few possible items you might find useful to classify your requests into Workload Groups:

CREATE FUNCTION [dbo].[RGClassifierFunction]() RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    
    RETURN (SELECT CASE
                    WHEN (APP_NAME() LIKE N'Microsoft SQL Server Management Studio%') THEN N'WorkloadGroup1'
                    WHEN (IS_MEMBER('DOMAIN\DomainGroup') = 1) THEN N'WorkloadGroup2'
                    WHEN (HOST_NAME() LIKE N'ComputerName') THEN N'WorkloadGroup3'
                    WHEN (SUSER_NAME() LIKE N'userName') THEN N'WorkloadGroup4'
                    ELSE N'default' 
            END);

END

One of the Resource governor best practices is to keep that function as simple as possible:

The classifier function extends login processing time. Avoid complex logic and long-running or resource-intensive queries in the classifier, particularly if queries use large tables. An overly complex function can cause login delays or connection timeouts.


Useful material: Tutorial: Resource governor configuration examples and best practices

Yes, I believe you have the needed minimum configuration set.

I don't yet trust my ability to classify my users into groups

This is an example function with a few possible items you might find useful to classify your requests into Workload Groups:

CREATE FUNCTION [dbo].[RGClassifierFunction]() RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    
    RETURN (SELECT CASE
                    WHEN (APP_NAME() LIKE N'Microsoft SQL Server Management Studio%') THEN N'WorkloadGroup1'
                    WHEN (IS_MEMBER('DOMAIN\DomainGroup') = 1) THEN N'WorkloadGroup2'
                    WHEN (HOST_NAME() LIKE N'ComputerName') THEN N'WorkloadGroup3'
                    WHEN (SUSER_NAME() LIKE N'userName') THEN N'WorkloadGroup4'
                    ELSE N'default' 
            END);

END

One of the Resource governor best practices is to keep that function as simple as possible:

The classifier function extends login processing time. Avoid complex logic and long-running or resource-intensive queries in the classifier, particularly if queries use large tables. An overly complex function can cause login delays or connection timeouts.


I want to monitor how much resources each group uses

I find it very useful to monitor some counters available on the built-in Windows system tool Perfmon, like comparing the total use of CPU against the use for each Workload Group with these counters:

Perfmon Counters

Perfmon Graph


Useful material: Tutorial: Resource governor configuration examples and best practices

documentation added
Source Link
Ronaldo
  • 6.6k
  • 2
  • 15
  • 44

Yes, I believe you have the needed minimum configuration set.

I don't yet trust my ability to classify my users into groups

This is a samplean example function with a few possible items you might usefind useful to classify your requests into Workload Groups:

CREATE FUNCTION [dbo].[RGClassifierFunction]() RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    
    RETURN (SELECT CASE
                    WHEN (APP_NAME() LIKE N'Microsoft SQL Server Management Studio%') THEN N'WorkloadGroup1'
                    WHEN (IS_MEMBER('DOMAIN\DomainGroup') = 1) THEN N'WorkloadGroup2'
                    WHEN (HOST_NAME() LIKE N'ComputerName') THEN N'WorkloadGroup3'
                    WHEN (SUSER_NAME() LIKE N'userName') THEN N'WorkloadGroup4'
                    ELSE N'default' 
            END);

END

One of the Resource governor best practices is to keep that function as simple as possible:

The classifier function extends login processing time. Avoid complex logic and long-running or resource-intensive queries in the classifier, particularly if queries use large tables. An overly complex function can cause login delays or connection timeouts.


Useful material: Tutorial: Resource governor configuration examples and best practices

I don't yet trust my ability to classify my users into groups

This is a sample function with a few possible items you might use to classify your requests into Workload Groups:

CREATE FUNCTION [dbo].[RGClassifierFunction]() RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    
    RETURN (SELECT CASE
                    WHEN (APP_NAME() LIKE N'Microsoft SQL Server Management Studio%') THEN N'WorkloadGroup1'
                    WHEN (IS_MEMBER('DOMAIN\DomainGroup') = 1) THEN N'WorkloadGroup2'
                    WHEN (HOST_NAME() LIKE N'ComputerName') THEN N'WorkloadGroup3'
                    WHEN (SUSER_NAME() LIKE N'userName') THEN N'WorkloadGroup4'
                    ELSE N'default' 
            END);

END

Yes, I believe you have the needed minimum configuration set.

I don't yet trust my ability to classify my users into groups

This is an example function with a few possible items you might find useful to classify your requests into Workload Groups:

CREATE FUNCTION [dbo].[RGClassifierFunction]() RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    
    RETURN (SELECT CASE
                    WHEN (APP_NAME() LIKE N'Microsoft SQL Server Management Studio%') THEN N'WorkloadGroup1'
                    WHEN (IS_MEMBER('DOMAIN\DomainGroup') = 1) THEN N'WorkloadGroup2'
                    WHEN (HOST_NAME() LIKE N'ComputerName') THEN N'WorkloadGroup3'
                    WHEN (SUSER_NAME() LIKE N'userName') THEN N'WorkloadGroup4'
                    ELSE N'default' 
            END);

END

One of the Resource governor best practices is to keep that function as simple as possible:

The classifier function extends login processing time. Avoid complex logic and long-running or resource-intensive queries in the classifier, particularly if queries use large tables. An overly complex function can cause login delays or connection timeouts.


Useful material: Tutorial: Resource governor configuration examples and best practices

Source Link
Ronaldo
  • 6.6k
  • 2
  • 15
  • 44

I don't yet trust my ability to classify my users into groups

This is a sample function with a few possible items you might use to classify your requests into Workload Groups:

CREATE FUNCTION [dbo].[RGClassifierFunction]() RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    
    RETURN (SELECT CASE
                    WHEN (APP_NAME() LIKE N'Microsoft SQL Server Management Studio%') THEN N'WorkloadGroup1'
                    WHEN (IS_MEMBER('DOMAIN\DomainGroup') = 1) THEN N'WorkloadGroup2'
                    WHEN (HOST_NAME() LIKE N'ComputerName') THEN N'WorkloadGroup3'
                    WHEN (SUSER_NAME() LIKE N'userName') THEN N'WorkloadGroup4'
                    ELSE N'default' 
            END);

END