SQL Server with Mr. Denny

Apr 12 2012   2:00PM GMT

CPU Affinity Mask and virtualizating SQL Servers



Posted by: Denny Cherry
Tags:
Error Message
Grant Fritchey
Hyper-V
SQL Server
SQL Server 2000
SQL Server 2005
SQL Server 2008
SQL Server 2008 R2

If you have physical SQL Servers that you plan on moving into a virtual environment you’ll want to double check your affinity mask settings before actually moving the machine from a physical server to a VM when using P2V software.  The reason for this is that if the affinity mask is set for specific CPUs and the number of CPU cores changes the affinity mask won’t be correct and you won’t be able to get into the advanced settings of sp_configure without getting an invalid settings error like that shown below.

Msg 5832, Level 16, State 1, Line 1
The affinity mask specified does not match the CPU mask on this system.

If you haven’t P2V’ed the system before you do simply change the various affinity masks to 0 which sets them for all processors.  If you have P2V’ed the system your best option is to log into the SQL Server using the dedicated admin connection and manually change the value in the system table by using the following query.

update sys.configurations
set value=0
Where Name = 'affinity mask'

Hopefully you never run across this problem, but if you do there’s the solution for you.

UPDATE: Paul Randal reminded me that CPU Affinity has been deprecated as of SQL Server 2008 R2 so you’ll probably not want to be configuring the CPU Affinity anyway.

Denny

1  Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Thomasrushton
    There's also the I/O affinity mask... and the 64-bit versions of both. Perhaps it might be better to use: [CODE]WHERE Name LIKE '%affinity%'[/CODE]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: