THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andrew Kelly

SQL2005 Adoption Rate

During my pre-con seminar last week at PASS in Seattle I asked a number of questions of the attendees and was caught off guard by one of the answers.  The question was “How many people are running live with SQL2005”.  The responses were pretty amazing in that 70% to 80% of the people raised their hands. When I asked that same question at TechEd 2006 just a few months earlier the figure was only around 30% to 40%. I thought it may have been a fluke so I asked it at my other sessions as well and the numbers were about the same as the Pre-con.  The word on the street has been that SQL2005 was slow to be adopted but I feel the tide has turned. I think people are past the first service pack jitters and are now anxious to take advantage of all the nice features in SQL Server 2005.  I know I sure am as there are just too many not to.  Of coarse there will always be late adopters for various reasons. I sat next to a developer on the plane on my way out to DevConnections in Las Vegas the week before and he mentioned his company was developing a new product due to be out in the next 6 months or so.  When I asked how they liked the new features in SQL Server 2005 he stated that they were using SQL Server 2000 instead. I asked why they would do something like that on a new product and he really didn’t know. Personally I think it is foolish to develop a new product from scratch and use an older version of software but I suppose they had their reasons. Any way it is good to see that SQL Server 2005 is taking off now.

 

Published Friday, November 24, 2006 2:08 PM by Andrew Kelly

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

RickHeiges said:

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.
November 25, 2006 7:47 AM
 

Mark Harrison said:

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?
November 26, 2006 4:14 PM
 

AaronBertrand said:

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.
November 27, 2006 1:39 PM
 

Mark Harrison said:

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.
November 28, 2006 7:13 PM
 

Kevin Kline said:

Kevin puts some thought into SQL2005 adoption rates.

March 8, 2007 12:00 PM
 

KKline said:

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

March 8, 2007 12:10 PM
 

Michael Danitz said:

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

March 24, 2007 1:59 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement