SQL Server Log Writer Workers

Published Feb 22 2022 06:28 AM 409 Views
Microsoft
Moved from: bobsql.com

 

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)

%3CLINGO-SUB%20id%3D%22lingo-sub-3198860%22%20slang%3D%22en-US%22%3ESQL%20Server%20Log%20Writer%20Workers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3198860%22%20slang%3D%22en-US%22%3E%3CH6%20id%3D%22toc-hId-1769228025%22%20id%3D%22toc-hId-1769228089%22%3EMoved%20from%3A%20bobsql.com%3C%2FH6%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESQL%20Server%202017%20leverages%20up%20to%204%20log%20writer%20workers%2C%20on%20hidden%20schedulers%2C%20to%20assist%20in%20transaction%20log%20processing%20activities.%26nbsp%3B%26nbsp%3B%20You%20may%20find%20the%20number%20of%20log%20write%20workers%20has%20been%20increased%20to%208%20when%20running%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fsql-server%2Fsql-server-2019%3F%26amp%3BOCID%3DAID739534_SEM_aPYxsJhV%26amp%3BMarinID%3DaPYxsJhV_79508514929538_sql%2520server%25202019%2520ctp_be_c__1272135298083179_kwd-79508677900128%3Aloc-190_%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Enewer%20releases%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eof%20SQL%20Server%20to%20accommodate%20larger%20systems.%26nbsp%3B%20(Caution%3A%20Pre-release%20software%20may%20change%20before%20final%20release.)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDuring%20SQL%20Server%20startup%20the%20number%20of%20log%20writer%20workers%20is%20determined.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20multiple%20log%20write%20workers%20are%20not%20allowed%20use%20a%20single%20log%20writer%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOtherwise%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ETotal%20number%20NUMA%20nodes%20*%202%3C%2FLI%3E%0A%3CLI%3ECount%20the%20number%20of%20available%20CPUs%20on%20the%20NUMA%20node%20(affinity%20mask%20can%20impact%20this%20count)%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Take%20the%20smaller%20of%20the%20above%20calculations%20and%20cap%20by%20the%20MAX_LOG_WRITERS%20allowed%20(4%20or%208%20depending%20on%20version)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-3198860%22%20slang%3D%22en-US%22%3E%3CH3%20class%3D%22single-title%22%20id%3D%22toc-hId-1057660407%22%3ESQL%20Server%20Log%20Writer%20Workers%3C%2FH3%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3198860%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBobSQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Feb 22 2022 06:28 AM
Updated by: