SQL Server Performance: 7 Brilliant 2025 Engine Internals

On July 14, 2026, SQL Server 2016 reached end of support, and Microsoft's own blog put it plainly: plan your next steps. For thousands of estates the next step is SQL Server 2025, generally available since November 2025. The marketing headline is AI. The reason DBAs should care is lower in the stack: SQL Server 2025 changes how the lock manager, the optimizer, tempdb and Always On replicas behave, and each change moves a SQL Server performance number you can measure.

This post is a tour of those SQL Server 2025 internals with the measurements that prove them. We cover optimized locking, optional parameter plan optimization, columnstore and batch mode, tempdb space resource governance, persisted statistics on secondaries and the high availability and data movement story, then close with a staged upgrade path. Feature facts come from Microsoft's official SQL Server site and SQL Server blog; T-SQL is version-pinned and every number in our examples is illustrative, not a benchmark.

SQL Server 2025 at a glance: the release facts

Microsoft announced SQL Server 2025 in November 2024, shipped a public preview in May 2025 and the first release candidate in August 2025, and the November 18, 2025 post on the SQL Server blog marked general availability. The product reports version 17.x and introduces compatibility level 170. Microsoft describes "over 50 enhancements made to the database engine". The SQL Server product page groups the ones that matter for SQL Server performance under three lines: batch mode and faster query processing, improved concurrency with optimized locking, and "reliable failover enhancements".

Express edition now supports databases up to 50 GB, which matters for small production servers that used to hit the old ceiling. And in September 2026, Microsoft made SQL Server on Azure Local generally available for connected and disconnected operations. Start every upgrade conversation by confirming what you actually run:

-- SQL Server performance baseline, step 0: know exactly what you are running.
-- SQL Server 2025 reports product version 17.x and supports compatibility level 170.
SELECT SERVERPROPERTY('ProductVersion')      AS product_version,   -- 17.0.xxxx.x on SQL Server 2025
       SERVERPROPERTY('ProductLevel')        AS product_level,     -- RTM / CU level
       SERVERPROPERTY('ProductUpdateLevel')  AS cumulative_update,
       SERVERPROPERTY('Edition')             AS edition;

-- Compatibility level per database: new optimizer behaviour is gated by it.
SELECT name,
       compatibility_level,
       is_read_committed_snapshot_on,
       is_accelerated_database_recovery_on
FROM sys.databases
WHERE database_id > 4
ORDER BY name;

Measure first: a SQL Server performance baseline worth trusting

Every engine change in this post is a hypothesis until a baseline proves it. Before touching binaries, we turn on Query Store with wait statistics capture and keep at least two weeks of history, then record lock waits and lock manager memory. Those three sources answer the questions that matter after the upgrade: did plans change, did waits change, and did the lock manager shrink.

-- SQL Server performance baseline, step 1: capture behaviour BEFORE changing anything.
-- Query Store keeps plans and runtime stats per query; size it for 2+ weeks.
ALTER DATABASE [SalesDB] SET QUERY_STORE = ON
(
    OPERATION_MODE              = READ_WRITE,
    QUERY_CAPTURE_MODE          = AUTO,          -- skip trivial ad hoc noise
    MAX_STORAGE_SIZE_MB         = 4096,
    INTERVAL_LENGTH_MINUTES     = 15,
    CLEANUP_POLICY              = (STALE_QUERY_THRESHOLD_DAYS = 30),
    WAIT_STATS_CAPTURE_MODE     = ON
);

-- Lock-related waits since startup: the numbers optimized locking should move.
SELECT wait_type,
       waiting_tasks_count,
       wait_time_ms / 1000.0                       AS wait_s,
       wait_time_ms * 1.0 / NULLIF(waiting_tasks_count, 0) AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE N'LCK[_]M[_]%'
ORDER BY wait_time_ms DESC;

-- Memory consumed by the lock manager right now.
SELECT type,
       SUM(pages_kb) / 1024.0 AS lock_memory_mb
FROM sys.dm_os_memory_clerks
WHERE type = N'OBJECTSTORE_LOCK_MANAGER'
GROUP BY type;

Export the Query Store data and the wait snapshot before the upgrade. Wait statistics are cumulative since the last restart, so a before-and-after comparison needs two snapshots over comparable business periods, not one number.

Inside the lock manager: how optimized locking works

