Optimizing T-SQL queries that change data

re: Optimizing T-SQL queries that change data

tobi — Fri, 25 Jan 2013 23:30:31 GMT

"it does not have a sort specially optimized for index updates which could make a best effort to sort within the memory allocated, but never spill to disk. Perhaps I should suggest this on Connect :)"

You should. I already had that thought before you mentioned it. It looks comparably easy to implement although it probably gets closed. Hopefully it lives on in their internal bug trackers.

re: Optimizing T-SQL queries that change data

Alejandro Mesa — Sat, 26 Jan 2013 01:20:12 GMT

Paul,

Thanks to you for sharing this with us. It was a pleasure reading, as always.

AMB

re: Optimizing T-SQL queries that change data

Hugo Kornelis — Thu, 31 Jan 2013 12:38:57 GMT

Thanks for an interesting read, Paul!

I do have to point out a correction, though. You present the three basic forms of update plan, but you don't mention that there are also mixed plans - plans where some indexes are updates on a per-row basis and others on a per-index basis.

This also invalidates your comment that "if the update target has an indexed view defined on it, a wide update plan is the only choice". A mixed plan is also possible. There will always be seperate operators for updating the index' clustered index (and possible nonclustered indexes built on top of that), but other indexes on the base table may be updated either per row or per index.

Cheers,

Hugo

re: Optimizing T-SQL queries that change data

Paul White — Thu, 31 Jan 2013 19:03:40 GMT

@tobi,

The more I think about it, the more I think of little complexities that might make a non-spilling sort a non-starter. I'm still thinking about it, I'll update the post if I do submit the suggestion.

@AMB

Thanks!

@Hugo

3,582 words in this post and you think it needs more detail? ;c)

Yes ok, I'll add that distinction somewhere at some point.

As an aside, I see you are from the 'per row' or 'per index' school of nomenclature. Out of curiosity, how would you describe the per-index and per-row update plan? I think I have now decided to use 'wide' versus 'narrow' only, reserving 'per-index' to describe only plans with a sort or eager spool where the difference is important.

re: Optimizing T-SQL queries that change data

Hugo Kornelis — Thu, 31 Jan 2013 22:22:13 GMT

Paul: I use the terms "per-index" and "per-row" because they enable me to remember which is which. For "wide" and "narrow", I always have to think or look it up.

That also solves the issue of having a mixed plan, because I can now point to specific operators or specific indexes and say that they are updated per row or per index. The terms "narrow" and "wide", to me, only make sense in the context of an entire plan.

re: Optimizing T-SQL queries that change data

Paul White — Thu, 31 Jan 2013 23:09:20 GMT

That's an interesting point, Hugo. I'll give it some more thought :)

re: Optimizing T-SQL queries that change data

Aaron Morelli — Mon, 04 Feb 2013 21:12:07 GMT

Paul,

Great post as always! And it just so happens to open a door for a Q that has bothered me for some time... :-)

Does SQL/SQLOS have any IO-reordering logic? For example, Unordered Prefetch would seem to result in a rapid-fire series of Async IOs that are only microseconds apart, and reordering IOs in page-order would be a "best effort" way of turning random into sequential. (Though of course, we are ultimately at the mercy of actual block order and the disk controller's ability to IO reorder).

This would also seem to benefit prefetches on NL Joins, logical-order index scans, and maybe an access method or two that I'm not thinking of?

Thanks!

Aaron

re: Optimizing T-SQL queries that change data

Paul White — Tue, 05 Feb 2013 07:33:30 GMT

Hi Aaron,

There's no generalized reordering for unordered prefetch, but unordered prefetch on a Nested Loops join may have the 'optimized' property set in which case a best-effort batch sort of keys is performed. As far as I know, this does not happen in any other case.

Paul

re: Optimizing T-SQL queries that change data

Mark Freeman — Tue, 05 Feb 2013 18:53:54 GMT

I vote with Hugo. Per-row and per-index are better terms, simply because they don't require an additional mental look-up. We need to optimize even the queries that run in our own heads. :-)

re: Optimizing T-SQL queries that change data

Paul White — Tue, 05 Feb 2013 22:43:28 GMT

Thanks Mark, I've updated the main text to a form I'm comfortable with and at least partly based on the feedback.