Microsoft® SQL Server® 2012 Bible

493

Chapter 19: Confi guring SQL Server

19

Best Practice

For most SQL Servers the default value of 0 for the lightweight pooling confi guration option gives

the best performance. In fact, changing the value from 0 to 1 may result in decreased performance. If

you do change the lightweight pooling option to 1, then be sure to test it thoroughly and evaluate

all other performance tuning opportunities fi rst.

If you still insist on changing the value of the lightweight pooling option, you can

either use Management Studio or T-SQL-code. In Management Studio, lightweight

pooling can be set to 1 (default is 0 ) by checking the Use Windows fi bers (lightweight

pooling) check box on the Server Properties Processor tab (refer to Figure 19-7 earlier in

this chapter).

In code, to set the lightweight pooling option:

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'lightweight pooling', 1;

RECONFIGURE;

SQL Server service must be restarted for the lightweight pooling option to take effect.

The need for the lightweight pooling option is reduced by improved context switching in Microsoft Windows

Server 2003 and 2008.

The lightweight pooling and clr enabled confi guration options are mutually exclusive. You can use one

of the two options: lightweight pooling or clr enabled. If you disable CLR, features that rely on it (such

as hierarchy data type, replication and Policy-Based Management) will not work properly.

Parallelism

On a multiprocessor server, SQL Server detects the best number of processors that can be

used to run a single statement for each parallel plan. The max degree of parallelism

confi guration option can be used to limit the number of processors to use in a parallel plan

execution.

SQL Server’s query optimizer is a cost-based optimizer, which means it chooses a plan

that returns the results in a reasonable amount of time with a reasonable resource cost.

SQL Server always considers a serial plan fi rst. If this serial plan costs less than the

cost threshold for parallelism value, then no parallel plan is generated. The

cost threshold for parallelism option refers to the cost of the query in seconds

on a specifi c hardware confi guration. If the cheapest serial plan costs more than the

c19.indd 493c19.indd 493 7/30/2012 5:42:56 PM7/30/2012 5:42:56 PM

http://www.it-ebooks.info