SQL Server lightweight pooling Option

The server configuration option lightweight pooling controls whether the SQL server instance uses Windows threads or fiber threads. The default value of this option is 0. This means that the SQL server instance plans a Windows Thread for each worker thread, and the number of worker threads cannot exceed the value set in the max worker threads option. If lightweight pooling is set to 1, SQL server uses fiber threads instead of Windows threads. This is called running in fiber mode. In fiber mode, the SQL server instance is used for each SQL planProgramAllocate a Windows Thread and assign a fiber to each worker thread. The number of worker threads cannot exceed the value set in the max worker threads option. SQL Server instances use the same windows thread or fiber pathAlgorithmSchedule and synchronize tasks. SQL Server express does not support fiber routes.

The lightweight pooling option can reduce the system overhead related to excessive context switches that are sometimes encountered in Symmetric Multi-processing (SMP) environments. If too many context switches occur, the light-weight pool can switch to the internal connection through context switches to reduce the conversion frequency of the user/kernel ring and increase the throughput.

Setting lightweight pooling to 1 will switch Microsoft SQL server to the fiber mode plan. The default value of this option is 0.

The lightweight pooling option is an advanced option. If you use the sp_configure system stored procedure to change this setting, you can change lightweight pooling only when show advanced options is set to 1. This setting takes effect after the server is restarted.

The overhead of switching thread context is not great. Most instances of SQL Server do not find any performance difference when the lightweight pooling option is set to 0 or 1. Only SQL Server instances running on computers with the following features may benefit from lightweight pooling:

Large multi-CPU servers.

All CPUs run at nearly the maximum capacity.

There is a high-level context switch.

If the lightweight pooling value is set to 1, the performance of these systems may be slightly improved.

Set the lightweight pooling method (here to disable the instance, you only need to change the 0 to 1 when enabling the method ):

 
Sp_configure 'Allow updates', '1' goreconfigure with overridegosp_configure 'lightweight', '0' gosp_configure 'Allow updates', '0' Gonet stop mssqlservernet start MSSQLServer