??? concurrency ?? ????? ?????????? ????? ?? SQL ????? ?? ???

???? ?????? ???? ??????
???? ID: 319892 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
BUG #: 102179 (SQLBUG_70)
BUG #: 356317 (SQLBUG_80)
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

??????

???????? ?? ????, ?? ??? SQL Server ??????? ????????? ???? ?? ??? ?? ?????? ??????????? ??? ????? ??? ?? ?????? ?? ????? ?????? ?? ????? ?? deadlock ???????????? ?? ?? ???? ?? ??? ????? ?? ???? ????

SQL Server 7.0 ?????? ??? 4 (SP4) ?? SQL Server 2000 ?????? ??? 3 (SP3) ???? ?? ????????? ????????? ????????? ?? ?????? ????????? monitoring ????????????? ????? ?? ????? ????????? ??? ???? ??? ??:
  • ????????
  • ??????? ????????
  • ?????/?????? (IO)
  • ??????
  • CPU
?? SQL ????? ?? ??? ????? ????????? ????????, ?? ?????? ???????, ???? ?? ????? ??? ?? ???? ???????? SQL ????? ?????? ??? ??? ??? ?? ???? ?? ?????? ????? ????????? ?? ???? ??? ????? ?? ??? ????? "???? ???????" ??? ?? ?? ???? ???

SQL Server 7.0

?????? 1223: ????????? ID % d: % d ???? ??????? ?????? ' %s ?? "% s" ??? ??????? ???? ??????? deadlock ?????? ?? ??? ???????? %d ?? ????? ??? % D ????????? ID %: % d ????? "% h" ?? ?????? ?? ???? ???

SQL Server 2000

?????? 1229: ????????? ID % d: % d ?????? ???????? ?? % d scheduler ??????????? ?? ????? ?? ??? ????
??? ?????? ????? ????? ???????? ????? ???

????????? ??? ????????:1223, 1229
Scheduler Hung ????????:17881, 17883
??? Schedulers Hung ????????:17882, 17884
???????:????? ??? ?? ????????? ??????? ?????? ?? ????? ??? ????? ???? ???? ???? ??? ?? ?? ?? ???? ?? ?????? ??? ?? SQL ????? ?????? ??? ?? ???????? ??? ???? ?? ????????? ???? ?? ??? ????????????? ?????? ????? ??? ?????? ???? ?????

?????? ?? ???, ???? 17883 ?????? ????? scheduler ?????? ????? ?? ???? ??? ??? ??, ?????? ??? ?? ?????? ?? ??? ??? ?? SQL ????? ????????? ?? ???? ?? ?? ???? ?????? ??? ????? ????? ?? ???? ??, ?? ????????? ?? ??? ????? ?????? ????? ???? ?? ???? ???
???:Microsoft ??? ??????? ?? ?????? 17883 ????????? ?? ??? ?????? ???? ?? ?????? ???? ??? ???????, 17883 ?????? ????? ?? ?????? ?? ?????? ?? ???? ??? ?? ????????? ??? ????? ??? Microsoft SQL Server ?????????? ?????? ?? ??? ????? ???????? ????? ???? ???? ???? ??, ????? 17883 ?????? ??????? SQL ????? ?????????? ?? ??? unrelated ???????????? ??? ?? ????? ?? ??? ?????? ?? ???, ????? ????????? CPU ????? ?? ????????? ?????? ?? ??? ?????? ? ??? ??? ??? ??????? reoccurrence ?? ?????? ?? ???? ?? ??? ?????? 17883 ?????? ????? ?? ??? ???? ????????? ???? ?????? ???

???? ???????

?? ??? outlines ????????? ????????????? ?? ??????? ?????? ????? ?? SQL ????? ?????? ??? ???? ?? ??? ??? ?? ???? ????

UMS

????? ??? ???????? ????????? ????? ???, ?? ??? ???? ?????? ???? ??? ???? SQL ????? ??? ?????????? ??? ?????????? (UMS) Ums.dll ????? ????? ??????

