SQL Server CPU Affinity Mask option

Hi Friends,

Yesterday, I was reading some interesting stuff about schedulers, threads, fibers, etc. In this context, I thought of writing something about Affinity Mask option in SQL Server. But before that, let me give you some background:

In SQL Server DB engine, every workload is a task or a request. A worker is assigned or bound to the request which schedules the request on a particular scheduler. A scheduler is the logical CPU. There is one scheduler per CPU, by default. Well, to be more precise, the scheduler receives the request and creates workers as required. A worker could be a thread or a fiber bound to a scheduler.


Now, here comes the concept of affinity. By default, SQL Server will use all the available CPUs. If the Affinity Mask option is set, then each scheduler is affinitized to a particular CPU – which means there is a hard binding (one-to-one). This is particularly useful in scenarios where you have a server with 32 CPUs and you have 4 instances of SQL Server instances running on it. You want that each instance to use their own set of 8 CPUs. You can now configure the affinity mask option for each instance; either using sp_configure or using SSMS. With the correct settings; each instance will use its own set of 8 CPUs and within each instance also, there will be a hard binding between the scheduler and the CPU.

Now, suppose you want to bind each instance with their own set of 8 CPUs but within the instance you don’t want a hard binding between the scheduler and CPU (meaning the scheduler can run on any CPU); you can start SQL Server with trace flag 8002.

I know I have over-simplified the entire concept but just wanted to give you a brief over view here.  I plan to play with a few stress tools in the coming week and will write more on CPUs.




About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

Leave a Reply

Your email address will not be published.