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
In SQL Server 2025 release a new backup enhancement feature was introduced by introducing new compression algorith “ZSTD” which will speed up backup performance compared to the traditional “MS_XPRESS” backup compression.
Let me explore perfomrance between the standard compression and new the compression algorithm:
BACKUP DATABASE [StackOverflowMini] TO DISK = N’D:\SQLServer2025_PREVIEW\2025\MSSQL17.MSSQLSERVER\MSSQL\Backup\StackOverflowMini.bak’ WITH NOFORMAT, NOINIT, NAME = N’StackOverflowMini-FULL-1′, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
Using traditional compression backup algorithm the backup was taken in 9 seconds with a size of 777,192 KB
BACKUP DATABASE [StackOverflowMini] TO DISK = N’D:\SQLServer2025_PREVIEW\2025\MSSQL17.MSSQLSERVER\MSSQL\Backup\StackOverflowMini_2.bak’ WITH NOFORMAT, NOINIT, NAME = N’StackOverflowMini-2′, SKIP, NOREWIND, NOUNLOAD, COMPRESSION (ALGORITHM = ZSTD), STATS = 10
GO
Using ZSTD compression algorithm backup was taken in 4 seconds with a size of 873,092 KB
So, as a conclusion the newly introduced compression algorithm is balances between size and speed in an optimized matter.
If you want this compression algorithm [ZSTD] to be used as a default in your instance :
sql server warrior 