Earlier, I demonstrated filtering the resources available to a process based on the User attempting to initiate the process. This example will use the application name as the filter - this allows users of more intensive applications to be throttled.
To keep things simple, I'll keep the resource pools the same, all I will modify is the Classifier function. Note: it will still use the same Groups as the previous example.
CREATE FUNCTION dbo.ResourceClassifier() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @GroupName AS sysname
IF (APP_NAME() LIKE '%Query Analyzer%')
SET @GroupName = 'ResourceAdmin'
ELSE
SET @GroupName = 'ResourceDefault'
RETURN @GroupName
END
Aslong as this function returns a group name, that can be consumed by the Resource Governor, you can use anything, even the GETDATE function to allow certain processing at certain times.
You might have a production server that is only active during 8am and 6pm, so outside of those hours, you might want to increase the resources available to reporting users.
The next example allows full resources only a Saturday
CREATE FUNCTION dbo.ResourceClassifier() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @GroupName AS sysname
IF (DAY(GETDATE()) = 'Saturday')
SET @GroupName = 'ResourceAdmin'
ELSE
SET @GroupName = 'ResourceDefault'
RETURN @GroupName
END