Classic SQL Server locking is pessimistic and granular. An UPDATE scans candidate rows with update (U) locks, converts qualifying rows to exclusive (X) locks and holds each X lock until the transaction commits. A large update therefore holds thousands of row locks, consumes lock memory and risks escalation to a table lock that blocks everyone. Microsoft's description of optimized locking is precise: it "reduce[s] lock memory consumption and minimize[s] blocking for concurrent transactions through Transaction ID (TID) Locking and Lock After Qualification (LAQ)".

TID locking changes what is held. Each modified row version carries the ID of the transaction that wrote it, and the transaction holds a single exclusive lock on its own ID. Row locks are taken only for the moment of modification and then released, because anyone who needs to wait for that row can wait on the TID instead. Lock after qualification changes when locks are taken: under read committed snapshot, the predicate is evaluated against the latest committed version without a U lock, and only rows that qualify are locked. Fewer locks, held for less time, is exactly what high-concurrency OLTP needs.

Optimized locking depends on two database settings that are already familiar: accelerated database recovery (ADR), whose persistent version store carries the row versions and their transaction IDs, and read committed snapshot isolation (RCSI) for LAQ. Turning RCSI on changes read semantics for existing code, so it deserves testing in its own right. The script below enables all three with verification before, a confirmation gate and validation after.

-- Optimized locking (SQL Server 2025 internals): enable per database, in a maintenance window.
-- Run with sqlcmd:  sqlcmd -S $(SQLHOST) -d master -v CONFIRM="NO" -i enable_optlock.sql
-- Changing RCSI needs exclusive access; WITH ROLLBACK IMMEDIATE disconnects sessions.
:setvar DB "SalesDB"

-- ---------- Verification BEFORE ----------
SELECT name,
       is_accelerated_database_recovery_on,
       is_read_committed_snapshot_on,
       DATABASEPROPERTYEX(name, 'IsOptimizedLockingOn') AS optimized_locking_on
FROM sys.databases
WHERE name = N'$(DB)';

-- ---------- Confirmation gate ----------
IF N'$(CONFIRM)' <> N'YES'
BEGIN
    RAISERROR(N'Dry run only. Re-run with -v CONFIRM="YES" to apply.', 10, 1);
    SET NOEXEC ON;                         -- compile but execute nothing below
END;
GO

-- 1. Accelerated database recovery: row versions in the persistent version store
--    carry the transaction ID that TID locking relies on.
ALTER DATABASE [$(DB)] SET ACCELERATED_DATABASE_RECOVERY = ON;

-- 2. Read committed snapshot: lets lock after qualification (LAQ) evaluate
--    predicates on the latest committed version without U locks.
ALTER DATABASE [$(DB)] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

-- 3. Optimized locking itself.
ALTER DATABASE [$(DB)] SET OPTIMIZED_LOCKING = ON;
GO
SET NOEXEC OFF;
GO

-- ---------- Validation AFTER ----------
SELECT name,
       is_accelerated_database_recovery_on,
       is_read_committed_snapshot_on,
       DATABASEPROPERTYEX(name, 'IsOptimizedLockingOn') AS optimized_locking_on   -- expect 1
FROM sys.databases
WHERE name = N'$(DB)';

-- ---------- Rollback (reverse order) ----------
-- ALTER DATABASE [$(DB)] SET OPTIMIZED_LOCKING = OFF;
-- ALTER DATABASE [$(DB)] SET READ_COMMITTED_SNAPSHOT OFF WITH ROLLBACK IMMEDIATE;
-- ALTER DATABASE [$(DB)] SET ACCELERATED_DATABASE_RECOVERY = OFF;

Then prove the effect of optimized locking on your own workload. With it on, an open transaction that updated thousands of rows shows a single XACT lock in sys.dm_tran_locks instead of a matching number of KEY or RID locks. Over a business cycle, compare the LCK_M_* waits and the OBJECTSTORE_LOCK_MANAGER clerk with the baseline.

-- SQL Server performance check: see the TID lock instead of thousands of row locks.
-- Session 1: an open transaction that updates many rows.
BEGIN TRANSACTION;
UPDATE dbo.Orders
SET    Status = N'SHIPPED'
WHERE  OrderDate >= '2026-09-01' AND OrderDate < '2026-09-02';
-- (leave the transaction open)

