She's a little runaway (query)

Stop that runaway query!

Have you ever noticed how SQL Server likes to just take over the CPU and memory of the box it is installed on? It just says, in it’s little SQL brain, “Gee, I think 90% of the CPU should be sufficient to run this query, and maybe I will just keep this memory in case I need it for something else.” Meanwhile, all other queries and/or applications start turning blue from lack of resources and die on the way to the query optimizer.

Are you ready to take back control of the CPU and memory and force applications or users to only use their fair share of the server’s resources? Well, another great, new feature of SQL Server 2008 is called the Resource Governor. You can set limits on how much CPU and memory can be used by creating functions that define an application name, a user name, a host name, a server role name, and so on.

You start by creating Resource Pools and assign minimum and maximum CPU and memory percentages to each pool. For example, you create a pool called Pool1 and assign it a minimum of 20% and a maximum of 30% CPU. This means that anything assigned to the pool will always have some CPU available (20%) but will never exceed 30%.

You also create Workload Groups which are assigned to specific pools. These workload groups can be assigned a high, medium or low priority within that pool. At this point, it would be fair to mention that all unclassified work is dumped into the Default Workload Group. This pool has UNLIMITED access to system resources, the reason that so many of us have seen runaway applications.

The third part is to create Classifier Functions. This defines which users, applications, roles, etc. (workloads), go into which workload groups. Now, we have control!

Just so you know, SQL Server 2008 reserves some CPU and memory for itself that is never given to any user or application. Didn’t you ever wonder how you could access a locked up server using the “SQLCMD –A” utility? There is always CPU and memory on reserve just to keep the motor running like the well oiled machine that SQL Server is.

And if you like, I can show you how to build one of these Resource Governor contraptions right here in my MS 6231 class which runs from October 18-22 (it’s actually covered on Friday, usually). And if all else fails…

“Just hit it with a hammer!”

- Peter Trast,  MCITP DBA, MCITP EA, MCT LinkIn with Peter