495
Chapter 19: Confi guring SQL Server
19
Many people recommend setting the max degree of parallelism option to 1 for OLTP workloads.
This can be a misleading over-generalization that folks who are not familiar with the topic might blindly
follow. Certain applications need the max degree of parallelism option set to 1. For example,
the max degree of parallelism option is set to 1 during the confi guration of BizTalk Server for the
SQL Server instances that host the BizTalk Server MessageBox databases. As per BizTalk’s documen-
tation, changing this to anything other than 1 can have a signifi cant negative impact on the BizTalk
Server stored procedures and performance. With this in mind, it is recommended to check with your
application vendor for any best practices for the max degree of parallelism option. If you cannot
contact the vendor or it is an in-house built application, you may want to test with different values of
the max degree of parallelism option to see which value gives the maximum performance gains.If you fi nd that setting the max degree of parallelism option to 1 or any low value works best for
your workload, it is recommended to change the option back to 0 (or 8 if you have more than 8 CPUs)
when you perform database maintenance tasks such as index creation, index rebuild, and checkdb
because this can speed up these tasks if they can leverage more CPUs. You can change the max degree
of parallelism option without the need to restart SQL Server.Although these server-tuning options can affect performance, performance begins with the database
schema, queries, and indexes. No amount of server tuning can overcome poor design and development.A safer alternative than simply enabling the max degree of parallelism setting at the instance level,
is to make use of the MAXDOP query hint. This, however, requires a deep understanding of what the
query needs and how it behaves. Query hints should be used with caution and tested extensively.You need to do extensive testing with your workloads to fi nd the confi guration that works best for your
particular setup. For more information on this topic, check out Adam Machanic’s blog on the matter:
http://sqlblogs.com/adam_machanic/parallelism/The default value of the cost threshold for parallelism option works well for most SQL Servers. Change
the default value only after performing thorough testing and considering other performance tuning opportunities.However, if you insist on changing the default value of the
cost threshold for parallelism option, then you can either use Management
Studio or T-SQL code. In Management Studio, the cost threshold for parallelism
option can be set by entering the desired value in the Cost Threshold for Parallelism box on
the Server Properties Advanced tab (refer to Figure 19-10 later in the chapter).The following code sets the cost threshold for parallelism option to 30 seconds.EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'cost threshold for parallelism', 30;
RECONFIGURE;c19.indd 495c19.indd 495 7/30/2012 5:42:58 PM7/30/2012 5:42:58 PM
http://www.it-ebooks.info