I gave a presentation this morning to my bosses and co-workers explaining some of the benefits of upgrading to SQL Server 2008. I thought it might be useful to some of you if I shared my reasons for recommending this upgrade even if the cost is daunting.

(Of course, it would be much easier to always stay on the newest version if there were some kind of rolling upgrade licensing in effect; for our licenses at least, buying into SQL 2008 means essentially throwing away some very expensive SQL 2005 licenses.)

For the purposes of my presentation, I wanted to highlight three areas where I think we will benefit the most: disk space, developer productivity, and monitoring and troubleshooting. Remember that because I constructed this solely for our specific scenario and environment, that your mileage may vary. I am not trying to tell you why *you* should upgrade; I am just trying to explain the motivations behind my recommendation for my company to do so.

Disk Space

The primary thrust of my presentation revolved around storage requirements, because it was the easiest to quantify. I hear a lot of people out in the community say that disk space is cheap; I even catch myself saying so as well. While this is true in general, e.g. attaching local disks to an existing server, for us it is not the case. We have multi-TB EMC Clariions for all of our production servers, and I have to tell you, there isn't a whole lot of breathing room. We have some runtime, but IT has a collective stroke whenever I even mention disk space… they don't want another SAN device to manage, nor do they want to present management with a line item for extending or adding to the SAN areas in our data centers.

So because disk space is our most senitive topic, and because our workload is mostly I/O- and memory-bound, I focused primarily on the features that reduce disk space usage. I'll list them and briefly explain the pro(s) and con(s) of each one.

Backup Compression

At a cost of slightly higher CPU, backup compression gives us significantly reduced backup sizes, and because the I/O reduction outweighs the increase in CPU cost, our backups finish faster. This improvement in space and speed has been relatively constant across all of the instances and database sizes that I've tested. We also eliminate the need to license, configure and maintain 3rd party solutions that achieve similar results.

Unfortunately, this feature is only available in Enterprise Edition, but if you are using other SKUs, you can employ similar technologies from 3rd party vendors. Note that some of these products offer advantages over native backup compression; for example, Red-Gate's product allows you to specify the level of compression. HyperBac also intercepts native backup commands without using any custom code in your backup commands or maintenace plans, so you can turn compression on and off in any supported version of SQL Server without touching any code.

Data Compression

While not quite as predictable as backup compression, data compression also allows us to can achieve significant space savings. The results varied depending on the nature of the data, but overall it was largely beneficial in our environment. We have several large tables that seem like the perfect use case for this compression technology: many repeated values stored on the same page, and many values not occupying all of the designated space. Some other tables do not compress quite so well, so we will be able to apply it selectively to the areas of hte system that will benefit the most.

Again, a problem here is that this feature is restricted to Enterprise Edition, so if that is not in your budget, you won't easily be able to use this line item as justification, unless the space savings are significant.

Filtered Indexes

Filtered indexes can reduce storage space, index maintenance, and query response time. In our case we can eliminate several indexes and indexed views which are maintained for an entire table, when only a small portion of that table is actually relevant. This feature, to me, looks kind of like table partitioning, with the added bonus of being relatively hands-off.

Date data type

The new date data type requires only 3 bytes to store a date value. While in some cases we use smalldatetime, in most cases, we use datetime simply for compatibility reasons, even though we very seldom need the time portion. This will save 5 bytes per tuple (more than 62.5% savings), and since many of our tables, particularly the ones that are ever-growing, include at least one datetime column, this can represent a substantial reduction in disk space. And, more importantly, memory footprint.

Monitoring and Troubleshooting

While I didn't talk about it in depth, the new Resource Governor feature sparked some interesting conversation. By allowing us to place caps or minimums on different workloads, we can have much more control over how our servers' resources are utilized, and I am pretty sure management got the gist here.

Developers were impressed with the new Activity Monitor, which is much more powerful than previous versions; most importantly, reducing the steps to get to a poorly performing query's text and execution plan to a mere 2 clicks. I also braced them up front for the biggest problem most people seem to have with the new version of the tool: it is now in the context menu for the topmost server node in Object Explorer, instead of buried deep within. (I think the primary reason for this is so that if you are investigating a performance problem, you don't make it worse or have to wait by waiting for all of those nodes to expand.)

Developer Productivity

Here I talked mostly about IntelliSense and debugging, and showed off new syntactic sugar such as:

-- declare + assign in one step:
DECLARE @foo int = 5;
 
-- compound operators:
SET @foo += 5;
 
-- something like a row constructor:
INSERT dbo.foo(i) VALUES(@foo),(@foo+1),(@foo+2);

This got some oohs and aahs from developers, but understandably, very little from management. The biggest hit with the developers was the new "Copy With Headers" feature in Results to Grid mode of Management Studio. We are often in a mode where we need to run a quick query, paste it into a spreadsheet, and pass it along. Having column headers included by default has had a surprising effect on my productivity in these scenarios.

The only boo I got here was when I suggested that people get in the habit NOW of terminating all T-SQL statements with a semi-colon, since it will eventually be required. So why not start future-proofing our code now?

Next Steps

Of course SQL Server 2008 offers a lot of new features, and your priorities may be different. If you are not sure what your goals are, or want to build a more balanced and informed opinion, you should read more in-depth about it.