Saturday, June 22, 2019

SQL Server 2019 CTP 3.0 - max number of transaction log writers increased

In SQL Server 2016, transaction log writing was enhanced to support multiple transaction log writers.  If the instance had more than one non-DAC node in [sys].[dm_os_nodes], there would be one transaction log writer per node, to a maximum of 4.

In SQL Server 2019, it seems the maximum number of transaction log writers has been increased.  The system below with 4 vNUMA nodes (and autosoftNUMA disabled) has eight transaction log writer sessions, each on their own hidden online scheduler, all on parent_node_id = 3/memory_node_id = 3 on processor group 1.

;with kgroups AS
(SELECT kgroup_count = COUNT(DISTINCT processor_group) 
 FROM sys.dm_os_nodes osn) 
SELECT SQLServer_version = SERVERPROPERTY('ProductVersion'), sinfo.scheduler_count, 
       sinfo.cpu_count, sinfo.softnuma_configuration_desc, sinfo.socket_count, 
    sinfo.numa_node_count, kgroups.kgroup_count 
FROM sys.dm_os_sys_info sinfo
CROSS JOIN kgroups;

SELECT req.session_id, req.command, sch.scheduler_id, sched_status = sch.[status], 
       sch.cpu_id, sch.parent_node_id, osn.memory_node_id, osn.processor_group
FROM sys.dm_exec_requests req
JOIN sys.dm_os_schedulers sch ON req.scheduler_id = sch.scheduler_id
JOIN sys.dm_os_nodes osn ON sch.parent_node_id = osn.node_id
WHERE req.command = 'LOG WRITER';

Will this difference matter?  I suspect there is a workload type that can benefit from this increase, otherwise I'd be surprised at the change.  If the horsepower of 8 transaction log writers is needed, then you might also want to know which schedulers share CPU with the transaction log writer hidden schedulers, in order to avoid them with busiest/most important queries.  That can be done, for example, by using affinity at the Resource Governor Resource Pool level.

That's all for now!


No comments:

Post a Comment