Updating Partitioned Table with Cross Apply

GokhanVarol 2013-06-24 23:11:53

I have been using this cross apply per partition to speed up queries quite a bit lately and wanted to try what happens if I used it in an update query. It looks like it got rid of the table spool and the worktable from the query. Would this be something I can rely on that would be more efficient to update partitioned tables or I was just lucky this time?
Thank you

link text

SQLkiwi 2013-06-28 04:59:42
In the first plan, the Eager Spool is required because both nonclustered indexes are being updated separately from the Clustered Index. This is known as a per-index update strategy, and looks to optimize for sequential I/O against the nonclustered index pages. The Eager Spool is used to store and replay changes to each index in turn.

In the second plan, the optimizer chooses to update one nonclustered index using a per-index strategy, while the other nonclustered index is updated per-row, at the same time as the clustered index update. Plan Explorer highlights this per-row maintenance in green.

The optimizer makes a cost-based choice whether to maintain each nonclustered index per-index or per-row. Reformulating the query in this case happened to change the cost estimates and so a different choice was made. There will not be a general correlation between the query forms and index maintenance strategies.

I wrote more about this in a recent blog post.