I gave a presentation at TechEd 2008 where I covered our new features in “A Day in the Life of a DBA”. Here’s more of what I showed for 10:00am – “Panic Button”:
While I’m examining the state of performance on my system, I discover that one set of queries is monopolizing most of the CPU and Memory resources. If you’re faced with this situation in SQL Server 2008, you now have the ability to “throttle” down the resources for a group of connections. Using connection metrics such as user name, host address and so on, you can create “Pools” that have a set amount of CPU percentage and memory sizes assigned to them. You then tie out the user to the pools, and that’s as much as the user’s connection will get. That way they run slower, but the other users don’t suffer as much. No code changes are needed.
Also with SQL Server 2008, the highest edition allows you to “Hot add” CPU’s to your system. A simple “Reconfigure with Override” statement brings them online into SQL Server.