Solve conflict between affinity mask and I/O affinity mask?

Question

I executed this on one of our SQL Server machines:

EXEC sp_configure 'show advanced options', 1

And then executed RECONFIGURE. This presented me with the following error message:

The affinity mask specified conflicts with the IO affinity mask specified. Use the override option to force this configuration.

By executing RECONFIGURE WITH OVERRIDE I can indeed force the configuration. But this is not a solution to the conflict mentioned in the error message.

The values in sp_configure are as follows:

name                 minimum        maximum       config_value   run_value
affinity I/O mask    -2147483648    2147483647    126            126
affinity mask        -2147483648    2147483647    126            126

I found some explanation here, but this basically just says "There should not be a conflict", nothing on how to solve it. Could someone knowledgeable please explain to me what this error message actually means and how/if I should try to solve it?

Update 12/12/2012

We couldn't find any reason for the setting of conflicting values for affinity I/O mask and affinity mask. I changed them back to the default values, 0 for both. After the reconfiguration affinity mask is set to 0 immediately, affinity I/O mask apparently requires a restart of the instance.

Answer 1

The 'affinity mask' and 'affinity I/O mask' configuration values of SQL Server should not conflict. Performance may suffer if you choose to affinitize a processor for both SQL Server worker thread scheduling and for I/O processing.

What is meant by the above statement is:

DO NOT CONFIGURE SUCH THAT A PROCESSOR IS SET IN BOTH WORKER AND I/O MASKS!

It's stated in the documentation and is protected from a mistyped attempt by the error message and requirement to specify WITH OVERRIDE to force the change through. That was your warning that something bad could happen.

Msg 5834, Level 16, State 1, Line 1 The affinity mask specified conflicts with the IO affinity mask specified. Use the override option to force this configuration.

IO affinity masking is a rarely used optimisation, which offloads IO completion activity to a dedicated CPU. When set, a hidden scheduler is created that handles only IO operations for that instance. If you configure identical CPU and IO masks, the two schedulers will battle it out for CPU cycles and cause chaos.

The masks you've set are creating exactly that scenario with 1111110 specified for both.

If your intention is the more common requirement to dedicate CPUs to particular instances on a shared server, use just the CPU affinity mask and leave the IO mask as 0 (disabled).

I've encountered a mis-configured server with matching masks only once. I was put in front of it because the logs suddenly started filling up with the message we all dread to see:

I/O requests taking longer than 15 seconds to complete on file...

After resetting the IO mask and restarting, the server behaved perfectly and the error above was never seen again.

Useful references:

• Books Online - affinity I/O mask Server Configuration Option.
• CSS - How it works: IO Affinity Mask - Should I Use It?.