By default, SQL Server operates in
thread mode, which means that the workers processing SQL Server requests are
threads.
SQL Server also lets user connections run in fiber mode. Fibers
are less expensive to manage than threads. The Lightweight Pooling option can
have a value of 0 or 1; 1 means that SQL Server should run in fiber mode. Using
fibers may yield a minor performance advantage, particularly when you have eight
or more CPUs and all of the available CPUs are operating at or near 100
percent. However, the trade-off is that certain operations, such as running
queries on linked servers or executing extended stored procedures, must run in
thread mode and therefore need to switch from fiber to thread. The cost of switching
from fiber to thread mode for those connections can be noticeable and in some
cases offsets any benefit of operating in fiber mode.
If you’re running in an environment using a high percentage of total CPU resources, and if System Monitor shows a lot of context switching, setting Lightweight Pooling to 1 might yield some performance benefit.
http://www.mybasicknowledge.com/2012/09/sql-server-database-options-and.html
http://www.mybasicknowledge.com/2012/09/using-windows-system-monitor-for-sql.html
