SQL Server 2025 operational enhancements: what DBAs should enable, test and watch

#The goal

You have a SQL Server 2025 instance, or an upgrade on the plan, and you want to know which new features change how you run it. This post skips the AI and vector features and covers only what affects blocking, backups, HA, tempdb and troubleshooting. For each one you get what it does, whether it is on by default, and the T-SQL to switch it on and check it.

#Edition changes that affect sizing

• Standard edition now uses up to the lesser of 4 sockets or 32 cores, and up to 256 GB for the buffer pool.
• Resource Governor is now in Standard (and the new free Standard Developer edition), with the same functionality as Enterprise.
• Express allows databases up to 50 GB and includes what used to be "Express with Advanced Services".
• Web edition is discontinued. Plan a licence move before you upgrade those servers.

Tip

Re-check your edition choice before you buy Enterprise for a mid-size workload. A 32-core, 256 GB Standard instance with Resource Governor covers many workloads that needed Enterprise before.

#Optimized locking

Optimized locking holds one lock per transaction ID instead of row and page locks for the whole transaction, and checks the predicate before taking a lock. You get less blocking, less lock memory and almost no lock escalation. It needs ADR, and lock after qualification only applies when read committed snapshot isolation (RCSI) is on.

-- 1. ADR first (needs an exclusive lock on the database)
ALTER DATABASE SalesDB SET ACCELERATED_DATABASE_RECOVERY = ON WITH ROLLBACK IMMEDIATE;

-- 2. RCSI, recommended for the full benefit
ALTER DATABASE SalesDB SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

-- 3. Optimized locking
ALTER DATABASE SalesDB SET OPTIMIZED_LOCKING = ON;

-- Verify
SELECT name, is_accelerated_database_recovery_on,
       is_read_committed_snapshot_on, is_optimized_locking_on
FROM sys.databases
WHERE name = 'SalesDB';

Warning

WITH ROLLBACK IMMEDIATE kills open transactions. Run it in a maintenance window. Turning on RCSI changes read behaviour: code that relies on readers blocking behind writers can return different results. Test the application first.

ADR keeps a persistent version store in the user database, so watch data file growth for the first few days. To turn ADR off later, disable optimized locking first.

#ZSTD backup compression

ZSTD is a new compression algorithm alongside MS_XPRESS. It is faster and compresses better, and you can choose a level.

BACKUP DATABASE SalesDB
TO DISK = N'G:\Backup\SalesDB_full.bak'
WITH COMPRESSION (ALGORITHM = ZSTD, LEVEL = MEDIUM), CHECKSUM, STATS = 10;

LEVEL accepts LOW (the default), MEDIUM or HIGH. Higher levels use more CPU for smaller files. To make ZSTD the server default:

EXEC sp_configure 'backup compression algorithm', 3;   -- 1 = MS_XPRESS, 2 = Intel QAT, 3 = ZSTD
RECONFIGURE;

SELECT name, value_in_use FROM sys.configurations
WHERE name IN ('backup compression default', 'backup compression algorithm');

Caution

Earlier SQL Server versions cannot read a compressed SQL Server 2025 backup, and you cannot restore a 2025 database to an older version anyway. Keep your rollback plan for the upgrade based on something other than a backup taken after the upgrade.

If the sp_configure call returns an error on your build, set the algorithm in each BACKUP command and check the SQL Server 2025 known issues page for your cumulative update. Time a LOW and a MEDIUM backup of your largest database before you change the agent jobs.

#Availability group changes

#Full and differential backups on secondaries

Before 2025 a secondary could take only COPY_ONLY full backups and log backups. Now it can take regular full, differential and log backups, so the whole backup load can move off the primary. The existing settings still decide where backups run:

ALTER AVAILABILITY GROUP AG1 SET (AUTOMATED_BACKUP_PREFERENCE = SECONDARY);
ALTER AVAILABILITY GROUP AG1 MODIFY REPLICA ON N'SQL02' WITH (BACKUP_PRIORITY = 80);

-- In the backup job, on every replica
IF sys.fn_hadr_backup_is_preferred_replica(N'SalesDB') = 1
    BACKUP DATABASE SalesDB TO DISK = N'\\backupshare\SalesDB_full.bak'
    WITH COMPRESSION (ALGORITHM = ZSTD), CHECKSUM;