-- Session 2: what locks does session 1 hold?
SELECT request_session_id,
       resource_type,               -- XACT = the transaction ID lock under optimized locking
       request_mode,
       COUNT(*) AS lock_count
FROM sys.dm_tran_locks
WHERE request_session_id = 61       -- session 1 spid
GROUP BY request_session_id, resource_type, request_mode
ORDER BY lock_count DESC;

-- Expected shape with optimized locking ON: one XACT X lock plus intent locks,
-- not one KEY or RID X lock per updated row.
-- Session 1: COMMIT;  (or ROLLBACK)

Optimizer and executor: fewer bad plans, faster scans

Optional parameter plan optimization

Parameter sniffing is the most common cause of "it was fast yesterday" incidents. Microsoft introduced optional parameter plan optimization (OPPO), "designed to enable SQL Server to choose the optimal execution plan based on customer-provided runtime parameter values and to significantly reduce bad parameter sniffing problems". The target is the catch-all search procedure where a parameter may be NULL to mean "any value": one cached plan cannot serve both the selective and the unrestricted case well.

-- SQL Server 2025 internals: optional parameter plan optimization (OPPO).
-- Targets the classic "optional parameter" pattern that sniffs one plan for all calls.
ALTER DATABASE [SalesDB] SET COMPATIBILITY_LEVEL = 170;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;
GO

CREATE OR ALTER PROCEDURE dbo.SearchOrders
    @CustomerId INT = NULL,          -- NULL means "any customer"
    @Status     NVARCHAR(20) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    -- Before OPPO: a plan compiled for @CustomerId = NULL (scan) could be reused for a
    -- selective @CustomerId (seek wanted), or the reverse. OPPO lets the optimizer keep
    -- separate plan variants depending on whether the optional parameter is supplied.
    SELECT OrderId, CustomerId, OrderDate, Amount, Status
    FROM dbo.Orders
    WHERE (CustomerId = @CustomerId OR @CustomerId IS NULL)
      AND (Status     = @Status     OR @Status     IS NULL);
END;
GO

-- Verify in Query Store: one parent query, multiple plan variants.
SELECT qv.parent_query_id,
       qv.query_variant_query_id,
       p.plan_id,
       rs.count_executions,
       rs.avg_duration / 1000.0 AS avg_ms
FROM sys.query_store_query_variant AS qv
JOIN sys.query_store_plan          AS p  ON p.query_id = qv.query_variant_query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
ORDER BY qv.parent_query_id, avg_ms DESC;

OPPO is gated by compatibility level 170, which also changes other optimizer behaviour. That is why the upgrade path at the end of this post raises the compatibility level as a separate, reversible step, with Query Store watching for regressions.

Batch mode, columnstore and ordered nonclustered columnstore

Microsoft cites "improvements in batch mode processing and columnstore indexing" in SQL Server 2025, and one customer quoted in the preview announcement reported that an "ordered non-clustered Columnstore index significantly improved query performance by over 63%". The internals are simple: when rowgroups are built in key order, each segment's min and max values cover a narrow range, so a date-range query can skip whole rowgroups instead of decompressing them.

-- SQL Server performance for analytics on OLTP tables: ordered nonclustered columnstore.
-- Ordering rowgroups by OrderDate makes min/max segment metadata tight, so date-range
-- queries skip most rowgroups (segment elimination).
CREATE NONCLUSTERED COLUMNSTORE INDEX ncci_Orders_Analytics
ON dbo.Orders (OrderDate, CustomerId, Amount, Status)
ORDER (OrderDate)
WITH (MAXDOP = 1);                  -- single-threaded build gives the cleanest ordering

-- Measure elimination: compare segment reads before and after.
SET STATISTICS IO ON;
SELECT CustomerId, SUM(Amount) AS revenue
FROM dbo.Orders
WHERE OrderDate >= '2026-09-01' AND OrderDate < '2026-10-01'
GROUP BY CustomerId;
SET STATISTICS IO OFF;
-- Look for "segment reads N, segment skipped M" in the Messages tab.

Treat the 63% as one customer's result, not a promise. The measurement that matters for your SQL Server performance is the ratio of segments skipped to segments read for your own range predicates.

Optimized Halloween protection

Update plans that read and write the same index need Halloween protection so a row is not updated twice, traditionally implemented with a spool that copies rows into tempdb. Microsoft lists "optimized Halloween protection" among the engine redesigns in SQL Server 2025. In practice we look for fewer eager spools in update plans and lower tempdb usage for large modifications after the upgrade.

tempdb: from shared hazard to governed resource

tempdb is shared by every database on the instance, and a single runaway sort, hash or snapshot-heavy report can fill it and stall everything else. SQL Server 2025 adds tempdb space resource governance, which Microsoft says helps "save time and reduce risk". The control sits in Resource Governor: a workload group gets a maximum amount of tempdb data space, and requests that exceed it fail instead of taking the instance down with them.

-- SQL Server 2025 internals: tempdb space resource governance.
-- Cap how much tempdb data space one workload group may consume, so a runaway
-- report cannot fill tempdb and stall the whole instance.
USE master;
GO
CREATE RESOURCE POOL rp_reporting;
GO
CREATE WORKLOAD GROUP wg_reporting
WITH (GROUP_MAX_TEMPDB_DATA_MB = 20480)        -- 20 GB of tempdb data space, illustrative
USING rp_reporting;
GO

-- Classifier: route the reporting login into the governed group.
CREATE OR ALTER FUNCTION dbo.fn_rg_classifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    RETURN CASE WHEN SUSER_SNAME() = N'svc_reporting' THEN N'wg_reporting' ELSE N'default' END;
END;
GO

-- Verification BEFORE: current classifier and groups.
SELECT classifier_function_id, is_enabled FROM sys.resource_governor_configuration;

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_rg_classifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;            -- applies to NEW sessions only
GO

-- Validation AFTER: usage and violations per workload group.
SELECT name,
       tempdb_data_space_kb / 1024           AS tempdb_mb_now,
       peak_tempdb_data_space_kb / 1024      AS tempdb_mb_peak,
       total_tempdb_data_limit_violation_count AS limit_violations
FROM sys.dm_resource_governor_workload_groups;

-- Rollback:
-- ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
-- ALTER RESOURCE GOVERNOR RECONFIGURE;

Size the cap from evidence: watch peak_tempdb_data_space_kb per group for a few weeks before enforcing a limit, and alert on total_tempdb_data_limit_violation_count afterwards. A cap that is too low simply moves the outage from the instance to the report.

High availability in SQL Server 2025: stable plans and reliable failover

Availability is also a SQL Server performance problem. After a failover, a new primary with cold caches and missing statistics can be "up" but too slow to serve traffic. Microsoft highlights "reliable failover enhancements" in SQL Server 2025, and the original announcement calls out one internal change directly: "Persisted statistics on secondary replicas prevent the loss of statistics during a restart or failover, thereby avoiding potential performance degradation."

The background: readable secondaries cannot write to the user database, so statistics the optimizer needs for read workloads were created as temporary statistics and lost on restart or role change. Persisting them means reporting queries on secondaries keep their plans across those events.

Monitoring stays DMV-based. Log send queue and redo queue are the two numbers that bound data loss and failover time, and they belong on every dashboard alongside synchronization state.

-- SQL Server performance and availability health (SQL Server 2025): run on the primary.
SELECT ag.name                                   AS ag_name,
       ar.replica_server_name,
       ars.role_desc,
       ar.availability_mode_desc,                -- SYNCHRONOUS_COMMIT / ASYNCHRONOUS_COMMIT
       ar.failover_mode_desc,
       drs.synchronization_state_desc,
       drs.log_send_queue_size                   AS log_send_queue_kb,   -- data not yet sent
       drs.redo_queue_size                       AS redo_queue_kb,       -- data not yet redone
       drs.last_commit_time
FROM sys.availability_groups               AS ag
JOIN sys.availability_replicas             AS ar  ON ar.group_id   = ag.group_id
JOIN sys.dm_hadr_availability_replica_states AS ars ON ars.replica_id = ar.replica_id
LEFT JOIN sys.dm_hadr_database_replica_states AS drs ON drs.replica_id = ar.replica_id
ORDER BY ag.name, ars.role_desc, ar.replica_server_name;

-- On a readable secondary: statistics the engine created for read workloads.
-- SQL Server 2025 persists these across restart and failover, so plans on the
-- secondary do not fall back to guesses after every role change.
SELECT OBJECT_NAME(s.object_id) AS table_name,
       s.name                   AS stats_name,
       s.is_temporary,
       STATS_DATE(s.object_id, s.stats_id) AS last_updated
