Introducing optimized locking v2
Executive summary New improvements in optimized locking further reduce the overhead of locks in the MSSQL database engine. For many transactions, row and page locks aren’t taken at all while still retaining ACID transaction semantics. The improvements require no changes in customer workloads or configuration and are a part of optimized locking in SQL Server 2025, Azure SQL Database, SQL database in Fabric, and Azure SQL Managed Instance. Optimized locking recap Optimized locking was originally released in Azure SQL Database in 2023. The feature has two components: Transaction ID (TID) locking Lock After Qualification (LAQ) With TID locking, while individual row and page locks are still taken, they are not held until the end of transaction because the transaction remains protected by a single TID lock. Concurrency is improved because lock escalation is much less likely to occur. Memory usage is also improved because locks are released soon after they are acquired. With LAQ, locks are taken just before a qualified row is modified. This optimistic locking strategy improves concurrency because locks aren’t held while qualifying rows for modification. At Infios, we are very excited about several new features in SQL Server 2025 and the vast amount of opportunities for performance improvements. We are most excited about the optimized locking feature and how that can drastically help reduce locking across our customers and all their unique workloads. - Tim Radney, SaaS Operations Manager at Infios and a Data Platform MVP Improvements In recent months, we have made two improvements that make a database more lock-free and make the benefits of optimized locking available to more workloads: Skip index locks (SIL) Query plan LAQ feedback persistence These improvements are in effect if read committed snapshot isolation (RCSI) is enabled for the database, which is the default in Azure SQL Database and SQL database in Fabric. To gain the most benefit from optimized locking, enable RCSI. Skip index locks With TID locking, lock escalation typically doesn’t occur because locks are released fast enough to avoid crossing the escalation threshold. A large transaction that modifies many rows without lock escalation must acquire and release many locks. These locks don’t consume a lot of memory because they are released quickly. However, each lock acquisition and release requires a call into the Lock Manager component within the engine. Cumulatively for a large transaction, the overhead of these calls might be significant. The SIL improvement reduces this overhead by skipping row and page locks when they are not required to guarantee ACID semantics. When transactions are protected by TID locks, and when RCSI and LAQ are used, an exclusive lock on a modified row is only necessary if there might be other queries accessing the row and expecting it to be stable. Examples of such queries are those that use the Repeatable Read or Serializable isolation levels, or the corresponding locking hints. We call such queries Row Locking Queries, or RLQ. In the SIL improvement, the engine was modified to maintain a special No-RLQ bit for each page in the buffer pool. This bit indicates the absence of RLQ queries accessing any row on the page. When this bit is set, a DML transaction can skip taking row and page locks and instead modify a row with just an exclusive page latch. Based on the Azure SQL Database telemetry, RLQ queries are uncommon in workloads using RCSI or snapshot isolation transactions. Telemetry also tells us that at least half of all DML queries, on average, use LAQ. This indicates that many workloads can benefit from the SIL improvement. Query plan LAQ feedback persistence LAQ uses an optimistic locking strategy. Locks are taken only after a row is qualified for modification, not while qualifying the row. This means that statement processing might need to be internally restarted if another transaction modified the row after it was read but before it was modified by the current transaction. If statement restarts happen frequently, the use of LAQ might introduce a noticeable overhead because the work to process the DML statement needs to be redone. The engine tracks the potentially wasted work and statement restarts. In the original version of optimized locking, this data is aggregated across all statements and tracked in memory at the database level. If the potentially wasted work or statement restarts exceed thresholds, LAQ is disabled for the entire database, and re-enabled if the potentially wasted work and restarts go below the thresholds. With the new improvement, in addition to tracking at the database level, the engine also tracks LAQ feedback at the query plan level and persists it in Query Store. If thresholds are exceeded for a particular query plan, LAQ is disabled only for that plan rather than for the entire database. The rest of the workload still benefits from LAQ. Skipping locks – a demo To demonstrate the SIL improvement, we used SQL Server 2025 to run a workload that executes an UPDATE statement 100 times in a loop. Each statement updates a random number of rows in a table with a clustered index. We used an extended events session with a histogram target to collect the lock_acquired event. Each histogram bucket counts the number of acquired locks for a specific resource type, including KEY and PAGE. Here are the results: For this execution of the demo, 99.6% of key locks and 79% of page locks are skipped. For details, see the Appendix later in this post. Improvement at the Azure scale To zoom out and demonstrate the SIL improvement broadly, the next chart summarizes telemetry data for about 25K databases in Azure SQL Database. The chart shows the ratio of the total skipped to total acquired locks by index type. Broadly, the improvement is most pronounced for nonclustered indexes, where 81% of locks are skipped. Conclusion Improvements in optimized locking show our continuing investment in this core database engine feature. We fine-tuned optimized locking beyond an already successful v1 release and further reduced the overhead of locks. For more information about these two improvements, see Skip index locks and LAQ heuristics. As of this writing, the improvements are enabled in SQL Server 2025 and for most databases in Azure SQL Database and SQL database in Fabric. The rollout to the remainder of the Azure SQL fleet including Azure SQL Managed Instance (with the always-up-to-date and SQL Server 2025 update policy) is in progress. Appendix Here are the T-SQL scripts to demonstrate SIL in action. Demo setup: /* /* Reset the demo */ USE tempdb; ALTER DATABASE olv2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE; DROP DATABASE IF EXISTS olv2; */ CREATE DATABASE olv2; GO USE olv2; /* Enable RCSI, ADR, and optimized locking */ ALTER DATABASE olv2 SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE; ALTER DATABASE olv2 SET ACCELERATED_DATABASE_RECOVERY = ON WITH ROLLBACK IMMEDIATE; ALTER DATABASE olv2 SET OPTIMIZED_LOCKING = ON WITH ROLLBACK IMMEDIATE; /* Create a sequence to generate clustered index keys */ CREATE SEQUENCE dbo.s_id AS int START WITH 1 INCREMENT BY 1; /* Create a test table */ CREATE TABLE dbo.t ( id int NOT NULL CONSTRAINT df_t_id DEFAULT (NEXT VALUE FOR dbo.s_id), dt datetime2 NOT NULL CONSTRAINT df_t_dt DEFAULT (SYSDATETIME()), u uniqueidentifier NOT NULL CONSTRAINT df_t_uid DEFAULT (NEWID()), s nchar(40) NOT NULL CONSTRAINT df_t_s DEFAULT (REPLICATE('c', 1 + 39 * RAND())), CONSTRAINT pk_t PRIMARY KEY (id) ); /* Insert 50,000 rows */ INSERT INTO dbo.t (s) SELECT REPLICATE('olv2', 10) AS s FROM GENERATE_SERIES(1, 50000); GO /* Create a stored procedure that updates a random number of rows */ CREATE OR ALTER PROCEDURE dbo.update_rows AS SET NOCOUNT, XACT_ABORT ON; /* Get the maximum key value for the clustered index */ DECLARE @MaxKey int = ( SELECT CAST(current_value AS int) FROM sys.sequences WHERE name = 's_id' AND SCHEMA_NAME(schema_id) = 'dbo' ); /* Get a random key value within the key range */ DECLARE @StartKey int = 1 + RAND(CAST(CAST(NEWID() AS varbinary(3)) AS int)) * @MaxKey; /* Get a random number of rows between 1 and 500 */ DECLARE @RowCount int = 1 + RAND() * 499; /* Update rows */ UPDATE TOP (@RowCount) dbo.t SET dt = DEFAULT, u = DEFAULT, s = DEFAULT WHERE id >= @StartKey; GO /* Create an event session to collect lock statistics */ IF EXISTS ( SELECT 1 FROM sys.server_event_sessions WHERE name = N'olv2_locks' ) DROP EVENT SESSION olv2_locks ON SERVER; CREATE EVENT SESSION olv2_locks ON SERVER ADD EVENT sqlserver.lock_acquired ( SET collect_database_name = 1 WHERE database_name = N'olv2' ) ADD TARGET package0.histogram ( SET filtering_event_name = N'sqlserver.lock_acquired', source = N'resource_type', source_type = 0 ); GO Execute the workload and collect a histogram of locks by resource type: USE olv2; ALTER EVENT SESSION olv2_locks ON SERVER STATE = START; GO EXEC dbo.update_rows; GO 100 WITH histogram_target AS ( SELECT TRY_CAST(st.target_data AS xml) AS target_data FROM sys.dm_xe_sessions AS s INNER JOIN sys.dm_xe_session_targets AS st ON s.address = st.event_session_address WHERE s.name = 'olv2_locks' ), lock_type_histogram AS ( SELECT hb.slot.value('(@count)[1]', 'bigint') AS slot_count, hb.slot.value('(value/text())[1]', 'int') AS lock_type FROM histogram_target AS ht CROSS APPLY ht.target_data.nodes('/HistogramTarget/Slot') AS hb(slot) ) SELECT mv.map_value AS lock_type_desc, lth.slot_count AS lock_acquired_count FROM lock_type_histogram AS lth INNER JOIN sys.dm_xe_map_values AS mv ON lth.lock_type = mv.map_key WHERE mv.name = 'lock_resource_type' ORDER BY lock_type_desc; ALTER EVENT SESSION olv2_locks ON SERVER STATE = STOP; GO Each workload run can produce slightly different results because other activities (query compilation, file growth, page splits, etc.) also take locks if they occur during the run. However, in this demo, the number of key locks with SIL enabled is always reduced. For comparison purposes, we used an undocumented and unsupported trace flag 7194 to disable SIL. To reenable SIL, disable this trace flag and then rerun ALTER DATABASE … SET OPTIMIZED_LOCKING = ON statement, or restart the database.SQL Server 2025: introducing optimized Halloween protection
Executive summary Optimized Halloween protection, available in the public preview of SQL Server 2025 starting with the CTP 2.0 release, reduces tempdb space consumption and improves query performance by redesigning the way the database engine solves the Halloween problem. An example in the appendix shows CPU and elapsed time of a query reduced by about 50% while eliminating all tempdb space consumption. Update 2025-09-02 During public preview of SQL Server 2025, we identified a potential data integrity issue that might occur if optimized Halloween protection is enabled. While the probability of encountering this issue is low, we take data integrity seriously. Therefore, we temporarily removed optimized Halloween protection from SQL Server 2025, starting with the RC 0 release. The fix for this issue is in progress. In the coming months, we plan to make optimized Halloween protection available in Azure SQL Database and Azure SQL Managed Instance with the always-up-to-date update policy. Enabling optimized Halloween protection in a future SQL Server 2025 update is under consideration as well. The Halloween problem The Halloween problem, named so because it was discovered on Halloween in 1976, occurs when a data modification language (DML) statement changes data in such a way that the same statement unexpectedly processes the same row more than once. Traditionally, the SQL Server database engine protects DML statements from the Halloween problem by introducing a spool operator in the query plan, or by taking advantage of another blocking operator already present in the plan, such as a sort or a hash match. If a spool operator is used, it creates a temporary copy of the data to be modified before any modifications are made to the data in the table. While the protection spool avoids the Halloween problem, it comes with downsides: The spool requires extra resources: space in tempdb, disk I/O, memory, and CPU. Statement processing by the downstream query operators is blocked until the data is fully written into the spool. The spool adds query plan complexity that can cause the query optimizer to generate a less optimal plan. Optimized Halloween protection removes these downsides by making the spool operator unnecessary. How it works When accelerated database recovery (ADR) is enabled, each statement in a transaction obtains a unique statement identifier, known as nest ID. As each row is modified by a DML statement, it is stamped with the nest ID of the statement. This is required to provide the ACID transaction semantics with ADR. During DML statement processing, when the storage engine reads the data, it skips any row that has the same nest ID as the current DML statement. This means that the query processor doesn't see the rows already processed by the statement, therefore avoiding the Halloween problem. How to use optimized Halloween protection To enable optimized Halloween protection for a database, the following prerequisites are required: ADR must be enabled on the database. The database must use compatibility level 170. The OPTIMIZED_HALLOWEEN_PROTECTION database-scoped configuration must be enabled. The OPTIMIZED_HALLOWEEN_PROTECTION database-scoped configuration is enabled by default. This means that when you enable ADR for a database using compatibility level 170, it will use optimized Halloween protection. You can ensure that a database uses optimized Halloween protection by executing the following statements: ALTER DATABASE [<database-name-placeholder>] SET ACCELERATED_DATABASE_RECOVERY = ON WITH ROLLBACK IMMEDIATE; ALTER DATABASE [<database-name-placeholder>] SET COMPATIBILITY_LEVEL = 170; ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_HALLOWEEN_PROTECTION = ON; You can also enable and disable optimized Halloween protection at the query level by using the ENABLE_OPTIMIZED_HALLOWEEN_PROTECTION and DISABLE_OPTIMIZED_HALLOWEEN_PROTECTION query hints, either directly in the query, or via Query Store hints. These hints work under any compatibility level and take precedence over the OPTIMIZED_HALLOWEEN_PROTECTION database-scoped configuration. When optimized Halloween protection is used for an operator in the query plan, the OptimizedHalloweenProtectionUsed property of the operator in the XML query plan is set to True. For more details, see optimized Halloween protection in documentation. Conclusion Optimized Halloween protection is another Intelligent Query Processing feature that improves query performance and reduces resource consumption when you upgrade to SQL Server 2025, without having to make any changes to your query workloads. We are looking forward to your feedback about this and other features during the public preview of SQL Server 2025 and beyond. You can leave comments on this blog post, email us at intelligentqp@microsoft.com, or leave feedback at https://aka.ms/sqlfeedback. Appendix The following script shows how optimized Halloween protection removes the protection spool in the query plan, and reduces tempdb usage, CPU time, and duration when enabled. /* Requires the WideWorldImporters sample database. SQL Server backup: https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak Bacpac: https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Standard.bacpac */ /* Ensure that optimized Halloween protection prerequisites are in place */ ALTER DATABASE WideWorldImporters SET ACCELERATED_DATABASE_RECOVERY = ON WITH ROLLBACK IMMEDIATE; ALTER DATABASE WideWorldImporters SET COMPATIBILITY_LEVEL = 170; ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_HALLOWEEN_PROTECTION = ON; GO /* Validate configuration */ SELECT d.compatibility_level, d.is_accelerated_database_recovery_on, dsc.name, dsc.value FROM sys.database_scoped_configurations AS dsc CROSS JOIN sys.databases AS d WHERE dsc.name = 'OPTIMIZED_HALLOWEEN_PROTECTION' AND d.name = DB_NAME(); GO /* Create the test table and add data */ DROP TABLE IF EXISTS dbo.OptimizedHPDemo; BEGIN TRANSACTION; SELECT * INTO dbo.OptimizedHPDemo FROM Sales.Invoices ALTER TABLE dbo.OptimizedHPDemo ADD CONSTRAINT PK_OptimizedHPDemo PRIMARY KEY CLUSTERED (InvoiceID) ON USERDATA; COMMIT; GO /* Ensure that Query Store is enabled and is capturing all queries */ ALTER DATABASE WideWorldImporters SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL); /* Empty Query Store to start with a clean slate */ ALTER DATABASE WideWorldImporters SET QUERY_STORE CLEAR; GO /* Disable optimized Halloween protection as the baseline */ ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_HALLOWEEN_PROTECTION = OFF; GO /* Insert data selecting from the same table. This requires Halloween protection so that the same row cannot be selected and inserted repeatedly. */ BEGIN TRANSACTION; INSERT INTO dbo.OptimizedHPDemo ( InvoiceID, CustomerID, BillToCustomerID, OrderID, DeliveryMethodID, ContactPersonID, AccountsPersonID, SalespersonPersonID, PackedByPersonID, InvoiceDate, CustomerPurchaseOrderNumber, IsCreditNote, CreditNoteReason, Comments, DeliveryInstructions, InternalComments, TotalDryItems, TotalChillerItems, DeliveryRun, RunPosition, ReturnedDeliveryData, ConfirmedDeliveryTime, ConfirmedReceivedBy, LastEditedBy, LastEditedWhen ) SELECT InvoiceID + 1000000 AS InvoiceID, CustomerID, BillToCustomerID, OrderID, DeliveryMethodID, ContactPersonID, AccountsPersonID, SalespersonPersonID, PackedByPersonID, InvoiceDate, CustomerPurchaseOrderNumber, IsCreditNote, CreditNoteReason, Comments, DeliveryInstructions, InternalComments, TotalDryItems, TotalChillerItems, DeliveryRun, RunPosition, ReturnedDeliveryData, ConfirmedDeliveryTime, ConfirmedReceivedBy, LastEditedBy, LastEditedWhen FROM dbo.OptimizedHPDemo; ROLLBACK; GO /* Enable optimized Halloween protection. Execute the following statement in its own batch. */ ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_HALLOWEEN_PROTECTION = ON; GO /* Execute the same query again */ BEGIN TRANSACTION; INSERT INTO dbo.OptimizedHPDemo ( InvoiceID, CustomerID, BillToCustomerID, OrderID, DeliveryMethodID, ContactPersonID, AccountsPersonID, SalespersonPersonID, PackedByPersonID, InvoiceDate, CustomerPurchaseOrderNumber, IsCreditNote, CreditNoteReason, Comments, DeliveryInstructions, InternalComments, TotalDryItems, TotalChillerItems, DeliveryRun, RunPosition, ReturnedDeliveryData, ConfirmedDeliveryTime, ConfirmedReceivedBy, LastEditedBy, LastEditedWhen ) SELECT InvoiceID + 1000000 AS InvoiceID, CustomerID, BillToCustomerID, OrderID, DeliveryMethodID, ContactPersonID, AccountsPersonID, SalespersonPersonID, PackedByPersonID, InvoiceDate, CustomerPurchaseOrderNumber, IsCreditNote, CreditNoteReason, Comments, DeliveryInstructions, InternalComments, TotalDryItems, TotalChillerItems, DeliveryRun, RunPosition, ReturnedDeliveryData, ConfirmedDeliveryTime, ConfirmedReceivedBy, LastEditedBy, LastEditedWhen FROM dbo.OptimizedHPDemo; ROLLBACK; GO /* Examine query runtime statistics and plans for the two executions of the same query. */ SELECT q.query_id, q.query_hash, qt.query_sql_text, p.plan_id, rs.count_executions, rs.avg_tempdb_space_used * 8 / 1024. AS tempdb_space_mb, FORMAT(rs.avg_cpu_time / 1000., 'N0') AS avg_cpu_time_ms, FORMAT(rs.avg_duration / 1000., 'N0') AS avg_duration_ms, TRY_CAST(p.query_plan AS xml) AS xml_query_plan FROM sys.query_store_runtime_stats AS rs INNER JOIN sys.query_store_plan AS p ON rs.plan_id = p.plan_id INNER JOIN sys.query_store_query AS q ON p.query_id = q.query_id INNER JOIN sys.query_store_query_text AS qt ON q.query_text_id = qt.query_text_id WHERE q.query_hash = 0xC6ADB023512BBCCC; /* For the second execution with optimized Halloween protection: 1. tempdb space usage is zero 2. CPU time and duration are reduced by about 50% 3. The Clustered Index Insert operator in the query plan has the OptimizedHalloweenProtection property set to True */SQL Server 2025: introducing tempdb space resource governance
An old problem Since the early days of SQL Server, DBAs had to contend with a common problem – running out of space in the tempdb database. It has always struck me as odd that all I need to cause an outage on an SQL Server instance is access to the server where I can create a temp table that fills up tempdb, and there is no permission to stop me. - Erland Sommarskog (website), an independent SQL Server consultant and a Data Platform MVP Because tempdb is used for a multitude of purposes, the problem can occur without any explicit user action such as creating a temporary table. For example, executing a reporting query that spills data to tempdb and fills it up can cause an outage for all workloads using that SQL Server instance. Over the years, many DBAs developed custom solutions that monitor tempdb space and take action, for example kill sessions that consume a large amount of tempdb space. But that comes with extra effort and complexity. I have spent more hours in my career than I can count building solutions to manage TempDB space. Even with immense time and effort, there were still quirks and caveats that came up that created challenges - especially in multi-tenant environments with lots of databases and the noisy-neighbor problem. - Edward Pollack (LinkedIn), Data Architect at Transfinder and a Data Platform MVP A new solution in the SQL Server engine SQL Server 2025 brings a new solution for this old problem, built directly into the database engine. Starting with the CTP 2.0 release, you can use resource governor, a feature available since SQL Server 2008, to enforce limits on tempdb space consumption. We rely on Resource Governor to isolate workloads on our SQL Server instances by controlling CPU and memory usage. It helps us ensure that the core of our trading systems remains stable and runs with predictable performance, even when other parts of the systems share the same servers. - Ola Hallengren (website), Chief Data Platforms Engineer at Saxo Bank and a Data Platform MVP Similarly, if you have multiple workloads running on your server, each workload can have its own tempdb limit, lower than the maximum available tempdb space. This way, even if one workload hits its limit, other workloads continue running. Here's an example that limits the total tempdb space consumption by queries in the default workload group to 17 GB, using just two T-SQL statements: ALTER WORKLOAD GROUP [default] WITH (GROUP_MAX_TEMPDB_DATA_MB = 17408); ALTER RESOURCE GOVERNOR RECONFIGURE; The default group is used for all queries that aren’t classified into another workload group. You can create workload groups for specific applications, users, etc. and set limits for each group. When a query attempts to increase tempdb space consumption beyond the workload group limit, it is aborted with error 1138, severity 17, Could not allocate a new page for database 'tempdb' because that would exceed the limit set for workload group 'workload-group-name'. All other queries on the server continue to execute. Setting the limits You might be asking, “How do I know the right limits for the different workloads on my servers?” No need to guess. Tempdb space usage is tracked for each workload group at all times and reported in the sys.dm_resource_governor_workload_groups DMV. Usage is tracked even if no limits are set for the workload groups. You can establish representative usage patterns for each workload over time, then set the right limits. You can reconfigure the limits over time if workload patterns change. For example, the following query lets you see the current tempdb space usage, peak usage, and the number of times queries were aborted because they would otherwise exceed the limit per workload group: SELECT group_id, name, tempdb_data_space_kb, peak_tempdb_data_space_kb, total_tempdb_data_limit_violation_count FROM sys.dm_resource_governor_workload_groups; Peak usage and the number of query aborts (limit violations) are tracked since server restart. You can reset these and other resource governor statistics to restart tracking at any time and without restarting the server by executing ALTER RESOURCE GOVERNOR RESET STATISTICS; What about the transaction log? The limits you set for each workload group apply to space in the tempdb data files. But what about the tempdb transaction log? Couldn’t a large transaction fill up the log and cause an outage? This is where another feature in SQL Server 2025 comes in. You can now enable accelerated database recovery (ADR) in tempdb to get the benefit of aggressive log truncation, and drastically reduce the possibility of running out of log space in tempdb. For more information, see ADR improvements in SQL Server 2025. Learn more For more information about tempdb space resource governance, including examples, best practices, and the details of how it works, see Tempdb space resource governance in documentation. If you haven’t used resource governor in SQL Server before, here’s a good starting point: Tutorial: Resource governor configuration examples and best practices. Conclusion SQL Server 2025 brings a new, built-in solution for the age-old problem of tempdb space management. You can now use resource governor to set limits on tempdb usage and avoid server-wide outages because tempdb ran out of space. We are looking forward to your feedback on this and other SQL Server features during the public preview of SQL Server 2025 and beyond. You can leave comments on this blog post, email us at sql-rg-feedback@microsoft.com, or leave feedback at https://aka.ms/sqlfeedback.Clustered Columnstore Index: Enforcing uniqueness constraint in table
First published on MSDN on Sep 14, 2015 SQL Server introduced ‘updateable’ clustered columnstore index (CCI) starting with SQL Server 2014 to speed up analytics by orders of magnitude up to 100x while significantly reducing storage, typically 10x.
