- A batch mode operator reports CPU and elapsed times for that operator alone
- A row mode operator reports times for itself and all its children
About This Blog
Friday 31 May 2024
Impossible Execution Plan Timings
Monday 13 November 2023
Why Batch Mode Sort Spills Are So Slow
Batch mode sorting was added to SQL Server in the 2016 release under compatibility level 130. Most of the time, a batch mode sort will be much faster than the row mode equivalent.
This post is about an important exception to this rule, as recently reported by Erik Darling (video).
No doubt you’ll visit both links before reading on, but to summarize, the issue is that batch mode sorts are very slow when they spill—much slower than an equivalent row mode sort.
This also seems like a good opportunity to write down some sorting details I haven’t really covered before. If you’re not interested in those details and background to the current issue, you can skip down to the section titled, “Erik’s Demo”.
Friday 20 October 2023
Fast Key Optimization for Row Mode Sorts
SQL Server row-mode sorts generally use a custom implementation of the well-known merge sort algorithm to order data.
As a comparison-based algorithm, this performs a large number of value comparisons during sorting—usually many more than the number of items to sort.
Although each comparison is typically not expensive, even moderately sized sorting can involve a very large number of comparisons.
SQL Server can be called upon to sort a variety of data types. To facilitate this, the sorting code normally calls out to a specific comparator to determine how two compared values should sort: lower, higher, or equal.
Although calling comparator code has low overhead, performing enough of them can cause noticeable performance differences.
To address this, SQL Server has always (since at least version 7) supported a fast key optimization for simple data types. This optimization performs the comparison using highly optimized inline code rather than calling out to a separate routine.
Saturday 23 July 2022
More Consistent Execution Plan Timings in SQL Server 2022
The updated showplan schema shipped with SSMS 19 preview 2 contains an interesting comment:
ExclusiveProfileTimeActive: true if the actual elapsed time (ActualElapsedms attribute) and the actual CPU time (ActualCPUms attribute) represent the time interval spent exclusively within the relational iterator.
What does this mean?
Tuesday 4 August 2020
SQL Server 2019 Aggregate Splitting
The SQL Server 2019 query optimizer has a new trick available to improve the performance of large aggregations. The new exploration abilities are encoded in two new closely-related optimizer rules:
GbAggSplitToRanges
SelOnGbAggSplitToRanges
The extended event query_optimizer_batch_mode_agg_split
is provided to track when this new optimization is considered. The description of this event is:
Occurs when the query optimizer detects batch mode aggregation is likely to spill and tries to split it into multiple smaller aggregations.
Other than that, this new feature hasn’t been documented yet. This article is intended to help fill that gap.
Sunday 26 July 2020
A bug with Halloween Protection and the OUTPUT Clause
Background
The OUTPUT
clause can be used to return results from an INSERT
, UPDATE
, DELETE
, or MERGE
statement. The data can be returned to the client, inserted to a table, or both.
There are two ways to add OUTPUT
data to a table:
- Using
OUTPUT INTO
- With an outer
INSERT
statement.
For example:
-- Test table
DECLARE @Target table
(
id integer IDENTITY (1, 1) NOT NULL,
c1 integer NULL
);
-- Holds rows from the OUTPUT clause
DECLARE @Output table
(
id integer NOT NULL,
c1 integer NULL
);
Sunday 5 July 2020
How MAXDOP Really Works
A few days ago I ran a Twitter poll:
The most popular answer gets highlighted by Twitter at the end of the poll, but as with many things on social media, that doesn’t mean it is correct:
Saturday 31 August 2013
Nested Loops Prefetching
Nested loops join query plans can be a lot more interesting (and complicated) than is commonly realized.
One query plan area I get asked about a lot is prefetching. It is not documented in full detail anywhere, so this seems like a good topic to address in a blog post.
The examples used in this article are based on questions asked by Adam Machanic.
Wednesday 24 July 2013
Two Partitioning Peculiarities
Table partitioning in SQL Server is essentially a way of making multiple physical tables (row sets) look like a single table. This abstraction is performed entirely by the query processor, a design that makes things simpler for users, but which makes complex demands of the query optimizer.
This post looks at two examples which exceed the optimizer’s abilities in SQL Server 2008 onward.
Wednesday 20 March 2013
The Problem with Window Functions and Views
Introduction
Since their introduction in SQL Server 2005, window functions like ROW_NUMBER
and RANK
have proven to be extremely useful in solving a wide variety of common T-SQL problems. In an attempt to generalize such solutions, database designers often look to incorporate them into views to promote code encapsulation and reuse.
Unfortunately, a limitation in the SQL Server query optimizer often means that views1 containing window functions do not perform as well as expected. This post works through an illustrative example of the problem, details the reasons, and provides a number of workarounds.
Note: The limitation described here was first fixed in SQL Server 2017 CU 30. Optimizer fixes must be enabled using trace flag 4199 or the database scoped configuration option. The fix is standard behaviour without optimizer hotfixes under compatibility level 160 (SQL Server 2022).
Friday 8 March 2013
Execution Plan Analysis: The Mystery Work Table
I love SQL Server execution plans. It is often easy to spot the cause of a performance problem just by looking at one closely. That task is considerably easier if the plan includes run-time information (a so-called ‘actual’ execution plan), but even a compiled plan can be very useful.
Nevertheless, there are still times when the execution plan does not tell the whole story, and we need to think more deeply about query execution to really understand a problem. This post looks at one such example, based on a question I answered.
Thursday 21 February 2013
Halloween Protection – The Complete Series
I have written a four-part series on the Halloween Problem.
Some of you will never have heard about this issue. Those that have might associate it only with T-SQL UPDATE
queries. In fact, the Halloween Problem affects execution plans for INSERT, UPDATE
, DELETE
and MERGE
statements.
This is a topic I have been meaning to write about properly for years, ever since I read Craig Freedman’s 2008 blog post on the topic, which ended with the cryptic comment:
“…although I’ve used update statements for all of the examples in this post, some insert and delete statements also require Halloween protection, but I’ll save that topic for a future post.”
That future post never materialized, so I thought I would have a go. The four parts of the series are summarized and linked below, I hope you find the material interesting.
Wednesday 20 February 2013
The Halloween Problem – Part 4
The Halloween Problem can have a number of important effects on execution plans. In this final part of the series, we look at the tricks the optimizer can employ to avoid the Halloween Problem when compiling plans for queries that add, change or delete data.
Monday 18 February 2013
The Halloween Problem – Part 3
The MERGE
statement (introduced in SQL Server 2008) allows us to perform a mixture of INSERT
, UPDATE
, and DELETE
operations using a single statement.
The Halloween Protection issues for MERGE
are mostly a combination of the requirements of the individual operations, but there are some important differences and a couple of interesting optimizations that apply only to MERGE
.
Friday 15 February 2013
The Halloween Problem – Part 2
In the first part of this series, we saw how the Halloween Problem applies to UPDATE
queries. To recap briefly, the problem was that an index used to locate records to update had its keys modified by the update operation itself (another good reason to use included columns in an index rather than extending the keys). The query optimizer introduced an Eager Table Spool operator to separate the reading and writing sides of the execution plan to avoid the problem. In this post, we will see how the same underlying issue can affect INSERT
and DELETE
statements.
Wednesday 13 February 2013
The Halloween Problem – Part 1
Much has been written over the years about understanding and optimizing SELECT
queries, but rather less about data modification. This series looks at an issue that is specific to INSERT
, UPDATE
, DELETE
and MERGE
queries – the Halloween Problem.
The phrase “Halloween Problem” was originally coined with reference to a SQL UPDATE
query that was supposed to give a 10% raise to every employee who earned less than $25,000. The problem was that the query kept giving 10% raises until everyone earned at least $25,000.
We will see later on in this series that the underlying issue also applies to INSERT
, DELETE
and MERGE
queries, but for this first entry, it will be helpful to examine the UPDATE
problem in a bit of detail.
Saturday 26 January 2013
Optimizing T-SQL queries that change data
Most tuning efforts for data-changing operations concentrate on the SELECT
side of the query plan. Sometimes people will also look at storage engine considerations (like locking or transaction log throughput) that can have dramatic effects. A number of common practices have emerged, such as avoiding large numbers of row locks and lock escalation, splitting large changes into smaller batches of a few thousand rows, and combining a number of small changes into a single transaction in order to optimize log flushes.
This is all good, but what about the data-changing side of the query plan — the INSERT
, UPDATE
, DELETE
, or MERGE
operation itself — are there any query processor considerations we should take into account? The short answer is yes.
The query optimizer considers different plan options for the write-side of an execution plan, though there isn’t a huge amount of T-SQL language support that allows us to affect these choices directly. Nevertheless, there are things to be aware of, and things we can look to change.
Monday 10 December 2012
MERGE Bug with Filtered Indexes
A MERGE
statement can fail, and incorrectly report a unique key violation when:
- The target table uses a unique filtered index; and
- No key column of the filtered index is updated; and
- A column from the filtering condition is updated; and
- Transient key violations are possible
Monday 15 October 2012
Cardinality Estimation Bug with Lookups
Estimated row counts on Key or RID Lookups where a filtering predicate is applied can be wrong in SSMS execution plans.
This error does not affect the optimizer’s ultimate plan selection, but it does look odd.
There are other cases where estimated row counts are inconsistent (for defensible reasons) but the behaviour shown in this post in certainly a bug.
Wednesday 12 September 2012
Why Doesn’t Partition Elimination Work?
Given a partitioned table and a simple SELECT
query that compares the partitioning column to a single literal value, why does SQL Server read all the partitions when it seems obvious that only one partition needs to be examined?