At PASS this year, we implemented a question at the time when attendees picked up their badges.  The question was "Does your organization currently have at least one system in production on SQL Server 2005?".  If the attendee said "Yes", the staff would affix a "Live on 2005" sticker to their badge.  As of Wednesday AM when I checked with the staff (about 2/3 of registrants had picked up their badges at that time), there were about 60% of the attendees who had indicated that they were Live on 2005 on at least one system.  I think that the earlier upgrade experiences from one version to another has made people very cautious about the SQL Server upgrade experience for 2005.

I'm working a conversion to SQL Server 2005 right now. I would still recommend more caution than many have shown before committing. This is not necessarily a drop in replacement even just for the database engine.

I am a consultant performing this conversion for a client so I have inherited a system that, frankly, has been somewhat thrown together and there is some pretty poor SQL code in this system. Rewriting all these queries is not an option.

The worst problem is that "NOT IN" has been used extensively in this system. The query optimizer on SQL Server 2000 produces far better execution plans than SQL Server 2005. We all know that this is a bad technique but we are stuck with it. Many of these queries may never complete or if they do they will take so long as to be academic. One of these queries I cancelled after two hours on SQL Server 2005. The same query on SQL Server 2000 take about 25 seconds. Another example that does complete takes 14 minutes or so on SQL Server 2005 but about 16 seconds on SQL Server 2000.

I suspect that the SQL Server 2000 query optimizer is rewriting the query under the covers and the SQL Server 2005 query optimizer is not. Certainly other databases like DB2 do that.

Microsoft have confirmed this behaviour but say there is nothing that can be done. To be fair to Microsoft however, this is early days and we intend to apply more pressure through my client company's corporate substantial connections.

Failure for Microsoft to address this means either staying with SQL Server 2000 for the indefinite future or converting the database engine to another database. Anyone got Oracle's phone number?

I've found the opposite of Mark Harrison.  Where previously I would play "whack-a-mole" by changing IN to EXISTS, I've found that in most cases on 2005 post-SP1, IN and EXISTS produce equal plans.

In fact, during a conversation on the newsgroups a couple of weeks ago, I was unable to construct an example on 9.00.3027 where EXISTS outperformed IN.  Maybe it had to do with the fact that my laptop doesn't have a 2TB data store, but I am unable to produce differing plans on such a system running 9.00.2047, either.

I've since had my observations confirmed by a Microsoft Escalation Engineer and a bug has been lodged. The odds are that this will be a long way off to a resolution. I would expect months if not a year.

Even more worrying, according to the Microsoft Escalation Engineer that has investigated this for me, SP2 (which is in beta) is four times worse than SP1. I have not personally tested this yet so this is hearsay, though rather well informed hearsay.

Kevin puts some thought into SQL2005 adoption rates.

I've been speaking at local UGs across the country, at least one per month, and adoption rates there range from 10-20%.  I find this is extremely worrisome.

I think the variation in adoption rates is directly due to the knowledge of the audience.

-Kevin

Hi all,

Andrew, Kevin, great to find you here :-)

Well, I can only confirm Mark's comment on the "optimizer bug". I have observed this strange behavior and investigated the problem: Parameter sniffing seems to be responsible for the sometimes very poor performance. I already spoke to one of the developers of the query engine at PASS2006... he did not quite seem to believe me.

Fact is, that due to this behavior, many of my customers stay with SQL2000, too!

However, I have found a work-around for those who want to make use of the other new features: Use plan guides for queries that suffer from parameter sniffing! There will usually only be a hand full of queries that would need a plan guide and those can easily be identified via a trace.

Of course this solution has a downside: You have to decide if you create the plan guide for either cursors (api, e.g.)  or queries - it does not work for both. If you run an application like Dynamics NAV (Navision) you may have a problem, because the same query can appear both ways... and one of them will end up throwing an error.

Anyways, I am glad to read that some people at Microsoft are aware of the problem, so that this bug will eventually be fixed one day.

- Michael