Data Modifications under Read Committed Snapshot Isolation
Why and when UPDATE
and DELETE
statements acquire locks under RCSI.
Why and when UPDATE
and DELETE
statements acquire locks under RCSI.
The detailed behaviour of RCSI compared with locking read committed isolation, including subtle aspects to be aware of.
The logical and physical guarantees provided by read committed isolation.
The guarantees provided by the repeatable read isolation level, including some allowed behaviours you might find surprising.
The cardinality estimation formulas for multiple OR
predicates in SQL Server 2014 and later (using the 'default' cardinality estimator).
Guarantees provided by the strongest transaction isolation level and what they mean in practice.
Concurrency considerations and the practical meaning of each component of the ACID acronym in a SQL Server context.
StarJoinInfo
in SQL Server Execution PlansDetails and demos for the StarJoinInfo
structures sometimes seen in SQL Server execution plans associated with an automatic performance optimization.
How selectivity is computed for multiple filters in the original and default SQL Server cardinality estimation models.
Multiple reasons to prefer using the NOEXPAND
hint on indexed views, even on Enterprise Edition.
How SQL Server row mode parallel execution plans work.
SQL Server nested loops join internals including ordered and unordered delayed prefetching and undocumented trace flags.
RECOMPILE
OptionsInternal details of SQL Server parameter sniffing, embedding (PEO), and parameterization.
A SQL Server incorrect results bug with merge join, partitioning, and a descending index.
A detailed analysis of a SQL Server optimizer bug that causes incorrect results when a descending non-unique index exists on a partitioned table.
Join and group by clauses may need to be written in a specific way to get the best results from the SQL Server query optimizer when using partitioning.
Improving performance for SQL Server MIN
and MAX
queries on partitioned tables and working around query optimizer limitations.
SQL Server cardinality estimation bugs with partial aggregates and union all concatenation. Merge and concat union details.