FROM sys.stats AS s
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
  AND s.is_temporary = 1
ORDER BY last_updated DESC;

Failover should be rehearsed, not assumed. A planned manual failover to a synchronized secondary loses no data, but it disconnects sessions, so we always run it behind a verification and a confirmation gate:

#!/usr/bin/env bash
# SQL Server performance drill, DISRUPTIVE: planned manual AG failover (no data loss).
# Run against the SYNCHRONOUS secondary that should become primary.
set -euo pipefail
: "${TARGET:?secondary instance}" "${AG:?availability group name}" "${SQLCMDPASSWORD:?}" "${SQLUSER:?}"
SQL=(sqlcmd -S "${TARGET}" -U "${SQLUSER}" -b -h -1 -W)

# --- Verification BEFORE: every database on the target must be SYNCHRONIZED.
NOT_SYNC=$("${SQL[@]}" -Q "SET NOCOUNT ON;
  SELECT COUNT(*) FROM sys.dm_hadr_database_replica_states drs
  JOIN sys.availability_groups ag ON ag.group_id = drs.group_id
  WHERE ag.name = N'${AG}' AND drs.is_local = 1
    AND drs.synchronization_state_desc <> N'SYNCHRONIZED';")
echo "Databases not synchronized on ${TARGET}: ${NOT_SYNC}"
[[ "${NOT_SYNC}" == "0" ]] || { echo "Refusing to fail over."; exit 1; }

read -r -p "Type 'FAILOVER ${AG} TO ${TARGET}' to continue: " ANSWER
[[ "${ANSWER}" == "FAILOVER ${AG} TO ${TARGET}" ]] || { echo "Aborted."; exit 1; }

# --- Action: clients reconnect through the listener.
"${SQL[@]}" -Q "ALTER AVAILABILITY GROUP [${AG}] FAILOVER;"

# --- Validation AFTER: the target should now report PRIMARY.
"${SQL[@]}" -Q "SET NOCOUNT ON;
  SELECT ars.role_desc FROM sys.dm_hadr_availability_replica_states ars
  JOIN sys.availability_groups ag ON ag.group_id = ars.group_id
  WHERE ag.name = N'${AG}' AND ars.is_local = 1;"

Scaling reads and analytics without loading the primary

Two SQL Server 2025 capabilities take analytical load off the OLTP primary entirely. Change event streaming "allows users to consume transaction log changes as events directly from SQL Server to Microsoft Azure Event Hubs", and mirroring to Microsoft Fabric replicates data to OneLake "in near real-time" with what Microsoft calls a zero-ETL experience. Both read from the log rather than running queries, so the cost on the primary is log processing, not scans. For estates spread across sites, Azure Arc adds inventory, AG health tracking and "disaster recovery and read scale-out to Azure SQL".

Scalability for new workloads: vectors, JSON and regex in the engine

SQL Server 2025 adds a native vector store and index "powered by DiskANN, a vector search technology using disk storage to efficiently find similar data points in large datasets", plus native JSON with aggregates and regular expressions. From a SQL Server performance standpoint the value is fewer round trips: similarity search, document handling and pattern matching run next to the data instead of in an application tier that pulls rows over the network.

-- SQL Server 2025 internals for AI workloads: native vector type and similarity search.
CREATE TABLE dbo.ProductDocs
(
    DocId      INT IDENTITY(1,1) CONSTRAINT PK_ProductDocs PRIMARY KEY,
    Title      NVARCHAR(200) NOT NULL,
    Body       NVARCHAR(MAX) NOT NULL,
    Embedding  VECTOR(1536)  NOT NULL      -- fixed-dimension float vector
);

-- Exact k-NN with VECTOR_DISTANCE: fine for small tables or filtered subsets.
DECLARE @q VECTOR(1536) = (SELECT Embedding FROM dbo.ProductDocs WHERE DocId = 42);
SELECT TOP (10)
       DocId,
       Title,
       VECTOR_DISTANCE('cosine', Embedding, @q) AS distance
FROM dbo.ProductDocs
ORDER BY distance;

-- Approximate search at scale uses a DiskANN vector index. Check the feature status
-- for your build (it shipped as preview in SQL Server 2025) before using it in production.
-- CREATE VECTOR INDEX vix_ProductDocs_Embedding
--     ON dbo.ProductDocs (Embedding)
--     WITH (METRIC = 'cosine', TYPE = 'diskann');

-- Regex and native JSON reduce round trips to the application tier.
SELECT DocId, Title
FROM dbo.ProductDocs
WHERE REGEXP_LIKE(Title, N'^(SQL|Azure) ', 'i');

Vector indexes are new territory for capacity planning: memory, build time and recall all need testing on your data. In September 2026 Microsoft announced DiskANN vector indexes as generally available in Azure SQL Database Hyperscale; check the status for your SQL Server build before relying on them.

What SQL Server 2025 internals will not fix

An honest upgrade plan also lists what stays the same. Optimized locking reduces lock footprint and blocking, but it does not shorten a transaction that holds its TID lock for minutes; long transactions still block writers to the same rows and still grow the version store. OPPO addresses optional-parameter patterns, not every form of parameter sensitivity. Columnstore improvements do not replace a missing rowstore index for point lookups. And none of the SQL Server 2025 internals compensates for undersized storage, a saturated log volume or a chatty application issuing thousands of single-row calls.

That is why we treat SQL Server performance work as two tracks running together: take the engine improvements the upgrade offers, and keep fixing the workload with the same measurement discipline as before.

A staged upgrade path that protects SQL Server performance

The fastest way to lose the benefits of SQL Server 2025 internals is to change everything at once. We separate binaries from behaviour: upgrade the engine while keeping the old compatibility level, enable ADR and RCSI, then optimized locking, then compatibility level 170, measuring after each step. Every step has its own rollback, and Query Store makes plan regressions visible and fixable by forcing the previous plan.

1. Baseline. Query Store and wait snapshots, at least two weeks.
2. Upgrade binaries. In place, or as a rolling upgrade through an availability group, keeping the old compatibility level.
3. Enable ADR and RCSI. Test read semantics; watch version store growth.
4. Enable optimized locking. Compare lock waits, lock memory and escalations with the baseline.
5. Raise compatibility level to 170. Unlock OPPO and other optimizer changes; watch Query Store for regressions.
6. Fix regressions. Force known-good plans, then fix the root cause and unforce.

SQL Server performance checklist for SQL Server 2025

For the wider picture, our earlier overview of SQL Server 2025 performance, scalability and high availability and our guide to SQL Server 2025 high availability cover deployment topologies, while this post stays on the engine internals.

How MinervaDB helps with SQL Server performance

MinervaDB is a vendor-neutral database infrastructure company providing SQL Server consulting, 24×7 consultative support and remote DBA services. A SQL Server 2025 engagement starts with measurement: Query Store, wait statistics and lock manager baselines, then a staged upgrade with a rollback at every step. Our response targets are 15 minutes for Severity 1, 12 hours for Severity 2, 24 hours for Severity 3 and 48 hours for Severity 4.

We have supported more than 900 enterprises from 46 cities over 15+ years. Because we sell no licences, we will also tell you when Azure SQL, PostgreSQL or another engine is the better fit for a workload.

Frequently asked questions

What is the latest SQL Server release?

SQL Server 2025, version 17.x, generally available since November 2025. It introduces compatibility level 170.

What is optimized locking in SQL Server 2025?

Optimized locking combines transaction ID (TID) locking and lock after qualification (LAQ) to reduce lock memory and blocking. It relies on accelerated database recovery, and LAQ uses read committed snapshot isolation.

Does SQL Server 2025 fix parameter sniffing?

It reduces a common case. Optional parameter plan optimization lets the optimizer keep different plans depending on whether optional parameters are supplied, under compatibility level 170.

What changed for high availability in SQL Server 2025?

Microsoft cites reliable failover enhancements, and statistics on readable secondary replicas now persist across restarts and failovers, which keeps read workloads stable after a role change.

How large can an Express edition database be in SQL Server 2025?

Microsoft states that SQL Server 2025 Express supports applications up to 50 GB in database size.

All T-SQL and scripts in this post are illustrative and version-pinned to SQL Server 2025 (17.x); thresholds and sizes are examples, not recommendations for your workload. Test every change in a non-production environment first, confirm your exact build and edition, and maintain a robust disaster-recovery posture, including verified backups and rehearsed failover, before applying anything to production.

Planning a SQL Server 2025 upgrade or chasing a SQL Server performance regression? Book a session with a MinervaDB principal architect, or email contact@minervadb.com.