Microsoft touts the new Resource Governor as one of the biggest enhancements in this release, and there's no question it will come in handy for shops that want to keep some processes from interfering with others. For example, one of the best fits for a resource governor would be on an OLTP system where you're forced to run reports against live OLTP data. You don't want the reports to get in the way of transactions, so you would push them to a group that caps their resources.
Still, this is Microsoft's first stab at granular resource management, and while it's a nice start, it's not going to be as immediately useful as many shops hope. First of all, you can specify only two resource measures to define a resource group: CPU and memory. You can specify min and max values for each one, but that's not nearly enough to define a rogue process on a busy system. Rogue processes take many forms, and capturing them requires additional metrics (number of I/Os, amount of time, etc.) beyond CPU and memory usage.
Further, once you define a process to be in a particular resource group, it stays there. Misbehaving queries cannot be moved dynamically to a different group, as you can do in Oracle Database. In order to assign a query to a different resource group, you would have to kill the process, assign it to the group, and then restart the Resource Governor. Something tells me you won't want to do that often on a really busy system.
Finally, you have to be careful with how you set up your groups. For example, if you have three groups and they all have a maximum 50 percent of the memory defined, then there's obviously going to be a problem. So there have to be rules that define how much everyone gets; you just have to be careful about how you define your groups and monitor them to make sure they're giving you what you want.
It's difficult not to compare Microsoft's Resource Governor with Oracle's. Oracle Database has had the feature longer, and it's certainly richer and more mature. But there's one advantage to Microsoft's you just can't ignore: It's included in the license, while Oracle's is a fairly expensive add-on.
Change Data Capture and nonlogged inserts
Change Data Capture (CDC) and nonlogged inserts are two nice enhancements to the SQL Server engine that will make a world of difference for some shops. These enhancements mainly revolve around warehouses, but can be useful on some OLTP systems as well. CDC will alter the way developers write ETL processes for warehouses. CDC employs the same log reader used by replication to write the specifics of DML (Data Manipulation Language) operations to a table instead of a distribution database. This allows warehouses to be very easily loaded from tables that only contain changed data -- no more having to modify legacy applications or ETL processes with update columns in all the tables to provide this functionality. And you can track with perfect precision the changes made to data in your tables without altering your schema or performing a messy and expensive query to filter out the differences.
Sign up for CIO Asia eNewsletters.