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 ...

I love SQL Server execution plans. It is often easy to spot the cause of a performance problem just by looking at one. The 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 where the execution plan does not ...

Most tuning efforts for data-changing operations concentrate on the SELECT side of the query plan. Sometimes people will also look at important storage engine considerations like locking and transaction log throughput that can have dramatic effects. As a consequence, a number of common practices have emerged, such as avoiding large numbers of ...

This post is for SQL Server developers who have experienced the special kind of frustration, which only comes from spending hours trying to convince the query optimizer to generate a parallel execution plan. This situation often occurs when making an apparently innocuous change to the text of a moderately complex query; a change which ...

You probably already know that it’s important to be aware of data types when writing queries, and that implicit conversions between types can lead to poor query performance. Some people have gone so far as to write scripts to search the plan cache for CONVERT_IMPLICIT elements, and others routinely inspect plans for that type of thing when ...

If you look up Table Hints in Books Online, you’ll find the following statement: If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek.
If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error. The interesting thing there is ...

Background One of the core assumptions made by the SQL Server query optimiser’s model is that clients will consume all of the rows produced by a query. This results in plans that favour the overall execution cost, though it may take longer to begin producing rows. Let’s look at an example:
The optimiser chooses to perform the ...

From time to time, I encounter a system design that always issues an UPDATE against the database after a user has finished working with a record – without checking to see if any of the data was in fact altered. The prevailing wisdom seems to be that “the database will sort it out”. This raises an interesting question: how smart is SQL ...

Iterators SQL Server uses an extensible architecture for query optimisation and execution, using ‘iterators’ as basic building-blocks.
Iterators are probably most familiar in their graphical showplan representation, where each icon represents a single iterator. They also show up in XML query plan output as RelOp nodes.
Each ...