Windows nt fibers

Question

Windows nt fibers

Juanita

SSCertifiable

Points: 5300
More actions
December 20, 2005 at 2:50 pm

#110504

HI. We are running sql server 2000 standard edition with quad processors on a windows nt operating system. i noticed that on the server properties in enterprise manager the option: BOOST SQL SERVER PRIORITY ON WINDOWS is checked and also USE WINDOWS NT FIBERS is checked. We average about 80 users on a daily basis maybe 100. Should the windows nt fibers be set? how do you determine if you should set it?
Thanks,
Juanita

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 1

From http://www.sql-server-performance.com:

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 may" be beneficial when all of the following circumstances exist:

Eight or CPUs are found on the server (the more the CPUs, the larger the benefit).
All of the CPUS are running near maximum (95-100%) most of the time.
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 20,000 context switches per second is considered high.
The server is making little or no use of distributed queries or extended stored procedures.

If all the above are true, then turning on "lightweight pooling," option in SQL Server may see a small 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.

You will also want to carefully test (before and after) the affect of this setting on your server's performance. While in test scenarios, some DBAs have noticed a boost in performance, in the real world, the benefit is much harder to quantify and identify.

Generally speaking, this option should only be tried in cases where you have tried every other option to boost performance, and there are no more to try.

This option is available under the SQL Server's "Properties", "Processor" tab, in addition to using sp_configure. [7.0, 2000] Updated 8-17-2003

K. Brian Kelley
@kbriankelley

erik-287769 Valued Member Points: 53 More actions · Answer 2

Microsoft recommends "Boost SQL Server priority on Windows" on servers that are dedicated servers.

And fiber mode should be used with caution. Here is a good article for you:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqldev/html/sqldev_02152005.asp

Regards,

Erik

Juanita SSCertifiable Points: 5300 More actions · Answer 3

Thank you so very much for the responses!! This is what I needed to know.

Juanita

forsqlserver SSCoach Points: 18902 More actions · Answer 4

Hi Brian,

I am finding "BOOST SQL SERVER PRIORITY" setting but not "USE WINDOWS NT FIBERS" in sql server 2008.

Where is It?and How WINDOWS NT FIBERS are useful in performance?

Thanks

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 5

forsqlserver (11/17/2011)
Hi Brian,
I am finding "BOOST SQL SERVER PRIORITY" setting but not "USE WINDOWS NT FIBERS" in sql server 2008.
Where is It?and How WINDOWS NT FIBERS are useful in performance?

The checkbox for Boost SQL Server Priority and Use Windows fibers (lightweight pooling) are right there together on the Processors tab.

Windows fibers *might* help you if you're seeing heavy, heavy context switches, which I've seen occur when another application has to be on the same server as SQL Server and they are fighting for who is #1.

In general, it reduces the # of context switches the OS has to perform. However, it rarely offers a performance gain. Only in rare cases does it. And it creates a lot more problems than it solves. A good read as to why to avoid it:

The Perils of Fiber Mode

K. Brian Kelley
@kbriankelley

forsqlserver SSCoach Points: 18902 More actions · Answer 6

In perfmon How Can I dead sure that another cpu is required for better Performance..

Thanks

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 7

Please post that question as another thread.

K. Brian Kelley
@kbriankelley