For the sake of illustration I will set maximum amount of TEMPDB usage is set at 200MB (of course in real world this is NOT a parctical value at all !! I am setting it to quickly reach the maximum configured resource governer value)
Will create a new custom workload and will call it limit_tempdb:
CREATE WORKLOAD GROUP limit_tempdb
WITH (
GROUP_MAX_TEMPDB_DATA_MB = 200
)
USING [default];
ALTER RESOURCE GOVERNOR RECONFIGURE;
You can check the current configured workloads at instance-level:
SELECT group_id,
name,
group_max_tempdb_data_mb,
group_max_tempdb_data_percent
FROM sys.resource_governor_workload_groups;
Then, I will create a classifier function:
USE master;
GO
CREATE FUNCTION dbo.RG_UserNameClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
IF (SUSER_SNAME() = ‘test’)
BEGIN
RETURN ‘limit_tempdb’;
END
RETURN ‘default’;
END;
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rg_classifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
I will then connect using “test” account using SQL Server Management Studio:
// I will create a dummy temporary table that will be created in TEMPDB database and populate it with dummy data intensively :
create table #test (Col1 varchar(80));
GO
insert into #test values (‘HELLO EMAD, HOW ARE YOU’)
GO 300000000
when the utilization reaches 200MB the following error will be thrown:
Msg 1138, Level 17, State 1, Procedure sp_checktempdb, Line 132 [Batch Start Line 0]
Could not allocate a new page for database ‘tempdb’ because that would exceed the limit set for workload group ‘limit_tempdb’, group_id 256
sql server warrior 