SQL Server 2005 Service Pack 1 (SP1)includes many bug fixes and improvements to the core SQL Server 2005 engine. Given the poor performance we had been experiencing after upgrading from SQL Server 2000 (despite simultaneously doubling the processors, tripling the memory, increasing hard drive speed, etc.) I was excited to try this out. This quote from the above URL looked especially promising:
"Several performance problems were fixed in the Database Engine query optimizer. These changes improve the upgrade process for third-party products that use SQL Server as their back-end database when you upgrade from SQL Server 2000 to SQL Server 2005."
What happened?
The good:
Via an off-the cuff estimate, it appears that things are indeed running slightly better. The processor was averaging around 92% CPU usage on Monday and dropped to 84% today after installation of SP1.
However, when put into perspective...the old SQL 2000 server (with half the machine, 1/3 the memory etc) was running at 80-90% CPU. So this is just one small step in the final journey. But nonetheless...it's a step in the right direction.
The bad/ugly:
Now for the bad news. After installing SP1 I thought everything worked fine. Today there were some problems with a replicated table. After probing here is what greeted me in replication monitor:
A complete 100% failure of all replication on every single publiccation and subscription to every single database. I didn't realize right away, because database mail was not working properly pre SP1 on x64 machines. There was a KB article on how to get around this bug, but it didn't work (I had a ticket open for it). Now it's a moot point because with SP1 it's been fixed! Yes...I will now enjoy plenty of warning when future service packs break things. Goody! :)
On this one it's tricky to figure out what the cause is. The normal history and log menus are completely disabled in replication monitor so I can't even see what the error/problem is. Arrrghh. Time to open another ticket...sigh. (I opened 3 previously today on SQL Server...and it's only 11:19PM so that makes 4 in one day. Can someone remind me again why I'm paying Microsoft for this product and not the other way around?)
4/27/2006: Update on the replication issue: I got away without opening a ticket (after getting some rest and thinking about it). I noticed that SQL Server lost the names of the replication databases (for some reason) on each subscription, after SP1 was installed. With some experimentation, I found there was a "simple"workaround...blowing away all publications and subscriptions and recreating them from scratch.
Simply wiping out just the subscriptions and redoing them would have been easier, but that didn't completly work. The new subscriptions functioned, but for some reason the old ones (with missing database names) were still there, like "phantoms". Leaving it this way would have made the replication monitor useless, because it would have always indicated an error on every publication (for the phantom subscriptions)..even when the "real" ones were fine.
So fixing it required blowing away the subscriptions as well. This is not a big deal if you only have a few tables that are small. Fortunately that's our case. Unfortunately one of them allows the sharing of information on site members across the Exhedra sites (and databases). This big guy is a few million rows. So this meant resnapshotting and sending all that info over the network to the other databases again. The users on those sites are not very happy with us right now, becuase this is killing their activity on the databases/sites. But it's one of those things that "has to be done". Otherwise no one could create new accounts...not a good situation either.
So my recommendation is to allocate extra time in your Service Pack1 Upgrade planned downtime to possibly handle rebuilding all of your replication. Good luck.
Ian Ippolito