Microsoft is running an ad campaign showing skyscraper sized batteries labeled "SQL Server 2005" plugged into enormous buildings. The message is obviously that this database is designed for big companies and serious mission critical systems.
SQL 2005 is packed with impressive new features...too many to mention here. Some of them are truly awesome...like the new reporting capabilities which come as part of the purchase price, rather than having to buy a seperate system. Unfortuntely my experience with running the current version on a mission critical system (PRE Service Pack 1) has not been as impressive as I was hoping.
Along with the upgrade from SQL Server 2000 to 2005, we also simultaneously upgraded the hardware substantially. We went from a 32-bit dual processor box to an expensive "screamer"...a 64-bit quad processor box with dual core (so from two 32 bit processors to virtually eight 64 bit processors). And yet performance in SQL Server 2005 is no better than in 2000. And in some cases it's worse. What as going on?
A part of the problem was the Database Tuning Advisor (DTA). SQL 2005 has so many cool new features, but they are useless if you can't optimize the database. And that lack of optimization has caused increased timeouts, deadlocks and alot of personal user dissatisifaction with our service. The first problem is that the advisor takes a LONG time to run. Just tuning a query used to take a few seconds in 2000...now it goes through a multi step process lasting 4-5 minutes. Running a real workload is worse. But that's something you can learn to live with. But the problem is that the long wait times come with an unexpected companion...timeouts and deadlocks. The old 2000 let you analyze it "live" without any reprecusions (which I did may times). But don't make the mistake of running the current version of DTA on a database with a decent load on it. I don't know what DTA does, but every time I did it, it started terminating 1/2 of my users as deadlock victims so it could do it's business. And after about 45 minutes of this, it got worse becuase it just HUNG. And it was a bad hang too...becuase just restarting the service wasn't enough. I had to physically reboot the machine to allow clients to reconnect to it. It takes a long time for that beast of a machine to reboot (about 7 minutes), and the users were not pleased with me. Thank you DTA...may I have another?
Unfortunately, DTA's answers to that question is "yes you may!". There is the abilty to run DTA on a remote server to "lessen the load" on the production server (according to the MSFT documentation). In my case, load wasn't really the problem...the CPU never went above 50% on the production machine. It's the hanging that was a problem. Anyway, I discovered that for whatever reason, remote tuning stopped the hangs. But this only led to more problems.
At first I could never complete even a single tuning session becuase I would get a cryptic "Tuning process exited unexpectedly. (DTAEngine)". I was reduced to hitting "stop" at random points before it finished to get partial recommendations...anything to stop any of those timeouts. But I needed a real solution. So I opened a ticket with MSFT and they confirmed found a bug in DTA in dealing with indexed views (and is fixing it...ETA 3 weeks).
So after I learned that indexed views were the culprit I thought I'd at least be able to tune it 50% by setting DTA to tune "tables only". Smart idea right? Well...kind of. It worked...sometimes. But about 50% of the time DTA bombs out with "Index (zero based) must be greater than or equal to zero and less than the size of the argument list. Turning process finished." Nice and vague. And again, the crash stops any recommendations from being made. So yes, I had to open another Microsoft support ticket. I have six open right now...is that some sort of record? Sadly I think it probably is not. The worse thing is that Microsoft has duplicated this bug, but didn't fix it in SP1 and may not even issue a hotfix for it. The MSFT tech explained that they cannot fix it until enough people complain about it. So until enough people running big databases adopt SQL 2005 and run into these same issues...I am stuck dead in the water on this issue. But until they get problems like this solved...people are not going to be moving their databases over. A true "Catch 22".
(update: 5/8/2006: This will be made into a post SP1 hotfix...thanks to everyone else who also complained!)
Conclusion:
SQL 2005 has some awesome features. But it doesn't do any good your database runs slower than the last version becuase of the lack of DTA. My advice to anyone that asks is to wait for Microsoft to fix these bugs with SQL Server 2005 SP1 before jumping in with anything that is mission critical.
Ian Ippolito