A Nonclustered Index Update Disaster

This article was originally published on 𝕏.

Introduction

Update execution plans are not something the T-SQL statement writer has much control over. You can affect the data reading side of the plan with query rewrites and hints, but there’s not nearly as much tooling available to affect the writing side of the plan.

Update processing can be extremely complex and reading data-changing execution plans correctly can also be difficult. Many important details are hidden away in obscure and poorly documented properties, or simply not present at all.

In this article, I want to you show a particularly bad update plan example. It has value in and of itself, but it will also give me a chance to describe some less well-known SQL Server details and behaviours.

Be Careful with LOBs and OPTION (RECOMPILE)

It sometimes makes sense to add OPTION (RECOMPILE) to a query. Typically this will be when:

• A good enough plan for the query is very sensitive to one or more parameters
• No good single value exists for the parameter to use in a hint
• Optimize for unknown doesn’t give a good result
• The plan might be expected to change over time
• The cost of recompiling the statement is much less than the expected execution time
• Recompiling every time is very likely to save more time and resources than it costs overall

All that is fairly well-known. The point of this short post is to draw your attention to another side-effect of adding OPTION (RECOMPILE) — the parameter embedding optimization (PEO).

The Segment and Sequence Project Iterators

In my last post I promised to cover the Segment iterator in more detail, so here we go.

Segment

The Segment iterator partitions rows into groups as they flow through a query plan, checking whether the current row belongs in the same group as the previous row. For this to work, the incoming rows must be presented in an order which guarantees that all members of a group are received sequentially.

Segment has a “Group By” argument to specify how it should partition its input. The iterator adds an additional column to the rows that flow through it. This new column is used to communicate with its parent iterator, and is named something like [Segment1003].

The new column is visible in the graphical query plan properties window, or by hovering your mouse cursor over the Segment operator. The attribute name in both cases is Segment Column).