Press "Enter" to skip to content

Category: Query Tuning

Fun with the TOP Operator

Published 2019-12-11 by Kevin Feasel

Jared Poche takes a look at the TOP operator and learns a bit along the way:

Sort is a blocking operator. Don’t feel bad if you haven’t heard of the term; I’ve been working with SQL Server for 15 years, and I’m sure I never heard the term until the incomparable Grant Fritchley mentioned it while he was lecturing at my place of employment.

So sorts and several other types of operators (eager spools, remote query\scan\etc, hash match joins, and more) will block the normal flow and gather all their results before passing any rows on. The hash match join only blocks while building its hash table from the first input, before probing the second.

Read the whole thing. Jared is just getting started with blogging, too, so go pay his blog a visit.

Comments closed

Eager Spooling Against Indexes

Published 2019-12-04 by Kevin Feasel

Erik Darling finds an eager spool even when there is a good index to use:

But he did write about Eager Index Spools recently, and the post ended with the following statement:

Eager index spools are often a sign that a useful permanent index is missing from the database schema.

I’d like to show you a case where you may see an Eager Index Spool even when you have the index being spooled.

Click through for Erik’s demonstration.

Comments closed

Understanding the Eager Index Spool

Published 2019-12-03 by Kevin Feasel

Paul White gives us a lesson on eager spools:

Index spools do not tell the optimizer they support output ordered by the spool’s index keys. If sorted output from the spool is required, you may see an unnecessary Sort operator. Eager index spools should often be replaced by a permanent index anyway, so this is a minor concern much of the time.

There are five optimizer rules that can generate an Eager Index Spool option (known internally as an index on-the-fly). We will look at three of these in detail to understand where eager index spools come from.

Read on for a detailed discussion of eager spools.

Comments closed

Comparing Dates in a WHERE Clause

Published 2019-12-02 by Kevin Feasel

Erik Darling has been diving into issues with date comparison lately, including comparing date columns in the WHERE clause:

A common dilemma is when you have two date columns, and you need to judge the gap between them for something.

For instance, say you have a table of orders and you want to figure out how long on average it takes an ordered item to ship, or a shipped item to be delivered.

You’re not left with many good ways to write the query to take advantage of indexes.

Read on to see what Erik means, and one way you can speed it up a bit.

Comments closed

Determining Your Isolation Level

Published 2019-10-18 by Kevin Feasel

Bob Pusateri explains different transaction isolation levels and where you can figure out your current settings:

Isolation level settings for the entire database exist only as a default. All connections created will inherit the database’s isolation level. Furthermore, a database only has two possible isolation levels:
– read committed
– read committed snapshot (also known as “RCSI”)

There are four additional isolation levels (read uncommitted, repeatable read, serializable, snapshot) which can only be selected at the connection or statement level. In SQL Server and Azure SQL Database Managed Instance, the default database isolation level is read committed. In Azure SQL Database, the default is read committed snapshot.

Read the whole thing.

Comments closed

When Readers Block Writers

Published 2019-10-07 by Kevin Feasel

Erik Darling takes us through a scenario where readers can block writers for an extended amount of time:

To hold onto Shared locks, you’d need to use an isolation level escalation hint, like REPEATABLE READ.

I could do that here if I were a lazy cheater.

Instead, I’m going to show you a more common and interesting scenario.

This leaves the classes of non-lazy cheater and a lazy non-cheater (because non-lazy non-cheater sounds batty). Regardless of your answer, great post by Erik.

Comments closed

Performance Spools Plus Nested Loop Joins

Published 2019-09-25 by Kevin Feasel

Paul White takes us through the different types of performance-related spools you might see in an execution plan:

All performance spools are lazy. The spool’s worktable is gradually populated, a row at a time, as rows stream through the spool. (Eager spools, by contrast, consume all input from their child operator before returning any rows to their parent).

Performance spools always appear on the inner side (the lower input in graphical execution plans) of a nested loops join or apply operator. The general idea is to cache and replay results, saving repeated executions of inner-side operators wherever possible.

When a spool is able to replay cached results, this is known as a rewind. When the spool has to execute its child operators to obtain correct data, a rebind occurs.

You may find it helpful to think of a spool rebind as a cache miss, and a rewind as a cache hit.

Read the whole thing. I am admittedly the type of person Paul mentions at the end (I reflexively hiss when I have a query performing poorly and see a spool).

Comments closed

Forced Parameterization and Filtered Indexes

Published 2019-09-23 by Kevin Feasel

Aaron Bertrand walks us through a case where filtered indexes become unhelpful:

Again, focusing on the areas highlighted in orange: the statement has a parameter @0 (previously it had @1) but, more importantly, the clustered index is scanned now instead of the filtered index. This has impacts throughout the plan, including how many rows are both estimated to be read and actually read in order to return those 11 rows. You can see a much higher I/O cost (about 22X), the predicate is now listed explicitly in the tooltip, and you can see warnings about residual I/O (which just means a lot more rows were read than necessary). The root operator still has the warning about the unmatched index, so at least the plan gives you some clue that a filtered index exists that might be useful if you change the parameterization setting for the database (or add OPTION (RECOMPILE) to the statement):

There are still ways to make filtered indexes work with forced parameterization, such as index hints, but Aaron does a great job explaining why something which seems like it should just work doesn’t always.

Comments closed