In the speaker rooms of SQL events everywhere, there is wondrous blog material to be had, free for the picking up (as long as you attribute properly). Speaking of which…
Saturday in the SQL Saturday #63 Dallas speaker room, Erin Welker (blog) asked about a problem with a query that’s getting varying results depending on the MAXDOP setting. Mr. Microsoft - David Browne (blog) – was in the room, and a little discussion revealed that the query contained ordered subqueries that use SELECT TOP N rows.
As succinctly as I can get it: While you can order subqueries, if you don’t include enough columns in the ORDER BY to sufficiently order the rows, you can’t guarantee getting the same results every time. For example, if you just order by Color, the sort order within a single color won’t necessarily stay the same from one execution to another. In the case of Erin’s problem query, this showed up with the MAXDOP settings because the query plan for parallel vs single threaded will be different.
Drew Minkin (blog, Twitter) noted that in some cases, you can use plan guides to avoid that issue some of the time. (I’m quoting him somewhat out of context…)
David has agreed to blog on this in more detail, so I’ll be watching his blog for the link to post back here.
Happy days,
Jen McCown
