I've had SQL Server 2019 with CU22 on Windows 11 Pro. We use this database during hardly 1 year. Usually between 26-28th every month we have scenario:

1.Database save in log file message "Using 'dbghelp.dll' version '4.0.5'" and "Unable to get thread context for spid 0"

2023-09-26 16:13:06.32 Server      CImageHelper::Init () Version-specific dbghelp.dll is not used
2023-09-26 16:13:06.32 Server      Using 'dbghelp.dll' version '4.0.5'
2023-09-26 16:13:06.32 Server      ***Unable to get thread context for spid 0
2023-09-26 16:13:06.32 Server      * *******************************************************************************
2023-09-26 16:13:06.32 Server      *
2023-09-26 16:13:06.32 Server      * BEGIN STACK DUMP:
2023-09-26 16:13:06.32 Server      *   09/26/23 16:13:06 spid 9668
2023-09-26 16:13:06.32 Server      *
2023-09-26 16:13:06.32 Server      * Non-yielding Scheduler
2023-09-26 16:13:06.32 Server      *
2023-09-26 16:13:06.32 Server      * *******************************************************************************
2023-09-26 16:13:06.32 Server      Stack Signature for the dump is 0x0000000000000158
2023-09-26 16:14:10.60 Server      Timeout waiting for external dump process 17012.

2023-09-26 16:14:10.60 Server      Process 0:0:0 (0x3f64) Worker 0x0000020289FB8160 appears to be non-yielding on Scheduler 21. Thread creation time: 13340206857857. Approx Thread CPU Used: kernel 2765 ms, user 0 ms. Process Utilization 0%. System Idle 93%. Interval: 73443 ms.

enter image description here

  1. In task viewer this error start with this error message.

enter image description here

  1. Then databse create dump file. If I do not restart database then I have more dumps.
  2. If I do not restart database to work fine next 1 month then we will get much more Non-yielding Scheduler error like below today.

enter image description here

  1. Then I have to restart database to all users works fine. If I do not made this then users have problems to logon database. Databse do not work stable.

All dumps files are here: https://www.dropbox.com/scl/fi/6poyttt4g76ggd7073evr/dumps.zip?rlkey=ur3p364500dwdg5ktrrs7ynjl&dl=0

It happen always between 26-28th every month. I've installed CU22. I disabled eset antivirus. I checked database consistency. Everthing is fine. I do not have any other tasks or raports which running between 26th-28th every month. PC it's Dell 7910 2CPU dozen CPU's 128GB RAM with checked NVME hard drives. I don't have idea what to do more beside waiting for next CU.

I don't have possibility to send this case to Microsoft because I don't have business account in Microsoft. I appreciate any help.

Our SQL Server 2019 database is used for company warehouse software only so all queries are not made by users but by warehouse software company, I mean every day the same. We do not start some monthly reports. Problem happed during the day but sometines during the night so it's not a problem of amount of connections do server or database on Windows 11 Pro. Based on you answer I made some changes few weeks ago:

  1. I changed amount of SQL Server memory to 100GB (server have total 128GB of RAM)
  2. I updated SQL Server 2019 to latest CU23
  3. I disabled Eset Internet Security 25th (yesterday)
  4. I checked again Windows Task scheduler, all aplications which are connected to database which can made some wrong query every month but I didn't find any problems.
  5. I disabled Windows updates.

Finally after one month of perfect database working the same problem happend today 26th at 13:04. After this I started SQL profiler. Maybe it will happen today again.

Dump file: https://www.dropbox.com/scl/fi/ty7ergbluv1cui3mj1wq6/sql_dump2.zip?rlkey=ypi7jzsagt8fngudm0rabztmx&dl=0

I really do not have idea what to do right know. Maybe is there any person on this group which can give us paid support to solve this problem?

enter image description here

Few minutes ago databese create dump only few minutes after server reboot. Only one user on database - me. So it can't be other tasks. It can be only some problem with SQL Server Standard 2019 or warehouse software only whihc is only one software which have database on our SQL Server.


1 Answer 1


Looking over all the dumps, it lines up with the data you've given as they're all on the 26th or 28th of the respective month(s). There is one item to note, which is that the first two dumps are actually from SQL Server Agent which means this might be the same issue or it might be something else (I didn't look at the agent dumps).

The first dump is from 3/26/2023 and the last one is from 9/26/2023, with 14 total dumps. Looks like someone ran !analyze on dump 12 but unfortunately !analyze has no idea how to deal with a NonYielding Scheduler dump (the "faulting" stack will be the NonYielding callback from the resource monitor and not the actual thread having the issue, analyze doesn't know SQL Server internals so it doesn't know that).

I took a glance at the first few and the last few, didn't really look at the ones in the middle as the pattern seems to be the same. In all of the cases the issue is contention on memory allocation and possibly also on scheduling (Windows, not specifically SQL).

SQLDump 0001

Looking at this, the thread that created the non-yielding event was OS Id 11180. In the dump, if we go to this thread and look at the current call stack, we see the following:

00 ntdll!NtWaitForSingleObject
01 KERNELBASE!WaitForSingleObjectEx
02 sqldk!SOS_Scheduler::SwitchContext
03 sqldk!SOS_Scheduler::SuspendNonPreemptive
04 sqldk!WaitableBase::Wait
05 sqldk!WorkDispatcher::DequeueTask
06 sqldk!SOS_Scheduler::ProcessTasks
07 sqldk!SchedulerManager::WorkerEntryPoint
08 sqldk!SystemThreadDispatcher::ProcessWorker
09 sqldk!SchedulerManager::ThreadEntryPoint
0a kernel32!BaseThreadInitThunk
0b ntdll!RtlUserThreadStart

That's doing a whole lot of nothing, it's an idle thread attached to a scheduler. If we look at what it was doing right before the dump was invoked, we see the following:

00 ntdll!NtMapUserPhysicalPagesScatter
01 kernel32!MapUserPhysicalPagesScatter
02 sqldk!SOS_MemoryBlockAllocator::CommitBlockAndCheckNumaLocality
03 sqldk!SOS_MemoryBlockAllocator::AllocateBlock
04 sqldk!SOS_MemoryWorkSpace::AllocatePage
05 sqldk!MemoryClerkInternal::AllocateReservedPages
06 sqlmin!BPool::Steal
07 sqlmin!PbufGetGrantedBufs
08 sqlmin!WFRSRCMGR::GetExtentBuf
09 sqlmin!WORKFILE::GetNewCrntBuf
0a sqlmin!WORKFILE::AllocRowBuf
0b sqlmin!RowsetWorkFile::InsertRow
0c sqlmin!CValRowHash::SetDataX
0d sqltses!CEsExec::GeneralEval4
0e sqlmin!CHashWorkfilePartitionGroup::Insert
0f sqlmin!CQScanHash::ConsumeBuild
10 sqlmin!CQScanHash::Open
11 sqlmin!CQScanNLJoinNew::Open
12 sqlmin!CQScanNLJoinNew::Open
13 sqlmin!CQScanNLJoinNew::Open
14 sqlmin!CQScanNLJoinNew::Open
15 sqlmin!CQueryScan::StartupQuery
16 sqllang!CXStmtQuery::SetupQueryScanAndExpression
17 sqllang!CXStmtQuery::InitForExecute
18 sqllang!CXStmtQuery::ErsqExecuteQuery
19 sqllang!CXStmtSelect::XretExecute
1a sqllang!CMsqlExecContext::ExecuteStmts<1,1>
1b sqllang!CMsqlExecContext::FExecute
1c sqllang!CSQLSource::Execute
1d sqllang!process_request
1e sqllang!process_commands_internal
1f sqllang!process_messages
20 sqldk!SOS_Task::Param::Execute
21 sqldk!SOS_Scheduler::RunTask
22 sqldk!SOS_Scheduler::ProcessTasks
23 sqldk!SchedulerManager::WorkerEntryPoint
24 sqldk!SystemThreadDispatcher::ProcessWorker
25 sqldk!SchedulerManager::ThreadEntryPoint
26 kernel32!BaseThreadInitThunk
27 ntdll!RtlUserThreadStart

Clearly this isn't idle. It's a query that's executing which is currently in a hash operator which is allocating some memory. According to the errorlog, it spent 1.2 seconds in kernel time and 0 in user time... now this could mean that the thread didn't actually run for quite some time and when it finally had a chance to do so (Windows, not SQL) that it spent the last few seconds in the kernel call, it could be that we're allocating small amounts of memory many times and the combination of allocation time with the number of allocations is resulting in this behavior, it could be a lot of things. Memory dumps are point-ish in time and don't really have everything you'd need to be fully self contained root cause analysis vehicles.

Looking at the rest of the threads in the dump at this time (we don't know what any of them were running only what they currently are running) there were some other queries also stuck in memory allocation.


Fast Forward a bunch of months into the future, having had at least 1 memory dump each month at the same date, we come to the most recent one. You'll notice that this will look eerily similar to the last. The current stack of the thread in question:

00 ntdll!NtMapUserPhysicalPagesScatter
01 kernel32!MapUserPhysicalPagesScatter
02 sqldk!SOS_MemoryBlockAllocator::CommitBlockAndCheckNumaLocality
03 sqldk!SOS_MemoryBlockAllocator::AllocateBlock
04 sqldk!SOS_MemoryWorkSpace::AllocatePage
05 sqldk!MemoryNode::AllocatePagesInternal
06 sqldk!MemoryClerkInternal::AllocatePagesWithFailureMode
07 sqldk!MemoryClerkInternal::AllocatePages
08 sqldk!MemoryObjectFactory::CreateMemObjectInt
09 sqldk!MemoryObjectFactory::CreateMemObject
0a sqlmin!CHashBitmap::Init
0b sqlmin!CQScanHash::PickInputAndPrepareOutput
0c sqlmin!CQScanHash::Open
0d sqlmin!CQScanHash::ConsumeProbe
0e sqlmin!CQScanHash::Open
0f sqlmin!CQScanHash::ConsumeProbe
10 sqlmin!CQScanHash::Open
11 sqlmin!CQScanSortNew::BuildSortTable
12 sqlmin!CQScanSortNew::OpenHelper
13 sqlmin!CQueryScan::StartupQuery
14 sqllang!CXStmtQuery::SetupQueryScanAndExpression
15 sqllang!CXStmtQuery::InitForExecute
16 sqllang!CXStmtQuery::ErsqExecuteQuery
17 sqllang!CXStmtSelect::XretExecute
18 sqllang!CMsqlExecContext::ExecuteStmts<1,1>
19 sqllang!CMsqlExecContext::FExecute
1a sqllang!CSQLSource::Execute

The thread stack before the dump occurred for this thread:

00 ntdll!NtMapUserPhysicalPagesScatter
01 kernel32!MapUserPhysicalPagesScatter
02 sqldk!SOS_MemoryBlockAllocator::CommitBlockAndCheckNumaLocality
03 sqldk!SOS_MemoryBlockAllocator::AllocateBlock
04 sqldk!SOS_MemoryWorkSpace::AllocatePage
05 sqldk!MemoryNode::AllocatePagesInternal
06 sqldk!MemoryClerkInternal::AllocatePagesWithFailureMode
07 sqldk!MemoryClerkInternal::AllocatePages
08 sqldk!CIncPageMgr::PbAllocateWithNewPage
09 sqldk!CMemProc::Alloc
0a sqldk!operator new[]
0b sqlmin!CHashBitmap::Init
0c sqlmin!CQScanHash::PickInputAndPrepareOutput
0d sqlmin!CQScanHash::Open
0e sqlmin!CQScanHash::ConsumeProbe
0f sqlmin!CQScanHash::Open
10 sqlmin!CQScanHash::ConsumeProbe
11 sqlmin!CQScanHash::Open
12 sqlmin!CQScanSortNew::BuildSortTable
13 sqlmin!CQScanSortNew::OpenHelper
14 sqlmin!CQueryScan::StartupQuery
15 sqllang!CXStmtQuery::SetupQueryScanAndExpression
16 sqllang!CXStmtQuery::InitForExecute
17 sqllang!CXStmtQuery::ErsqExecuteQuery
18 sqllang!CXStmtSelect::XretExecute
19 sqllang!CMsqlExecContext::ExecuteStmts<1,1>
1a sqllang!CMsqlExecContext::FExecute
1b sqllang!CSQLSource::Execute

Once again, memory allocation in a hash operator. Note that the logs show that once again this only spent ~3 seconds in kernel time and 0 seconds in user time, which is quite odd.

There were a total of 354 threads in this dump, the system (and SQL) was only up for 15 minutes before the issue occurred.

My Thoughts

Given the above, I think there are a combination of issues.

  • Queries that need tuning (whatever you're running on these days)
  • The max server memory for this instance should be checked
  • OS level items should be checked, such as page file settings

I doubt we're hitting max server memory in the 15 minutes that the server was up (although in the first dump, 0001, the system was up for 4 day) but it's always good to double check it, especially when something like this happens. Memory allocation shouldn't take a long time but it is or at least looks that way. The threads are clearly making forward progress so the system isn't grinding to a halt, though it's impossible to tell why this is the case from a user mode dump like these.

The number of worker threads shoots up rapidly, over 300 (out of a max 1024 for 36 cpus), which could be a cause of contention, as with Windows server (if I recall correctly) before 2022 there was a single workspace lock for the process which can lead to similar issues.

I believe your biggest impact is going to be tuning the queries that are running during this time. I have no idea what they are since I only have public symbols.

Outside of that, you could dig deeper with some ETW tracing and performance monitoring (which may or may no yield interesting results). It doesn't really look like a SQL Server issue per se, more of a system that needs tuning.

Update 11/27/2023

The latest dump shows the same issue with NYS on memory allocation. I helped author this a long time ago and while I do not believe it will help in fixing your issue (since it seems you're already using LPIM) but I do believe, as I previously stated, that ETW tracing is really the next stage so understand what is going on at the kernel level, or even a kernel dump when it is about to occur... and someone who can investigate that data.

Having said that, you could try not using LPIM and setting the max server memory much lower to see if that helps (as the workspace memory for the program will no longer be larger, could make virtual memory scans faster).

  • Thank you Sean. Because writing about progress with removing this problem on this website is prohibited so I will back only with 100% solution if any. My previous message about progress was removed.
    – Roman J
    Commented Oct 28, 2023 at 11:49
  • I created a site to automatically look at dump files: SQLDumpAnalysis.com it may or may not help you - if it's the same issue it won't be of any further help than what I've already stated, it looks like queries need tuned. Commented Oct 28, 2023 at 14:45
  • OK. I upladed new dump file but get message "Sorry, there's nothing at this address." in Vew a Generated Report tab.
    – Roman J
    Commented Oct 29, 2023 at 16:02
  • If you don't keep the unique URL from the upload, then there is really no way for me to view it either. If you have it, what is the upload guid? Commented Oct 29, 2023 at 21:14
  • Hello Sean This is a link with unique URL SQLDumpAnalysis.com/ViewReport/…. Today this problem happen again at 01:35:00. Only 1 user works at this time. I checked SQL profiler because it was running during error time. But there is nothing special. I thinking about some hardware or some RAM problem because no other ideas.
    – Roman J
    Commented Nov 27, 2023 at 8:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.