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:

 120309 plan1 Interesting 2008 partitioned view perf bug fixed in SP1 CU4

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

 120309 plan2 Interesting 2008 partitioned view perf bug fixed in SP1 CU4

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.