Process 0:0:0 (0x1ffc) Worker 0x00000001E580A1A0 appears to be non-yielding on Scheduler 3. Thread creation time: 13153975602106.

標籤：closed   imp   logs   can   this   san   temp   void   likely   

現場報錯如下：

Process 0:0:0 (0x1ffc) Worker 0x00000001E580A1A0 appears to be non-yielding on Scheduler 3. Thread creation time: 13153975602106. 
Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 21%. System Idle 76%. Interval: 73616 ms.

從報錯來看是某個worker佔用scheduler不釋放導致，伺服器具體的scheduler個數可以從sys.dm_os_sys_info.scheduler_count列中看到，一般來說伺服器也就那麼些核心數，其中如果被non-yeilding的worker長久佔用一些，將可能導致非常嚴重的效能問題。引發此問題的原因通常是某些查詢並行度很高的SQL，也與一個BUG有關，目前從一些文檔來看，此BUG並未被解決，詳見：https://connect.microsoft.com/SQLServer/feedback/details/521928/worker-w-appears-to-be-non-yielding-on-scheduler-s

從2009年SQL Server2005開始，到2017年9月SQL Server 2016版本這個問題依然被報告，微軟並未給出明確的解決辦法。

因此目前只能在出現問題後及時找出相關的SQL最佳化，並且通過修改查詢管理員或者並行查詢閾值來避免問題出現。

從官網的解釋來看：這是一個微軟winsock API自己的BUG。

 

I am with the SQL Protocols Dev team. I‘ve looked at the issue and most likely it is 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 not offer creation of
non-inheritable socket handles. If a child process of SQL Server is created after a connected socket 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 be 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.
Please be careful if you are doing this on a cluster to avoid checkpoint issue and registry misconfiguration which would prevent SQL from coming online.
SQL Server needs to be 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 be non-yielding on Scheduler 3. Thread creation time: 13153975602106.