SQL Server database thread and Fiber

I. Basic Concepts

(1) threads

SQL Server uses the operating system thread to execute concurrent tasks. If no fiber is used, SQL Server will start the thread and allocate the thread to the CPU by the OS. thread management is controlled by the OS kernel. When a thread completes the process of exiting the CPU, when other thread scheduling occupies the CPU, a context switch will occur. This switch is between the user mode of the application and the kernel mode managed by the thread. Therefore, a certain price is required, avoid such switching as much as possible

(2) optical fiber

To reduce context switching and introduce the fiber process concept, enable the Fiber Process in SQL Server. The fiber process is a sub-module of the thread and is managed by the code running in user mode, therefore, it is much less costly to switch the fiber process than to switch the process, because the fiber mode does not need to be switched between the user mode and the kernel mode, and the switch thread needs this conversion.

(3) differences between the two

SQL Server manages the scheduling of fiber processes, while OS management threads. In thread mode, SQL Server creates a thread for each concurrent user, in fiber mode, SQL Server allocates a thread to each CPU and creates a fiber thread for each concurrent user. A thread can contain multiple fiber threads, fiber only switches in the thread, without context switching

Note: SQL Server Personal Edition and Desktop Edition do not support fiber mode; Win95 and 98 do not support thread pools.

Ii. Management of threads and fibers by SQL Server

SQL Server can maintain both a thread pool and a fiber pool for user connection (if the fiber Pool Mode is set, the system only maintains the fiber pool, not the thread pool) in the connection pool, both the thread and the fiber are considered as work fiber threads, and the max worker threads option can be used to set the maximum number of threads or fiber threads that SQL Server can allocate. Generally, the default value is 255.

When a statement needs to be executed by SQL Server:

(1) If Idle threads are used in the thread pool, SQL Server allocates a thread for the statement.

(2) If there are no Idle threads available in the thread pool and the maximum number of working threads is not reached, SQL Server will create a new working thread for this command.

(3) If the maximum number of threads is used and there are no Idle threads, the process must wait until other processing is completed to release a thread.

3. Configure the SQL Server thread Fiber

(1) how to enable the fiber path mode:

Enterprise Manager-> server properties-> processor-> use Windows nt fiber or use sp_configure to set lightweight pooling to 1

(2) max worker threads Option

This option is used to configure the number of available working threads of the SQL Server process. The default value is 255.

Change method:

Enterprise Manager-> server properties-> processor-> maximum worker thread or use sp_configure to modify max worker threads

(3) affinity mask Option

This option can improve the performance of the symmetric multi-processor system when the system load is too heavy, associate the thread with a specific processor, and specify the processor used by the SQL Server

Change method:

Enterprise Manager-> server properties-> processor-> Processor Control-> select one or more processors that combine the affinity masks or use sp_configure to modify affinity mask

(4) max degree of parallelism options

This option can limit the number of processors used in parallel execution.

Change method:

Enterprise Manager-> server properties-> processor-> parallelism-> select the number of processors that execute parallel queries or use sp_configure to modify max degree of parallelism.