Microsoft® SQL Server® 2008 Bible

Chapter 69. Resource Governor

IN THIS CHAPTER

• Determining whether Resource Governor is the right tool for the job

• Configuring Resource Governor

• Resource Governor performance monitoring

Whenever I think of the Resource Governor, I get a picture of an old southern gentleman in a white suit with a white bow tie.

"Excuse me, Gov'nor, but I'd be much obliged but you could please restrict the CPU traffic so my job could run smooth.... Many thanks to you, sir. Why sure, I'd love a cold lemonade. Thank you kindly."

Fitting right in with Microsoft's theme of SQL Server for the large enterprise, Resource Governor is designed to limit CPU and proc cache memory for specific queries, which improves SQL Server's ability to handle conflicting loads. For example, a few reports pulling data from a parallelized query can tie up a normally fast order processing database. But limit those reports to 10% of the CPU, the reports will take longer to run but the call center doesn't swamp the help desk with complaints.

Although Resource Governor sounds like a panacea for all sorts of server performance ills, it does have its limitations:

• Sessions must be assigned to the workload group at the beginning of the session. As the DBA, you can't alter the workload group of a runaway query to corral it into submission. If it's an assigned runaway session, it stays a runaway session.

• Resource Governor requires Enterprise Edition, so it can't help the organization with a single dual-core server running Standard Edition that ...