This is an interesting performance bug concerning a broken query optimizer rule in 2008. Thanks to Dan Shargel (Twitter) for sending me info on this and letting me use some of the stuff he sent.

The scenario involves using MIN or MAX in a query against a partitioned view. In 2005 the query plan includes a TOP (1) operator which uses the right index, but in 2008 the optimizer rule was broken and the plan turned into a stream aggregate, much more expensive in this case.

Here's the 2005 query plan:


and here's the 2008 query plan (before the bug fix):


You can get the fix in CU4 for 2008 SP1 (or later) and read a bit more about it in KB 973255.

Note that you have to turn on trace flag 4199 to enable the fix – that requirement will be removed in SQL11.