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!
Ciao!!
No comments:
Post a Comment