SQL Server database threads and fibers

I. Basic CONCEPTS

(i) thread

SQL Server uses the operating system's threads to perform concurrent tasks. Without a fiber, SQL Server starts the thread and assigns the thread to the CPU by the OS, thread management is controlled by the OS kernel, and a context switch occurs when one thread completes exiting the CPU and another thread schedules the CPU. This switch is between the user mode of the application and the thread-managed kernel mode, so it pays a price and should try to avoid this switch

(ii) Fiber process

In order to reduce context switching, the introduction of the concept of fiber, you can enable fiber in SQL Server, which is a child of a thread, which is managed by code running in user mode, so switching fiber is much less costly than switching processes, because fiber mode does not need to switch between user and kernel mode. Switching threads requires this conversion.

(iii) The difference between the two

SQL Server Management fiber scheduling, while OS management thread scheduling; in thread mode SQL Server creates a thread for each concurrent user, while in fiber mode SQL Server assigns a thread to each CPU and creates a fiber for each concurrent user, A thread can have more than one fiber, the fiber only in the thread switch, without context switching

NOTE: SQL Server Personal Edition and Desktop Edition do not support fiber mode; WIN95,98 does not support thread pooling

Ii. SQL Server Management of threads and fibers

SQL Server can maintain a thread pool for user connections. You can also maintain a fiber pool (if you set the fiber mode, the system maintains only the fiber pool, not the thread pool) in the connection pool, threads and fibers are considered as working fibers, and the max worker threads option can be used to set SQL The maximum number of threads or fibers that can be allocated by the server, typically with a default value of 255.

When a statement requires SQL Server execution:

(1) If the line Cheng Chili with an idle thread, SQL Server assigns a thread to the statement

(2) SQL Server creates a new worker thread for the command if there is no idle thread in the thread pool and the maximum number of worker threads is not reached

(3) If the maximum number of threads is used and there is no idle thread, the process must wait for the other processing to complete and release a thread

III. Configuring SQL Server thread Fiber

(1) How to enable fiber mode:

Enterprise Manager-> Server Properties-> processor-> Use Windows NT fiber or set lightweight pooling to 1 with sp_configure

(2) max worker threads option

This option configures the number of worker threads available for SQL Server processes by default of 255

Change the method:

Enterprise Manager-> Server Properties-> processor-> maximum worker thread or modify max worker threads with sp_configure

(3) Affinity mask option

This option increases the performance of the Chenduo processor system when the system is overloaded, relates the thread to a specific processor, and specifies the processor used by SQL Server

Change the method:

Enterprise Manager-> Server Properties-> Processor-> Processor control-> Select one or more processors that combine affinity masks or modify sp_configure with affinity mask

(4) max degree of parallelism options

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

Change the method:

Enterprise Manager-> Server Properties-> processor-> Parallel-> Select the number of processors to perform parallel queries or modify Max sp_configure of degree with parallelism.