You work as a database administrator at ABC.com. ABC.com is using a SQL Server 2008
instance. You have received instruction from management to set the instance to utilize one thread
for searches which owns an estimated execution cost less than 6.
What steps should you take?
A.
Change the sp_configure cost threshold for parallelism option.
B.
Use the DBCC SHRINKFILE command.
C.
Execute DBCC INDEXDEFRAG.
D.
Create an appropriate stored procedure.
E.
Change the sp_configure cost boost for parallelism option.
F.
Change the sp_configure the max worker threads option.
Explanation:
Use the cost threshold for parallelism option to specify the threshold at which Microsoft SQL
Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for
a query only when the estimated cost to run a serial plan for the same query is higher than the
value set in cost threshold for parallelism. The cost refers to an estimated elapsed time in seconds
required to run the serial plan on a specific hardware configuration. Only set cost threshold for
parallelism on symmetric multiprocessors.
Longer queries usually benefit from parallel plans; the performance advantage negates the
additional time required to initialize, synchronize, and terminate the plan. The cost threshold for
parallelism option is actively used when a mix of short and longer queries is executed. The short
queries execute serial plans while the longer queries use parallel plans. The value of cost
threshold for parallelism determines which queries are considered short, thus executing only serial
plans.
In certain cases, a parallel plan may be chosen even though the query’s cost plan is less than the
current cost threshold for parallelism value. This is because the decision to use a parallel or serial
plan, with respect to cost threshold for parallelism, is based on a cost estimate provided before the
full optimization is complete.
The cost threshold for parallelism option can be set to any value from 0 through 32767. The
default value is 5.
If your computer has only one processor, if only a single CPU is available to SQL Server because
of the affinity mask configuration value, or if the max degree of parallelism option is set to 1, SQL
Server ignores cost threshold for parallelism.
cost threshold for parallelism is an advanced option. If you are using the sp_configure system
stored procedure to change the setting, you can change cost threshold for parallelism only when
show advanced options is set to 1. The setting takes effect immediately (without a server stop andrestart).