494
Part V: Enterprise Data Management
cost threshold for parallelism, then a parallel plan is produced. The parallel plan
cost is compared with the serial plan cost, and the cheaper one is chosen.Complex queries benefi t the most from parallelism because generating a parallel query
execution plan, synchronizing the parallel query, and terminating the query all require
additional overhead. To determine whether a query uses parallelism, view the query execu-
tion plan in Management Studio. A symbol shows the merger of different parallel query
execution threads.The default value of the max degree of parallelism option is 0, which tells SQL Server to use all the avail-
able processors.In Management Studio, you can set the max degree of parallelism option by enter-
ing the maximum number of processors to use in a parallel plan in the Max Degree of
Parallelism box on the Server Properties Advanced tab (refer to Figure 19-10 later in the
chapter).The following code sets the max degree of parallelism option to 4.EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'max degree of parallelism', 4;
RECONFIGURE;Best Practice
The default value of 0 for the max degree of parallelism option works well for SQL Servers that
have up to 8 processors. The performance of the SQL Server can actually degrade if more than 8 pro-
cessors are used in a parallel plan. It is recommended to change the max degree of parallelism
option on SQL Servers that have more than 8 processors from the default value of 0 to 8 or less. For
servers that have NUMA confi gured, the max degree of parallelism option should not exceed
the number of CPUs that are assigned to each NUMA node.For servers that have hyperthreading enabled, the max degree of parallelism option should not
exceed the number of physical processors.Although a parallel query execution plan can be much faster, there is a point at which the parallel
query execution becomes ineffi cient and can even extend the execution time. For example, parallel
queries performing small joins and aggregations on small data sets might be ineffi cient. Also, due to
different degrees of parallelism chosen at execution time, response times for one query can be dif-
ferent depending on resource availability such as CPU and memory.c19.indd 494c19.indd 494 7/30/2012 5:42:56 PM7/30/2012 5:42:56 PM
http://www.it-ebooks.info