In my last post, we saw how a query featuring a scalar aggregate could be transformed by the optimizer to a more efficient form. As a reminder, here’s the schema again:
About This Blog
Monday 8 July 2013
Wednesday 26 June 2013
Optimization Phases and Missed Opportunities
There are two complementary skills that are very useful in query tuning. One is the ability to read and interpret execution plans. The second is knowing a bit about how the query optimizer works to translate SQL text into an execution plan.
Putting the two things together can help us spot times when an expected optimization was not applied, resulting in an execution plan that is not as efficient as it could be.
The lack of documentation around exactly which optimizations SQL Server can apply (and in what circumstances) means that a lot of this comes down to experience, however.
Monday 17 June 2013
Improving Partitioned Table Join Performance
The query optimizer does not always choose an optimal strategy when joining partitioned tables. This post looks at an example of that, showing how a manual rewrite of the query can almost double performance, while reducing the memory grant to almost nothing.
Tuesday 11 June 2013
Hello Operator, My Switch Is Bored
This post is in two parts. The first part looks at the Switch execution plan operator. The second part is about an invisible plan operator and cardinality estimates on filtered indexes.
Thursday 4 April 2013
Optimizer Limitations with Filtered Indexes
One of the filtered index use cases mentioned in the product documentation concerns a column that contains mostly NULL
values. The idea is to create a filtered index that excludes the NULLs
, resulting in a smaller nonclustered index that requires less maintenance than the equivalent unfiltered index.
Another popular use of filtered indexes is to filter NULLs
from a UNIQUE
index, giving the behaviour users of other database engines might expect from a default UNIQUE
index or constraint: Uniqueness enforced only for non-NULL
values.
Unfortunately, the query optimizer has limitations where filtered indexes are concerned. This post looks at a couple of less well-known examples.
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
.