From: jmcl4video on
We are having a problem with one of our SQL servers, and in comparing
it to the backup server which is working fine, I noticed some
differences. I attempted to correct the differences, but no luck.

The dell server has 4 dual-core processors and at one point hyper-
threading was enabled. One of our DBAs recommended that the SAs turn
off the Hyper-Threading which they did. We didn't have any major
problems until recently and it seems that getting this setting right
(Affinity Mask) is the lynchpin. I am not sure how to correct the
problem or get the affinity mask to match that of the system.
Any suggestions?



John





EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH
OVERRIDE

GO

EXEC sys.sp_configure N'affinity mask', N'0'

GO

EXEC sys.sp_configure N'affinity I/O mask', N'0'

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH
OVERRIDE

GO



-----------------------------------------
ERROR


Configuration option 'show advanced options' changed from 0 to 1. Run
the RECONFIGURE statement to install.

Msg 5832, Level 16, State 1, Line 1

The affinity mask specified does not match the CPU mask on this
system.

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51

The configuration option 'affinity mask' does not exist, or it may be
an advanced option.

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51

The configuration option 'affinity I/O mask' does not exist, or it may
be an advanced option.

Msg 5832, Level 16, State 1, Line 1

The affinity mask specified does not match the CPU mask on this
system.

Configuration option 'show advanced options' changed from 1 to 0. Run
the RECONFIGURE statement to install.

Msg 5832, Level 16, State 1, Line 1

The affinity mask specified does not match the CPU mask on this
system.
From: Andrew J. Kelly on
You should not be using affinity mask at all unless you know exactly what
the consequences are and that it will indeed help. If they wanted to turn
off HT they should do it fromt he BIOS not the affinity mask. I would reset
it back to the default.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


<jmcl4video(a)juno.com> wrote in message
news:68e575ce-26a7-452c-bdb4-57564ed3bff9(a)w28g2000hsf.googlegroups.com...
> We are having a problem with one of our SQL servers, and in comparing
> it to the backup server which is working fine, I noticed some
> differences. I attempted to correct the differences, but no luck.
>
> The dell server has 4 dual-core processors and at one point hyper-
> threading was enabled. One of our DBAs recommended that the SAs turn
> off the Hyper-Threading which they did. We didn't have any major
> problems until recently and it seems that getting this setting right
> (Affinity Mask) is the lynchpin. I am not sure how to correct the
> problem or get the affinity mask to match that of the system.
> Any suggestions?
>
>
>
> John
>
>
>
>
>
> EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH
> OVERRIDE
>
> GO
>
> EXEC sys.sp_configure N'affinity mask', N'0'
>
> GO
>
> EXEC sys.sp_configure N'affinity I/O mask', N'0'
>
> GO
>
> RECONFIGURE WITH OVERRIDE
>
> GO
>
> EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH
> OVERRIDE
>
> GO
>
>
>
> -----------------------------------------
> ERROR
>
>
> Configuration option 'show advanced options' changed from 0 to 1. Run
> the RECONFIGURE statement to install.
>
> Msg 5832, Level 16, State 1, Line 1
>
> The affinity mask specified does not match the CPU mask on this
> system.
>
> Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
>
> The configuration option 'affinity mask' does not exist, or it may be
> an advanced option.
>
> Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
>
> The configuration option 'affinity I/O mask' does not exist, or it may
> be an advanced option.
>
> Msg 5832, Level 16, State 1, Line 1
>
> The affinity mask specified does not match the CPU mask on this
> system.
>
> Configuration option 'show advanced options' changed from 1 to 0. Run
> the RECONFIGURE statement to install.
>
> Msg 5832, Level 16, State 1, Line 1
>
> The affinity mask specified does not match the CPU mask on this
> system.

