SQL Server CPU Affinity – Difference Between Task Manager and SSMS

cpusql server

What's the difference of setting up CPU affinity between by Server's Task Manager and by SSMS?
Which one is more reliable?

Thanks in advance.

Best Answer

Update based on comment from Max Vernon.

Setting the affinity mask with Task Manager will only have effect until SQL Server is restarted. Using sp_configure will ensure the change remains effective across restarts.

Using task manager make sure you pick the right process especially if you have multiple SQL instance. With SSMS it is obvious which one you are changing.

Microsoft recommend to use:

sp_configure 'show advanced options', 1;  
RECONFIGURE;  
GO  
sp_configure 'affinity mask', 38;  
RECONFIGURE;  
GO

Details here.

Word of caution from the same article.

Do not configure CPU affinity in the Windows operating system and also configure the affinity mask in SQL Server. These settings are attempting to achieve the same result, and if the configurations are inconsistent, you may have unpredictable results. SQL Server CPU affinity is best configured using the sp_configure option in SQL Server.

Related Question