Is there any benefit for me in turning on the "lightweight pooling" SQL Server configuration option?

Question

We are running SQL Server on a server has 4 CPUs and 2GB RAM. Generally, CPU utilization rarely gets over 80%, and when it does, it's not for more than 60 seconds at a time. Given all this, is there any benefit for me in turning on the "lightweight pooling" SQL Server configuration option?

Answer

By default, SQL Server runs 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.

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 circumstances exist:

• Two 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," which is an advanced SQL Server configuration option, 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.

So in your particular circumstances, I would not recommend turning on "lightweight pooling" to make use of fibers.