Trace Flag 4199 has been in SQL Server for a while. I’ve long thought of this as the “Bucket of Optimizer Hotfixes” trace flag: enabling it turns on a variety of hotfixes that have been implemented over the years.
A little while back I wrote about Why Indexes Reduce Locks for Update and Delete Queries.
I got a great question on the post from Zac:
What’s not super clear is why it takes out a lock on the whole table, is this because it does a lock escalation as a result of the Full Scan? Will this always happen, or is there a threshold of record update counts where this will occur?
This was tough to answer in just a comment, so I promised a full post on the topic.
I recently got an interesting question from a reader about running a CREATE INDEX statement with DROP_EXISTING
I’ve never claimed to be great at math, but until recently I thought I knew how to count to one. Zero… one. That’s what we learned in kindergarten.
Apparently SQL Server didn’t go to kindergarten.
Over the years, I’ve come across a pattern fairly frequently: an application in an OLTP database periodically creates new tables, loads some data into them and fixes it up, then does a switcheroo and replaces old tables with the new tables.
This can cause major problems with blocking if anyone else is querying the table.
I recently received a question from a vendor about databases created by software vendors.
Short answer: the SQL Server optimizer will know that the table was truncated, but statistics might not update when you expect.
For the long answer, let’s walk through an example using the WideWorldImporters sample database.
Copyright (c) 2025, Catalyze SQL, LLC; all rights reserved. Opinions expressed on this site are solely those of Kendra Little of Catalyze SQL, LLC. Content policy: Short excerpts of blog posts (3 sentences) may be republished, but longer excerpts and artwork cannot be shared without explicit permission.