We've been dealing with this issue for 3 weeks now, and i've gotten tired,
frustrated, and as some of you may have noticed: a little cranky.

We have an application that we wrote. It runs fine on our development 2005
machine, it runs fine on their test 2005 server, but when they installed it
on their live 2005 machine, it runs differently. Our developement server is
not too impressive (4GB, dual core), their test server was cheapo machine
hanging around. Their live server is quad core 8GB monster.

There is one representative (note: representative, for examples sake, a case
in point) query that takes 90k logical reads here, and 90k logical reads on
the test server, but 200k logical reads on the live server - and runs
noticably slower. The execution plans are identical on development and test,
but different on live. The query can be tuned, and then on live it has an
identical execution plan to development and test.

The data is identical (backuped and restord). All statistics are up to date
WITH FULLSCAN.

The question is why is it running poorly on live. In fact the question isn't
even why is it running poorly on live, why is it running differenly on live?

   Boss: "Ian, what i don't understand is why is it running differently on
live? It runs fine here on 2005 with the ninety thousand reads. Why is it
running differently on live."

   Ian: (very nearly quoting Andrew J. Kelly) "That's just the way it is.
The optimizer in 2005 is deciding to run the query different. This sort of
thing is going to happen when people move to 2005 from 2000. It happened
when people moved from 7 to 2000, and it happened when people moved from 65
to 7. The optimizer in 2005 is choosing to run the query differently, and
there's nothing you can do about it except to tune the query. Most things
will be faster in 2005 and those aren't the ones we see. There will be some
queries that perform worse. And there's no choise to re-write them, and when
they do they'll be faster than they were even on 2000."

   Boss: "i understand that. But this isn't an upgrade from 2000. We're
2005 here, they tested it on 2005. Why is it running differently on their
live 2005 server? Of course you could tune queries, but the queries already
run fine on 2005. What's going on that's making it perform poorly on live?"

   Ian: "i don't know. Nobody can answer that question. The optimizer is
deciding what to do, and there's little you can do to understand why it
chooses to do something some way. There's no way to understand why the
optimizer chose to do something some way. "

So, SQL Server community, i turn to you tired and exhausted. Is the answer,
"i don't know?"  Is the answer that they need to move the database back to a
rinky dink server, which can run it better than the super-expensive live
monster they have, that runs 20 other databases fine?  Of course we could
re-tune queries, and in an ideal world we would have retuned them 3 weeks
ago and be done with it. But that's not the question. Customer wants to know
why it runs good on other 2005 machines, but not good on the server they
want it to be installed on.

And my hand-waving arguments about optimizer differences between 2000 and
2005 just isn't gonna work.

note: my past confusion came from knowing that years ago it was on 2000, but
it had since been ported to 2005 - where the queries in question run
identically as they did on 2000.