SQL Server 7.0 ?? Microsoft SQL Server 2000 ??????? schedulers ?? ????? ????? ????????? ???? ?? SQL ????? maximizes ????? ??????? ?????? ???? ?? relation ???????? ?????? ?????? ????? ???? ?? ??? ?? schedulers ??? ????? ????????? ???? ?? SQL ????? ??? ??? ?? ????? Win32 ????? strictly ????? ?? fiber (?? ?????) ????????? ???? ?? ??? UMS ??? ???? ??????? ???????? ?????? ?? ??? ??????? ????? Strictly ???????? ??????? ?? fibers ??? ???? ???, ?????? SQL Server ???? ??? CPU ????? ???? ?? ??? ???? ?????? ??????? primitives relates ?? ??? ????

?????? ?? ???, ??????? ????? ?? ????? ?? permits ????? waiters ????? (?? Win32 ????? ?? WaitForSingleObject) ?? ??? ??????? ???? releases ??????? (SetEvent) ?? ??? ??? ???? ?? ??? ??????

??? ?? ??????? ??????? ????

?? (????? ?????) ?????? ???? ????? ?????? ?? ??? ????? ?? ??? ????? ?????? ?? ????????? ???? ??? ??? ??? ??? SPID ???? ?????? ????? scheduler ???? ?? ??? ??????? ??? ?????????? ?? ??????? ??? ????? ??? ?? ????? ????? ????? ??? ?? ?? ??? ????? ??? ?? ????? ????? un-resolvable ????????? ?????? ??? ???, ?? ????? ?????? ????? SQL ????? ?????? ??? ???? ?? ??? ???? ??:

SQL Server 7.0

?????? 1223: ????????? ID % d: % d ???? ??????? ?????? ' %s ?? "% s" ??? ??????? ???? ??????? deadlock ?????? ?? ??? ???????? %d ?? ????? ??? ????????? ID % d: % d ????? "% h" ?? ?????? ?? ???? ???
???????? ?????:
  1. SPID ?? ????????? ?? ??? ??
  2. ECID (?? ????????? ???????? ID) ?? ????????? ?? ??? ??
  3. ??????? ??? ???
  4. ?????? ???
  5. ??????? UMS ???????? ID
  6. SPID owning
  7. ECID owning
  8. Owning ?????? ???

SQL Server 2000

?????? 1229: ????????? ID % d: % d ?????? ???????? ?? % d scheduler ??????????? ?? ????? ?? ??? ????
???????? ?????:
  1. SPID owning
  2. (?? ????????? ???????? ID) ECID owning
  3. ??????? UMS ???????? ID owning

???? ?? ??? ?????

SQL ????? ??? ?? ????????? ?????????? ????? ???? ?? ??? ?? ????? ???? ???

?????????? ??????? ?? ????? ???? ?? ??? ????? ??????? ??? ?? ???? ?? ?? ?????:
  • ?? ????????? ???????? (??????? ????-T###)
  • DBCC traceon (?? ????? ????###)
SQL Server 7.0:-T1216

SQL Server 2000:-T1261

???:?? ?? typographical ?????? ???? ??? -????? ???? deadlock ?????? ?? ??? ?????? ?? ??? ??? T1216 SQL Server 2000 ?? ???? ?? ?? ????? ??? ??? ?????, 1261 ????? ??? ?? ???? ????? ???

????? ????????

1 ??????? SQL ????? ?? ?????? ???? ???

1 ??????? ??? ????????? ??????? ?? ???? ?? ?? ???? ?? ?????? ???? ?? ?? Transact SQL ???? ????? ???

?????? ?? ???::
begin tran
update authors set au_lname = 'test'
1 ??????? ?? ???? IDLE, sleeping, ?? ???? ???? ????????? ??? ?? ?? ???? awaiting ?? ??? ??? ????? ?????? ??sysprocesses?????? ?????

??????? 255 ?? 2: ???? 254 ???? ??????? SQL ????? ?? ??? ?? ???? ?? ???? ??? ?????? ?????? ?? ?????? ?? ??????? ????? ??? ?? ????? ?? ??? ???????

1 ??????? ????????? ?? ???? ???? ?? ?????? ???? ?? ????? ??? ????? ????? ??? tied ??????? 255 ?? 2 ?????? ??????? ?????????? ?? ???? ?? ???

????????

?? ?????? ????? ????????? ????????? ????? ?????? ?????? ??? ?? ??? ??????? ?????? ???? (???? ???????? 5 ?????), ????? ?? ???? ?? ???? SQL ????? ?????? ????

???:???? ????? ?? ???????? SPID/ECID ?????? ?????? ?? ????? ?? ??? ?? ?? ?? ??? ??? ?? ??? ?????, ???? ????? ?????? ??????????? ?? ????? ?? ????? ??? ?? ???? ????

SQL ????? ?? ?????? ???????? ??? ?? ?? ???? ????? ???????, ?? ????? ????? ?????? (1223 ?? 1229) ?????? ????? ?? ??? ??? ???????? ?? ?? ?????? ?? ???? ??, ?? ?? ?? ?????? ?? ??? ?????? ??? ??? ?? ?? ?????

??????? ???? ?? ?????? ???????

??? ?????? ?? ????? ???? ??????? ???? ?? ??? ?????? ???????, ?????? ??????? ??? ?? ??????? ?? ????? ?? ??? ??? timeouts ??? ???????, ?????? warrants ???? ??????? ??? concurrency ??? ??? ????????? induced reduction ?????? ???

Transact-SQL KILL

??? ???? ?????????? ?? ?????? ???? ??? ????? ??sysprocesses?????? ????, ?? ????? ?? ???? Transact SQL ????? ' ???? ???????? SPID ?? ?????? ???? ?? ??? ?? ??????? ????? SPIDS ????? ????? ?? ???? ????, ?? ?????? ?? ?? ??????? ?????? ??? ???? ?? ??? ?????? ???? ?? ????
251004SQL Server 7.0 ???????? ??????? ???? ?? ??? ???? ????
271509SQL Server 2000 ???????? ??????? ???? ?? ??? ???? ????
263889??????? SQL [[COMPILE]] ?? ??????? ?? ??? ???? ??

?????? ?? ??????

??? ?? ??????? ???? ?? ????sysprocesses?????? ?????? ??????? ?? ??????? (Sqlservr.exe) ?? ?? ????????? ???? ????????? ?? ?? ???????? ???? ?? ??? Microsoft SQL Server ?????? ?? ?????? ?????

Parallel queries

In rare circumstances, this error message can occur because of a poor parallel query plan selection. If the parallel query elects to use a significant number of available SQL Server workers to complete the query, it can exhaust the SQL Server worker pool. Thesysprocessessystem table contains an ECID column to indicate the number of workers that are being used on behalf of the individual SPID. If the ECID value is high in relationship to the physical CPUs on the computer, it is generally an indication of a poorly tuned query. Review the query plan and themax degree of parallelism(MAXDOP) query option setting to correctly tune the query in question.

Schedulers matter

The number of logical schedulers matters. When SQL Server starts, themax worker threadsetting is divided equally among the logical schedulers. As the number of CPUs available to the SQL Server increases the worker queue is divided more. An application that exhibits undesirable transaction scope activity can manifest resource shortage scenarios faster when more CPUs are involved. For this type of scenario, the applications transaction scope is immediately corrected.

The following table shows the worker pool assignments based on the number of CPUs if thesp_configurestored procedure setting formax worker threadsis equal to 255.
?? ?????? ?? ??????? ?????? ?????? ?? ??????? ????
CPUs blockingChain length
1255
2128
464
832
1616
We recommend that you keep themax worker threadsetting at the default of 255.???? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ????? ???? ?????? ????? ????::
319942How to determine proper SQL Server configuration settings

UMS health

A new internal routine has been added to check the health of the logical scheduler every 60 seconds. If the scheduler is deemed as stalled, or it has stopped responding, the appropriate error message is logged to the SQL Server error log. An error is logged every 60 seconds until the issue is resolved.

As mentioned earlier, these messages are often an indication of a previous event. Carefully consult the SQL Server error log and application event logs to help you determine the root cause of the problem.

???:A snapshot is taken every 60 seconds. Therefore, it can be 120 seconds before the condition is first detected.

SQL Server 7.0

Error: 17881 - The Scheduler %1!ld! appears to be hung. PSS 0x%2!p!, EC 0x%3!p !, UMS Context 0x%4!p!
Error: 17882 - Potential deadlocks exist on all the schedulers

Trace Flags

You can disable these two checks if you start SQL Server 7.0 with the-T1217startup parameter.

SQL Server 2000 SP3

8.00.760 (SP3)
Error: 17883 - The Scheduler %1!ld! appears to be hung. SPID %2!ld!, ECID %3! ld!, UMS Context 0x%4!p!

8.00.765

Starting with the 8.00.765 hot fix, the message has been changed to be more descriptive.
Error: 17883 - Process %1!ld!:%2!ld! (%3!lx!) UMS Context 0x%4!p! appears to be non-yielding on Scheduler %5!ld!

??????::
2003-03-21 08:22:20.27 server Error: 17883, Severity: 1, State: 0
2003-03-21 08:22:20.27 server Process 51:0 (dbc) UMS Context 0x018DA930 appears to be non-yielding on Scheduler 0.
2003-03-21 08:22:22.45 server Stack Signature for the dump is 0x00000000
??????: 17884 - ??????? deadlocks ?? ????? ??? schedulers

???? ?? ??? ?????

??? ?? SQL ????? ?? ??????? ?? ?? ???? ?? ????? ?? ???? ??? - T1260 ????????? ???????? ?? ????

SQL Server 2000 ???????? ?????

SQL Server 2000 SP3 ?? ??? ??????? ?? ??? ??, ??? ???????? ????????? ?? ?????? ???? ?? ?????? ?? ??? ????? 8.00.765 ????? ?? ??? ??????? ?? ??? ??, ?? SQL ????? ?? ??? stalled scheduler ???? ??? ????? ??, ?? ??? ???????? ????? ?? ????? ???? ??? ???

?? ?????? ??????? (17883 and17884) ?? ??? ???????? ??????? ?? ?????? ????? ?? ????? ?? ??? ??????? ??????? ?? SQL ????? ????????? ?? ???? ?? ??? ??? ??? ???????? ????? ????? ?? ???? ????? ?? ???????? ??????? ?? ??? ??? ???????? ????? ?? ????? ???? ?? ??? ????? ???? - T1262 ???? ?????

???????? ????? ??? LOG ?? ????? ???? ??? ?? ??????? SQLDmpr ?? ?? ### .mdmp. ?? ???????? ????? ?????????? ???? ?? ???? ??? ?????? ?? ???? ?? ??? ????? ??? ??? ???? ?? ??? Microsoft ?????? ???

?????? 17881 ?? 17883 ??????

These messages indicate a single UMS scheduler has experience a yield problem. The health monitoring has detected what appears to be a scheduler with a worker thread that is not allowing other workers to progress, and the scheduler is being flagged as non-responsive. A scheduler that has stopped responding is generally a bug with the SQL Server product or an external component (XProc, COM object, and so on).

The following are examples of known 17833 conditions. Make sure that you search the Microsoft Knowledge Base for related articles. If your system requires an updated patch, apply it accordingly.
815056FIX: The checkpoint process can delay SQL Server database activity and does not yield Scheduler correctly causing Error: 17883 to occur
810885High-end disk subsystems may experience error 17883
If you cannot determine the root cause immediately, consult the error log for problems and engage in extended support efforts.

When a scheduler is not properly responding, it can reduce overall concurrency for SQL Server. SQL Server can also appear to be stalled or it might stop responding.

Error 17882 and 17884

These messages indicate that all the UMS schedulers have experienced yield problems. This indicates a SQL Server system wide problem and SQL Server will appear to have stopped responding. As with the 17881 and 17883 messages, consult the error log and Microsoft the Knowledge Base for more information. If necessary, engage in extended support efforts.

???

???? ID: 319892 - ????? ???????: 04 ?????? 2010 - ??????: 2.0
???? ???? ???? ??:
  • Microsoft SQL Server 2000 Service Pack 3
??????: 
kbsample kberrmsg kbbug kbfix kbinfo kbmt KB319892 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:319892

??????????? ???

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com