Four Underutilized Features of SQL Server Enterprise Edition (and One of Standard Edition)

I started thinking about this post a few months ago—we were running into database performance issues on one of our major customer facing applications, and I noticed we weren’t using partitioning or compression on a very large database. So I decided to put together a presentation on compression, and that led me into thinking what features aren’t utilized as often as they should be in SQL Server Enterprise Edition. SQL Server Enterprise Edition licenses cost a lot—at a minimum your company is paying about $28,000 (MSRP) for SQL Server Enterprise Edition—so what features might you be leaving on the table.

1. Compression—Compression (both Row and Page level compression) was introduced in SQL Server 2008, and is great feature for a lot of workloads. In the past, our workloads were CPU constrained, and using compression on a database, just wasn’t a good idea, however in the era of underutilized powerful CPUs, compression makes a lot of sense for many workloads. Microsoft’s official recommendation is that you should row compress all of your data–I don’t necessarily agree with that sentiment, but using compression carefully, can greatly increase the performance of your read workloads. There’s a great Microsoft white paper on compression, and when and where to use it here. Additionally, compression is one of the only features that the DBA can use to increase the performance of a third party application.* (* Make sure your vendor is cool with this)

   SQL

2. Partitioning—the origin of this blog post was a 112 GB table, that was neither partitioned nor compressed. Index rebuild jobs were running into the batch window, and the DBA asked me for assistance in job scheduling. When I dug into the jobs and saw that table, needless to say I was alarmed. Partitioning isn’t easy to use—you have to think about your data and how it should be broken down. There are some great resources around partitioning from Microsoft here and Kendra Little (b|T) here. Partitioning has the potential to make your databases easier to maintain, your queries run faster, and increasing your salary (well after the other two things happen). It’s also another way to improve the performance of a third party application, and lastly if your application doesn’t have built in archiving functions, it’s a way to archive data, if you have long data retention requirements.

    

3. Resource Governor—this feature isn’t for everyone—it allows you to control the amount of CPU and Memory available to given resource pool. You can assign users to resource pool, and limit the CPU and memory available to that pool. Pinal Dave does a good job explaining how to configure it here. The Microsoft demos I’ve seen tend to show how you might give the CEO priority to run his reports, I don’t know about you, but everywhere I’ve worked the CEO has minions to run his reports, so he or she isn’t that concerned about it. The real-world situation where I’ve used this feature is in a multi-tenant SQL Server—I had a couple of databases that were overwhelming the rest of the server. Fortunately both of these databases accessed SQL Server as a process user—I was able to add that process use to a resource pool. Note—there is no way to directly limit the I/O utilization directly, but if you limit CPU enough, the user won’t be able to generate that many IOs. I’m looking at you VSphere database.

    

4. Management Data Warehouse—Ok, I lied, this feature isn’t in Enterprise Edition. It’s also not for everyone—if you have more than 50 servers, it probably won’t scale that well. This was introduced in SQL Server 2008, to allow administrators to collect performance data from a group of servers, and return it to a central server. It is extendable and customizable, and Microsoft includes a large number of canned reports. It is limited to servers that are running SQL Server 2008 and above, but you should really be getting off of SQL 2005 soon anyway. The other caveat is that if the time comes that you want to stop using MDW, it leaves some remnants behind. Fortunately, Aaron Bertrand (b|t) has blogged on how to configure and remove it successfully here.

    

5. Peer to Peer Transactional Replication—Do you like hitting yourself in the thumb with a hammer? This might be the SQL Server feature for you! Seriously, if you want to do this, enlist professional help before going down the path, it’s not simple, and can certainly be a headache.