|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
The affinity mask specified does not match the CPU mask on this system
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. 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. -- Show quoteHide quoteAndrew J. Kelly SQL MVP Solid Quality Mentors <jmcl4vi***@juno.com> wrote in message news:68e575ce-26a7-452c-bdb4-57564ed3bff9@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.
Show quote
Hide quote
On Nov 20, 3:32 pm, "Andrew J. Kelly" <sqlmvpnooos***@shadhawk.com> 1. They did turn off HT from the BIOS.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***@juno.com> wrote in message > > news:68e575ce-26a7-452c-bdb4-57564ed3bff9@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 - 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." 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 :) -- Show quoteHide quoteKevin G. Boles TheSQLGuru Indicium Resources, Inc. <jmcl4vi***@juno.com> wrote in message news:1c61a9b3-065a-49c1-bf78-3a7ddadd11ca@41g2000hsh.googlegroups.com... > On Nov 20, 3:32 pm, "Andrew J. Kelly" <sqlmvpnooos***@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***@juno.com> wrote in message >> >> news:68e575ce-26a7-452c-bdb4-57564ed3bff9@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." Correction - use 3 if the CPUs are really single core HTs.
-- Show quoteHide quoteKevin G. Boles TheSQLGuru Indicium Resources, Inc. "TheSQLGuru" <kgbo***@earthlink.net> wrote in message news:13k6nf2hlf2u912@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. > > > <jmcl4vi***@juno.com> wrote in message > news:1c61a9b3-065a-49c1-bf78-3a7ddadd11ca@41g2000hsh.googlegroups.com... >> On Nov 20, 3:32 pm, "Andrew J. Kelly" <sqlmvpnooos***@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***@juno.com> wrote in message >>> >>> news:68e575ce-26a7-452c-bdb4-57564ed3bff9@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." > >
Show quote
Hide quote
On Nov 20, 5:24 pm, "TheSQLGuru" <kgbo***@earthlink.net> wrote: Thanks. I tried with and without ticks, with 7 and 0, and individually> Correction - use 3 if the CPUs are really single core HTs. > > -- > Kevin G. Boles > TheSQLGuru > Indicium Resources, Inc. > > "TheSQLGuru" <kgbo***@earthlink.net> wrote in message > > news:13k6nf2hlf2u912@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. > > > <jmcl4vi***@juno.com> wrote in message > >news:1c61a9b3-065a-49c1-bf78-3a7ddadd11ca@41g2000hsh.googlegroups.com... > >> On Nov 20, 3:32 pm, "Andrew J. Kelly" <sqlmvpnooos***@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***@juno.com> wrote in message > > >>>news:68e575ce-26a7-452c-bdb4-57564ed3bff9@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."- Hide quoted text - > > - Show quoted text - as well. No luck I didn't see the rest of your original post since it appeared after your
signature. But you should be able to set it back to the SQL Server default by running this: sp_configure 'affinity mask', 0x00000000 Although I think 0 should have worked as well. If that doesn't work you might try turning HT back on and trying to reset it then turn them back off. -- Show quoteHide quoteAndrew J. Kelly SQL MVP Solid Quality Mentors <jmcl4vi***@juno.com> wrote in message news:bf5ef2f2-8e37-467f-947d-3ffac7ca9488@f3g2000hsg.googlegroups.com... > On Nov 20, 5:24 pm, "TheSQLGuru" <kgbo***@earthlink.net> wrote: >> Correction - use 3 if the CPUs are really single core HTs. >> >> -- >> Kevin G. Boles >> TheSQLGuru >> Indicium Resources, Inc. >> >> "TheSQLGuru" <kgbo***@earthlink.net> wrote in message >> >> news:13k6nf2hlf2u912@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. >> >> > <jmcl4vi***@juno.com> wrote in message >> >news:1c61a9b3-065a-49c1-bf78-3a7ddadd11ca@41g2000hsh.googlegroups.com... >> >> On Nov 20, 3:32 pm, "Andrew J. Kelly" <sqlmvpnooos***@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***@juno.com> wrote in message >> >> >>>news:68e575ce-26a7-452c-bdb4-57564ed3bff9@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."- Hide quoted text - >> >> - Show quoted text - > > Thanks. I tried with and without ticks, with 7 and 0, and individually > as well. No luck  
Other interesting topics
Snapshot size
SQL Server 2005: log shipping SQL Server Transaction Log growing need urgent help Why sql server always choose index scan instead of index seek for my query? Creating indexes ms sql cluster: should i and how to place tempdb on local drive BCP import large text file to sql tables I dont see any hostname in sysprocesses SQL 2000 : space available is zero |
|||||||||||||||||||||||
