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?


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...

  • I had a PSS engineer tell me once that GUIDs, while being unique, are intended to be random in nature, so did not recommend them as index choices. – jl01 Feb 7 '12 at 15:30
  • 1
    @jl01: correct, not good as clustered index. Will clarify my meaning, thanks – gbn Feb 7 '12 at 15:31
  • Extremely thorough list. Thank you very much. I'll try to hit these items one-by-one. – Nick Vaccaro Feb 7 '12 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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