Using: SQL Server 2008 R2

Under what circumstances would you set the Affinity Mask and Affinity I/O Mask options in sp_configure in SQL Server?

We are currently running on a machine with 16 cores dedicated to SQL Server. We are also seeing slowness in our I/O operations. Some writes (insert 1 row) can take up to 500 ms. We would like to support many writes per second. The process that inserts data can spawn up to 4 threads for executing the insert operations.

I'm considering dedicating 8 cores to the affinity mask and the other 8 to affinity I/O mask.

Is this recommended?

1 Answer 1


I'd consider many other options before I played with these

Some things to check here on SO: https://stackoverflow.com/a/2009693/27535


  • indexes: are your writes scanning not seeking?
  • key: do you have GUIDs as clustered indexes (bad idea!)
  • triggers: no emails being sent on INSERT or CURSORs?
  • disk layout: separate fast RAIDs for data and logs, per database?
  • autoshrink/autoclose + associated file growth
  • unmaintained statistics and indexes
  • ..

Basically, your problem will be poor design poor indexing or poor hardware...


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.