Page Free Space

Properly Persisted Computed Columns

Improving execution plans and performance for queries involving computed columns using a new trace flag in SQL Server 2016.

Reordering Concatenation Operator Inputs

It makes sense for the optimizer to consider reordering the inputs to a concatenation when less than the full potential result set is needed. Surprisingly, SQL Server does not do this in general.

SQL Server Temporary Object Caching

Deep dive into SQL Server temporary table and variable caching, including common misconceptions and practices that disable this optimization.

Cardinality Estimation for a Filter on a COUNT Expression

The statistical science and algorithms behind the way SQL Server calculates a cardinality estimate for a filter on a COUNT aggregate.

The Sort that Spills to Level 15,000

SQL Server sometimes produces misleading information about sort operations in execution plans.

The Internals of WITH ENCRYPTION

How WITH ENCRYPTION is implemented and how to reverse it using T-SQL.

Changes to a Writable Partition May Fail Unexpectedly

Why an update may fail when a partition has some data on a read-only filegroup due to "rowset sharing". Includes several workarounds for the issue.

Another Reason to Use NOEXPAND hints even in Enterprise Edition

The SQL Server query optimizer does not use uniqueness information obtained through automatic indexed view matching.

Update Query Analysis and Optimization

Follows the process of optimizing a simple update query and explores some deep internals including "lazy latches" along the way.

Hash Joins on Nullable Columns

Optimized and static bitmap filters, null rejection, and worst-case hash join performance.

Interesting things about INSTEAD OF triggers

Surprising implement­ation details for INSTEAD OF triggers and a cardinality estimation bug in SQL Server 2014 onwards.

Improving the Top Based Pre-2012 Median Solution

Avoiding large memory grants and excessive scalar computations.

Improving the Pre-2012 Row Numbering Median Solution

Analyzing the cause of lower than expected performance and applying a correction.

Locking and Performance

The type and number of locks acquired and released during query execution can have a surprisingly large impact on performance, even if no waiting or blocking occurs.

Calculating the Median with a Dynamic Cursor

A fast way to compute single and grouped medians using a dynamic T-SQL cursor.

Internals of the Seven SQL Server Sorts—Part 2

SQL Server sort internals, including in-memory sorts and two new classes specific to In-Memory OLTP.

Internals of the Seven SQL Server Sorts—Part 1

The first four of the seven SQL Server sorts described in detail.

The Read Uncommitted Isolation Level

The guarantees provided by the weakest of the standard transaction isolation levels. Includes examples of less well-known concurrency effects.