Excellent question...most intriguing. Thanks.

Jeff Moden (7/30/2011)

---

There's another thing to consider. IIRC, both INTERSECT and EXCEPT essentially do a NULL = NULL comparison without any special considerations. If such a thing is...

tfifield (7/29/2011)

---

I hadn't actually thought INTERSECT and EXCEPT through to the DISTINCT part. When you look at it that way, then it is likely to not perform quite as...

Sort of. I think the best understanding comes from reading all three in sequence, but that's just my opinion.

More reading for you, directly on CXPACKET, and why it doesn't matter:

http://sqlblog.com/blogs/adam_machanic/archive/2011/04/13/how-queries-are-processed-a-month-of-activity-monitoring-part-13-of-30.aspx

http://sqlblog.com/blogs/adam_machanic/archive/2011/04/14/blocking-blockers-and-other-b-words-a-month-of-activity-monitoring-part-14-of-30.aspx

http://sqlblog.com/blogs/adam_machanic/archive/2011/04/15/seeing-the-wait-that-matters-most-a-month-of-activity-monitoring-part-15-of-30.aspx

GilaMonster (7/28/2011)

---

The 'backup the tail of the log' option has no place in maint plans. Shouldn't be there, absolutely no reason to ever select it and a nasty trap for...

kieron.dye (7/28/2011)

---

From this am I right in thinking that as the two queries are the same they both implicitly convert to VARCHAR(5) and that the conversion process is using the...

There's no inherent problem, no. Given a sensible design, appropriate indexing, and a sensible query, SQL Server will generally have no problem performing such a join.

Most likely, something about...

Your script returns different results because of the way implicit conversions are handled. In the padding off example, all columns are implicitly converted to VARCHAR(5). Some of these...

There's nothing special about EXCEPT or INTERSECT....

I humbly suggest this:

http://www.simple-talk.com/content/article.aspx?article=1250

A detailed discussion of CXPACKET will be in a future instalment. There's...

Roy Ernest (7/26/2011)

---

Now here is one of the reason why I raised the question of MAXDOP. I never did like the idea of giving MAXDOP 1 on the server level....

Sounds like it's not broken = may not need fixing 😉

From the projection...

Ninja's_RGR'us (7/26/2011)

---

That bugs me that you don't see anything for the DegreeOfParallelism either. Looks like there's something I don't understand in there (either the caching or the XML query)....

Hey Roy,

I don't think there's much to choose here between DOP 1 and query hints on the very few queries that would benefit, versus DOP 4 (say) and a higher...