Process 0:0:0 (0X1FFC) Worker 0x00000001e580a1a0 appears to being non-yielding on Scheduler 3. Thread creation time:13153975602106.

The site error is as follows:

Approx Thread CPU used:kernel 0 ms, user 0 ms. Process utilization 21%. System Idle 76%. interval:73616 Ms.

From the error point of view is a worker occupation scheduler not release caused, the server specific number of scheduler can be seen from the Sys.dm_os_sys_info.scheduler_count column, in general, the server is also a number of cores, If the non-yeilding worker is occupied for a long time, it can cause very serious performance problems. This problem is usually caused by some queries that have a high degree of parallelism in SQL, but also related to a bug, currently from some documents, this bug has not been resolved, see: https://connect.microsoft.com/SQLServer/feedback/ Details/521928/worker-w-appears-to-be-non-yielding-on-scheduler-s

Starting with SQL Server2005 2009, the issue of SQL Server 2016 in September 2017 is still being reported, and Microsoft has not given a definitive solution.

Therefore, it is only possible to find the relevant SQL optimization in time after the problem, and to avoid the problem by modifying the query governor or parallel query threshold.

From the official website explanation: This is a Microsoft Winsock API own bug.

I am with the SQL protocols Dev team. I ' ve looked at the issue and most likely it's related to the creation of child processes from within SQL server-e.g.
xp_cmdshell, 3rd party backup or compression software, etc. The problem occurs due to a limitation of the Winsock API which does is not an offer creation of
non-inheritable socket handles. If a child process of SQL Server are created after a connected sockets is created and before the inheritance flag of the
socket is cleared, the child process inherits the socket handle. When closing the corresponding client connection, a proper TCP cleanup cannot is performed
before the child process of SQL Server is closed.

To implement a temporary workaround, add the Tcpabortiveclose SQL parameter to registry as described in KB article/HTTP/ support.microsoft.com/kb/307197.
Careful is doing this on a cluster to avoid checkpoint issue and registry misconfiguration which would p Revent SQL from coming online.
SQL Server needs to is restarted in order the changes to take effect. No significant perf impact is expected from the change, no functional impact should be either.

　　

Process 0:0:0 (0X1FFC) Worker 0x00000001e580a1a0 appears to being non-yielding on Scheduler 3. Thread creation time:13153975602106.