🙂 Thank you for the replies. The code unfortunately, is confidential. However, it would still be too complex even if I could post it. This is part of the code base for a product and heence so. The query is against a view, which is based on joins two other views(aliased twice each, I think) and a table. The views at this level are also based on one other view and a few tables, with a UNION ALL of 3 queries(Yes I know !! :w00t:). Some of those views also used derived tables in the join etc.

Its a long story(too long to go into it here) as to why these queries need to be so incredibly complex. However, the bottomline is this: The tables this query goes after may total about 5 GB in data in a database thats about 10 - 12 GB in size, and tempdb bloats up to 70 GB (!!!) if the MAXDOP hint is not supplied. As I mentioned earlier, I had opened a case with MS in July 2011 where they had agreeed that it was a bug. I tested again with SP2, and its only marginally better. On different hardware, on SP2, the query used about 25 GB of tempdb space without the MAXDOP hint. Using MAXDOP 1 reduces tempdb usage to about 4 / 5 GB. Go figure !!