From: jmcl4video on
On Nov 20, 3:32 pm, "Andrew J. Kelly" <sqlmvpnooos...(a)shadhawk.com>
wrote:
> You should not be using affinity mask at all unless you know exactly what
> the consequences are and that it will indeed help. If they wanted to turn
> off HT they should do it fromt he BIOS not the affinity mask. I would reset
> it back to the default.
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> <jmcl4vi...(a)juno.com> wrote in message
>
> news:68e575ce-26a7-452c-bdb4-57564ed3bff9(a)w28g2000hsf.googlegroups.com...
>
>
>
> > We are having a problem with one of our SQL servers, and in comparing
> > it to the backup server which is working fine, I noticed some
> > differences. I attempted to correct the differences, but no luck.
>
> > The dell server has 4 dual-core processors and at one point hyper-
> > threading was enabled. One of our DBAs recommended that the SAs turn
> > off the Hyper-Threading which they did. We didn't have any major
> > problems until recently and it seems that getting this setting right
> > (Affinity Mask) is the lynchpin. I am not sure how to correct the
> > problem or get the affinity mask to match that of the system.
> > Any suggestions?
>
> > John
>
> > EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH
> > OVERRIDE
>
> > GO
>
> > EXEC sys.sp_configure N'affinity mask', N'0'
>
> > GO
>
> > EXEC sys.sp_configure N'affinity I/O mask', N'0'
>
> > GO
>
> > RECONFIGURE WITH OVERRIDE
>
> > GO
>
> > EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH
> > OVERRIDE
>
> > GO
>
> > -----------------------------------------
> > ERROR
>
> > Configuration option 'show advanced options' changed from 0 to 1. Run
> > the RECONFIGURE statement to install.
>
> > Msg 5832, Level 16, State 1, Line 1
>
> > The affinity mask specified does not match the CPU mask on this
> > system.
>
> > Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
>
> > The configuration option 'affinity mask' does not exist, or it may be
> > an advanced option.
>
> > Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
>
> > The configuration option 'affinity I/O mask' does not exist, or it may
> > be an advanced option.
>
> > Msg 5832, Level 16, State 1, Line 1
>
> > The affinity mask specified does not match the CPU mask on this
> > system.
>
> > Configuration option 'show advanced options' changed from 1 to 0. Run
> > the RECONFIGURE statement to install.
>
> > Msg 5832, Level 16, State 1, Line 1
>
> > The affinity mask specified does not match the CPU mask on this
> > system.- Hide quoted text -
>
> - Show quoted text -

1. They did turn off HT from the BIOS.
2. I am trying to set it back to the default, but keep getting the
error "The affinity mask specified does not match the CPU mask on
this
system."
From: TheSQLGuru on
1) Try without the ticks around the zero (i.e. let it be numeric not
string).

2) try setting it to 7, which would be all 8 ACTUAL cpus in your 4 dual core
setup. set it to 4 if the cpus are really single core HT cpus and you now
have HT off.

3) try doing reconfigure after each setting change (just reaching here :)

--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.


<jmcl4video(a)juno.com> wrote in message
news:1c61a9b3-065a-49c1-bf78-3a7ddadd11ca(a)41g2000hsh.googlegroups.com...
> On Nov 20, 3:32 pm, "Andrew J. Kelly" <sqlmvpnooos...(a)shadhawk.com>
> wrote:
>> You should not be using affinity mask at all unless you know exactly what
>> the consequences are and that it will indeed help. If they wanted to turn
>> off HT they should do it fromt he BIOS not the affinity mask. I would
>> reset
>> it back to the default.
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> <jmcl4vi...(a)juno.com> wrote in message
>>
>> news:68e575ce-26a7-452c-bdb4-57564ed3bff9(a)w28g2000hsf.googlegroups.com...
>>
>>
>>
>> > We are having a problem with one of our SQL servers, and in comparing
>> > it to the backup server which is working fine, I noticed some
>> > differences. I attempted to correct the differences, but no luck.
>>
>> > The dell server has 4 dual-core processors and at one point hyper-
>> > threading was enabled. One of our DBAs recommended that the SAs turn
>> > off the Hyper-Threading which they did. We didn't have any major
>> > problems until recently and it seems that getting this setting right
>> > (Affinity Mask) is the lynchpin. I am not sure how to correct the
>> > problem or get the affinity mask to match that of the system.
>> > Any suggestions?
>>
>> > John
>>
>> > EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH
>> > OVERRIDE
>>
>> > GO
>>
>> > EXEC sys.sp_configure N'affinity mask', N'0'
>>
>> > GO
>>
>> > EXEC sys.sp_configure N'affinity I/O mask', N'0'
>>
>> > GO
>>
>> > RECONFIGURE WITH OVERRIDE
>>
>> > GO
>>
>> > EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH
>> > OVERRIDE
>>
>> > GO
>>
>> > -----------------------------------------
>> > ERROR
>>
>> > Configuration option 'show advanced options' changed from 0 to 1. Run
>> > the RECONFIGURE statement to install.
>>
>> > Msg 5832, Level 16, State 1, Line 1
>>
>> > The affinity mask specified does not match the CPU mask on this
>> > system.
>>
>> > Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
>>
>> > The configuration option 'affinity mask' does not exist, or it may be
>> > an advanced option.
>>
>> > Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
>>
>> > The configuration option 'affinity I/O mask' does not exist, or it may
>> > be an advanced option.
>>
>> > Msg 5832, Level 16, State 1, Line 1
>>
>> > The affinity mask specified does not match the CPU mask on this
>> > system.
>>
>> > Configuration option 'show advanced options' changed from 1 to 0. Run
>> > the RECONFIGURE statement to install.
>>
>> > Msg 5832, Level 16, State 1, Line 1
>>
>> > The affinity mask specified does not match the CPU mask on this
>> > system.- Hide quoted text -
>>
>> - Show quoted text -
>
> 1. They did turn off HT from the BIOS.
> 2. I am trying to set it back to the default, but keep getting the
> error "The affinity mask specified does not match the CPU mask on
> this
> system."


