Question about NT fibers

We have an standar 4-SMP MSSQL Server 2000.
What impact has the option NT Fibers on performance?
Under what circunstances is appropiate use it?
Than in advanceDO NOT USE THEM!!
You will lose lots of useful functionality.
"Miguel Castanuela" <maac2002@.hotmail.com> wrote in message
news:uM9L3ip3FHA.716@.tk2msftngp13.phx.gbl...
> We have an standar 4-SMP MSSQL Server 2000.
> What impact has the option NT Fibers on performance?
> Under what circunstances is appropiate use it?
> Than in advance
>|||I can't say you'll lose functionality but I can say I've never had a
situation where they improved performance.
"Bud Y. Zer" <bud@.Gibralter.Bra> wrote in message
news:e0tgq3r3FHA.1188@.TK2MSFTNGP12.phx.gbl...
> DO NOT USE THEM!!
> You will lose lots of useful functionality.
>
> "Miguel Castanuela" <maac2002@.hotmail.com> wrote in message
> news:uM9L3ip3FHA.716@.tk2msftngp13.phx.gbl...
>> We have an standar 4-SMP MSSQL Server 2000.
>> What impact has the option NT Fibers on performance?
>> Under what circunstances is appropiate use it?
>> Than in advance
>>
>|||"Miguel Castanuela" <maac2002@.hotmail.com> wrote in message
news:uM9L3ip3FHA.716@.tk2msftngp13.phx.gbl...
> We have an standar 4-SMP MSSQL Server 2000.
> What impact has the option NT Fibers on performance?
> Under what circunstances is appropiate use it?
> Than in advance
>
Here is some additional information from an article on Performance Auditing.
I believe the author was McGehee.
Lightweight Pooling
SQL Server 7.0 and 2000, by default, run in what is called "thread mode."
What this means is that SQL Server uses what are called UMS (User Mode
Schedulers) threads to run user processes. SQL Server will create one UMS
thread per processor, with each one taking turns running the many user
processes found on a busy SQL Server. For optimum efficiency, the UMS
attempts to balance the number of user processes run by each thread, which
in effect tries to evenly balance all of the user processes over all the
CPUs in the server.
SQL Server also has an optional mode it can run in, called fiber mode. In
this case, SQL Server uses one thread per processor (like thread mode), but
the difference is that multiple fibers are run within each thread. Fibers
are used to assume the identity of the thread they are executing and are
non-preemptive to other SQL Server threads running on the server. Think of a
fiber as a "lightweight thread," which, under certain circumstances, takes
less overhead than standard UMS threads to manage. Fiber mode is turned on
and off using the "lightweight pooling" SQL Server configuration option. The
default value is "0", which means that fiber mode is turned off.
So what does all this mean? Like everything, there are pros and cons to
running in one mode over another. Generally speaking, fiber mode is only
beneficial when all of the following conditions exist:
a.. Two or more CPUs are found on the server (the more the CPUs, the
larger the
benefit).
b.. All of the CPUS are running near maximum (90-100%) most of the time.
c.. There is a lot of context switching occurring on the server (as
reported
by the Performance Monitor System Object: Context Switches/sec. Generally
speaking, more than 5,000 context switches per second is considered high.
d.. The server is making little or no use of distributed queries or
extended stored procedures.
If all the above are true, then turning on the "lightweight pooling" option
in SQL Server may see a 5% or greater boost in performance.
But if the four circumstances are all not true, then turning on "lightweight
pooling" could actually degrade performance. For example, if your server
makes use of many distributed queries or extended stored procedures, then
turning on "lightweight pooling" will definitely cause a problem because
they cannot make use of fibers, which means that SQL Server will have to
switch back-and-forth from fiber mode to thread mode as needed, which hurts
SQL Server's performance.
As with the other settings, if you find in your audit that this setting is
some value other than the default, try to find out why. In addition, check
to see if the four conditions above exist. If they do, then turning
"lightweight pooling" on may be beneficial. If these four conditions do not
exist, then use the default value of "0".
Rick Sawtell
MCT, MCSD, MCDBA