Microsoft® SQL Server® 2012 Bible

1131

CHAPTER

50

Resource Governor

IN THIS CHAPTER

Understanding Fundamentals

Using Performance Monitoring of the Resource Governor

Exploring Views and Limitations

I

t’s a DBA’s nightmare. A query runs and eats up resources to the point that everything else slows

to a crawl. The fault might belong to a software developer who didn’t consider the resources that

his queries chew up, but you get the call. And saying that you don’t know what the problem is

won’t satisfy anyone.

You might get to the bottom of the problem, but what then? Do you sever the connection that the

errant application comes in on? That could be a disaster for a mission-critical process. These issues

challenged SQL Server DBAs in the past.

The 2008 release of SQL Server introduced the Resource Governor feature, which enables you to

limit the CPU and memory usage by a specifi c application, hostname, user, or any other attribute

of the connection session. Now you can stop having those nightmares because you can limit the

resources for a query that might typically bring the server to a crawl.

Resource Governor is also an Enterprise Edition-only feature.

You can confi gure the Resource Governor in two ways. The fi rst is through T-SQL commands. The

second is via the Object Explorer from within SQL Server Management Studio.

There is a difference between the Resource Governor and the Work Load Governor. The Workload Governor was part of earlier

versions of SQL Server and limited the performance of SQL Server as a whole. It was not granular so that it could limit parts of

SQL Server. Although this was useful, it was not nearly as useful as the Resource Governor.

The Resource Governor can’t actually limit queries, but rather workloads. A workload is an identifi able process, identifi able by

an application name, a hostname, or a login.

c50.indd 1131c50.indd 1131 7/31/2012 10:22:28 AM7/31/2012 10:22:28 AM

http://www.it-ebooks.info