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