Detecting and Troubleshooting Non-Yielding Worker Threads in SQL Server 2005/2008. I had a little bit of excitement with one of my production servers couple of days ago, that I thought I would document (especially since it turned out well in the end).
I have a little VB.NET WinForms application that calls a series of DMV stored procedures on the production database instances. Below (with some sensitive information obscured) is a screenshot of Operations Monitor (during a normal time). As I woke up and checked the system with Operations Monitor, I noticed several unusual readings that led me to believe that one of my database servers was not a happy camper. First, my Avg Task Count for the instance was running in the 25-30 range (where it normally is in the 4-7 range). Second, my SQL Server CPU utilization was in the 25-30% range (where it is normally in the 10-15% range).
I initially thought that I had an issue with Integrated Full Text Search (iFTS), so I ran a command to resume population, which had no effect. Oh-oh, that was not good. Non-yielding scheduler due to issue with Tcp. Another day when SQL Server became unresponsive and I was brought in to the RCA.
Fortunate enough, I happened to find a KB article which matched with the problem here. However, our SQL server had all the fixes what that KB article offered to us. Now that is really, so here the story unfolds.. This was a SQL Server 2008 R2 SP1 (10.50.2796) running on Windows Server 2008 R2 SP1. From Errorlog, I saw those famous non-yielding scheduler messages which goes like this: There were no other preceding messages of interest, nothing much in Event logs either.
--Set the symbol path 0:049> .symfix+ D:\publicsymbols DBGHELP: Symbol Search Path: SRV*D:\publicsymbols* DBGHELP: Symbol Search Path: SRV*D:\publicsymbols* --Load the symbols 0:049> .reload /f --Change the context to the hanging thread 0:049> ~~[0x1a5c]s ntdll! I know this is the correct call stack for the hanging thread. How to analyze Non-Yielding scheduler or Non-yielding IOCP Listener dumps Like this: Like Loading...
How To Diagnose and Correct Errors 17883, 17884, 17887, and 17888. Published: June 26, 2006 SQL Server Technical Article Authors: Bob Dorr, Microsoft SQL Server Senior Escalation Engineer Sameer Tejani SQL Server Development Technical Lead – SQLOS Reviewers: Ron Dar Ziv, SQL Server Software Development Engineer – SQLOS Published: April 2006 Applies To: SQL Server 2005 and SQL Server 2008 Summary: Learn what SQL Server errors 17883, 17884, 17887, and 17888 indicate and how to properly diagnose and correct the error conditions.
On This Page Introduction SQL Server Scheduling 17883 Detection 17884 and 17888 Detection I/O Port Completion Stalls (17887 Detection) Investigating a 17883 Error Nonyielding Examples Reported to Microsoft Conclusion Introduction SQL Server error messages 17883, 17884, 18887, and 17888 indicate a health state affecting worker scheduling that can have detrimental affects on the concurrency and throughput of Microsoft® SQL Server™. SQL Server Scheduling SQL Server 7.0 introduced a logical scheduling model to the core database engine. Worker. Inside the Storage Engine: Anatomy of a page. Next up in the Inside the Storage Engine series is a discussion of page structure.
Pages exist to store records. A database page is an 8192-byte (8KB) chunk of a database data file. They are aligned on 8KB boundaries within the data files, starting at byte-offset 0 in the file. Here’s a picture of the basic structure: Header The page header is 96 bytes long. Note that I didn’t include the fields starting with Metadata:. Records See this blog post for details. Slot Array It’s a very common misconception that records within a page are always stored in logical order.