SQL Server - Processor Settings - Part 2 - Processor affinity mask and others

Welcome back everyone to second and last part of our processor settings series. Though it was not as soon as I expected it to be, but as saying goes, better late than never. 
It is important to cover these options\settings because unlike the settings we discussed in part 1 (like MAXDOP etc.) of this series, tweaking these options can have a huge negative impact on system, so it is better to have more details about them so that we can be more clear whether to proceed or not.

First up is the "Processor Affinity Mask". In the below image you can see a brief description of "Processor affinity mask" directly out from Books Online\MSDN.

Image 1

So, long story short, by using "processor affinity mask" (or I/O affinity mask, both go in conjunction) option in multi processor environment, you can decide which processor or core only should be used for processing the query (only) and which can be used for other tasks (like I/O) hence reducing the context switching as now each processor\core know what kind of work it has to do instead of catering to all types of threads\processes as they come.

Default look of Processor Options

Setting Processor and I/O affinity mask

But like every options we have discussed earlier, here also we have few catches. The first point is in the definition itself. As we have seen in the "Image 1" above, the first line itself says in "Microsoft Windows 2000 and Server 2003", which means these are kind of legacy options and with each iteration of windows the context switching behaviour improves and options like these will become less relevant and it goes for all other options which we going to discuss later in this article. Second point, is that it already bound to remove in future version and new T-SQL option is available for the same which you should use instead of the GUI mode. Also, unlike the GUI option, the T-SQL version can handle more than 64 CPUs\cores. The link for the same can found here. Third catch is that this option may not benefit you on virtual infra, why, because though your OS may understand what restrictions your SQL Server has implied on the usage of cores\processor to reduce context switching but in the background, the processors available to a particular VM (virtual machine\server) comes from a pool of processors assigned to a VM Host (a server hosting multiple VMs) and same distinction may not happen at this level (will depend upon your VM infra).

So, considering all above points the obvious question comes to our mind is when to use it and this is where things become tricky. Although it is easier to conclude that this option is best suited for physical boxes as you use what you have, but even that also requires thorough testing with continuous benchmarking to see whether things have actually improved. And same goes for virtual infra as well however you need to check with Microsoft and VM vendors though for the same. In case it not supported or you don't see any improvement at SQL end you may have to seek for better hardware options to reduce the context switching load (like higher clock speed processor for the physical box serving as VM host).

Now, let us quickly cover the remaining three options. Just like the one above these options are there for legacy support only and are better left untouched, why, lets see one by one.

First (of these three) is "Max Worker thread", which defaults at "0", which means SQL Server will itself decide at run-time how many threads it requires depending upon the load(isn't it a good thing). This option was basically created so that we can change the same once we upgrade our SQL 2000 server to 2005 or 2008 as in SQL 2000 it was just 255. Though we have table available to refer but I would strongly advice to keep it "0" only in order to avoid various worker thread related issues which may come afterwards.

Next in line is "Boost SQL Priority". This option is also bound to be removed (for good) from future version of SQL (but still there in 2014). This option was introduced for machines running with multiple applications including MS SQL Server and where you want your OS to give SQL priority over others when it comes to CPU demand for processing. But, the big problem with this option is that there is no way you control the priority base index and once you check it on, SQL process will start running with priority base 13 instead of default 0 (which to OS means priority base 7) and that is in league of system processes. Now if you go through books online it also have a very alarming statement which goes as: "Raising the priority too high may drain resources from essential operating system and network functions, resulting in problems shutting down SQL Server or using other operating system tasks on the server.". As we know SQL can be very demanding and if it start interfering with OS processes we can run into bigger problems which we haven't expected, so it is better to leave this option off. Read more about it here.

Last in the lot is Windows Fibers or Lightweight Pooling. Windows Fibers or Lightweight pooling is introduced to tackle performance bottleneck issues occurs due to excessive "context switching" and poor handling of the same in some environment. Though there is no wording of it getting removed in near future, this is again kind of legacy option having full support in Windows Server 2003. But again it also have few catches here like (first) SQL's CLR functions and execution are not compatible with it, so be very carefully before turning it on especially in PROD as SQL will starting refusing connection even from SSMS and you'll have a lot of work and explaining to do in order tackle the situation. Secondly, this option is only helpful for particular and rare case of context switching bottleneck and the way this is handled has been improved a lot in Windows Server 2008 and with each iteration of windows Server we expect it to improve only, hence we will see even less number of cases where this would actually be required.

So friends, this it, with this post we have covered the remaining in-built processor options and as we can see these are the options which are better left untouched and even in any case they are required to be changed then that very case needs to be tested and discussed thoroughly before implementation.

Please don't forget to share in case you like the article.