Implantei uma instância do SQL Server 2019 no Kubernetes e habilitei o Change Data Capture (CDC) no SQL Server.
Periodicamente, encontro um erro “Non-yielding Scheduler” nos logs, após o qual o banco de dados começa a consumir todos os recursos de CPU alocados e para de responder às consultas.
Não há sinais de escassez de recursos até que o problema ocorra e o banco de dados não esteja sob carga pesada, pois esta é uma configuração de desenvolvimento. Tanto o Kubernetes quanto o banco de dados são armazenados em dois SSDs no Raid, então duvido que os limites de leitura e gravação sejam a causa.
Antes de ativar o CDC, o banco de dados funcionava sem problemas. Imagem: 2019-CU25-ubuntu-20.04 (env: PID - Desenvolvedor, AGENT_ENABLED - true) Logs:
getspinlock pre-Sleep(): spid 0, 1790 yields on lock type "XDESMGR" (adr 00000010029316C0)
getspinlock pre-Sleep(): spid 0, 1685 yields on lock type "XDESMGR" (adr 00000010029316C0)
getspinlock pre-Sleep(): spid 0, 1467 yields on lock type "XDESMGR" (adr 00000010029316C0)
getspinlock pre-Sleep(): spid 0, 3232 yields on lock type "XDESMGR" (adr 00000010029316C0)
getspinlock pre-Sleep(): spid 0, 3124 yields on lock type "XDESMGR" (adr 00000010029316C0)
getspinlock pre-Sleep(): spid 0, 2904 yields on lock type "XDESMGR" (adr 00000010029316C0)
getspinlock pre-Sleep(): spid 0, 4531 yields on lock type "XDESMGR" (adr 00000010029316C0)
getspinlock pre-Sleep(): spid 0, 4276 yields on lock type "XDESMGR" (adr 00000010029316C0)
getspinlock pre-Sleep(): spid 0, 3827 yields on lock type "XDESMGR" (adr 00000010029316C0)
getspinlock pre-Sleep(): spid 0, 692 yields on lock type "XDESMGR" (adr 00000010029316C0)
getspinlock pre-Sleep(): spid 0, 691 yields on lock type "XDESMGR" (adr 00000010029316C0)
2024-03-23 14:53:13.66 Server Using 'dbghelp.dll' version '4.0.5'
getspinlock pre-Sleep(): spid 0, 667 yields on lock type "XDESMGR" (adr 00000010029316C0)
2024-03-23 14:53:14.15 Server ***Unable to get thread context for spid 0
2024-03-23 14:53:14.16 Server * *******************************************************************************
2024-03-23 14:53:14.16 Server *
2024-03-23 14:53:14.16 Server * BEGIN STACK DUMP:
2024-03-23 14:53:14.16 Server * 03/23/24 14:53:14 spid 468
2024-03-23 14:53:14.16 Server *
2024-03-23 14:53:14.16 Server * Non-yielding Scheduler
2024-03-23 14:53:14.17 Server *
2024-03-23 14:53:14.17 Server * *******************************************************************************
2024-03-23 14:53:14.17 Server Stack Signature for the dump is 0x0000000000000014
getspinlock pre-Sleep(): spid 0, 5171 yields on lock type "XDESMGR" (adr 00000010029316C0)
2024-03-23 14:53:20.87 Server External dump process return code 0x20000001.
External dump process returned no errors.
2024-03-23 14:53:20.88 Server Process 0:0:0 (0x570) Worker 0x000000101E398160 appears to be non-yielding on Scheduler 0. Thread creation time: 13355613019271. Approx Thread CPU Used: kernel 100 ms, user 38760 ms. Process Utilization 12%. System Idle 0%. Interval: 70011 ms.
getspinlock pre-Sleep(): spid 0, 4854 yields on lock type "XDESMGR" (adr 00000010029316C0)
2024-03-23 14:53:25.96 Server Process 0:0:0 (0x168) Worker 0x000000101A354160 appears to be non-yielding on Scheduler 15. Thread creation time: 13355563362779. Approx Thread CPU Used: kernel 0 ms, user 37360 ms. Process Utilization 12%. System Idle 0%. Interval: 77297 ms.
getspinlock pre-Sleep(): spid 0, 4403 yields on lock type "XDESMGR" (adr 00000010029316C0)
2024-03-23 14:53:30.97 Server Process 0:0:0 (0x88) Worker 0x00000010389FE160 appears to be non-yielding on Scheduler 9. Thread creation time: 13355657814870. Approx Thread CPU Used: kernel 70 ms, user 35910 ms. Process Utilization 12%. System Idle 0%. Interval: 77313 ms.
getspinlock pre-Sleep(): spid 0, 1243 yields on lock type "XDESMGR" (adr 00000010029316C0)
getspinlock pre-Sleep(): spid 0, 1246 yields on lock type "XDESMGR" (adr 00000010029316C0)
.....
Como posso resolver ou rastrear a causa desse problema?
O que tentei: Tentei resolver o erro “Agendador sem rendimento” aumentando os limites de recursos e definindo a quantidade mínima e máxima de memória que o SQL Server pode consumir. Apesar destes ajustes, o problema persistiu sem qualquer melhoria.
O que eu esperava: esperava que ajustar os limites de recursos e as configurações de consumo de memória do SQL Server aliviasse o problema, permitindo que o banco de dados operasse sem acionar o erro “Non-yielding Scheduler” e o subsequente esgotamento dos recursos da CPU.
Microsoft SQL Server 2019 (RTM-CU25) (KB5033688) - 15.0.4355.3 (X64) 30 de janeiro de 2024 17:02:22