Whew!  So I've been super busy the past week and a half upgrading our production server from SQL 2000 to SQL 2008 and haven't had time for any new posts.  Finally starting to see everything calm down again though and running smoother than ever before.  I had hoped to do a comparison of 2000 and 2008 disk activity pre and post compression, but due to the number of "emergencies" in the week prior to the changeover and the number of times our plans changed with reconfiguring raid arrays etc. I never had an opportunity to get good solid numbers.  I can report a very rough estimate though. 

The compression on my objects is distributed as:

• Our database went from 75g to about 30g after a good amount of page compression. 
• Our CPU Usage hasn't gone up that I've noticed.  I haven't seen it break 30% total utilization yet.  If it did go up, it didn't go up enough to become a bottleneck.
• Many of the queries are running significantly faster and I haven't really seen any decrease on any others. 
• Our disk usage has gone down significantly.
• Our page life expectancy has gone through the roof.  Before we averaged around 6000 and would occasionally drop down to under 2000 or even down to 0, now we're averaging upwards of 150,000.
• Average Disk Queue Length post upgrade is around .1 for both data and tempdb arrays.

I will admit that I built a few additional indexes on this database with the help of some of the new DMV's in 2008(More on this in my next post), but that didn't account for the bulk of this change. 

Overall the upgrade went fairly smooth.  Here are a few notes about things that I had to fix during the upgrade process.

Check your identity values after backup and restore from 2k -> 2k8.  I used this little script to do it.  You'll get a lot of errors for tables that don't have identity columns but you can just ignore them.

exec sp_msforeachtable 'DBCC CHECKIDENT("?")' 

Beware temp table creation with nullable columns.

Somewhere between sql 2000 and sql 2008 the nullability properties of temp table columns changed.  If you do something like:

SELECT * 
INTO #1
FROM YourTable
WHERE 1=2

You won't necessarily get the same temp table in SQL 2000 vs sql 2008.  In SQL 2000, more of those columns might have been nullable.  In SQL 2008, it properly pulls the nullability from the base table.  One of ways you used to be able to force this in 2000 is to wrap a COALESCE(yourcolumn,NULL) around the column you need to be nullable in the creation.  That has been hit or miss in 2008.  I wouldn't rely on it and would really advise just building the table properly and not using SELECT INTO to create your table. 

Very similar to this, I've seen things that were working fine with hard coded values that you intend to fill in later fail in 2008 that worked in 2000.  For example,

SELECT Field1,
0 IntToBeCalculatedLater
Field3
INTO #1
FROM MyTable

In my testing I can't duplicate one that works in 2000 and not in 2008, but I've had stored procedures start failing in my database because of this, so apparently it can happen.  Note that you can use something like CAST(0 as int) or CAST(NULL as int) instead of just putting 0 there and it will make the column nullable.