Empty Parallel Zones in SQL Server Execution Plans
Why you can sometimes see two sequential Parallelism operators in SQL Server execution plans.
Articles
Page 3 of 11
— showing articles
37 to
54
of 188
How Parallel Plans Start Up—Part 5
How additional parallel tasks cooperate to complete plan execution, including returning rows to the client.
How Parallel Plans Start Up—Part 4
Exchange synchronization, CXPACKET waits, and parallel task opening.
How Parallel Plans Start Up—Part 3
Starting the query scan, "early phases" processing, and starting the first parallel tasks.
How Parallel Plans Start Up—Part 2
Iterative execution, invisible plan performance profilers, and how the top-level query scan begins.
How Parallel Plans Start Up—Part 1
The role of the parent task (coordinator) in preparing a plan for parallel execution. Includes initializing each operator and adding hidden profilers to collect runtime performance data such as row count and elapsed time.
Incorrect Results with Parallel Eager Spools and Batch Mode
An incorrect results bug with batch mode hash joins and a parallel eager table spool used for Halloween Protection. Unfixed before SQL Server 2022.
Understanding Execution Plan Operator Timings
How SQL Server actual execution plan operator timings work, and why parallel row mode plan timings can be misleading.
SQL Server batch text hash and the sql_handle
Describes the structure of a sql_handle and explains how the SQL Server batch text hash component is calculated. Provides T-SQL functions to replicate the calculations.
Closest Match with Sort Rewinds
A SQL Server sort operator can replay its results, but only when the sort operates on at most one row. This article employs a sort to avoid a table spool in a query designed to find the closest match to a given value in a second table.
When Do SQL Server Sorts Rewind?
The rare circumstances when a SQL Server sort operator inside a nested loop can rewind (replay) cached results to avoid re-executing child plan operators.
SQL Server 2019 Aggregate Splitting
Exploring a new query optimizer exploration rule in SQL Server 2019 can split large batch mode hash aggregates to avoid or reduce spilling to tempdb.
A bug with Halloween Protection and the OUTPUT Clause
SQL Server has a bug with Halloween Protection when using an OUTPUT clause on a DELETE statement.
How MAXDOP Really Works
A MAXDOP n hint does not limit a SQL Server execution plan to using a maximum of n CPU cores concurrently 🤯
Pulling Group By Above a Join
How and when the SQL Server query optimizer can pull a logical Group By operator above a logical Join.
New Metadata-Only Column Changes in SQL Server 2016
New instant column data type changes possible in SQL Server 2016 when using data compression.
Finding Distinct Values Quickly
A few different ways to retrieve distinct values from a table, including some big "it just runs faster" wins in SQL Server 2019.
The Eager Index Spool and The Optimizer
The SQL Server Eager Index Spool execution plan operator, and the optimizer rules capable of adding it into plans.