HOW IT WORKS: IO Affinity Mask - Should I Use It?
The IO Affinity mask question has come across my desk several times in the last week so it is time to blog about it again.
The IO Affinity mask is a very targeted optimization for SQL Server. I have only seen 6 cases where the use of it improved performance and was warranted. It is much more applicable to very large database needing high rates of IO on 32 bit systems. Allow me to explain with a simplified example.
If you are running on 32 bit you have a limited amount of RAM so the LazyWriter process could be very busy with buffer pool maintenance as large data scans and such take place. This means the SQL Server could be producing large rates of IO to flush older buffers and supply new buffers to the SQL Server in support of your queries. In contrast a 64 bit system can utilize larger RAM and reduce the IO churn for the same scenario.
Each IO that SQL Server processes requires completion processing. When the IO completes SQL Server has to check for proper completion (bytes transferred, no operating system errors, proper page number, correct page header, checksum is valid, etc…) This takes CPU resources.
IO Affinity was designed to offload the completion CPU resources to a hidden scheduler. When IO Affinity is enabled a hidden scheduler is created with a special lazy writer thread that only does IO operations. This is why documentation tells you to never assign the affinity mask and IO affinity mask to the same schedulers. If you do the schedulers will compete for the same CPU resources, just what you were trying to avoid. When you don't use IO affinity the SQL Server worker handles (posts) the IO and takes care of the IO completion on the scheduler the worker was assigned to.
Customer Scenario: I want Instance #1 to only use CPU #1 on my system so I set affinity mask and IO affinity mask. (WRONG)
REASON: By setting both it results in a context switch for each IO request to a different worker on the hidden scheduler. Just setting the affinity mask would be sufficient as the IO would be processed on the normal scheduler the worker was assigned to already.
The following shows the affinity mask and IO affinity mask assigned to the same scheduler (improper configuration) as they compete for the same CPU resources.
The following shows the proper setup of affinity mask and IO affinity mask.
Notice that in the proper configuration the only SQL Server activity assigned to the IO affinity scheduler is the IO activity. This configuration would assume that the amount of IO activity on the SQL Server is intense enough to consume significant resources on its own CPU. In the vast majority of installations, especially 64 bit, this is simply not the case and IO affinity is not necessary.
Bob Dorr - Principal SQL Server Escalation Engineer
Comments
Anonymous
November 19, 2010
Bob, I think it would be helpful if you showed the configuration dialog in SSMS for setting affinity mask properly. Have you seen cases where turning affinity for cpu 0 off is helpful?Anonymous
December 29, 2010
Hi - we have 2 physical processors and only one licence. SQL Ent running 32 bit version on Windows 2003 64 bit. At the moment both sets of check boxes are checked in the Processors page. Should we uncheck some/all of them. We have 8 cores per physical processor. CPUs 0 to 7 hqave both cehck boxes checked. Is this correct? Should we uncheck half them one each list? thanksAnonymous
January 06, 2011
You don't want them both checked. An easy way to tell this is to run the sp_configure and reconfigure in a query window. When the values overlap a warning is generated. This warning is not presented by the UI and leads to some of the confusion.