490
Part V: Enterprise Data Management
is not recommended because doing so will cause contention on the CPU for both resources.
The bit corresponding to each CPU should be one of the following:■ (^) 0 for both affinity mask and affinity I/O mask options
■ 1 for affinity mask and 0 for affinity I/O mask option
■ (^) 0 for affinity mask and 1 for affinity I/O mask option
As an example, say on a 8-CPU system, you want to allocate CPUs 0, 1, 2, and 3 for process-
ing SQL Server threads, CPUs 4 and 5 for disk I/O processing, and CPUs 6 and 7 for other
non-SQL Server activities. This means the last 4 bits will be one for the affinity mask
bitmap (00001111) that is 15 in decimal and the fi fth and sixth bits will be one for the
affinity I/O mask (00110000) that is 48 in decimal.
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'affinity mask', 15;
RECONFIGURE;
EXEC sp_configure 'affinity I/O mask', 48;
RECONFIGURE;
The affi nity mask setting takes effect immediately without requiring a restart of the SQL
Server service whereas the affinity I/O mask setting takes effect only after restarting
the SQL Server service.
The default value of 0 for the affinity mask option indicates that all the processors on the server are available
for processing SQL Server threads. The default value of 0 for the affinity I/O mask option indicates that any
CPUs that are eligible to process SQL Server threads are available for disk I/O processing.
The affi nity mask feature will be removed in a future version of Microsoft SQL Server. It is advised that you do not use
this feature in new development work.
In Management Studio, processor affi nity is confi gured by means of the check boxes on the
Server Properties Processors tab (refer to Figure 19-7).
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'affinity mask', 3;
RECONFIGURE;
Affi nity support for SQL Servers with 33 to 64 processors is available only on 64-bit SQL Servers and requires the
additional use of affinity64 mask and affinity64 I/O mask confi guration options.
c19.indd 490c19.indd 490 7/30/2012 5:42:55 PM7/30/2012 5:42:55 PM
http://www.it-ebooks.info