We are trying to understand the issue where SQL server was suddenly restarted on its own-
Its a SQL server 2012 SP4 -GDR applied with 40 CPu's HT enabled , thus total of 80 logical processors-
MAXDOP=8 , CTOP 5
From error logs we found
BEGIN stack Dump
Non- Yielding Scheduler /**********************/
Error messages just at the time of restart of SQL. Also there was no mini dump created as checked.
Yes we have quite some queries running at that time. Top 3 waits seen were:-
1.TranLogIO 2.CXPACKET 3.PAGELATCH SH
However we also noticed wait called
SOS WORKER as collected from system health XE which i believe is nothing else but
THREADPOOL. Therefore i went further and analyse the query processing details from that system health and found at the time of non yielding scheduler errors below was logged
oldest pending taskwait time0 pending task 4
oldest pending taskwait time256987 pending task 165
At 19:51 we started seeing restart happened.
Question is why there would be a threadpool wait if almost 2000 workers are still available from above calculation. Also why those pending task count 165 when queries have so many schedulers available to run and complete the request for bunch of queries waiting on CXPACKETS?
Edit: Updating my question for couple of waits also seen from XE
As i am reading info on above wait here https://www.sqlskills.com/help/waits/sos_memory_toplevelblockallocator/
Based on the blog there is a fix in SP3 CU but with TF T8075 : I am currently on SP4-GDR latest patch, do i still need to apply the TF, though i do not see any messages like
Failed allocate pages: FAIL_PAGE_ALLOCATION 513 in error log
Not sure if it helps- i see total server memory dropping a gig now and then and then matching upto target server memory which is = max server memory (750GB)
Most of the times total=target=max server memory. The drops are just by a gig and not much
Memory info= total ram: 880 GB Max memory : 750GB Min server memory: 130 GB Its a 2 node windows cluster and no other SQL instance is shared. Resource governor is not enabled
max_server_memory? Is it a single SQL Server instance? Is the governor turned on?