Quick Scan Report – Lightweight Pooling

What this check looks for

sys.configurations where lightweight pooling has a value_in_use other than 0.

Why it matters

Lightweight pooling switches SQL Server from Windows threads to fibers, and a fiber is not a thread as far as the rest of Windows is concerned. That distinction breaks several things that assume a real thread underneath.

What stops working, or works badly:

  • The CLR is not supported in fiber mode at all. Anything using SQL CLR, including some third party functions and several Microsoft features, will not run.
  • Distributed transactions through MSDTC are unreliable, because the transaction context is thread affine.
  • Extended stored procedures and anything else calling out to Windows APIs that expect thread local storage behave unpredictably.
  • Several DMVs report differently, because the scheduler model underneath them changed, which makes performance troubleshooting harder at exactly the moment you would be doing it.

And the benefit it was designed for essentially no longer exists. Fiber mode reduces context switching, which mattered on hardware where context switches were expensive and core counts were tiny. Microsoft’s own guidance narrows it to a very specific situation: a server already pegged at high CPU, showing a very high context switch rate, with a large number of processors, where every other avenue has been exhausted. That is a rare enough combination that most DBAs will never meet it, and even then the gain is small.

So in practice this setting is nearly always one of two things: a leftover from a tuning exercise years ago, or something copied from an article without the caveats. It usually travels with priority boost and an affinity mask, both of which have the same provenance and both of which have their own checks on this report.

How to confirm it yourself

SELECT [name], [value], [value_in_use], [is_dynamic], [description]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] = 'lightweight pooling';

value and value_in_use disagreeing means a change is pending a restart.

The settings that usually accompany it, worth checking in the same pass:

SELECT [name], [value], [value_in_use]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] IN ('lightweight pooling', 'priority boost', 'affinity mask',
                  'affinity I/O mask', 'set working set size', 'max worker threads')
 ORDER BY [name];

Whether the machine is even in the territory where fiber mode was ever argued for:

SELECT [cpu_count], [scheduler_count], [max_workers_count]
  FROM sys.dm_os_sys_info WITH (NOLOCK);

SELECT [object_name], [counter_name], [cntr_value]
  FROM sys.dm_os_performance_counters WITH (NOLOCK)
 WHERE [counter_name] = 'Context Switches/sec';

How to fix it

Turn it off:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'lightweight pooling', 0;
RECONFIGURE;

It requires a service restart to take effect. Until then value reads 0 and value_in_use still reads 1, and the instance is still in fiber mode.

Check the neighbours while you are arranging the restart, because they need one too and they are usually wrong together:

EXEC sp_configure 'priority boost', 0;
EXEC sp_configure 'affinity mask', 0;
EXEC sp_configure 'affinity I/O mask', 0;
RECONFIGURE;

Then find out what the original problem was. Somebody enabled this to solve something. If that something was CPU pressure, it is still there and still unaddressed, and the honest answers are usually elsewhere: max server memory set so the instance is not thrashing, a cost threshold that stops trivial queries going parallel, an index that removes a large scan, or simply more CPU. Each of those has a check or a report on this instance.

If you genuinely are in the narrow case Microsoft describes, a server pegged at high CPU with an extreme context switch rate on a large number of processors, then measure it: record the context switch rate and the CPU profile before and after, on a real workload. It is a setting to arrive at with evidence, not to leave on because it is already there.

How long it takes

About two hours, nearly all of it waiting for a window to restart the service.

Report Why you would go there
Configuration Values Every setting against its default, where the rest of these live.
SQL CPU Schedulers The scheduler model and whether there is real CPU pressure.
Server Overview The hardware, and what else runs on it.
Waits SOS_SCHEDULER_YIELD and what the CPU pressure actually looks like.
CPU by Query Where the CPU is going.
Check
Priority Boost enabled The setting this one is nearly always found beside.
Not using all cores Affinity, the third member of the same group.
Max server memory The setting that usually should have been changed instead.
Cost threshold for parallelism Another common answer to the CPU pressure this was aimed at.

Frequently asked questions

It has been on for years with no trouble. Then nothing on the instance uses the CLR or distributed transactions, which is fortunate rather than safe. The first feature that needs either will fail in a way that takes a long time to explain.

Do I really need a restart? Yes. It is not dynamic, and value_in_use tells you what is actually running.

Is there any case for it? A narrow one that Microsoft documents, and it requires measurement to justify. If you cannot point at the context switch rate that motivated it, it is not that case.

Will turning it off hurt performance? On any modern machine, no. Thread scheduling is not the bottleneck it was when this setting was introduced.