How To Diagnose and Correct Errors 17883, 17884, 17887, and 17888
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 error messages 17883 and 17884 were introduced in SQL Server 2000 Service Pack 3 and in SQL Server 7.0 Service Pack 4 (as error messages 17881 and 17882) to provide basic scheduler health monitoring. Error messages 17887 and 17888 are new health monitoring conditions that were added in SQL Server 2005.
This white paper outlines the conditions in which these errors can be reported and what steps the database administrator can take to both understand and remedy these errors.
SQL Server Scheduling
Understanding the conditions necessary for SQL Server to log a scheduler health message requires a basic understanding of SQL Server scheduling mechanisms.
SQL Server 7.0 introduced a logical scheduling model to the core database engine. This model allowed greater scalability for SQL Server and added support for fibers.
Since the introduction of the user mode scheduler and the addition of scheduler health error messages, various misconceptions have arisen. The User Mode Scheduler and health messages are loosely documented in various publications, SQL PASS presentations, and newsgroup responses. This white paper thoroughly documents scheduler health details and associated troubleshooting techniques.
For more information
To fully understand the SQL Server user mode scheduler and the terminology used in this white paper, first read the following articles by Ken Henderson on the Microsoft Developer Network (MSDN).
Inside the SQL Server 2000 User Mode Scheduler
In addition, read Chapter 10, “User Mode Scheduling,” in Ken’s book, The Guru’s Guide to SQL Server Architecture and Internals.
These materials pull key information from Microsoft training, case work, and code design. This information is provided by Microsoft SQL Server Development and Microsoft SQL Server Escalation Support.
You can find the latest information and more details on known bugs or conditions that cause these errors in other Knowledge Base articles and white papers and in Microsoft SQL Server Books Online. Go to the Microsoft Help and Support site (https://support.microsoft.com) and search for the following keywords: 17883, scheduling, UMS (User Mode Scheduling), or SQL Server I/O (SQL Server I/O papers outline the techniques used by the scheduler(s) to handle I/O operations).
Schedulers
The SQL Server scheduler is a logical CPU that is used by SQL Server workers. The scheduler is called the User Mode Scheduler (UMS) in SQL Server 2000 and the SQL Server Operating System (SOS) Scheduler in SQL Server 2005. The following is a brief description of the core SQL Server scheduling rules.
The max worker thread setting is used to assign per-scheduler worker limits to the schedulers. The calculation is roughly:
Workers Per Scheduler Limit = (Max Worker Threads / Online Schedulers)
Only a single worker can own the scheduler thereby limiting the number of active workers and increasing scalability.
I/O requests (reads and writes) are tracked on the scheduler in which they were issued unless I/O affinity has been established. The following Microsoft Knowledge Base article outlines I/O affinity details.
INF: Understanding How to Set the SQL Server I/O Affinity Option (298402)
Workers are not bound to a physical CPU unless the sp_configure affinity mask configuration value has been properly established. This behavior can be extended with trace flags as outlined in the following Microsoft Knowledge Base article.
FIX: Trace Flag -T8002 Treats Affinity Masks Like Process Affinities (818765)
SQL Server does not distinguish between multi-core, hyper-threaded, or physical CPUs. The operating system reports a total number of CPUs. SQL Server is designed to use the reported number of CPUs as if they were physical. The only time SQL Server uses the logical CPU ratio is to determine proper licensing constraints. Details about hyper-threaded environments are covered in the following Microsoft Knowledge Base article.
SQL Server support in a hyper-threaded environment (322385)
SQL Server creates the schedulers based on several factors as outlined below.
SQL Server 2000
In SQL Server 2000, a logical scheduler is created for each reported CPU unless the sp_configure, affinity mask option has been established to restrict scheduler count.
SQL Server 2005
In SQL Server 2005, a logical scheduler is created for each reported CPU regardless of the sp_configure, affinity mask setting. Schedulers are set to ONLINE or OFFLINE based on the affinity mask settings. This allows the database administrator to reconfigure the affinity mask without restarting SQL Server. The reconfigure operation will bring schedulers ONLINE or OFFLINE as necessary and migrate worker counts appropriately to accommodate the max worker thread settings.
Note: When migrating a scheduler from ONLINE to OFFLINE status, work that is already assigned to the scheduler must be fully completed. No new work is assigned to the scheduler. However, the scheduler is not considered fully OFFLINE until all active and pending requests are processed by the assigned scheduler.
To view basic information about the activity of the scheduler(s), you can use the following Transact-SQL commands.
SQL Server 2000
DBCC SQLPERF(umsstats)
SQL Server 2005
SELECT * FROM sys.dm_os_schedulers SELECT * FROM sys.dm_os_workers SELECT * FROM sys.dm_os_threads SELECT * FROM sys.dm_os_tasks SELECT * FROM sys.dm_os_waiting_tasks SELECT * FROM sys.dm_os_ring_buffers
Warning: These dynamic management views (DMVs) are powerful but they require synchronized access to the internal data structures and lists. Frequent query activity against these DMVs can impact overall system performance and some queries can return large result sets. Access to the information is thread-safe but it is recommended that you query these system DMVs only when necessary.
Worker
The worker represents a thread or fiber. SQL Server database engine developers write code to execute on a worker instead of using thread- or fiber-centric code. This way, the thread or fiber is encapsulated from the core engine. Following is a brief summary of core worker rules.
Workers are bound to their logical scheduler and the underlying thread or fiber. Workers may be affinitized to a physical CPU when the affinity mask has been established.
Each scheduler is assigned a worker limit count and can create or destroy workers as required.
The worker processes an entire request (task) before processing another request or returning to an idle state.
A worker is not created until the scheduler receives a request (task) and there are no idle workers available to process the request.
If a worker has been idle for 15 minutes or more, the worker may be trimmed. This destroys the thread or the fiber as well.
SQL Server 2005 may aggressively trim idle workers when under memory pressure.
Note: SQL Server 2005 uses the guaranteed stack size setting on operating systems that allow it. SQL Server 2005 may use more physical memory to support the actual worker stacks than did previous versions.
SQL Server 2000 running on X64 in WOW mode also uses more memory per worker (768 KB = 512 KB for standard stack + 256 KB used by WOW to support the stack).
The design of the worker pool makes SQL Server very efficient. Most administrators are surprised to find that on large systems the worker count is often far below the sp_configure max worker thread setting. The administrator can monitor worker counts using the basic information queries outlined in the previous section.
Requests
The unit of work for a worker is a request (SQL Server 2000: UmsWorkRequest, SQL Server 2005: SOS_Task).
A request can be loosely equated to a batch (IE. SQLBatch:Started trace event). Take the following as an example.
SELECT @@VERSION GO SELECT ‘This is a test’ GO
This example results in two requests from the client. The client submits the first request SELECT @@VERSION, processes the results, and then submits the second request.
At the server, each request is associated with a UmsWorkRequest or a SOS_Task. A worker is bound to the request and processes the entire request before handling any other request.
A common misunderstanding is that a worker can process a request , switch to another , and return to the original request. This is not the case in SQL Server 7.0 , SQL Server 2000 , or SQL Server 2005. The request (task) and worker are bound tightly until full completion of the request.
Once it is bound to a request, the worker must complete the request. Take the simple example of a SELECT statement that becomes blocked—the worker is bound to the blocking query until the blocking condition is resolved and the request (task) completes.
SQL Server 2000
During SPID creation, the SPID is assigned to a scheduler. All requests for the SPID are assigned to the same scheduler. The SPID assignment is made at connect time. The SPID is assigned to the scheduler that has the lowest user count at connect time.
SQL Server 2005
The SQL Server 2005 work load assignment algorithm no longer binds a connection to a scheduler. Instead, SQL Server 2005 maintains a preferred scheduler for the connection. The preferred scheduler is the scheduler that the last batch request of the connection was executed on.
Tasks are assigned to schedulers based on the current number of tasks (not on users as in SQL Server 7.0 and 2000) assigned to ONLINE schedulers. When a user logs on, the user is assigned to the scheduler with the fewest tasks. After that, if any ONLINE scheduler has a task count that is less than 120 percent of the preferred scheduler, the new task is assigned to the scheduler that has the lower task count. This helps prevent unbalanced workload conditions where applications make several connections but a single connection submits most of the workload. As each new request arrives it is assigned accordingly.
Note: The connection remains constrained to the same NUMA node. The choice of scheduler assignments is limited to the schedulers on the node with which the connection was originally associated.
Note: The SQL Server 2005 scheduling design does not provide finite load balancing.
Yielding
The design of the logical scheduler makes the worker non-preemptive to the database engine. The worker owns the scheduler until it yields to another worker on the same scheduler.
Each scheduler has a runnable list. The workers that are available to execute on the scheduler are tracked in the runnable list. However, they remain in a WaitForSingleObject call waiting on a private event with an INFINITE timeout. The INFINITE timeout keeps the worker off the dispatcher lists of the operating system, thereby improving the performance of the operating system.
Note: The operating system can preempt the worker with all the rules and regulations that the operating system implements. SQL Server is simply controlling which workers can actively acquire CPU resources, thereby limiting the work of the operating system dispatcher. This allows for more productive CPU usage.
Throughout the database engine there are logical yield points that relinquish ownership of the scheduler and do so in an optimized way. For instance, if the worker is going to block on a lock, it registers itself with the lock resource and yields the scheduler. Relinquishing ownership results in a SQL Server worker context switch.
The yield action checks the SQL Server timer list and the I/O list of the SQL Server scheduler, removes the worker at the head of the runnable list, and sets the private event of that worker. The worker then calls WaitForSingleObject on its own private event, thereby completing the transition. The yielding worker is no longer owner of the scheduler. The signaled worker has become the new scheduler owner and the SQL Server worker context switch is considered complete.
In the example above when the owner of the lock resource (the blocking user) is finished, the owner checks the wait list of the lock resource and places the First In First Out (FIFO) ordered, compatible workers onto their respective runnable lists. The scheduler continues switching contexts as workers yield. Soon the waiting worker will become the head of the runnable list, transition to the scheduler owner, and acquire the lock.
Other places in the SQL Server code use similar designs to the lock resource to yield. These designs allow the database engine to maximize CPU resources in conjunction with the needs of the database primitives.
Keep in mind that the worker quantum target is 4ms (milliseconds). The following is a short list of yield examples.
Whenever a worker fetches a database page, the worker execution quantum is checked. If it exceeds 4ms, the worker yields.
Every 64-KB count of sort records results in a yield.
Yields are sprinkled throughout the compile logic.
If the worker is going to make an external call to an extended procedure, it yields. The process of SwitchPreemptive is described later in this document.
If the client can’t fetch results as fast are they are returned, the worker yields.
When it is forced to wait on a latch, the worker yields.
After the execution of each statement in a batch, the worker yields.
When no worker is currently on the runnable list, the yielding worker is allowed another quantum or performs the necessary idle scheduler maintenance.
SwitchPreemptive and SwitchNonPreemptive
The scheduler provides a way for a worker to protect SQL Server from outside activity that the logical scheduler cannot control. Whenever an external call (API, extended stored procedure invocation, linked server invocation, and so on) is attempted, the call is wrapped with a switch call. For example:
SwitchPreemptive IRowset->GetRows(...) SwitchNonPreemptive
In this example, SwitchPreemptive forces another worker to become owner of the scheduler. It does this by making the head of the runnable list the new owner and removing the current worker from logical scheduler control. The worker transitions ownership and is removed from SQL scheduler control until the external activity is complete. When the external activity is complete, the worker returns to the end of the runnable list by calling SwitchNonPreemptive.
This external transition allows more than one thread per scheduler to be active at the operating system level and can reduce overall system performance if the activity becomes excessive. However, it protects the logical scheduler from stalls when the thread is entering a code line that is uncontrolled by SQL Server scheduling and is of varying duration.
Note: Not all API calls are encapsulated by the switch logic. API calls that allow asynchronous activity (such as ReadFile and WriteFile) are often used to avoid kernel mode transitions and to allow the worker to continue processing, thereby maximizing system resources.
17883 Detection
SQL Server installations include a wide range of hardware and software configurations. These can impact the logical scheduler activities.
The vast majority of 17883 error reports are the result of a stuck or stalled API call. This is often the result of a problem with the subsystem, driver, or environment, and not with the server that is running SQL Server.
In SQL Server 2000 SP3 and SP4 and in SQL Server 2005, several engine-specific bugs were identified and corrected. The first action you should take when experiencing error reports is to apply a newer build of SQL Server that contains known corrections.
If you use SQL Server 2000 SP3 (8.00.0760) you should strongly consider upgrading to SQL Server 2000 SP4 or SQL Server 2005. These versions produce extra diagnostics when errors are detected so that you can better determine the root cause of an error. SQL Server 2000 SP4 also contains hot fixes for the sort and compile code lines that can affect machines with larger memory footprints.
For more information
The following Microsoft Knowledge Base article outlines details about the 17883 and 17884 error message additions as well as the changes in message context in new builds.
New concurrency and scheduling diagnostics have been added to SQL Server (319892)
SQL Server 2000 SP3
In direct response to some difficult debugging scenarios to determine where the scheduler was stuck or stalled, scheduler health monitoring was introduced in SQL Server 2000 SP3.
When the owner of the scheduler has not yielded within 60 seconds and as a result pending requests (tasks) are stalled, SQL Server 2000 SP3 logs a 17883 error message.
The SchedulerDeadlockCheck routine is used to check for errors 17883 and 17884. The health check in SQL Server 2000 SP3 is based on a snapshot of the scheduler information from the previous check compared to current scheduler values. Every 60 seconds the following is checked by a SQL Server 2000 SP3 build.
if Snapshot Context Switches = Current ContextSwitches and Snapshot Idle Context Switches = Current Idle ContextSwitches and Entries exist in the runnable queue (scheduler has queued requests) then 17883 Error is logged and mini-dump may be captured
The logic determines that the worker has not transitioned to another user in at least 60 seconds and the runnable queue has a user who should be allowed to execute. This is a clear indication of a problem that will impact concurrency and that needs further investigation.
Note: This logic not only checks each scheduler for the stalled or stuck 17883 condition but can also raise the 17884 message when it is determined that no new work requests have been processed for 60 seconds across all schedulers.
Error 17884 is slightly different. At least one queued item must processed in the last 60 seconds for this error to arise. Error 17884 is discussed in more detail later in this paper.
SQL Server 2000 SP4
SQL Server 2000 SP4 contains health checks but the algorithm has been enhanced.
The same 60-second logic covered in the previous section still applies but there is a flaw in the SQL Server 2000 SP3 logic. In a rare circumstance a worker could run and be properly yielding but not switching contexts because no other work is pending. A new worker would be placed on the runnable list and to SchedulerDeadlockCheck it would appear like a misbehaved worker causing a 17883 error.
In SQL Server 2000 SP4, the logic was changed. In this version, a scheduler-based yield counter is incremented each time a yield is attempted, regardless of the actual context switch activity. The logic for detecting 17883 conditions was changed to the following, thereby removing a rare set of false 17883 reports.
if Snapshot Yields = Current Yields
and Scheduler is not currently idle
Note: Idle indicates that the runnable list is empty. An idle scheduler has no meaningful work to perform because of waits and/or because no requests (tasks) are present.
The false 17883 generally only occurs on higher numbered scheduler IDs such as 5, 6, 7, and so on. By nature the lower numbered schedulers receive the base system tasks, such as the lazy writer or lock monitor. These tasks execute at intervals of less than 5 seconds, thereby preventing the false reports.
SQL Server 2005 - SchedulerMonitor
In SQL Server 2005, 17883/17884 detection is extended and refined. The logic is more sophisticated and more details are captured, thereby allowing better visibility.
Because what was learned from the 17883 error condition in SQL Server 2000 SP3 and SQL Server 2000 SP4 was applied to the product, several upgrades took place to the detection and reporting algorithms. Many of these enhancements have been in place for SQL Server 2005 for several years, so a multitude of test passes have been made, making the SQL Server engine an unlikely cause of nonyielding conditions.
SchedulerMonitor replaces SchedulerDeadlockCheck (from previous SQL Server versions). SchedulerMonitor is a thread created to monitor the schedulers associated with the given scheduling node; each scheduling node has its own SchedulerMonitor and is not under SQL Server scheduling control.
Detection interval
The first big improvement introduced by SchedulerMonitor is the reduction of the nonyielding watch interval from 60 seconds to 5 seconds. SchedulerMonitor wakes every 5 seconds and checks the current state of the schedulers on the scheduling node. This granularity improvement allows more detailed tracking of the scheduler work load. For example, during the monitoring interval the information about the owning worker, such as kernel and user mode time, can be tracked as it directly relates to the elapsed wall clock time.
Phases
SchedulerMonitor is designed around a phased approach. This section describes each phases as follows:
Detection phase: basic check
Extended reporting phase: threshold and resource boundary checks
Each phase gets progressively more intense in detecting and defining the health state of the schedulers.
1. Detection phase (basic check)
The first phase detects nonyielding conditions similar to those implemented in SQL Server 2000 SP4.
If:
the scheduler is not in an idle state
and the number of yields has not changed
and the worker is not performing an action such as taking a mini-dump
then the worker is considered to be nonyielding.
Note: A flag can be set on a per-worker basis to scope a worker’s activity so that it won’t be considered to be nonyielding. This is how SQL Server 2005 scopes a worker when it is handling an exception.
Each scheduler contains an internal tracking structure that is populated or updated if the basic check identifies a nonyielding worker. If this is the beginning of a nonyield condition, the data capture becomes the initial baseline. If the nonyield condition persists for the same worker, subsequent tracking activities update the data. This allows the 17883 error message and other detection logic to look at accumulated values such as kernel and user mode time and compare the values to elapsed wall clock time. A normal yielding condition restarts the basic check phase, resetting the tracking information appropriately.
When a worker is considered to be nonyielding, the appropriate nonyielding callbacks are invoked. SQL Server and SQL CLR nonyielding callbacks are described in detail later in this paper.
2. Extended reporting phase (threshold and resource boundary checks)
The extended reporting phase is invoked if the basic check locates a nonyielding worker. These checks do not affect the nonyielding callbacks. Instead, how and when entries are recorded in sys.dm_os_ring_buffers.
The threshold check is:
- If the nonyield situation (elapsed wall clock time) >= nonyield threshold
where the threshold is 10 seconds.
The heartbeat of the SchedulerMonitor algorithm is 5 seconds. During each beat, the basic test is checked. When the basic check evaluates to true, tracking of the worker begins. So there is approximately 15 seconds between the time of the last yield on the scheduler and the time that the threshold check becomes true.
Once the threshold check becomes true, the resource boundary check is invoked. The resource boundary check involves kernel and user mode time comparisons against the elapsed wall clock time.
The worker is considered nonyielding if either of the following conditions are met:
If the workers (tracked kernel + user mode time) <= 5 percent
If the workers (tracked kernel + user mode time) >= 40 percent
and:
- the SQL Server process is able to obtain sufficient processor time
This check is based on SQL Server process utilization and overall processor availability. If SQL Server cannot obtain appropriate CPU resources, it does not consider the worker to be nonyielding.
The reason for the new heuristics of 5 percent and 40 percent is to avoid reporting nonyield actions when the worker is impacted by outside influences. Low values commonly indicate that the worker is stuck in an API call (blocked) and waiting on an external resource, whereas high values indicate situations such as unbounded loops.
Values that fall in the mid-range indicate that the worker is attempting to make progress but is likely being impacted by external factors. Experience shows that external factors such as a high priority process or excessive paging can prevent SQL Server from getting reasonable processor time. For these situations, taking a mini-dump of the SQL Server process does not capture the overall system behavior. The ring buffer entries and System Monitor are often used to diagnose and analyze this type of problem.
To obtain more details about the heuristic decisions, enable trace flag -T8022. Use this trace flag with caution and only under the guidance of Microsoft SQL Server Product Support Services, as this trace flag can generate large amounts of data in the SQL Server error log.
Each time the heuristic decision is made, details about the decision (either nonyield or yielding) are logged in the SQL Server error log.
The following is an example of the trace flag output.
2005-07-05 08:16:56.38 Server Scheduler monitor trace: Scheduler 1 is STALLED 2005-07-05 08:17:01.38 Server Scheduler monitor trace: Worker : 036BA0E8 (pass: 274) Wall : 319765518 System Use : 639527562, User 371593, Kernel 639155968, Idle: 38835593 System idle : 99 Thread Use : 140 Worker util : 0 Process util : 0 Page Faults : 0 WS delta : 0 Starved? : N Verdict : NON-YIELD
Callbacks
SQL Server 2005 provides a callback infrastructure for dealing with nonyielding workers. During SQL Server startup a series of nonyielding callback functions are registered with SchedulerMonitor. The Detection phase (basic check) declares a nonyielding worker. Once a nonyielding worker is being tracked (~5 seconds) the callbacks are invoked one at a time. The callbacks decide the intervals at which to take further action.
Currently there are two nonyield callbacks used by SQL Server 2005: SQL nonyield reporting and SQLCLR forced yield. The SQLCLR callback forces CLR yield behavior to occur. The SQL reporting nonyielding scheduler callback handles reporting and mini-dump capture.
The CLR engine is integrated with the SQL Server 2005 engine. They share a common thread pool, a memory pool, and synchronization mechanisms. This integration allows for improved performance, extended deadlock detection, and better resource management. When calling a CLR assembly as a stored procedure, user function, or user-defined type SQL Server does not switch the worker to preemptive mode.
There are many natural yield points in the CLR engine, allowing the worker to yield (via the hosting interfaces) to the SQL Server scheduler appropriately. To protect the SQL Server from CLR code which may not yield properly, SchedulerMonitor helps protect the scheduler. When a nonyield condition is detected, the SQLCLR nonyield callback is invoked. If the worker is processing a CLR task, SQL Server uses the CLR hosting interfaces to force a yield.
Note: Nonyielding CLR code commonly involves a calculation-intensive loop devoid of further memory allocations. To correct the problem, add a Sleep(0) call to the loop. The Sleep(0) call causes the worker to yield to the scheduler using the hosting interfaces.
Once the CLR task yields, the task is not scheduled again unless no other work is available on the scheduler or it has completed the wait. This protects the scheduler.
For example, take a 5-second nonyielding CLR condition and the SQL Server 2005 scheduler quantum target of 4ms. The task is forced to wait until 1250 quantums have elapsed (5000ms / 4ms = 1250 quantums). The forced wait is currently capped at 5 seconds and prevents runaway CLR tasks from causing larger impacts on SQL Server scheduling.
The formula for the punishment quantums is:
= min(5000, (Time At Yield – Time of scheduler ownership)/4ms )
Here is a simplified example of what happens during runaway CLR task punishment.
quantums_to_yield = <<formula from above>>;while(quantums_to_yield > 0)
{
YieldToScheduler(0 /* no wait time */); -- See note below
quantums_to_yield--;
}
Note: Performing a yield to the scheduler with a value of zero is similar to running a Sleep(0) from the Microsoft Windows® API. The worker is simply placed on the tail of the runnable list and the switch is performed. If the tail and the head of the runnable list are one and the same (this worker) then no other worker has current processing needs on this scheduler. Therefore, the same worker is allowed to immediately return to an active state for another quantum. The loop is written in this manner to make sure that the wait is not always (quantums_to_yield * 4ms) when no other work is being affected.
Avoiding the punishment is strongly encouraged. While the punishment logic can quickly return the CLR task to active work, the detection and interruption of a runaway CLR task can result in concurrency issues. Anytime a task is allowed to take 10+ seconds on scheduler it can lead to unwanted behavior. For example, if the runaway CLR task is assigned to the same scheduler as log writer, it can hold up log write activities for 10 seconds. This holds up commit operations on all schedulers for 10 seconds and so forth.
When SchedulerMonitor forces a CLR task to yield, the task logs the number of times the forced yield occurred and the input buffer in the SQL Server error log.
To force the CLR task to yield, Garbage Collection (GC) is invoked to preempt the task. CLR has GC threads running on each CPU at real-time priority. When invoked, they can strongly tax the CPUs workload and attribute to higher CPU for SQL Server.
The SQL nonyielding callback handles the traditional 17883 reporting and data capture. This is different from SQL Server 2000. Here, the callback is involved on the first nonyield detection threshold of 10 seconds and then every 5 seconds thereafter for as long as the nonyield condition persists. The SQL nonyield callback only reports the 17883 message at 60-second intervals to avoid flooding the error log with repetitive information when under a nonyield condition.
Note: If a CLR nonyielding condition has been detected, the SQL nonyielding callback ignores the nonyield condition.
Output
The nonyielding callback can produce a SQL Server error log report as well as generate mini-dumps.
17883 Error Reporting
The 17883 error is reported in 60-second intervals. The amount of this interval can be altered by using trace flag –T1262 under the direction of Microsoft SQL Server Product Support Services. It is important to note that SQL Server 2000 SP4 and SQL Server 2005 log the 17883 message after the generation of the mini-dump to avoid any delay in the thread data capture. Microsoft SQL Server Product Support Services has seen I/O problems lead to 17883 conditions. Writing to the error log can also encounter the I/O problem, so attempting to capture the dump sooner can provide better troubleshooting information.
17883 Mini-Dump Generation
When the nonyielding situation is detected, SQL Server may attempt to capture an all-thread mini-dump.
A dump is not taken until an specific nonyield situation has reached 60 seconds in total duration. Once a 17883 mini-dump is captured, no further 17883 mini-dumps are captured until trace flag -T1262 is enabled or the SQL Server process is restarted. However, 17883 error message reporting continues, regardless of the mini-dump capture.
Trace Flag –T8024 can be used to change the mini-dump capture decision.
-T8024 Setting |
Mini-dump action |
OFF (Default) |
No further affect on mini-dump decision; dump will be captured. |
ON |
Additional checks to decide when the mini-dump should be captured. To capture a mini-dump, one of the following checks must also be met.
Additional check #1 is targeted at runaway CPU users. Additional check #2 is targeted at workers with lower utilizations that are probably stuck in an API call or similar activity. |
Trace Flag –T1262 can be used to change the mini-dump capture decision.
SQL Server 2000
The default behavior of SQL Server is to capture a mini-dump on the first 17883 report only. Further reports of 17883 do not capture the mini-dump. However, starting the SQL Server process with the –T1262 trace flag as a startup parameter instructs SQL Server to generate a mini-dump each time the 17883 error is reported.
Add –T1262 as a startup parameter for the SQL Server instance and cycle the service to enable the behavior.
SQL Server 2005
If trace flag -T1262 is enabled, SQL Server 2005 gathers a mini-dump(s) during each unique occurrence of a nonyield situation. When –T1262 is enabled, a mini-dump is generated when the nonyielding condition is declared (15 seconds) and at subsequent 60-second intervals for the same nonyield occurrence. A new nonyielding occurrence causes dump captures to occur again.
Note: -T1262 is no longer a startup-only trace flag. It can be dynamically enabled with DBCC TRACEON(1262, -1) or disabled with DBCC TRACEOFF(1262, -1) to achieve desired behavior.
Caution: Use –T1262 with care. The mini-dumps it generates include all thread stacks. This can produce larger files on IA64 and X64 installations.
Trace flag –T1260 can be used to disable mini-dump generation for any of the 17883, 17884, 17887, or 17888 error messages. The trace flag can be used in conjunction with trace flag –T1262. For example, you could enable –T1262 to get 10- and a 60-second interval reporting and also enable –T1260 to avoid getting mini-dumps.
Watson
The 17883 mini-dump generation has been enhanced in SQL Server 2005 to honor Watson reporting settings. If the SQL Server 2005 process is allowed to report to Watson, the 17883 mini-dump is uploaded to Watson. Prior versions of SQL Server are not enabled for 17883 Watson error reporting.
17884 and 17888 Detection
The 17884 and 17888 errors report scheduler deadlock conditions. When SchedulerMonitor detects that work is not progressing, a 17884 or 17888 message is recorded.
Following are a number of common causes.
All schedulers have encountered a 17883 condition.
All workers are blocked on a critical resource.
All workers are executing a long-running query.
Note: In practice, Microsoft has seen very few 17884 conditions. The problem is usually the second or third cause in the previous list. When a 17884 error is reported and is related to a product bug, prior errors that indicate the true root cause of the stall are usually found in the SQL Server error log.
SQL Server 2000 SP3 and SP4
In SQL Server 2000 SP3 and SP4, when SchedulerDeadlockCheck cycles through the schedulers looking for 17883 nonyield conditions, it also checks the following core condition.
Queued work requests (tasks) > 0
and Snapshot Work Processed == Current Work Processed
Each time a new work request is pulled from the schedulers work queue, the Work Processed counter is incremented. So, if the scheduler has work queued and has not processed, one of the work requests in 60 seconds the scheduler is considered stuck.
If all schedulers are considered stuck, 17884 error is reported. The first report of a 17884 error message generates a mini-dump.
SQL Server 2005
In SQL Server 2005, when SchedulerMonitor cycles through the schedulers looking for nonyield conditions, it detects the 17884 condition as well. The criteria to determine if a scheduler may be stuck is as follows.
Queued work requests exist
or new workers ‘being created’
or the Snapshot Work Processed == Current Work Processed
If the criteria is met, SQL Server 2005 considers this an individual scheduler deadlock condition. If ALL schedulers are considered stuck and SQL Server is not under active memory pressure, then the 17884 or 17888 condition is reported.
Note: The sys.dm_os_ring_buffers DMV contains detailed entries when SchedulerMonitor detects that the 17883/17884 condition exists or has been resolved.
Like the 17883 callbacks, the 17884 (scheduler deadlock) callbacks are invoked when all schedulers are considered deadlocked. There is only one callback currently installed in SQL Server 2005. This deadlock callback is used to capture the mini-dump and log the 17884 error message.
Just like the 17883 design, the callback reports only at 60-second intervals no matter how often it is invoked. The message is always logged. The mini-dump is generated only on the first report unless trace flag –T1262 is enabled. The logic in determining when to generate a mini-dump is as follows.
No dump was previously taken
and the SQL Server process is not currently CPU starved
Error 17888, a variation of error 17884, was introduced in SQL Server 2005. This error is logged when a deadlocked scheduler issue is detected and over 50 percent of the workers are waiting on a common resource type (for example, LCK_M_X, NETWORK_IO, or SOAP_READ). In this case, a dump is not automatically taken, since it is assumed that this condition is caused by an external factor. A dump can still be taken if trace flag –T1262 is specified. DMVs such as sys.dm_exec_sessions, sys.dm_exec_requests, sys.locks, and others can provide detailed insight into the problem.
I/O Port Completion Stalls (17887 Detection)
I/O port completion stalls is a new detection feature added to SQL Server 2005 SchedulerMonitor. Each time SchedulerMonitor executes it checks for I/O port completion success. The completion port is used for network I/O.
The 17887 is encountered if:
An I/O completion is active and no new I/O completion has been processed since the last SchedulerMonitor heartbeat and the condition has persisted for 10 seconds and the SQL Server process is not currently CPU starved or under memory pressure
SchedulerMonitor then considers the condition as stuck in an I/O completion routine.
The I/O completion callbacks are invoked once an I/O completion stall is declared. The reporting interval is 10 seconds for the 17887 error message. A mini-dump is captured on the first occurrence only. A mini-dump is always captured when –T1262 is enabled.
As with errors 17883 and 17884, the root cause of error 17887 might be attributable to prior errors. Always review previous errors in the SQL Server error log before attempting to determine the root cause of any of these errors as prior errors may be the actual cause of the problem.
SchedulerMonitor does some other things that SchedulerDeadlockCheck does not. It:
Updates the ring buffer every 60 seconds indicating general scheduling health.
Helps manage the ONLINE and OFFLINE worker thread pool and may do things such as create new worker threads to prepare for demand.
Investigating a 17883 Error
The investigation steps for 17883, 17884, 17887 or 17888 errors are all the same. This section outlines the investigation of 17883 errors. The same troubleshooting steps can be used for any of the errors.
The 17883, 17884, 17887 and 17888 errors do not immediately tell you what the problem is or was. However, error reporting is designed to capture a mini-dump, including all thread stacks of the SQL Server process. The messages and the mini-dumps have information pointing to the condition that is deemed nonyielding.
Note: Starting with SQL Server 2005, the mini-dumps generated during the 17883 report can be uploaded to the Microsoft Watson server. If a response is available, the response is logged in the application event log.
SQL Server 2000 (17883)
If error 17883 occurs when running SQL Server 2000, the following error is logged to the error log. This error indicates that the SPID:EC, thread ID, and scheduler ID have been deemed to be nonyielding for the 60-second interval.
Process 51:0 (dbc) UMS Context 0x018DA930 appears to be non-yielding on Scheduler 0.
Note: The message context has changed from the original SP3 implementation. This is covered in Microsoft Knowledge Base articles FIX: Error 17883 May Display Message Text That Is Not Correct and New concurrency and scheduling diagnostics have been added to SQL Server.
SQL Server 2005 (17883)
The SQL Server 2005 error message for error 17833 has been extended to provide more details about the worker that is deemed to be nonyielding. The message is expanded. It includes the session and request ID information, thread ID, scheduler ID, and provides detailed information about the thread. The following is an example of the SQL Server 2005 17883 error message.
Process 51:0:0 (0xdbc) Worker 0x036BA0E8 appears to be non-yielding on Scheduler 1. Thread creation time: 12764721496978. Approx Thread CPU Used: kernel 15 ms, user 171 ms. Process Utilization 0%. System Idle 99%. Interval: 325602683 ms.
The Approx Thread information provides details about the problem. The kernel and user (ms) time is the amount of kernel and user mode time used by the thread since it was deemed to be nonyielding, not since the thread was created. To view total kernel and user mode time that was used by the thread since it was created, use the following query.
SELECT * FROM sys.dm_os_threads WHERE os_thread_id = << Integer thread Id value goes here >>
The Process Utilization, System Idle, and Interval information show details about the SQL Server process itself.
If you look at the error message information, you see that certain behaviors emerge. For example:
If user mode time quickly climbs and continues to do so, the likely cause is an unbounded loop in the SQL Server engine that is not properly yielding.
If kernel mode time climbs quickly, the thread is spending the majority of its time in the operating system and will require kernel debugging to determine the root cause of this behavior.
If neither the kernel time nor the user time increase quickly, the thread is likely waiting for an API call such as WaitForSingleObject, Sleep, WriteFile, or ReadFile to return. Or, the thread may not be getting scheduled by the operating system. API stall conditions generally require kernel mode debugging to determine their root cause.
If
System Idle%is low andProcess Utilization%is low, then SQL might not be getting enough CPU cycles. Check the CPU utilization of other applications on the system. Also, check to see if paging is going on in the system. ASELECT * FROM sys.dm_os_ring_buffersstatement can provide more details as well.If
kernel + usertimes are low butProcess Utilizationis high, the error condition could indicate that preemptive thread(s) are consuming all the CPU (e.g., GC).
Combining information with the system utilization and idle time can provide insight into the nature of the problem.
The example 17883 message indicates that 15ms of kernel mode time has been used since the worker has been tracked. The error message was from the 10 second (-T1262) 17883 report. The thread only used 15ms of kernel mode and only 171ms of user mode time over the elapsed 10-second period. The scenario in this example is a call to an API that resulted in a lower-level security check which had to retrieve information from the PDC. The times remain low because the thread is not doing kernel or user mode processing—instead, it is waiting on a network response from the PDC.
Note: The solution for this example is two-fold. SQL Server protects the scheduler by wrapping the API invocation with SwitchNonPreemptive logic as described earlier. The administrator must look into improving the response time from the PDC to avoid stalling SQL Server work loads that require security checks with the PDC.
Be aware that changing SQL Server to SwitchPreemptive for this situation is not always the best solution. In this scenario, SQL Server implemented the SwitchPreemptive logic around a login security API invocation. As a result, hundreds of workers become stalled instead of one worker per scheduler. While switching workers to preemptive mode returned a minimum level of diagnostic capability to the SQL Server, the outcome was still stalled processing of requests to the SQL Server due to the PDC response problem.
The kernel and user mode time can fool you in ways you might not expect. A reproduction of an orphan spin lock (internal SQL Server synchronization primitive) will result in a 17883 report showing only small amounts of kernel and user mode time.
Because this is called spin lock, one might expect the SQL Server process to be using lots of CPU. In fact, spins occur very fast and then the code calls SwitchToThread or Sleep.
The kernel mode time does not climb because the thread is in a Sleep call. The process utilization actually drops when threads are stuck in SpinToAcquire. For the user mode time to climb, a nonyielding loop is required.
Transact-SQL and dynamic management views
Transact-SQL additions to SQL Server 2005 can be used to view kernel and user mode time with the dynamic management view (DMV) sys.dm_os_threads. When combined with sys.dm_os_workers and sys.dm_os_schedulers, it is possible to see details that pertain to the system and scheduler utilization on an active server.
WARNING: The SQLOS DMVs (sys.dm_os_*) require synchronized access to critical, high activity lists and also use kernel mode calls such as GetThreadTimes. This activity is optimized and designed for thread safety but the use of these DMVs should be limited to necessary troubleshooting operations. Regular and repeated query activity against these DMVs is not a recommended practice.
In SQL Server 2000, gaining access to the same type of scheduler data requires a user mode process dump. Those familiar with the debugging utilities may have used the command (!runaway) to view thread timing information. The following is a sample query that uses the dm_os_threads DMV to achieve !runaway on a live SQL Server 2005 installation.
SELECT kernel_time + usermode_time as TotalTime, * FROM sys.dm_osthreads ORDER BY kernel_time + usermode_time desc
A common technique that is used by Microsoft SQL Server Product Support Services to check scheduler health focuses on the scheduler’s timer list. The timer list is checked during each yield so the value can be used to show the amount of elapsed time since the owning worker last yielded. The following query can be run against a live SQL Server 2005 installation to see the milliseconds that have elapsed since the scheduler last checked the timer list.
SELECT yield_count, last_timer_activity, (SELECT ms_ticks from sys.dm_os_sys_info) - last_timer_activity AS MSSinceYield, * FROM sys.dm_os_schedulers WHERE is_online = 1 and is_idle <> 1 and scheduler_id < 255
Note: Microsoft SQL Server Support Services and Development can also produce equivalent information to the DMVs against a user mode dump.
Always check for previous errors first
The 17883, 17884, 17887 and 17888 error messages are often symptoms pointing to a problem. Before investigating the error report, always review overall system health information such as performance information, event logs, SQL Server error logs, and other application logging information.
Understanding prior problems on the system may quickly clarify why SQL Server detected and reported a health error. Previous errors should always be addressed before the investigation continues.
For more information
The following Microsoft Knowledge Base article outlines several of scenarios that could lead to the 17883, 17884, or 17887 message.
New concurrency and scheduling diagnostics have been added to SQL Server (319892)
Nonyielding Examples Reported to Microsoft
SQL Server 2000 ships with public symbols for the SQL Server process in the BINN\EXE and BINN\DLL directories. The symbols are not shipped with SQL Server 2005 but they are available on the public symbol server. By using public symbols and the debug utilities from Microsoft, in either SQL Server 2000 or SQL Server 2005 the administrator can look at the stack to determine the possible error condition. This investigation does not have to be done on the SQL Server computer itself.
Debugging tools can be installed from the following location.
https://www.microsoft.com/whdc/devtools/debugging/default.mspx
Following is the location of the public symbol server.
http://msdl.microsoft.com/download/symbols
To view a thread stack that was deemed to be stalled
This procedure shows you how to look at the thread stack that was deemed to be stalled (17883).
Copy and open the SQLDmpr####.mdmp file to the debugging machine or open it (from the File menu, select Open Crash Dump) in the debugger from the remote location.
Establish the proper symbol file path by issuing the following command in the debugger command window.
.sympath=srv*c:\symbols*http://msdl.microsoft.com/download/symbols; c:\program files\microsoft sql server\mssql\binn
Obtain the matching SQL Server error log containing the 17883 error message. Locate the thread id (0xdbc) in the error message.
Process 51:0:0 (0xdbc) Worker 0x036BA0E8 appears to be non-yielding on Scheduler 1. Thread creation time: 12764721496978. Approx Thread CPU Used: kernel 15 ms, user 171 ms. Process Utilization 0%. System Idle 99%. Interval: 325602683 ms.
In the debugger command window, issue the following command.
~~[TID]s
where TID is the thread ID from the 17883 message to set the proper thread context. In this example the command would be ~~[0xdbc]s.
Issue the command kb 1000 in the debugger command window to symbolize the stack.
Once you have the stack you can more closely evaluate the problem condition. Following are some examples of stacks that have been reported to Microsoft SQL Server Support Services.
I/O subsystem problem
An I/O subsystem problem is one of the most common causes of 17883 errors. The SQL Server 2000 I/O Basics white paper meticulously documents how SQL Server performs I/O operations. Specifically, SQL Server does I/O asynchronously and should not get stuck in an I/O call.
The following stack shows a write attempt to a database file. The I/O attempt has become stuck in a WriteFile call. A thread stuck in an I/O call indicates a system level problem.
NTDLL!NtWriteFileGather KERNEL32!WriteFileGather ums!UmsScheduler::GatherWriteAsync sqlservr!UmsGatherWriteAsync sqlservr!FCB::GatherWrite ... ... ums!ProcessWorkRequests ums!ThreadStartRoutine msvcrt!_threadstart KERNEL32!BaseThreadStart
Common causes of stuck or stalled I/O
The database file is using NTFS compression.
A filter driver (virus, backups, ...) is not performing properly.
The nonpaged pool is under severe memory pressure.
An I/O path problem such as failure retries or lost Storage Area Network connectivity is taking place.
Troubleshooting
Troubleshooting an I/O problem requires a kernel debugger and getting an IRP trace to track down the I/O request state within the operating system. SQL Server already expected the I/O request to act in an asynchronous way, so adding a SwitchPreemptive does not resolve the problem. SwitchPreemptive would only cause a large number of workers to become stuck generating I/O requests and increasing overall context switching.
SQL Server uses latches to protect buffers while they are in I/O. This situation can lead to errors such as latch timeouts for buffers that are stuck in I/O.
User display under the SQL Server service
Developers of DLLs and COM objects assume that user-display capabilities are available. However, services such as SQL Server use a hidden desktop. Windows, message boxes, and other visual components are created on the hidden desktop. It is not possible for a user to respond to such objects. So, the worker becomes permanently wedged until SQL Server is restarted.
The following stack clearly shows that the thread is attempting to create a window but SQL Server runs as a service so user input is not allowed. This window or dialog will wait for a response that can never occur.
user32!InternalCallWinProc user32!UserCallWinProcCheckWow user32!DispatchClientMessage user32!fnINLPCREATESTRUCT ntdll!KiUserCallbackDispatcher user32!NtUserCreateWindowEx user32!_CreateWindowEx ...
Common causes of service-based window creation activity
The SetErrorMode setting is incorrectly modified by a linked server or xproc.
A DLL has makes an incorrect assumption that a user response is always available.
Troubleshooting
Use a debugger to determine the text or title of the dialog. This can often give you a better understanding of the condition that led to the display attempt. The stack itself helps outline the component and other details around the display attempt.
SQL Server can’t prevent all user object input attempts, so the owner of the component must make a correction so that it executes properly when run within a service. SQL Server 2005 adds basic protection against this by not allowing GUI activity from CLR components. Future releases of SQL Server may hook the creation of any window and terminate the attempt.
VirtualQuery of a fragment address space
Windows APIs are used by Windows-based applications to obtain operating system services. SQL Server uses many Windows API calls. SQL Server does not switch the worker to a preemptive state when the API call is expected to return quickly. If the API call does not return quickly, scheduler health issues arise.
This stack is from a known SQL Server issue and has been corrected in newer SQL Server 2000 and SQL Server 2005 releases. When multiple backups are started at the same time, the calls to VirtualQuery become heavy. Machines with fragmented virtual address spaces can encounter 17883 errors.
NTDLL!ZwQueryVirtualMemory KERNEL32!VirtualQueryEx KERNEL32!VirtualQuery sqlservr!BackupOperation::GetLargestFreeVirtualRegion sqlservr!BackupOperation::ChooseBufferParameters sqlservr!BackupOperation::InitiateStartOfBackup sqlservr!BackupEntry::BackupLog ...
Common conditions leading to lengthy VirtualQuery operations
The virtual address space has become fragmented due to allocation and deallocation operations.
Many SQL Server backups are started at the same time.
Heavy usage of VirtualAlloc / VirtualFree calls (unusual for SQL Server) while VirtualQuery is taking place.
Troubleshooting
SQL Server 2000 SP4 and SQL Server 2005 changed the backup buffer size decision logic to avoid heavy use of VirtualQuery.
Other activities can stress the virtual address space (VAS) fragmentation. For example, calls to the Windows API FormatMessage use VirtualAlloc / VirtualFree. This gets called when logging to the error log and event log. For example, if you enable audit logins, more calls to FormatMessage are made. These alone have not been reported as conditions for 17883 errors but they could contribute.
Virus scanner problem
Upon first glance, the stack encountered in this scenario appears to indicate a VirtualQuery problem. Further examination shows the MyVirusScanner functions on the stack. This virus vendor had an option supporting buffer overflow protection and had hooked a series of API calls. This stack shows the hooking of CryptAcquireContextW. The vendor had a bug that called VirtualQuery excessively and had to correct the problem.
NTDLL!ZwQueryVirtualMemory+0xb KERNEL32!VirtualQueryEx+0x1b KERNEL32!VirtualQuery+0x13 MyVirusScanner+0x36c3 MyVirusScanner+0x34d0 MyVirusScanner+0x117e <-- Virus hook ADVAPI32!CryptAcquireContextW+0xb8 sqlservr!SecureHash+0x117 sqlservr!FEvalPasswdW+0x35 sqlservr!FCheckPswd+0xe4 sqlservr!FindLogin+0x417 sqlservr!login+0x20f sqlservr!process_login+0x7d sqlservr!process_commands+0x201 ums!ProcessWorkRequests+0x272 ums!ThreadStartRoutine+0x98 msvcrt!_threadstart+0x87 KERNEL32!BaseThreadStart+0x52
As described earlier in this document, SQL Server 2000 SP4 made a change to SwitchPreemptive before calling CryptAcquireContextW but all this lead to is hundreds of workers becoming stuck. The problem in the virus software caused SQL Server to stop processing logins and rendered the SQL Server unusable and a preemptive worker is no longer detected as nonyielding.
Registry flush wait
The following stack was caused by a known issue that was corrected in SQL Server 2000 SP4. SQL Server 2000 SP3 added a heartbeat task to update the registry every 60 seconds so monitoring utilities could determine uptime information. The assumption was that a RegOpenKey and RegWrite would occur quickly so there was not a need to SwitchPreemptive.
NTDLL!NtOpenKey+0xbADVAPI32!LocalBaseRegOpenKeyADVAPI32!RegOpenKeyExW sqlservr!CServerHeartbeat::UpdateUptimeRegKey sqlservr!CServerHeartbeat::OnHeartbeatsqlservr!CHeartbeatTask::ProcessTskPkt ... ...
This assumption turned out to be incorrect because if this attempt occurs while the system is flushing the registry cache, new calls to the registry can be blocked for longer periods than expected.
Bug fixes are available for SQL Server 2000 SP3, SQL Server 2000 SP4, and SQL Server 2005 so SQL Server switches to preemptive scheduling before making any registry calls.
Conclusion
The examples presented in this paper identify a few bugs that have been corrected in later versions of SQL Server. The majority of known issues have been corrected in SQL Server 2000 SP4, SQL Server 2000 SP4-based hot fix releases, and SQL Server 2005. In addition, testing was increased before the release of SQL Server 2005 making it unlikely that SQL Server-based bugs are present.
The majority (95 percent or more) of issues reported to Watson and SQL Server Support Services are identified as external problems affecting the server that is running SQL Server and not the SQL Server process.
There have been several fixes in the areas of sort and compile operations that cause nonyielding conditions for SQL Server 2000 IA64. Before deploying SQL Server 2000 in this type of environment, obtain the latest hot fix for your SQL Server 2000 SP4-based deployment.
For more information:
https://www.microsoft.com/technet/prodtechnol/sql/default.mspx
Download
.gif "Cc917684.icon_word(en-us,TechNet.10).gif"){kind=icon}
DiagandCorrectErrs.doc
229 KB
Microsoft Word file