December 26, 2018 at 2:24 pm
This happened now two weeks in a row. Error log shows Non Yielding Scheduler. I've pulled extended events snapshot as well. Any idea what could be going wrong with this or what I need to monitor or fix? Sql server just hangs, no connection is accepted or times out and we'll end up restarting the box to get the SQL up and running. Any help is appreciated.
This is from error log
2018-12-26 09:31:50.61 spid16s average writes per second: 6.91 writes/sec
average throughput: 0.13 MB/sec, I/O saturation: 2559, context switches 5798
2018-12-26 09:31:50.61 spid16s last target outstanding: 2, avgWriteLatency 270
2018-12-26 10:15:30.44 Server ***Unable to get thread context for spid 0
2018-12-26 10:15:30.44 Server * *******************************************************************************
2018-12-26 10:15:30.44 Server *
2018-12-26 10:15:30.44 Server * BEGIN STACK DUMP:
2018-12-26 10:15:30.44 Server * 12/26/18 10:15:30 spid 3844
2018-12-26 10:15:30.44 Server *
2018-12-26 10:15:30.44 Server * Non-yielding Scheduler
2018-12-26 10:15:30.44 Server *
2018-12-26 10:15:30.44 Server * *******************************************************************************
2018-12-26 10:15:30.44 Server Stack Signature for the dump is 0x000000000000001E
2018-12-26 10:15:37.53 Server External dump process return code 0x20000001.
External dump process returned no errors.
2018-12-26 10:15:37.53 Server Process 101:0:115 (0x7f74) Worker 0x0000005AFA956160 appears to be non-yielding on Scheduler 36. Thread creation time: 13190308558984. Approx Thread CPU Used: kernel 0 ms, user 65046 ms. Process Utilization 5%. System Idle 97%. Interval: 70206 ms.
2018-12-26 10:16:37.72 Server Process 101:0:115 (0x7f74) Worker 0x0000005AFA956160 appears to be non-yielding on Scheduler 36. Thread creation time: 13190308558984. Approx Thread CPU Used: kernel 0 ms, user 123515 ms. Process Utilization 5%. System Idle 97%. Interval: 137522 ms.
2018-12-26 10:17:38.17 Server Process 101:0:115 (0x7f74) Worker 0x0000005AFA956160 appears to be non-yielding on Scheduler 36. Thread creation time: 13190308558984. Approx Thread CPU Used: kernel 0 ms, user 179531 ms. Process Utilization 5%. System Idle 97%. Interval: 197963 ms.
This is from Extended events
<resource lastNotification="RESOURCE_MEM_STEADY" outOfMemoryExceptions="0" isAnyPoolOutOfMemory="0" processOutOfMemoryPeriod="0">
<memoryReport name="Process/System Counts" unit="Value">
<entry description="Available Physical Memory" value="90988613632" />
<entry description="Available Virtual Memory" value="139823561981952" />
<entry description="Available Paging File" value="91318370304" />
<entry description="Working Set" value="445741101056" />
<entry description="Percent of Committed Memory in WS" value="99" />
<entry description="Page Faults" value="768356411" />
<entry description="System physical memory high" value="1" />
<entry description="System physical memory low" value="0" />
<entry description="Process physical memory low" value="0" />
<entry description="Process virtual memory low" value="0" />
</memoryReport>
<memoryReport name="Memory Manager" unit="KB">
<entry description="VM Reserved" value="890867064" />
<entry description="VM Committed" value="437749020" />
<entry description="Locked Pages Allocated" value="0" />
<entry description="Large Pages Allocated" value="0" />
<entry description="Emergency Memory" value="1024" />
<entry description="Emergency Memory In Use" value="16" />
<entry description="Target Committed" value="456339464" />
<entry description="Current Committed" value="437749024" />
<entry description="Pages Allocated" value="397394928" />
<entry description="Pages Reserved" value="0" />
<entry description="Pages Free" value="23724656" />
<entry description="Pages In Use" value="21141736" />
<entry description="Page Alloc Potential" value="412380752" />
<entry description="NUMA Growth Phase" value="0" />
<entry description="Last OOM Factor" value="0" />
<entry description="Last OS Error" value="0" />
</memoryReport>
</resource>
December 26, 2018 at 2:30 pm
What service pack, CU are you currently on? May want to check this article and see if you are patched to the level with the fix for some of these:
FIX: "Non-yielding Scheduler" error and SQL Server appears unresponsive in SQL Server 2014, 2016 and 2017
Sue
December 26, 2018 at 2:45 pm
Microsoft SQL Server 2016 (RTM-CU5) (KB4013105) - 13.0.2197.0 (X64) Feb 25 2017 12:07:22 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: )
December 26, 2018 at 2:49 pm
SQL_Surfer - Wednesday, December 26, 2018 2:45 PMMicrosoft SQL Server 2016 (RTM-CU5) (KB4013105) - 13.0.2197.0 (X64) Feb 25 2017 12:07:22 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: )
Looking at the link posted by Sue it looks like you need to patch SQL Server to SP2.
December 26, 2018 at 2:50 pm
SQL_Surfer - Wednesday, December 26, 2018 2:45 PMMicrosoft SQL Server 2016 (RTM-CU5) (KB4013105) - 13.0.2197.0 (X64) Feb 25 2017 12:07:22 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: )
And it was addressed with SP2, CU 2 for SQL Server 2016. So you could be hitting the issue in the article.
Sue
December 26, 2018 at 3:09 pm
Thanks Sue.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply