Microsoft® SQL Server® 2012 Bible

488

Part V: Enterprise Data Management

Index create memory option is self-confi guring and usually works without additional adjustments. The option to mod-

ify index creation memory is an advanced option and should only be changed by an experienced database adminis-

trator or certifi ed SQL Server technician.

Processor-Confi guration Properties

You can use the processor-confi guration properties (listed in Table 19-3) to control how SQL

Server uses multiprocessor computers.

TABLE 19-3 Processor-Confi guration Properties

Property Level* Graphic Control Code Option

Processors Used S Management Studio EXEC sp_configure

'affinity mask'

Maximum Worker

Threads

S Management Studio EXEC sp_configure 'max

worker threads'

Boost SQL Server Priority

on Windows

S Management Studio EXEC sp_configure 'pri-

ority boost'

Use Windows NT Fibers S Management Studio EXEC sp_configure

'lightweight pooling'

Number of processors for

parallel execution of

queries

S Management Studio EXEC sp_configure 'max

degree of parallelism'

Minimum query plan

threshold for parallel

execution

S Management Studio EXEC sp_configure 'cost

threshold for

parallelism'

* The confi guration level refers to server, database, or connection.

The Processors tab (see Figure 19-7) of the SQL Server Properties page determines how SQL

Server uses multiprocessor computers. Most of these options are moot in a single-processor

server.

Processor Affinity

In a multi-CPU server, the operating system can move processes to CPUs as the load requires.

The SQL Server processor affi nity, or the relationship between a task and a CPU, can be

confi gured on a per-CPU basis. By enabling the affi nity between SQL Server and a CPU, you

make that CPU available to SQL Server, but it is not dedicated to SQL Server. Therefore,

although a CPU can’t be forced to run SQL Server, it can be segmented from SQL Server.

c19.indd 488c19.indd 488 7/30/2012 5:42:53 PM7/30/2012 5:42:53 PM

http://www.it-ebooks.info