Your backup chain now spans replicas. Make sure every replica writes to the same share or URL, and that your restore scripts can find files from every node.

#Fail over faster on persistent health issues

By default the cluster tries one restart of the AG resource before failing over. In SQL Server 2025 you can set RestartThreshold to 0 so it fails over straight away:

(Get-ClusterResource -Name "AG1").RestartThreshold = 0
Get-ClusterResource -Name "AG1" | Format-List Name, RestartThreshold

Warning

A value of 0 is less tolerant of transient problems and can cause failovers you did not need. Use it only where the application reconnects cleanly through the listener.

#Other AG items

• Asynchronous page request dispatching speeds up undo-of-redo during DR failover. It is on by default and helps most over high-latency links.
• availability group commit time (milliseconds, 0 = default of 10 ms) and max ucs send boxcars (WAN flow control, needs a restart) are new advanced sp_configure options. Leave them at the defaults unless you have measured commit latency or WAN throughput problems.
• Query Store now runs on readable secondaries by default, so you can tune reporting queries where they actually run.
• TLS 1.3 with TDS 8.0 is supported for AG, FCI, log shipping and replication endpoints.

#tempdb: governance and ADR

#Cap tempdb per workload group

A single report that fills tempdb can stop the whole instance. You can now cap tempdb data space per workload group. A request that goes over the cap fails with error 1138, and the other workloads keep running.

ALTER WORKLOAD GROUP grp_reporting WITH (GROUP_MAX_TEMPDB_DATA_MB = 20480);
ALTER RESOURCE GOVERNOR RECONFIGURE;

SELECT name, tempdb_data_space_kb, peak_tempdb_data_space_kb,
       total_tempdb_data_limit_violation_count
FROM sys.dm_resource_governor_workload_groups;

GROUP_MAX_TEMPDB_DATA_PERCENT works only when tempdb files have a fixed MAXSIZE, or are UNLIMITED with FILEGROWTH of zero. Otherwise you get warning 10989 and no limit. The version store and the tempdb log are not counted.

Caution

Do not put a small limit on the default group. SSMS Object Explorer and system tooling run there and start failing with error 1138.

#ADR in tempdb

ADR in tempdb gives instant rollback and aggressive log truncation, which deals with a tempdb log that fills behind a long-running transaction.

ALTER DATABASE tempdb SET ACCELERATED_DATABASE_RECOVERY = ON;
-- Takes effect after the Database Engine restarts

Give tempdb data files some extra space for the version store after you enable it.

#Troubleshooting tools

Stop a known bad query without changing application code. It fails with error 8778 until you clear the hint:

EXEC sys.sp_query_store_set_hints
     @query_id = 39,
     @query_hints = N'OPTION (USE HINT (''ABORT_QUERY_EXECUTION''))';

EXEC sys.sp_query_store_clear_hints @query_id = 39;

Extended Events sessions can now stop by themselves after a set time with the MAX_DURATION option, so a diagnostic trace started during an incident cannot keep running for weeks. DOP feedback is now on by default. If a plan's degree of parallelism changes after the upgrade, check whether DOP feedback changed it before you start tuning.

#Upgrade checklist

1. Read the SQL Server 2025 known issues page for your target CU. Known items include slow persisted-statistics threads on instances with many databases (startup trace flag 15608), access violations on Windows Server 2025 with Lock Pages in Memory, and start-up failures with more than 64 logical cores per NUMA node.
2. Logins now use PBKDF2 password hashing by default. Measure SQL authentication login latency on busy connection paths.
3. Enable ADR and then optimized locking one database at a time, starting with the database that blocks the most.
4. Switch one backup job to ZSTD, compare duration and size, then roll it out.
5. Move full and differential backups to a secondary and test a restore that uses files from more than one replica.
6. Put tempdb caps on reporting and ad hoc workload groups before the next month-end run.

#Prevent it next time

Record every new setting from this list (ADR, optimized locking, RCSI, backup algorithm, RestartThreshold, tempdb caps) in your build standard and check for drift every week with sys.databases, sys.configurations and sys.resource_governor_workload_groups. New 2025 servers then come up the same way, and nobody has to rediscover a setting during an incident.