SQL Server Log Writer Workers


SQL Server 2017 leverages up to 4 log writer workers, on hidden schedulers, to assist in transaction log processing activities.   You may find the number of log write workers has been increased to 8 when running newer releases of SQL Server to accommodate larger systems.  (Caution: Pre-release software may change before final release.)

During SQL Server startup the number of log writer workers is determined. 

If multiple log write workers are not allowed use a single log writer

Otherwise

  • Total number NUMA nodes * 2
  • Count the number of available CPUs on the NUMA node (affinity mask can impact this count)

       Take the smaller of the above calculations and cap by the MAX_LOG_WRITERS allowed (4 or 8 depending on version)

Bob Dorr

Comments (3)
  1. LondonDBA1 says:

    Thanks very much, Bob. Two quick questions. Are there any DMVs/extended events that show the beneficial effects of these multiple log writer workers or show stats for them in action ? And is there a trace flag to change to only one log writer worker (for comparison testing) ? I’d like some stats that quantify the effect of multiple log writer workers. Thanks again.

  2. xxflop says:

    I think you can disable the multi-threaded log writer with trace flag 9038 but, unless you have a very very fast storage and you see an abnormal amount of spinlocks (LOGFLUSHQ), it will be better to keep it on.

  3. bytehd says:

    Benchmarks would be nice too

Comments are closed.

Skip to main content