Optimizing T-SQL queries that change datahttp://sqlblog.com/blogs/paul_white/archive/2013/01/26/optimizing-t-sql-queries-that-change-data.aspxMost 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. AsenCommunityServer 2.1 SP2 (Build: 61129.1)re: Optimizing T-SQL queries that change datahttp://sqlblog.com/blogs/paul_white/archive/2013/01/26/optimizing-t-sql-queries-that-change-data.aspx#47316Fri, 25 Jan 2013 23:30:31 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47316tobi<p>&quot;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 :)&quot;</p>
<p>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.</p>re: Optimizing T-SQL queries that change datahttp://sqlblog.com/blogs/paul_white/archive/2013/01/26/optimizing-t-sql-queries-that-change-data.aspx#47317Sat, 26 Jan 2013 01:20:12 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47317Alejandro Mesa<p>Paul,</p>
<p>Thanks to you for sharing this with us. It was a pleasure reading, as always.</p>
<p>--</p>
<p>AMB</p>re: Optimizing T-SQL queries that change datahttp://sqlblog.com/blogs/paul_white/archive/2013/01/26/optimizing-t-sql-queries-that-change-data.aspx#47410Thu, 31 Jan 2013 12:38:57 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47410Hugo Kornelis<p>Thanks for an interesting read, Paul!</p>
<p>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.</p>
<p>This also invalidates your comment that &quot;if the update target has an indexed view defined on it, a wide update plan is the only choice&quot;. 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.</p>
<p>Cheers,</p>
<p>Hugo</p>
re: Optimizing T-SQL queries that change datahttp://sqlblog.com/blogs/paul_white/archive/2013/01/26/optimizing-t-sql-queries-that-change-data.aspx#47422Thu, 31 Jan 2013 19:03:40 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47422Paul White<p>@tobi,</p>
<p>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.</p>
<p>@AMB</p>
<p>Thanks!</p>
<p>@Hugo</p>
<p>3,582 words in this post and you think it needs more detail? ;c)</p>
<p>Yes ok, I'll add that distinction somewhere at some point.</p>
<p>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.</p>
re: Optimizing T-SQL queries that change datahttp://sqlblog.com/blogs/paul_white/archive/2013/01/26/optimizing-t-sql-queries-that-change-data.aspx#47428Thu, 31 Jan 2013 22:22:13 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47428Hugo Kornelis<p>Paul: I use the terms &quot;per-index&quot; and &quot;per-row&quot; because they enable me to remember which is which. For &quot;wide&quot; and &quot;narrow&quot;, I always have to think or look it up.</p>
<p>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 &quot;narrow&quot; and &quot;wide&quot;, to me, only make sense in the context of an entire plan.</p>
re: Optimizing T-SQL queries that change datahttp://sqlblog.com/blogs/paul_white/archive/2013/01/26/optimizing-t-sql-queries-that-change-data.aspx#47429Thu, 31 Jan 2013 23:09:20 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47429Paul White<p>That's an interesting point, Hugo. I'll give it some more thought :)</p>
re: Optimizing T-SQL queries that change datahttp://sqlblog.com/blogs/paul_white/archive/2013/01/26/optimizing-t-sql-queries-that-change-data.aspx#47480Mon, 04 Feb 2013 21:12:07 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47480Aaron Morelli<p>Paul,</p>
<p>Great post as always! And it just so happens to open a door for a Q that has bothered me for some time... :-)</p>
<p>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 &quot;best effort&quot; 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). </p>
<p>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?</p>
<p>Thanks!</p>
<p>Aaron</p>re: Optimizing T-SQL queries that change datahttp://sqlblog.com/blogs/paul_white/archive/2013/01/26/optimizing-t-sql-queries-that-change-data.aspx#47486Tue, 05 Feb 2013 07:33:30 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47486Paul White<p>Hi Aaron,</p>
<p>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.</p>
<p>Paul</p>
re: Optimizing T-SQL queries that change datahttp://sqlblog.com/blogs/paul_white/archive/2013/01/26/optimizing-t-sql-queries-that-change-data.aspx#47493Tue, 05 Feb 2013 18:53:54 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47493Mark Freeman<p>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. :-)</p>re: Optimizing T-SQL queries that change datahttp://sqlblog.com/blogs/paul_white/archive/2013/01/26/optimizing-t-sql-queries-that-change-data.aspx#47504Tue, 05 Feb 2013 22:43:28 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47504Paul White<p>Thanks Mark, I've updated the main text to a form I'm comfortable with and at least partly based on the feedback.</p>