4

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

At 19:46 maxworkers- 2944 workers created 789 oldest pending taskwait time 0 pending task 4

At 19:51 maxworkers- 2944 workers created 982 oldest pending taskwait time 256987 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

SOS_MEMORY_TOPLEVELBLOCKALLOCATOR

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

Thanks

  • you should use sys.dm_tran_locks to find out at which point (table) your query is taking longer time and causing CXPACKETS. Also check your MAXDOP settings. – Rajesh Ranjan Sep 22 at 17:31
  • edited my question with MAXDOP settings – BeginnerDBA Sep 22 at 19:42
  • How much RAM does your Windows Server have and what are the settings for min_server_memory and max_server_memory? Is it a single SQL Server instance? Is the governor turned on? – John aka hot2use Sep 23 at 4:58
  • 1
    @Johnakahot2use: Just updated the question – BeginnerDBA Sep 23 at 13:38
3

I would start by saying that best person to analyze dump in MS guy or person who knows about it. I would just try to point out some basics from the log you posted

At 19:46 maxworkers- 2944 workers created 789 oldest pending taskwait time 0 pending task 4 At 19:51 maxworkers- 2944 workers created 982 oldest pending taskwait time 256987 pending task 165

Please see that taskwait time 256987 and pending task 165. This means the scheduler was hung and 165 tasks were waiting on it to get a scheduler and run. In this case MS SQL Server was not able to get out of this hung scheduler scenario and waited for a while but again it decided that it would be best to restart itself to come out of this hung scheduler scenario and hence it restarted. Now for reason why it hang is beyond my ability to tell with the information you have posted.

Also note a thread is assigned a scheduler and it should run on that scheduler and that is why so many threads were waiting on this hung scheduler.

| improve this answer | |
  • thanks! I am not able to understand why when there are so many schedulers available , SQL is going back on same thread for a process. We see queries waiting on parallelism coming from different app servers. Should'nt they be assigned diff worker threads may be on diff scheduler if one is hung? – BeginnerDBA Sep 22 at 19:46
  • @beginnerDBA Are you sure other schedulers were "free" ? SQL Server must be assigning parallel threads since you have parallelism and one of the threads should have come to scheduler that is hung, but still SQL Server believes it will come out so may be assigning threads. Later when it came to conclusion that it cannot get out it restarted. – Shanky Sep 23 at 16:33
  • I am just believing it to be true based on diff between maxworkers available and workers created. Also i did not see any high CPU usage, i mean i can see CPU been stable at around 40% during that time – BeginnerDBA Sep 23 at 16:37
  • Would it be possible for you to post complete errorlog on shared drive, please mask the sensitive data. – Shanky Sep 23 at 16:40
  • I will see if i can. Also do you think we need T8075 on SP4 even based on that wait SOS_MEMORY_TOPLEVELBLOCKALLOCATOR seen. Just saw two events for 2 queries just few mins before restart – BeginnerDBA Sep 23 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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