Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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

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

Additionally...

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

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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