You are responsible for managing an instance of SQL Server 2008 that is installed on a server that has fourprocessors. The instance contains a database named
FinanceDB . The FinanceDB database is accessed by several users using different types of Transact-SQL queries. You wantto ensure that the SQL server creates and runs a parallel plan for a query if the estimated elapsed time forrunning a serial plan for the query is greater than one second. Which Transact-SQL should you execute?

A.
sp_configure ‘show advanced options’, 0; GO
RECONFIGURE; GO
sp_configure ‘cost threshold for parallelism’, 1; GO
RECONFIGURE; GO

B.
sp_configure ‘show advanced options’, 1; GO
RECONFIGURE; GO
sp_configure ‘cost threshold for parallelism’, 1; GO
RECONFIGURE; GO

C.
sp_configure ‘show advanced options’, 1; GO
RECONFIGURE WITH OVERRIDE; GO
sp_configure ‘max degree of parallelism’, 1; GO
RECONFIGURE WITH OVERRIDE; GO

D.
sp_configure ‘show advanced options’, 0; GO
RECONFIGURE WITH OVERRIDE; GO
sp_configure ‘max degree of parallelism’, 1; GO
RECONFIGURE WITH OVERRIDE; GO

Explanation:

You should execute the following Transact-SQL:
sp_configure ‘show advanced options’, 1; GO
RECONFIGURE; GO
sp_configure ‘cost threshold for parallelism’, 1; GO
RECONFIGURE; GO
The cost threshold for parallelism configuration option allows you specify when the SQL server creates andruns a parallel plan for a query. A parallel plan for a query is created only when the estimated elapsed time to runa serial plan for the query is greater than the value configured in the cost threshold for parallelism option.Therefore, to ensure that a parallel plan is created for a query if the estimated elapsed time for running a serialplan for the query is greater than one second, you should set a value of 1 for the
cost threshold for parallelism option. The cost threshold for parallelism option is an advanced configuration option. To be able to changeadvanced configuration options by using the sp_configure system stored procedure, you must first ensure thatthe value for the show advanced options configuration option is set to 1. You should not execute the following Transact-SQL:
sp_configure ‘show advanced options’, 0; GO
RECONFIGURE; GO
sp_configure ‘cost threshold for parallelism’, 1; GO
RECONFIGURE; GO
The cost threshold for parallelism option is an advanced option. To be able to change this option by using the sp_configure system stored procedure, you must first ensure that the value for the show advanced options configuration option is set to 1. You should not execute the following Transact-SQL because it does not allow you to specify the threshold atwhich SQL Server creates and executes a parallel plan for a query:
sp_configure ‘show advanced options’, 1; GO
RECONFIGURE WITH OVERRIDE; GO
sp_configure ‘max degree of parallelism’, 1; GO
RECONFIGURE WITH OVERRIDE; GO
When you install SQL Server 2008 on a multiprocessor computer, it automatically detects the optimal number ofprocessors that should be used to run a single statement for each parallel plan execution. This is referred to asthe degree of parallelism. The max degree of parallelism configuration option can be used to prevent SQLServer from deciding the number of processors to use for each parallel plan. You can use the max degree ofparallelism option to explicitly specify the number of processors that should be used for executing parallel plans.The default value for this setting is 0, which indicates that all available processors should be used. The maxdegree of parallelism option is an advanced configuration option. To be able to change advanced options byusing the sp_configure system stored procedure, you must first ensure that the value for the show advancedoptions configuration option is set to 1. You should not execute the following Transact-SQL:
sp_configure ‘show advanced options’, 0; GO
RECONFIGURE WITH OVERRIDE; GO
sp_configure ‘max degree of parallelism’, 1; GO
RECONFIGURE WITH OVERRIDE; GO
The max degree of parallelism option is an advanced configuration option. To be able to change advancedconfiguration options by using the sp_configure system stored procedure, you must first ensure that the value forthe show advanced options configuration option is set to 1. Also, the max degree of parallelism option is usedto prevent SQL Server from deciding the number of processors to use for each parallel plan. It does not allow youto specify the threshold at which SQL Server creates and executes a parallel plan for a query.

Objective:
Maintaining SQL Server Instances

Sub-Objective:
Manage SQL Server Agent jobs.

References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Administration > ManagingServers > Setting Server Configuration Options > cost threshold for parallelism Option