You are the SQL administrator for your company. A SQL Server 2008 instance named SQL1 contains severalproduction databases.
Because of the number of users accessing the databases, performance is the primaryconcern.
You configure a SQL trace that monitors the CPU Threshold Exceeded event class.
After the trace is running forseveral days, the trace does not generate any warnings of the condition.
You are concerned that the thresholdthat was specified is too large.
You want to reset the threshold to its default value. What should you do?
A.
Run the sp_configure system stored procedure.
B.
Execute the ALTER WORKLOAD GROUP statement with the
WITH REQUEST_MAX_CPU_TIME_SEC parameter set to 0 seconds.
C.
Run the sp_trace_setevent system stored procedure.
D.
Execute the CREATE WORKLOAD GROUP statement with the
WITH REQUEST_MAX_CPU_TIME_SEC parameter set to 0 sec
Explanation:
You should execute the ALTER WORKLOAD GROUP statement with the WITHREQUEST_MAX_CPU_TIME_SEC parameter set to 0 seconds. This will configure the threshold to a value of 0seconds, which is the default value. You should not run the sp_configure system stored procedure. The sp_configure system stored procedure isnot used to manage the threshold for the CPU Threshold Exceeded event class. It is used to manage server-wide settings. You should not run the sp_trace_setevent system stored procedure. This system stored procedure is used toadd or remove events from a trace. It cannot be used to manage the CPU threshold for the Resource Governor. You should not execute the
CREATE WORKLOAD GROUP statement with the WITHREQUEST_MAX_CPU_TIME_SEC parameter set to 0 seconds. This setting is already configured with a value.
Therefore, you need to edit the value using the ALTER WORKLOAD GROUP statement.Objective:
Optimizing SQL Server PerformanceSub-Objective:
Implement Resource Governor.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 >ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Monitoring > MonitoringEvents > SQL Server Event Class Reference > Errors and Warnings Event Category (Database Engine) > CPUThreshold Exceeded Event Class TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > ALTER WORKLOAD GROUP (Transact-SQL)