Improved RCSI Ghost Cleanup in SQL Server 2022
An example of poor update performance under RCSI as compared with locking read committed. Details of an improvement in SQL Server 2022 that can help avoid the worst effects.
Articles
Page 1 of 10
— showing articles
1 to
18
of 179
SQL Server Forced Plans Overwite the Query Hash
A 16-year old SQL Server bug that means 'forced plans' have the query plan hash in place of the expected query hash. Includes an explanation and discussion of the term 'morally equivalent plan'.
Why a Self-Join Requires Halloween Protection
I was asked recently why Halloween Protection was needed for data modification statements that include a self-join of the target table. This gives me a chance to explain, while also covering some interesting product bug history from the SQL Server 7 and 2000 days.
Current State of the ANY Aggregate Transformation
The internal ANY aggregate may be considered by the query optimizer when particular T-SQL syntax is used. This article describes the transformation involved and the status of a bug in its implementation from SQL Server 2008 to 2022 inclusive.
A Small Sample of SQL Server Chaos
Ever wondered what might go wrong if SQL Server allowed NOLOCK on the target of a data-changing operation? Find out courtesy of the weird locking behaviour of the TABLESAMPLE clause.
A Nonclustered Index Update Disaster
Updates to a nonclustered index are often performed as a delete followed by an insert. Most of the time, you won't notice the difference. There are edge cases where the internal operations can have a very dramatic effect on performance.
Don't Mix with Datetime
Mixing datetime with the newer date and time data types can result in unexpected query results. SQL Server 2016 intoduced a breaking change into the way the engine behaves to provide arguably more accurate results. The trouble is, you don't always want this kind of accuracy.
SQL Server Parallel Index Builds
Deep internal technical details of how SQL Server builds an index using multiple cores. Simple offline index builds can use a more efficient plan involving a sort operator running in "multi sort" mode. Other parallel index builds, including online and resumable, use a strategy first released in SQL Server 2000.
Impossible Execution Plan Timings
An execution plan timing puzzle featuring a parallel Sort operator that runs for longer than the root Gather Streams. The explanation covers BPSORT waits and other execution plan internal details.
Setting a Fixed Size for Transaction Log VLFs
Wouldn't it be nice not to have to worry about the virtual log file sizes SQL Server creates? There's an undocumented procedure in SQL Server 2022 to achieve this. Though currently unsupported, maybe this facility will become available in a future version.
Why Batch Mode Sort Spills Are So Slow
A deep dive into why SQL Server batch mode sorts can be so slow when they spill—much slower than the equivalent row mode sort. Includes many other internal details of row and batch mode sorting.
Fast Key Optimization for Row Mode Sorts
Details of an internal optimization SQL Server uses to speed up row mode sorts.
The optimization is only available for a small number of sort key data types.
Importing a File in Batches
An efficient technique to batch bulk load structured data from a file into multiple tables on the server. Allows for server-side identity columns and foreign key relationships involving those identity values.
The Lock Escalation Threshold—Part 3
More on the deep internals of SQL Server lock escalation, including the use of Lock Classes and lock lifetime extension.
The Lock Escalation Threshold—Part 2
How parallelism affects lock escalation.
The Lock Escalation Threshold—Part 1
How and when lock escalation is triggered in SQL Server, clarifying misleading information you may have seen in official documentation and other sources.
Reducing NESTING TRANSACTION FULL Latch Contention
How to reduce contention on the NESTING TRANSACTION FULL latch in SQL Server 2019 using an undocumented trace flag.
Simple Parameterization and Trivial Plans—Part 6
Simplification, trivial plans, parameter safety, and parallelism. Includes a flowchart of the whole simple parameterization process.