From: TheSQLGuru on
Correction - use 3 if the CPUs are really single core HTs.

--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.


"TheSQLGuru" <kgboles(a)earthlink.net> wrote in message
news:13k6nf2hlf2u912(a)corp.supernews.com...
> 1) Try without the ticks around the zero (i.e. let it be numeric not
> string).
>
> 2) try setting it to 7, which would be all 8 ACTUAL cpus in your 4 dual
> core setup. set it to 4 if the cpus are really single core HT cpus and
> you now have HT off.
>
> 3) try doing reconfigure after each setting change (just reaching here :)
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
>
> <jmcl4video(a)juno.com> wrote in message
> news:1c61a9b3-065a-49c1-bf78-3a7ddadd11ca(a)41g2000hsh.googlegroups.com...
>> On Nov 20, 3:32 pm, "Andrew J. Kelly" <sqlmvpnooos...(a)shadhawk.com>
>> wrote:
>>> You should not be using affinity mask at all unless you know exactly
>>> what
>>> the consequences are and that it will indeed help. If they wanted to
>>> turn
>>> off HT they should do it fromt he BIOS not the affinity mask. I would
>>> reset
>>> it back to the default.
>>>
>>> --
>>> Andrew J. Kelly SQL MVP
>>> Solid Quality Mentors
>>>
>>> <jmcl4vi...(a)juno.com> wrote in message
>>>
>>> news:68e575ce-26a7-452c-bdb4-57564ed3bff9(a)w28g2000hsf.googlegroups.com...
>>>
>>>
>>>
>>> > We are having a problem with one of our SQL servers, and in comparing
>>> > it to the backup server which is working fine, I noticed some
>>> > differences. I attempted to correct the differences, but no luck.
>>>
>>> > The dell server has 4 dual-core processors and at one point hyper-
>>> > threading was enabled. One of our DBAs recommended that the SAs turn
>>> > off the Hyper-Threading which they did. We didn't have any major
>>> > problems until recently and it seems that getting this setting right
>>> > (Affinity Mask) is the lynchpin. I am not sure how to correct the
>>> > problem or get the affinity mask to match that of the system.
>>> > Any suggestions?
>>>
>>> > John
>>>
>>> > EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH
>>> > OVERRIDE
>>>
>>> > GO
>>>
>>> > EXEC sys.sp_configure N'affinity mask', N'0'
>>>
>>> > GO
>>>
>>> > EXEC sys.sp_configure N'affinity I/O mask', N'0'
>>>
>>> > GO
>>>
>>> > RECONFIGURE WITH OVERRIDE
>>>
>>> > GO
>>>
>>> > EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH
>>> > OVERRIDE
>>>
>>> > GO
>>>
>>> > -----------------------------------------
>>> > ERROR
>>>
>>> > Configuration option 'show advanced options' changed from 0 to 1. Run
>>> > the RECONFIGURE statement to install.
>>>
>>> > Msg 5832, Level 16, State 1, Line 1
>>>
>>> > The affinity mask specified does not match the CPU mask on this
>>> > system.
>>>
>>> > Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
>>>
>>> > The configuration option 'affinity mask' does not exist, or it may be
>>> > an advanced option.
>>>
>>> > Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
>>>
>>> > The configuration option 'affinity I/O mask' does not exist, or it may
>>> > be an advanced option.
>>>
>>> > Msg 5832, Level 16, State 1, Line 1
>>>
>>> > The affinity mask specified does not match the CPU mask on this
>>> > system.
>>>
>>> > Configuration option 'show advanced options' changed from 1 to 0. Run
>>> > the RECONFIGURE statement to install.
>>>
>>> > Msg 5832, Level 16, State 1, Line 1
>>>
>>> > The affinity mask specified does not match the CPU mask on this
>>> > system.- Hide quoted text -
>>>
>>> - Show quoted text -
>>
>> 1. They did turn off HT from the BIOS.
>> 2. I am trying to set it back to the default, but keep getting the
>> error "The affinity mask specified does not match the CPU mask on
>> this
>> system."
>
>