May 19, 2003 at 12:11 am
Here is a excerpt from microsoft site. "Improvements in Windows Server 2003 Fiber Mode features permit faster application concurrency with SQL Server 2000 when SQL Server 2000 is run in 'Fiber Mode.' ". What is this Fiber Mode. Is it new feature in 64-bit one?
mcp mcse mcsd mcdba
May 19, 2003 at 4:05 am
Fibers (AKA Lightweight pooling) have been around for a while. I know all the way to Windows 3.51 SP3, but I am not sure when SQL started using except I know 7 and 2000 have.
Basically a Fiber is multiple pieces of code set on a single thread to execute and controlled by an internal piece of code written in the SQL executable and not by Windows itself.
From MSDN Library January - 2001
quote:
SQL Server ThreadingThe threading model used by SQL Server affects how efficiently the processors are used. This includes how many concurrent users it can handle and how quickly each of the users gets processing power.
(Image was removed for posting)
SQL Server 6.5 uses native Window NT threads. The Windows NT kernel uses its own scheduler to manage the SQL Server thread scheduling as well as synchronization services. Although this process has produced high concurrency on a small number of processors, it does have limitations. At high loads, the thread context switching has some overhead. (Although the implementation of I/O completion ports aided significantly here). Because SQL Server lets Windows NT manage the threads, it has little control over which threads are preempted on and off of the processor and how often. The Windows NT kernel scheduler and dispatch lock periodically sees contention with a large number of threads. Although this contention can exist, SQL Server 6.5 has achieved more than 5,000 concurrent users on a four-way processor in TPC-C testing.
SQL Server 7.0 has improved the threading model.
(Image was removed for posting)
The most important enhancement is the replacement of the Windows NT kernel schedule with the Microsoft SQL Server scheduler called User Mode Schedulers (UMS). SQL Server has one UMS for every processor. This UMS controls the scheduling of fibers and/or thread requests.
Fibers are a new Windows NT feature exploited in SQL Server 7.0, and are useful on CPU-busy systems with high-context switching. Fibers are lightweight and run on top of native Windows NT threading as shown in the preceding illustration. SQL Server now uses only one Windows NT thread per processor and many lightweight fibers on top of that thread. Fibers automatically assume the identity of the Windows NT thread they are running on and are nonpreemptive with respect to other SQL Server threads running on the other processors. Fibers, called lightweight pooling, should be turned on only if CPU is 100 percent and context switching is high.
The most CPU-intensive database applications benefit from this added thread scheduling flexibility. You now have the ability to move to an even higher number of concurrent users and achieve greater transaction throughput.
Recommendation:
Leave lightweight pooling off, unless CPUs are saturated (close to 100 percent busy) and you notice a consistently high level of context switching.
May 19, 2003 at 11:38 am
This I believe is was written at Microsoft, author unknown.
SQL Server Task Scheduling
Microsoft® SQL Server™ uses Microsoft Windows® threads, and sometimes fibers, to execute several concurrent tasks efficiently. SQL Server version 7.0 always runs several threads for system processes: one or more threads for each server Net-Library, a network thread to handle login requests, and a signal thread for communicating with the service control manager.
SQL Server has an internal layer that implements an environment similar to an operating-system for scheduling and synchronizing concurrent tasks without having to call the Windows kernel. This internal layer can schedule fibers as effectively as it works with threads. SQL Server maintains a pool of either threads or fibers for user connections. The maximum size of this pool is controlled by the max worker threads server configuration option.
The server configuration lightweight pooling option controls whether SQL Server uses threads or fibers. The default is for lightweight pooling to be set to 0, in which case SQL Server schedules a thread per concurrent user command, up to the value of max worker threads. If lightweight pooling is set to 1, then SQL Server uses fibers instead of threads. This is called running in fiber mode. In fiber mode, SQL Server allocates one thread per CPU, then allocates a fiber per concurrent user command, up to the max worker threads value. SQL Server uses the same algorithms to schedule and synchronize tasks when using either threads or fibers. SQL Server Desktop Edition does not support fibers.
A SQL Server batch is a set of one or more Transact-SQL statements sent from a client to SQL Server for execution as a unit. As SQL Server receives batches from clients, it associates each batch with an available free thread or fiber from the worker pool. If there are no free threads or fibers and the max worker threads value has not been reached, SQL Server allocates a new thread or fiber for the new batch. If there are no free threads or fibers available and the max worker threads value has already been reached, the new batch blocks until a thread is freed. After a thread or fiber is associated with a batch, it remains associated with the batch until the last of the result sets generated by the batch has been returned to the client. At that time, the thread or fiber is freed and can be scheduled to the next available batch.
While threads and fibers are lightweight in their use of resources, they still consume resources. In systems with hundreds or thousands of user connections, having one thread or fiber per connection could consume enough resources to reduce the efficiency of SQL Server. Allocating a thread or fiber for each user connection is also not necessary because most connections actually spend much of their time waiting for batches to be received from the client. The worker pool only needs to be large enough to service the number of user connections that are actively executing batches at the same time. Leaving max worker threads at its default value of 255 lets SQL Server effectively map user connections over a number of threads or fibers that do not consume too many resources.
(c) 1988-98 Microsoft Corporation. All Rights Reserved.
John Zacharkan
John Zacharkan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply