You are the database administrator of your company. You need to manage the resource usage on an instance of SQL Server 2008 named SQL1.
You decide to enable the Resource Governor. Which Transact-SQL statement should you run?
A.
ALTER RESOURCE GOVERNOR ENABLE;
B.
ALTER RESOURCE GOVERNOR WITH RECONFIGURE;
C.
ALTER RESOURCE GOVERNOR RECONFIGURE;
D.
ALTER RESOURCE GOVERNOR RESET STATISTICS;
Explanation:
You should run the following Transact-SQL statement: ALTER RESOURCE GOVERNOR RECONFIGURE; The Resource Governor is a new feature in SQL Server 2008 that allows you to control SQL Server workload andresources by specifying limits on resource consumption by incoming requests. The Resource Governor usesresource pools, workload groups, and classification functions to control resource consumption. Resource poolsrepresent the physical resources of the SQL server. By using resource pools, you can configure a minimum andmaximum percentage for CPU use and a minimum and maximum percentage for memory use. Workload groupsare containers for session requests that match the classification criteria that are applied to each request.Workload groups are assigned to resource pools, and they are used to group applications and limit SQL Serverresources. By using workload groups, you can configure settings, such as a maximum memory allocation and amaximum CPU time limit for requests. Classification functions are used to assign workloads to workload groups.When you install SQL Server 2008, the Resource Governor is disabled by default. To be able to use theResource Governor, you must first enable it. To enable the Resource Governor, you can either use the ALTERRESOURCE GOVERNOR RECONFIGURE Transact-SQL statement or use SQL Server Management Studio. Toenable the Resource Governor by using SQL Server Management Studio, you should right-click the ResourceGovernor node under the Management node and select the Enable option. You should not run the following Transact-SQL statement: ALTER RESOURCE GOVERNOR ENABLE; ENABLE is not a valid clause of the ALTER RESOURCE GOVERNOR statement. You should not run the following Transact-SQL statement: ALTER RESOURCE GOVERNOR WITH RECONFIGURE; The WITH clause is used to specify a CLASSIFIER_FUNCTION parameter value. Specifying the WITH clausewith the RECONFIGURE parameter will generate an error. You should not run the following Transact-SQL statement:
ALTER RESOURCE GOVERNOR RESET STATISTICS;
The RESET STATISTICS parameter is used to reset statistics on all workload groups and resource pools. Thisparameter cannot be used to enable the Resource Governor.Objective:
Optimizing SQL Server PerformanceSub-Objective:
Implement Resource Governor.References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > ALTER RESOURCE GOVERNOR (Transact-SQL) MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Performance > ManagingSQL Server Workloads with Resource Governor > Introducing Resource Governor MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Performance > ManagingSQL Server Workloads with Resource Governor > Resource Governor Concepts