{"id":39465,"date":"2025-07-22T10:32:51","date_gmt":"2025-07-22T08:32:51","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=39465"},"modified":"2025-07-22T10:32:53","modified_gmt":"2025-07-22T08:32:53","slug":"sql-server-2025-optimized-locking","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/","title":{"rendered":"SQL Server 2025 &#8211; Optimized Locking"},"content":{"rendered":"\n<p>Within the past few days I was investigating one of the new features of SQL Server 2025: Optimized Locking. I was curious about the capabilities, the behaviour and as well the limits of the feature. Optimized Locking is based on two primary components:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Transaction ID (TID)<\/li>\n\n\n\n<li>Lock after qualification (LAQ)<\/li>\n<\/ul>\n\n\n\n<p>Here we have the transaction ID working as a unique identifier for a transaction. Each row which is modified by this transaction will be labelled with its transaction ID. This produces only one single lock on the TID which is used instead of many key- or RID-locks. To be precise: Update- and Exclusive Locks will be placed, but released immediately without waiting for the transaction to be commited.<br>This behaviour helps lock manager enormously to keep the locks he has to maintain at a minimum and thus saves a lot of space (memory).<\/p>\n\n\n\n<p>Lock after qualification (LAQ) at the other hand provides a silent qualification for rows affected by an update in the background without having the need to place Shared Locks on those while scanning through. Only if a row has been qualified \u2013 means it will be affected by this update \u2013 an attempt to place an Update Lock will happen.<br>In order to benefit from this mechanism, Read Committed Snapshot Isolation (RCSI) must be enabled on database-level.<\/p>\n\n\n\n<p>My Demo environment looks as follows:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SQL Server 2025 CTP 2.0 (17.0.700.9)<\/li>\n\n\n\n<li>SQL Server Management Studio 21 (21.3.6) with Copilot activated<\/li>\n<\/ul>\n\n\n\n<p>My colleague St\u00e9phane Haby wrote blog posts about SQL Server 2025, for example this one:<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-dbi-blog wp-block-embed-dbi-blog\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"lu0QJgO2W0\"><a href=\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-what-news-on-the-instance-configuration\/\">SQL Server 2025: What news on the instance configuration<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; visibility: hidden;\" title=\"&#8220;SQL Server 2025: What news on the instance configuration&#8221; &#8212; dbi Blog\" src=\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-what-news-on-the-instance-configuration\/embed\/#?secret=0VjExyTloF#?secret=lu0QJgO2W0\" data-secret=\"lu0QJgO2W0\" width=\"500\" height=\"282\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n\n\n\n<p><br>In the meantime there was SQL Server 2025 CTP 2.1 released by Microsoft with a few improvements explained on BOL:<br><br><a href=\"https:\/\/learn.microsoft.com\/en-us\/sql\/sql-server\/what-s-new-in-sql-server-2025?view=sql-server-ver17\">https:\/\/learn.microsoft.com\/en-us\/sql\/sql-server\/what-s-new-in-sql-server-2025?view=sql-server-ver17<\/a><\/p>\n\n\n\n<p>Copilot is not part of the game when we discuss Optimized Locking, but as AI is omnipresent these days, I want to mention the blog post of my colleague Steven Naudet where he describes the enabling process for Copilot in SSMS:<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-dbi-blog wp-block-embed-dbi-blog\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"eoi72EJhaq\"><a href=\"https:\/\/www.dbi-services.com\/blog\/step-by-step-guide-to-enabling-copilot-in-ssms\/\">Step-by-Step Guide to Enabling Copilot in SSMS<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; visibility: hidden;\" title=\"&#8220;Step-by-Step Guide to Enabling Copilot in SSMS&#8221; &#8212; dbi Blog\" src=\"https:\/\/www.dbi-services.com\/blog\/step-by-step-guide-to-enabling-copilot-in-ssms\/embed\/#?secret=5w2KdMSHxp#?secret=eoi72EJhaq\" data-secret=\"eoi72EJhaq\" width=\"500\" height=\"282\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n\n\n\n<p>Now, back to business, back to Optimized Locking \u2013 Let\u2019s see this performance tuning feature in action!<\/p>\n\n\n\n<p>First things first \u2013 I&#8217;ve created a new database called \u201cOptimizedLocking\u201d and checked what features are in place. To determine if Optimized Locking is enabled on a database you can us either the function DATABASEPROPERTYEX or grab the information from sys.databases directly:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Query the dedicated DATABASEPROPERTYEX f\u00fcr Optimized Locking\nUSE &#091;OptimizedLocking]\nGO\nSELECT DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn') AS IsOptimizedLockingOn\nGO\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>USE &#091;master]\nGO\nSELECT name AS DatabaseName, is_read_committed_snapshot_on, is_optimized_locking_on, is_accelerated_database_recovery_on \nFROM sys.databases\nWHERE name = 'OptimizedLocking'\nGO\n<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"209\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-17-1024x209.png\" alt=\"\" class=\"wp-image-39467\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-17-1024x209.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-17-300x61.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-17-768x157.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-17.png 1064w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>Why do I request additionally the columns \u201cis_read_committed_snapshot_on\u201d and \u201cis_accelerated_database_recovery_on\u201d?<br>Well, the latter is absolutely necessary to get Optimized Locking work and RCSI enables lock after qualification (LAQ) which I will show you a little bit later.<\/p>\n\n\n\n<p>Now it\u2019s time to create a simple table within the database \u201cOptimizedLocking\u201d:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Create a table to show locks\nUSE &#091;OptimizedLocking]\nGO\nDROP TABLE IF EXISTS T1\nGO\nCREATE TABLE T1\n(\nT1_ID INT NOT NULL,\nValue INT\n);\n\nINSERT INTO T1 (T1_ID, Value) \nVALUES (1,10),(2,20),(3,30);\nGO\n\nSELECT * FROM dbo.T1\nGO\n\nSELECT DB_NAME(database_id) AS DatabaseName, OBJECT_NAME(object_id) AS TableName, index_type_desc FROM sys.dm_db_index_physical_stats(DB_ID('OptimizedLocking'), OBJECT_ID('dbo.T1'), NULL, NULL, 'DETAILED')\nGO\n<\/code><\/pre>\n\n\n\n<p>The content of the table T1 looks as follows \u2013 and it\u2019s of course a Heap because I didn\u2019t neither add a PRIMARY KEY constraint to any of it\u2019s column nor a CLUSTERED INDEX was specified:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"482\" height=\"252\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-18.png\" alt=\"\" class=\"wp-image-39468\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-18.png 482w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-18-300x157.png 300w\" sizes=\"auto, (max-width: 482px) 100vw, 482px\" \/><\/figure>\n\n\n\n<p>I ran two concurrent update statements, the first will change the row with the ID = 1 (in the left pane of SSMS) and the second one tries to change the row with the ID = 2 (in the middle pane). Within the pane on the right I placed the statement to show you the locks which are present.<br>The second statement is blocked, because it can not acquire the necessary Update Lock which is needed to update the row:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"382\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-20-1024x382.png\" alt=\"\" class=\"wp-image-39470\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-20-1024x382.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-20-300x112.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-20-768x286.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-20.png 1065w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>Now I change the database in terms of setting OPTIMIZED_LOCKING to ON and I do the same demo as above again. As mentioned above, ACCELERATED_DATABASE_RECOVERY is necessary as well to fulfil this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>USE &#091;master]\nGO\nALTER DATABASE &#091;OptimizedLocking] SET ACCELERATED_DATABASE_RECOVERY = ON;\nALTER DATABASE &#091;OptimizedLocking] SET OPTIMIZED_LOCKING = ON;\nGO\n<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"114\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-21-1024x114.png\" alt=\"\" class=\"wp-image-39471\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-21-1024x114.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-21-300x33.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-21-768x86.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-21.png 1067w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>As you can see now, the behaviour remains the same, the second statement is blocked trying to place a Shared Lock on the rows \u2013 but we don\u2019t have those 4 \u201cnormal\u201d locks we had before but only 2 XACT Locks instead, means we have a reduction of the amount of locks that have to be managed by the lock manager:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"352\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-22-1024x352.png\" alt=\"\" class=\"wp-image-39472\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-22-1024x352.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-22-300x103.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-22-768x264.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-22.png 1066w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>Let\u2019s go one step further and enable RCSI on database-level and see what happens now:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>USE &#091;master]\nGO\nALTER DATABASE &#091;OptimizedLocking] SET READ_COMMITTED_SNAPSHOT = ON;\nGO\n<\/code><\/pre>\n\n\n\n<p>Et voil\u00e0, both statement have successfully had their Exclusive Lock requests (escalated from the Update Lock placed in advance) approved\/granted:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"351\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-23-1024x351.png\" alt=\"\" class=\"wp-image-39473\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-23-1024x351.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-23-300x103.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-23-768x263.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-23.png 1066w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p><strong>Heap vs. Clustered Index<\/strong><\/p>\n\n\n\n<p>But what happens now, if we use a Clustered Index instead of leaving our data (un)organized as a Heap? Let\u2019s dig into this as well by sorting the T1_ID column using a clustered index:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>USE &#091;OptimizedLocking]\nGO\nCREATE CLUSTERED INDEX CI_T1 ON dbo.T1 (T1_ID)\nGO\n<\/code><\/pre>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"654\" height=\"139\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-24.png\" alt=\"\" class=\"wp-image-39474\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-24.png 654w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-24-300x64.png 300w\" sizes=\"auto, (max-width: 654px) 100vw, 654px\" \/><\/figure>\n\n\n\n<p>All the features that we switched on earlier are now deactivated to be able to begin from scratch again:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"110\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-25-1024x110.png\" alt=\"\" class=\"wp-image-39475\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-25-1024x110.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-25-300x32.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-25-768x83.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-25.png 1067w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>But what\u2019s going on now? None of the former mentioned features is enabled but both statements have their requested locks granted:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"377\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-26-1024x377.png\" alt=\"\" class=\"wp-image-39477\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-26-1024x377.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-26-300x110.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-26-768x282.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-26.png 1066w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p>This is because the potentially critical Exclusive Locks (X) are placed on different keys (i.e. different rows) and the Intent Exclusive Locks (IX) set on page level are compatible with each other.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<p><strong>Wrap-Up<\/strong><\/p>\n\n\n\n<p>During my consulting at customer site I often see database with a huge amount of Heaps.<br>Don\u2019t misunderstand me, Heaps are great on tables, where we expect only INSERTS (e.g. a logging-table) but if we deal with the other DML-Statements as well at the same time, a Clustered Index would be the better choice.<br>Related to this demo and the feature \u201cOptimized Locking\u201d can be said, that if we are using a Clustered Index on our tables, we don\u2019t need this feature to be enabled in terms of concurrency, but regarding the overhead for the lock manager it\u2019s definitely worth using \u201cOptimized Locking\u201d.<\/p>\n\n\n\n<p>But wait: until now we dealt with the default isolation level of SQL Server \u2013 READ_COMMITTED. What will happen if we turn this into a higher level, for example SERIALIZABLE? Will Optimized Locking support us in having fewer locks and blockings?<br>The answer is no &#8211; not at all. Do you agree?<br>And this makes sense regarding the mechanism of such an isolation level like SERIALIZABLE. When we choose such a high isolation level, we are (or at least should be) aware that we are blocking others for a (longer) period of time because we tell SQL Server to behave like this. Everyone who is \u00a0familiar with the locking behaviour in depth in SQL Server using different isolation levels knows, that SQL Server has to change\/align his behaviour to be able to take the ACID principle into account.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<p><strong>Isolation Level SERIALIZABLE<\/strong><\/p>\n\n\n\n<p>Here is the output of the same demo I did above several times except that I executed the first UPDATE statement with the transaction isolation level SERIALIZABLE. As you can see, the second UPDATE statement (in the middle pane) is blocked during the attempt of placing an Exclusive Lock on the row with ID = 2 because the first UPDATE statement had to place an Exclusive Lock on the range where the row with ID = 2 is part of, based on the requirements of the Isolation Level:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"398\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-27-1024x398.png\" alt=\"\" class=\"wp-image-39478\" srcset=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-27-1024x398.png 1024w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-27-300x117.png 300w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-27-768x298.png 768w, https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-27.png 1066w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p><strong>Conclusion<\/strong><\/p>\n\n\n\n<p>From my point of view, \u201cOptimized Locking\u201d is a real game changer. Regardless of the underlying structure (Heap or Clustered Index), locking becomes easier and in a certain way more \u201clightweight\u201d.<\/p>\n\n\n\n<p>As we saw, the optimal way to use this feature is to have turned on Read Committed Snapshot Isolation (RCSI) in addition and \u2013 if this make sense for storing the data in this way in terms of the access pattern \u2013 use a Clustered Index to organize tables. Either way, however, we benefit from the fact that the Lock Manager has to manage fewer locks which saves a significant amount of memory in any case.<\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Within the past few days I was investigating one of the new features of SQL Server 2025: Optimized Locking. I was curious about the capabilities, the behaviour and as well the limits of the feature. Optimized Locking is based on two primary components: Here we have the transaction ID working as a unique identifier for [&hellip;]<\/p>\n","protected":false},"author":138,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[198,368,99],"tags":[644,51],"type_dbi":[],"class_list":["post-39465","post","type-post","status-publish","format-standard","hentry","category-database-management","category-development-performance","category-sql-server","tag-performance-tuning","tag-sql-server"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>SQL Server 2025 - Optimized Locking - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2025 - Optimized Locking\" \/>\n<meta property=\"og:description\" content=\"Within the past few days I was investigating one of the new features of SQL Server 2025: Optimized Locking. I was curious about the capabilities, the behaviour and as well the limits of the feature. Optimized Locking is based on two primary components: Here we have the transaction ID working as a unique identifier for [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2025-07-22T08:32:51+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-07-22T08:32:53+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-17.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1064\" \/>\n\t<meta property=\"og:image:height\" content=\"217\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Roger Sch\u00f6nmann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Roger Sch\u00f6nmann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/\"},\"author\":{\"name\":\"Roger Sch\u00f6nmann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/fd2a9801ad83d36521b3af1428abc2c6\"},\"headline\":\"SQL Server 2025 &#8211; Optimized Locking\",\"datePublished\":\"2025-07-22T08:32:51+00:00\",\"dateModified\":\"2025-07-22T08:32:53+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/\"},\"wordCount\":1253,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-17-1024x209.png\",\"keywords\":[\"Performance Tuning\",\"SQL Server\"],\"articleSection\":[\"Database management\",\"Development &amp; Performance\",\"SQL Server\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/\",\"name\":\"SQL Server 2025 - Optimized Locking - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-17-1024x209.png\",\"datePublished\":\"2025-07-22T08:32:51+00:00\",\"dateModified\":\"2025-07-22T08:32:53+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/fd2a9801ad83d36521b3af1428abc2c6\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-17.png\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-17.png\",\"width\":1064,\"height\":217},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2025 &#8211; Optimized Locking\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/fd2a9801ad83d36521b3af1428abc2c6\",\"name\":\"Roger Sch\u00f6nmann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/c887ebbff56054cbf9c1ca629a82c4821d58f8eaac23147702bbd5254d642fc6?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c887ebbff56054cbf9c1ca629a82c4821d58f8eaac23147702bbd5254d642fc6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c887ebbff56054cbf9c1ca629a82c4821d58f8eaac23147702bbd5254d642fc6?s=96&d=mm&r=g\",\"caption\":\"Roger Sch\u00f6nmann\"},\"honorificPrefix\":\"Mr\",\"gender\":\"male\",\"knowsLanguage\":[\"German\",\"English\",\"French\",\"Italian\"],\"jobTitle\":\"Senior SQL Server Consultant\",\"worksFor\":\"Roger Sch\u00f6nmann\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/rogerschonmann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server 2025 - Optimized Locking - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2025 - Optimized Locking","og_description":"Within the past few days I was investigating one of the new features of SQL Server 2025: Optimized Locking. I was curious about the capabilities, the behaviour and as well the limits of the feature. Optimized Locking is based on two primary components: Here we have the transaction ID working as a unique identifier for [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/","og_site_name":"dbi Blog","article_published_time":"2025-07-22T08:32:51+00:00","article_modified_time":"2025-07-22T08:32:53+00:00","og_image":[{"width":1064,"height":217,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-17.png","type":"image\/png"}],"author":"Roger Sch\u00f6nmann","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Roger Sch\u00f6nmann","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/"},"author":{"name":"Roger Sch\u00f6nmann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/fd2a9801ad83d36521b3af1428abc2c6"},"headline":"SQL Server 2025 &#8211; Optimized Locking","datePublished":"2025-07-22T08:32:51+00:00","dateModified":"2025-07-22T08:32:53+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/"},"wordCount":1253,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-17-1024x209.png","keywords":["Performance Tuning","SQL Server"],"articleSection":["Database management","Development &amp; Performance","SQL Server"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/","name":"SQL Server 2025 - Optimized Locking - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-17-1024x209.png","datePublished":"2025-07-22T08:32:51+00:00","dateModified":"2025-07-22T08:32:53+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/fd2a9801ad83d36521b3af1428abc2c6"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-17.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2025\/07\/image-17.png","width":1064,"height":217},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2025-optimized-locking\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2025 &#8211; Optimized Locking"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/fd2a9801ad83d36521b3af1428abc2c6","name":"Roger Sch\u00f6nmann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/c887ebbff56054cbf9c1ca629a82c4821d58f8eaac23147702bbd5254d642fc6?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/c887ebbff56054cbf9c1ca629a82c4821d58f8eaac23147702bbd5254d642fc6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c887ebbff56054cbf9c1ca629a82c4821d58f8eaac23147702bbd5254d642fc6?s=96&d=mm&r=g","caption":"Roger Sch\u00f6nmann"},"honorificPrefix":"Mr","gender":"male","knowsLanguage":["German","English","French","Italian"],"jobTitle":"Senior SQL Server Consultant","worksFor":"Roger Sch\u00f6nmann","url":"https:\/\/www.dbi-services.com\/blog\/author\/rogerschonmann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/39465","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/138"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=39465"}],"version-history":[{"count":19,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/39465\/revisions"}],"predecessor-version":[{"id":39510,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/39465\/revisions\/39510"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=39465"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=39465"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=39465"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=39465"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}