Page Free Space

This article was originally published on 𝕏. Introduction Update execution plans are not something the T-SQL statement writer has much co...

This article was originally published on 𝕏. Introduction Microsoft encourages us not to use the datetime data type: Avoid using date...

Why Batch Mode Sort Spills Are So Slow Batch mode sorting was added to SQL Server in the 2016 release under compatib...

Fast Key Optimization for Row Mode Sorts SQL Server row-mode sorts generally use a custom implementation of the well-kn...

Reducing Contention on the NESTING_TRANSACTION_FULL latch Each additional worker thread in a parallel execution plan ex...

Be Careful with LOBs and OPTION (RECOMPILE) It sometimes makes sense to add OPTION (RECOMPILE) to a query. Typically ...

Closest Match with Sort Rewinds In When Do SQL Server Sorts Rewind? I described how most sorts can only rewind when th...

SQL Server 2019 Aggregate Splitting The SQL Server 2019 query optimizer has a new trick available to improve the perfor...

Batch Mode Bitmap Demos This is a companion post to my main article Batch Mode Bitmaps in SQL Server . This post provid...

SQL Server Temporary Object Caching Creating a table is a relatively resource-intensive and time-consuming operation. T...

Allocation Order Scans When an execution plan includes a scan of a b-tree index structure, the storage engine may be ab...

Improving Partitioned Table Join Performance The query optimizer does not always choose an optimal strategy when joinin...

Optimizer Limitations with Filtered Indexes One of the filtered index use cases mentioned in the product documentation ...

Halloween Protection – The Complete Series I have written a four-part series on the Halloween Problem . Some of you wi...

Why Doesn’t Partition Elimination Work? Given a partitioned table and a simple SELECT query that compares the partitio...

Compute Scalars, Expressions and Execution Plan Performance The humble Compute Scalar is one of the least well-underst...

Deletes that Split Pages and Forwarded Ghosts Can DELETE operations cause pages to split? Yes. It sounds counter-int...

Parallel Execution Plans Suck Summary : A deep dive into SQL Server parallelism, and a potential performance problem wi...

Dynamic Seeks and Hidden Implicit Conversions A LIKE predicate with only a trailing wildcard can usually use an index...

Is Distinct Aggregation Still Considered Harmful? Back in 2008, Marc Friedman of the SQL Server Query Processor Team wr...