Threads/Fibers
-
Are there any general guidelines to the use of "NT Fibers" in SQL Server? Do people recommend them or avoid them like the plague?
-
Ok, first read this from SQL BOL (2000 Updated)
quote:
Thread PoolingThe Microsoft® SQL Server™ error log may display the message:
The working thread limit of 255 has been reached
This message is an informational message and does not indicate any problem with the system.
SQL Server maintains a pool of operating-system threads for executing batches of SQL statements as they arrive from clients. On Microsoft Windows NT®, if the server lightweight pooling configuration option is set to 1, SQL Server maintains a pool of fibers instead of threads; fibers use fewer resources than threads. Using a pool of threads or fibers allows SQL Server to optimize the allocation of processing time when executing multiple SQL statements at the same time. The threads or fibers in this pool are known collectively as the worker threads. For more information, see Thread and Task Architecture.
The number of worker threads is controlled by the max worker threads server configuration option. The default is 255 and rarely needs to be changed.
When a batch of Transact-SQL statements is received from a client, if an existing worker thread is free, it is allocated to execute the batch. If no existing worker threads are free and the number of worker threads is less than max worker threads, a new worker thread is allocated. If no worker threads are free and max worker threads is reached, the new batch waits until an existing worker thread completes its current batch and becomes free. When the number of worker threads reaches max worker threads, SQL Server displays this message:
The working thread limit of 255 has been reached
Having all worker threads allocated does not mean that the performance of SQL Server will degrade. Typically, a new batch has only a short wait for a free thread. Allocating more threads may degrade performance because of the increased work required to coordinate resources among the threads. Many SQL Server systems running in production reach this state and run with very high performance levels.
Now to try to explain.
Imagine that a thread is a element of execution for a program. Now eac threads is given a segment of time on the CPU for processing. The more threads you have the small those segments are and the slower the processor will seem to go (keep in mind the process of changing threads takes processor time as well, this is called context switching). So at some point the number of threads will be too high for the processor to handle and you will start to see errors as threads are dorpped or the machine could even lock up. This is where fibers come in.
Fibers allow multiple processes to run within the same thread. SQL has built into it a control process to handle the internal process of swapping the processes between active on thread and inactive. This allows you to process the same amount of code but with fewer threads. They do have several draw backs and do not function as fast as true threads since there is a user process handling the flow of processing so they are not suggested unless you are maxing the CPU out.
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.
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.
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.
One con seen by some folks is that items thrown into parallel procesing can give unexpected results so you then need to in some cases increase the value for max degree of parallelism.
-
Thanks. For some reason I'd not managed to find the two topics you showed
Sounds to me like the real governing factor lies in the fact that threads are pre-emptive whereas Fibers are not (with respect to each other.) "Don't touch that dial" probably.
quote:
Ok, first read this from SQL BOL (2000 Updated)Now to try to explain.
.
.
.
From MSDN Library (January 2001)
.
.
.
Recommendation:
Leave lightweight pooling off, unless CPUs are saturated (close to 100 percent busy) and you notice a consistently high level of context switching.
-
Yea, that is pretty much it. I at one time was curious on trying fibers in a C++ app I was playing with and when I got to reading I pretty much decided there was no guarantee in what I did unless I am a dran good C++ programmer and I am far from that.
-
quote:
Yea, that is pretty much it. I at one time was curious on trying fibers in a C++ app I was playing with and when I got to reading I pretty much decided there was no guarantee in what I did unless I am a dran good C++ programmer and I am far from that.I'm no judge of that
As for Threads/Fibers in SQL Server, it depends on who you trust the most when it comes to scheduling parallel tasks in the database, the Operating System Authors or the Database Authors... -
There are other implications that were not mentioned here like:
DTS DOESN'T WORK on fiber mode and that is huge for some enviroments.
* Noel -
quote:
There are other implications that were not mentioned here like:DTS DOESN'T WORK on fiber mode and that is huge for some enviroments.
That sounds like a pretty major reason to avoid fibers
, in some circumstances. Thanks for the tip. -
quote:
There are other implications that were not mentioned here like:DTS DOESN'T WORK on fiber mode and that is huge for some enviroments.
I have *not* found this to be the case in our shop (40+ SQL Server machines). NT Fibers are enabled here on most if not all of the servers, and DTS works fine.
However, one SQL Server machine did run into a problem described in this link:
Turning off NT Fibers was a workaround, but then I had a problem with Linked Servers on that machine. I still have a call open with HP support on it.
Jon
Edited by - shew01 on 08/13/2003 11:35:06 AM
-
Thanks for the input Jon,
I think in summary that for some, "Fibers" are to be found in "cans of worms", while for others they come out of "Pandora's box".
quote:
quote:
There are other implications that were not mentioned here like:DTS DOESN'T WORK on fiber mode and that is huge for some enviroments.
I have *not* found this to be the case in our shop (40+ SQL Server machines). NT Fibers are enabled here on most if not all of the servers, and DTS works fine.
However, one SQL Server machine did run into a problem described in this link:
...
Turning off NT Fibers was a workaround, but then I had a problem with Linked Servers on that machine. I still have a call open with HP support on it.
Jon
Edited by - shew01 on 08/13/2003 11:35:06 AM
-
quote:
There are other implications that were not mentioned here like: DTS DOESN'T WORK on fiber mode and that is huge for some enviroments.It is the case that DTC doesn't work with fiber mode:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303287
As for Threads/Fibers in SQL Server, it depends on who you trust the most when it comes to scheduling parallel tasks in the database, the Operating System Authors or the Database Authors...
, in some circumstances. Thanks for the tip.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply