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.
SQLDump0013
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).
