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.
