??? INF: SQL Server ??? ????? ?????

?????? ????????? ?????? ?????????
???? ???????: 224453 - ??? ???????? ???? ????? ????? ??? ???????.
????? ???? | ?? ????

?? ??? ??????

??????

?? ??? ??????? ???? ????? "?????" ??? ????? ???? ???? ??? ????? ????????. ???? ?? ????? ??? ?? ???? ???? (SPID). ?? ?? ??? SPID ???? ???? ?????? ??? ??? ???? ???????? ??? ????? ?? ??? ??? ???? ????? ?????? ?? ??????? ???????. ????? ?? ???? ????? SPID ??? ?? ????? ?????? ????? ???????? ???????? ??? ???? ????? ????? ???? ?? ???? ????. ????? ???? ???? ?? ???? ???? ???? ?? ????. ?? ????? SQL Server, ???? ?????? ?? ??????? ?????? ?? ????? ???? ???? ??? ???? ??????? ???? ???? ??? ??? ??????? ?? ??? ??????? ?????? ?? ??? ????? ??????? ??????. ???? ??? ????? ???? ????? ??? ??? ????? ?? ?? ??? ???? emanate ?? ??? ??????? ?? ??????? ?????? ??? ????? ??????? ?????? ??????.

??????? ????

?? ??? ???? unavoidable ???? ????? ??? ????? ?????? ??????? "(RDBMS) ?? ???????? ??? ????? ???????. SQL Server ??? ???? ????? ????? SPID ???? ????? ??? ???? ???? SPID ???? ????? ?????? ??? ??? ????? ??????? ??? ??? ??????. ????? ?? ???? ?????? ?????? ???? SPID ????? ????? ?????? ????? ????. ??? ??? ?????? ??????? ??????? ?????? ????? ?????? ??? ????? ????? ?? ??? ?????? ??????? ????????. ??? ?? ?????? ?????? ??? ?? ???? ??? ???? ???? ???? ??????? ?? ????? ?? ????? ??????? ??? ???? ??????.

???? ???????? ?????? ??????? ????? ????? ???? ???????? ??? ???????? ?? ????? ?, ???????? ? ?????? ??? ??????? ??? ????????? ????. ??? ?? ??? ????? ????????? ???? ?????? (??? ??????? ?? ??????? ???????) ? ????????? ?? ?????? SELECT ??? ??? ????? ??????? ??? ???? ?? ??? ?????? ??? ???????, ???? ?? ??? ??? ?????????. ??? ????? ?? ??????? ?? ?????? INSERT ? UPDATE ? DELETE ???? ????????? ?? ????? ???????? ? ?????? ????????? ??? ??????? ?? ??? ??? ?????.

????? ?????? ???????????? ??? ??? ???? ????? ????????? ????? ?????? ??? ????????? ????? ??? ???????? ?????? ?? ?? ??????? ??????? ?? ?????????. ?????? ??? ??? ?? ????? ??????? ????? ? ??????? ??? ????????, ???? ???????? ??????? ?? "??? SQL Server ??? ??????":
  • ????? ?? ???? ????? ??????
  • ????? ????? ?????? ??????? ??
  • ????? ???????
  • ????? ?????
  • ?? ??????? ??? ???????? ??? ????? ??????? ?? ???? ????? ??????
  • ?????? ?? ????????? (???? ????? ??????)
??? ????? ??? ????? ??? ?????? ??? ????? detrimental ??? ???? ?????? ??? ???? ??? ??? ??????? ???????:
  • ????? SPID ????? ??? ?????? ?? ??????? ????? ????? ?? ????? ??? ????? ???. ??? ????? ?? ??? ??? ???? ????? ????? ???? ???? ??? ????? ??????.
  • SPID ????? ??????? ??? ?????? ?? ??????? ???????? ????? ???. ?? ??? ????? ???? ??? ????? ?? ??? ?? ???? ?????? ??? ??????? ???????? ???? ??? ?????.
?? ????????? ????? ????? ??????? ??? ?? ???? ??? ????? ??? SPID ????????? ?? ???????. ??? ???? ???? ??????? ???? ?? ????? SPID ?????? ??? ??? ????? ???? ????? ?????, ????? ??? ??? ??????. ???? ?????? ???? ????? ??????? ??????? ???????? ???????? ????? ??????? SQL Server ?? ????????? SQL ??????? ??? ???????. ????? ?????? ?????? ?? ???? ??????? ???? ??????.

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

??? counteract ??????? ?? ??????? ????? ??????? ? ???? ?????? ????? ?????? ??????? ??????? ?????? SQL ???? ?????? ???? ??????? ??? SQL Server. ???? ?? ??? ??????? ?????? ???? ????? ?????? ????? ????? ?????? ??? ??? ???? ????? ???????. ?????? ??? ??? ?? ????? ?????? ??? ??????? ?????? SQL ???? ???????? ??????? ?? "????? ??????? ?? Microsoft" (????? ?? ??? ???????? ??? ???? ?? ???? ??????? ??? ?? ??? ??? ?????? ??????????):
271509????? ?????? ??? ?? SQL Server 2005 ??? SQL Server 2000
???? ????? ??????? ?????? ?? ??? ??????? ?????? ?????. ????? ????, ????? ????? ?????? ??? ??? ????????? ?? SQL Server ????? Studio.
  1. ????? SPID (????? ???? ?????) ?? ??? ????? ??? ? ????? SQL.
    ???????? ??? ??????? ??????? ?????? ?? ????? ????? ???????? ??????? ????? ??????, ????? ????? ??? ????? ??? ???????? ??????? ???? ??? ??????? ?? ???? Studio ????? ???? SQL. ?????? ????? ?????? ???? ????????? ?????????:
    • ???? ??? ?????? ?????? ??? ???? ?????? ????? ?????? ????? ?????? ????? ? ?? ???? ??? ?????? ? "???? ????????? ?????". ???? ??? ??????? ?????? ????????? ?? ??? ??? ?????. ??? ??? ?????? ???????? ????? ??????? ????????? ???? ??? ????? ?????? ?????? ?????. ???? ????? ??? ??????? ????? ?? "??? ????? SQL" ? "???????? SQL ????."
    • ?????? INPUTBUFFER(<spid>) DBCC ??????? ??????? ???? ?? ??????? ?????? SPID.
  2. ????? ?? ????? ????? ????????? ????? ????? ??? SPID.
    ????? ?????? ??????? ???????? SPID ?? ????? ????? @@ TRANCOUNT. ??? ???? ???? ???? ????? ?? ???? SPID ?????? ????????? ?? ?????? sysprocesses ??? ???:

    SELECT open_tran FROM master.sys.sysprocesses WHERE SPID=<blocking SPID number>
    go
    						
    ?????? ???? ?? ??????? ?? ?????? @@ TRANCOUNT SPID. ???? ??? ??? ????? ????? ????? ????????? ?? ??? SPID ???? ????? ???? ???? ????? ??? ????? ???? ???????. ??? ???? ??????? ??? ???? ?????? ???? ?? ????? SPID ???? midst ?????? (?? ?????? ???? ??? ??????? ???????? ????????? ????? ???? ????? ? ???????? ??? ????? ??? ????????).

    ????? ????? ?????? ??? ??? ??? ?? ??????? ?????? ????? ????? ??????? ?? ????? ???????? ???????? OPENTRAN DBCC database_name.

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

???????? ??? ????????? ???????? ????? ?? ??????? ???? ?????? ???? ???? ????? ??????? ??????? ??? ?????? ??????? ????? ????? ??? ??? SQL Server. ?? ????? statementthat ?????? ??? ????? ?? ??????? ?????? ?????? ??????? ?? ??????? ?????? ?????? ?? ???? ????? SPID ?????? ?????? ???? ??????,. ??? ???? ?? ??? ??????? ??????? ??? ????? ??? ?? ???? ??? ?????. ???? ????? ???? ???? ????? ??????? ?????? ???? ??????? ??????? ?????? SPID ???? ???????? ???????. ?????? ??????? ??????? ??? ????? SQL Server ???? ????? ??????? ??????? ????? ?????.
  1. ???? SQL Server ???? ????? ???????.
  2. ?? ??????? ???? ??? ??? ???? ?? ???? ??? ????.
  3. ?? ??????? ???? ??? ????? ???? ???? ??? ??????? ????????.

    ??? ??? ?? ??? ????? ??? ?????? ??? ??? ?????? ????? ?? ????????. ???? ???? ??? ???? ????? ?????? ?? ????? ?????. ????? ????? ?? ?????? ??????? ????? ?????? ??????.
  4. ??? ????? ??????? "????? ???????" ? ???? ?????? ????? ???????? ????? ???? ??????? ? ????? ???? ???????.
  5. ??? ????? ??????? ????? ??????? ????? ????? ??????? ???? ?? ????? ?? ?????? 1 ?????? ????? ??.

    ???????? ??? ???? ?? ????? ????? ??????? ?????? ??????? ?? ?????? 2 ?????? ??? ???? ?? ?????????. ??? ??? ????? ?? ???? ????? ??????? ????? ?? ???? ???????? ??????? ??? ?? ?????? 1 ??? ?? ????? ?????? ???????? ??????? ???????? ??? ????. ??? ?? ??? ????? ?????? ?? ?????? 2 ?? ???? ?? ????? ?????? ???? ????? (?? ?? ???? ??? ??????? ???????? ?????? ??? ???? ??????? ?? ????? multi-statement). ??? ???? ??? ?? ??? ??????? ?? ?????? 2 ???? ????? ????? ??????? ??? ?????? ?????? ??? ??????? ??????.
???? 1: ??? ?????
?? ??? ??????????? ??? ??????
????????????
??????? ???????????????????
??????? ??????????????????
????? ??????????? ????? ??????
????? ??????????? ????? ??????
????? ?????????? ??????
??????? ?????RPC: ???????
tsqlSQL:BatchStarting

???? 2: ????? "??????? ??????"
?? ??? ??????????? ??? ??????
????????????
?????????DTCTransaction
?????????SQLTransaction
??????? ?????RPC: ?????
tsqlSQL:BatchCompleted
??????? ?????SP:StmtStarting
??????? ?????SP:StmtCompleted

?????? ??? ???? ?? ????????? ??? ??????? ???? ????? ??????? ?? SQL Server ?????? ?????? SQL Server ????????.

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

?? ???? ??? ????????? ?????, ????? ????? ??? ?????? ??? ????. ????? ??????? ?? ??? ??????? ?????? ??? ????? ??????? ??? ????????? ??? ?????? ??? ??? ????? ????????? ??????? ??? ????????. ??? ???????? ????? ??? ??????? ?????? ?????? ?? ??????? 271509 (??????? ??????) ??? ?????? ??????? ??? SPID ??? ????? ???? ???? ????? ??????? ?? ????? ??????? ?????.

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

???? sys.sysprocesses ?????? ???? ??????? ???
??? ?? ??? ????? ????? ?????? ??????? ?????? ??? ????? ????? ??????? ?????? "SPID ?? ??? ??? ???????" ???? SPID ??? ??? SPID ?????? ?? ????? ???????? ?????.
SPIDs at the head of blocking chains
??? ???? ?????? ????? ? ?? ???? ????? ????? ???? ??? ???? ?????? ??? ????? sys.sysprocesses ??????? ??????? ?????? SPID ???? ??? ????? ?? ?????? ????????.
???? ??????? sys.sysprocesses ?????? ??? ??????? ??? SPID ?? ??? ??????? ??????.
?? ????? ?? ??? ????? ?????? sys.sysprocesses ???????:

??????

???? ??? ?????? ?????? SPID ?????. ?????? ??? ??????? ??? ???? ???? ????? ??????? SPID ?? ????? ??????? ????? ????????? ?? ???? ???. ???? ??? ???? runnable??? ??????? ?? sos_scheduler_yield SPID ???? ?????? ???????. ???? ?????? ?????? ??????? ?????? ?? ??? ?????? ??????.
?? ??? ??????????? ??? ??????
?????????? ??? ??????
?????SPID ??? ????? ???? ????? ??? ????? ?? ???? ???? ???.
?????? ??? ?????? ????? SPID. ???? ??? SPID ????? ??? ?? ???????.
?????SPID ??? ??????? ?????? ??? ?????.
runnableSPID ??? ???????? runnable ??? ????? ? ?????? ?????? ??? ??? ?????.
Sos_scheduler_yield?? ????? SPID ??? ??? ??????? ???? ????? ????? ????? ?? ??? ????? ?????? SPID ??? ?????? ??? ??? ?????.
????? ?????????? ?????? ??? ????? ?? ????? SPID.
?????? ??? ?????? ???????SPID ??? ????????? ?? ???? ?????????.
Defwakeup??????? ??? ?????? SPID ???? ??? ????? ??? ???????. ??? ?? ???? ??? ??? waitresource ?????? ???????.

Open_tran

???? ??? ????? ????? ????? ????????? SPID. ??? ???? ??? ?????? ???? ?? 0, ??? ?????? ?????? SPID ?? ?? ???? ????? ????????? ???? ?? ?????? ???? ?? ??? ?? ???? ???? ????????.

Lastwaittype waittype, ? waittime

??? lastwaittype ?? ????? ????? ????? waittype ???? ????? ????? ?????. ??? ??? waittype 0x0000 ? ?? ??? SPID ?????? ??? ??? ????? ???? lastwaittype ?????? waittype ????? ????? SPID. ??? ?? ??? waittype ??? ??? ?????? lastwaittype ?????? waittype SPID.

?????? ??? ??? ????? ?????? lastwaittype ???? waittype ???? ??????? ??????? ?? ????? ????? Microsoft:
822101??? waittype ?????? lastwaittype ?? ?????? master.dbo.sysprocesses ?? SQL Server 2000 ? SQL Server 2005
????? ?? ????????? ??? sys.dm_os_wait_stats ???? "??? SQL Server ??? ??????".

???? ??????? ?????? waittime ?????? ?? ??? ??? ????? SPID ??????. ??? ??????? ????????? ?? ???? sys.sysprocesses ???? ???? ?? ?????? waittime ??? ?? ???? waittime ?? ??????? ????? sys.sysprocesses ??? ???? ??? ?? ??? ???? ?????? ????? ????? (??????? waittime ??? ?????) ?? ?????? ??? ????? ??????? ???? ??????? ??. ???? ?????? ??? ?? ???? ?????? waitresource ??? sys.sysprocesses ???????.

Waitresource

???? ??? ????? ??? ?????? ?? ?????? SPID. ???? ?????? ?????? ??????? waitresource ?????? ? ?????? ??? ????:
?? ??? ??????????? ??? ??????
???????????????
??????DatabaseID:ObjectID:IndexID???????: 5:261575970:1
?? ??? ??????? ??? 5 ???? ????? ?????? ????? ????? ?????? pubs ?? ???? 261575970 ???? ?????? ?????? ? 1 ?? ???? ?????? ????????.
????DatabaseID:FileID:PageID??????: 5:1:104
?? ??? ??????? ??? 5 ???? ????? ?????? pubs 1 ???? ????? ?? ??? ???????? ??????? ? ?????? 104 ???? ???? ????? ??? ???? ??????.

?????? ??? ???? ???? ?????? ????? ??? ??????? ????? PAGE DBCC (dbid, fileid ? pageid, output_option) ????? m_objId. ??? ???? ??????:
DBCC TRACEON ( 3604 )
DBCC PAGE ( 5 , 1 , 104 , 3 )
?????DatabaseID:Hobt_id (???? ??????? ???? ???????)?????: 5:72057594044284928 (3300a4f361aa)

?? ??? ??????? 5 ???? ????? ?????? Pubs, 72057594044284928 Hobt_ID ????? 2 index_id ??? ??????? ???????? ?? ???? ?????? 261575970 (?????? ??????). ?????? ????? ????? ???????? sys.partitions ?????? hobt_id ????? ???? ???? ?? ???? ????. ?? ???? ??? ????? unhash ??????? ???? ??????? ??? ???? ????? ???? ????.
??DatabaseID:FileID:PageID:Slot(row)?????: 5:1:104:3

?? ??? ??????? ??? 5 ???? ????? ?????? pubs ? 1 ???? ????? ?? ??? ???????? ???????, ?????? 104 ???? ???? ????? ??? ???? ?????? ? ???? 3 ???? ??? ???? ???? ??????? ??? ??????.
??????? ???????DatabaseID:ObjectID [[???????]]TAB: 5:834102012 [[???????]] ??? ??? ????? ???? ???? ????? ?? ??????? ??????? ????? ??? ????? ????. ???? ????? ???????? 5 pubs, ?? ???? 834102012 usp_myprocedure ??????? ??????. ???? 263889 ????? ????? ????? ?????? ??? ???? ?? ????????? ??? ??? ??? ????? ???????.
????? ????

???? ?? ???? ??????? sys.sysprocesses ???????? ???? ????? ????? ?????. ????? ????? ?????? ??? ????? ??? ?????? ???????. ??? ???? ??????? ????? ????? ???? ???? ??????? ??? ?? ????? ????? (??????) ??? ??? ?????? ???? ??????? (net_library) ? ????? ??? ?????? ??????? ??????? ?? ??? SPID (last_batch) ?????.
???? ??????? INPUTBUFFER DBCC.
??????? SPID ?? ???? ????? ??? ?? ?? waittype ????? ????? ?????? ???????? ????? ??? INPUTBUFFER DBCC ?????? ????????? ?????? ?? ??? SPID.

?? ???? ?? ???????? ????? ??? ?? ????????? ???? ???? ?? ???? ????? ??? ??? ?????????? ??????? ???????? ????????. ??? ???? ?? ???? ?????? SPID ????????? ?? ???? ?? ?????? ?????? ??????? ?????? ?????? ??????? ???????. ?????? ??? ????? ??? ??????? ???? ????? ??????? ?????? SPID inputbuffer ???? ???.

?????? ??? ???????? ????? ??? ????? ?? ??? ?????, ??? ?????? ??? ?? ???? SPID ?? ?????? ????? ???? ????? ??? ???? ??? ????? ????????? INPUTBUFFER DBCC ?????? ???? ???? ???? ?? ??? ? ?? ??? ?????? INPUTBUFFER. ???? ??? ??? ??? ??? ?? ???? ?? ??? SPID ??? ??? ?? ?? ?? ???? ?????. ??? ??? ??????? ????? ??? ??????? ????? ???????? ????? ??? ????? ??????? ???? ?????? inputbuffer ??? ??? (??? ????? ?? ??? ????? ???? ?? ????) ? ?? ??? ???????? ???? ????? ??????? ???????? ?? ??? ?????? ?????? ?????? ?? ?? ????????? ?? ??????? SPID.

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

??? ??? ???????? ???? ????? ??????? ?????? ???? ?????? ?? ?? ????? ???????. ?????? ????? ??? ???? ??? ??? ???? ?? ????? ?? ?? ??? ????? ??????; ???? ??????. ???? "???? ????? ??????? ?????????? ???? ?????? ???? ???? ??? ???????? ????????. ?? ???? ?????? ????? (?? ??????? ???? ???? ??? ?????) ? ???? ????? ??????? ???? ?? ????? ???????? ???????? ?????? ????? ????? ???????? ?? ?????? ??????? (?????) ?????? ????? ???????? ?????? ????? ???????. ????? ????? ???? ????? ?? ???? ???? ?????? ??? ??? ????? ??? (?? ??????? ????? ???? ??? ???). ????? ????? ??? ???????? ???? ????? ??????? ??? ???? SQL Server (?? ??????? ???? ??? ??? "??? ???? ? ?? ???? ??? ????) ????? ????????? SQL ????? ????.

?? ????? ??? ????? ??????? ??? ??? ??? ???? ?????? ??????. ??? ??? ?????? ??? ??????? ??? ???? ??? ?????? ????? ???????? ???? ?? ??????? ??? ??? ????? ??????. ??? ??? ???? ???? ?? ?????? ????? (?? ??????? ???? ???? ??? ??? ????) ?? ????? ???? (?? ??????? ???? ???? ??? ???) ??? ????????. ??? ????? ??? ??? ???? ??????, ??????? ?? ???? ???? ?????? ???????? ??? ?????, ??? ?? ???? ???? ????????. ???? ????? ? ????? ????? ?????? ???????? ??? ?????? ???????? ?? ????? ?????? ?????.

?? ???? ????? ????:
  • ?? ??????? ????? ??? SPID ??? ????? ??? ????? ???????? ??????? ?
    ????? ?????? ?????? SPID ????? ?? ??? ????? ??? (?? ??????? ???? ???? ??? ?????; ?? ??? ????? ??????? ????? ??????? ?????? ?????? SPID). ?? ????? ??? ????? ?? ????? ??? ????? ??? ?? ??? SPID ??????. ??? ??? ?????? ????? ????????? ???? ?????? ?????? ??? ??? ????? ??????. ????? ????? ????? ???? ?? BEGIN, ?????? ????? ?? ????? ????? ????????. ?????? ???? open_tran ?? ?????? sysprocesses ?????? ?? ?????? ???? ??????? ????????. ????? ????? ??????? ? ???? ???????? ???? ?? ????? ????? ??? ????? SPID ???????.

    ????? ????? ????? ??????? ????? ?????????. ????? ?? ???? ??? ??? ???? ??? ??????? ????????. ??? ?? ??? SPID ??? ????????? ??????? ????? SP: ??? ????? ?? SP: ?????? ?????; ??? SQLBatch ?????? RPC ????? ??????? ?????. ??? ????? SP ??? ?????? ??? ????? ??? ????? ???????? ???.
  • ?? ?? ??? ??????????? SPID ?? ??? ??? ??????? ?
    ??? ??? ?????? ????? ???????? ????? ????? ?????? ????? ??? ????? ?????????. ????? ??? ?? ?????? ??? ??????????? ??????? ????? ????? ???? ????? ?? ???. ?????? ??? ????? ????????? ???? ??? ???? ???????? ???????? (CPU) ? ??????? ? ??????? ??????? ????? ????? ??? ???????.

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

????? ?????? ????? ??????? ??????? ?????? ??? ??????? ???????. ????? ??? ?????? ????? ?? ?????? ????????? ??? ????? ??????? ?? ?????? "?????? ????? ????? ???????"?"?????" ?? ??? ??????? ?????. ???? ????????? sysprocesses ??????? WaittypeOpen_Tran ?????. ??? ? ???? ?????? ??? ??? ???? ?? ???? ??? ?????? ??.

?? ??? ??????????? ??? ??????
?????????WaittypeOpen_Tran?????????? ??? ??????? ???? ???????? ??????
1???? ???>= 0runnable???? ??? ?????? ?????????.??????? ?/Physical_IO, ???? ???????? ???????? (CPU) ?? Memusage ????? ?? ?????. ???? ????? ??? ?????? ??? ?????????.
20x0000>0??????? ??? ???? ??????? SPID.?? ???? ?????? ????? ?????? ?? ???? ????? ??????? ??? ?????? ?????? ??? ??? SPID ???? ???? ????????? ?? ????? ?????.
30x0000>= 0runnable"??". ??? ?? ?? ??? ??? ???? ?????? ?? ????? ????? ??????. ???? ??????? SPID ???? ?? ?????? ??? 30 ?????.??? ??? open_tran = 0? ? SPID ????? ????????? ????? ???? ????? ??? ???????? ????????? (????? COMMMITTED) ? ??? ??? ???????.
4?????>= 0runnable"??". ??? ?? ?? ??? ???? ??????????? ?? ????? ??????? ??????. ???? ??????? SPID ???? ?? ?????? ??? 30 ?????.?????? ??? ?????? ?? sysprocesses ?? SPID ?? ??? ????? ??? ???? ??? ??? SPID ???? ????.
50x0000>0?????? ??? ?????? ??????????.?? ???? ?????? ????? ?????? ?? ???? ???? ????? ??????? ?????? SPID ??? ???? ??? ???? ??????? ?? ????? ??? ?? ????? ?????? ????? ?????? ??? ?????? ???????.
60x0000>0??????????. ????? Windows NT ????? ???? ????? ???? ???? ?? ???? ???? SQL ???? ??? ???????.???? last_batch ?? sysprocesses ???? ????? ?? ????? ??????.

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

????? ?????????? ???????? ????? ???? ???????? ?? ?????? ?????. ???? ??? ?????? ?????? ?????? ??? ??????? ???????? ??? ?????? ??? ???.
  1. ??? ???? ?????? ????? ??????? ????????? ?? ??? ????? ????

    ???:
    ??? ?? ??? ????? ?? ??????? ??? ????? ?? ??? ?????? ?????? ?????????. ?? ???????? ??? ????? ?? ??? ??????? ?? ??? ???? ????? ?? ???? ? ?????? ?????? ???? ???. ?????? ??? ??????? ??? ??????? ??????? ???????? ??????? ??? ????? ????? ???? ????? "????? ????? Microsoft" ???????:
    243589????? ??????? ????? ??????????? ??? ????? SQL Server 7.0 ?? ????????? ??????
    ??????? ????? ?????? ??????? ??????? ????????? ???? ????? ????? ???????? ???????:
    224587??? TO: ??????? ????? ???? ??????? ?? ???? SQL
    ????? ?? ?????????? ???? ??????? ??? SQL Server 2008 ??????? ?????? ???? ?? ??? ?????? How-to ??? ???? MSDN ??????:
    http://msdn.microsoft.com/en-us/library/ms187830.aspx
    ??? ??? ???? ??????? ??????? ????? ????? ??? ??? ?????????? ??????? ???? ????? ???? ???????? ??? ?? OLTP ???? ???? ??? ????.
  2. ??? ???? ?????? SPID Sleeping ???? ?? ????? ???? ????????? ????? ???????

    ???? ????? ??? ????? ?? ??? ?????? ?????? SPID ?? ???? ?? ????? ???, ??? ????????? ?????? ?? ????? ????? (@@ TRANCOUNT ? open_tran ?? sysprocesses) ???? ?? ?????. ???? ?? ???? ??? ??? ??? ??????? ????? ???? ??????? ?? ????? ?? ????? ??? ????? ????? ????? ??????? ?? ?????? ????? ?/?? ????????. ????? ????? SPID ???? ????????? ?? ????? ?????, ???? ???? ????? ????????? ?????? ? ???????, ???? ???????? ??????? ?? ????? ????????. ??????? ??????? ?? ???, ??? SQL Server ?? ????? ?? ?????? ??????? ??? ?? ??? ??????? ?? ?????? ????? ???? ????? ??????? ????. ???? ????????? ?? ????? ?????? ?? ?????? ???? ????? ????? SPID ?? ???? ???? ????? ???????.

    ??? ??? ???, ????? ??????? ???? ??????? ?? "??????? ????":

    BEGIN TRAN 
    SELECT * FROM SYSOBJECTS S1, SYSOBJECTS S2
    
    -- Issue this after canceling query
    SELECT @@TRANCOUNT
    ROLLBACK TRAN
    						
    ????? ????? ????????? ?????? ???? ??? ???? ????? ?????. ??? ????? ????????? SELECT @@ TRANCOUNT ???? ??? ????? ??????? ?????? ?????. ???? ??? ?? UPDATE ?? DELETE ??? ??????? ?? ?? ?? ????????? HOLDLOCK ??? SELECT ? ???? ????????? ?????? ?? ?? ???? ????????. ??? ?? ??????? ????? ??? ??? ?????? ??? ??????? ??? ????? ????????? ?????? ?? ????????, ????? ?? ???? ???????? ????? ?? ????? SELECT ?????.

    ???:

    • ??? ?? ????????? ?? ????? ??????? ????? ????????? ???? ???? ?? ???? ?? ???? ????? ??? ??????? ????????? ???? ??????? ???????. ???? ?? ???? ??? ?? ???? ??? ?????:
      1. ?? ????? ??????? ?? ????? ?????? ?????? IF @@ TRANCOUNT > 0 ??? ????? ?? ??? ??? ??? ????? ?????? ????? ??? ?????? TRAN ????? ???. ??? ?????, ??? ????? ???? ??????? ????? ??????? ?? ???? ?????? ??? ??? ????? ??????. ?????? ???? ????? ??? ????? ?????????? ??? ??????? ?? ??????? ??? ??????? ??????? ???? ??? ??? ??? ??????? ???? ?????? IF <>@@ ??? 0 ? ????? ???????? ??? ???????? ???????? ?????? ??? ?????? ??????? ?? ???? ??????? ?? ??? ???????.
      2. ??????? SET ON XACT_ABORT ????? ?? ?? ????????? ??????? ????? ???? ????????? ?? ????? ??????? ??? ??? ???? ???. ???? ????? ?? ??????? ???????? ?? ????? ?????? ??? ?????? ??? ??????? ????? ???? ??? ?? ??? ???????. ???? ??? ?? ??? ????? ??? SQL T ??? ????? ???? ???? ?? ?????.
      3. ??? ???? ????????? ??? ????????? ?? ????? ???? ???? ??????? ????? ??? ??? ???? ?? ??????????? ??? ????? ??????? ??? ???? ??? ???? ??? ????? ????? ??? ??? ????? ????? ??????? ?????? ?? ????? ???? ??????? ??? ??? ????? ????? ?????? ??????? ??????? ???? ?????. ?? ???? ????? ???? ????????? "? ????? ??????? ????? ????? ???? ???? ?? SQL Server ???????? ??? ???? ??? ?????? ???? ??????? ?? ??????? ??????.
      4. ??? ?? ????? ????? ??????? ???? ?????? ?????? SQL Server 2000 ?? ???? ???? ????? ???? ????????? ?????? ??? MDAC 2.6 ?? ????. ???? ??? ??????? ?? ?????? MDAC ??? ?????? ????? ODBC ?? ???? OLE DB ???? ??????? ?? ???? "?????" ??? ??? ????? ?????????. ??????? ??? sp_reset_connection ????? ?? ??????? ???? ?? ??? ?????? (???? ?? ??? ??????? ?????? ??????? DTC ?? ?????) ? ???? ????? ????? ???????? ?????????? ??????? SET ????? ??????. ???? ????? ??????? ?? ??? ??? ????????? ?? ????? ??????? ??? ??? ?????? ?? ?????? ?? ??? ?????? ?? ??? ??????? ????? ??????? ???? ??? ?? ?? ??? ????????? ???? ???? ????? ????? ???????? ?? ???? ??????. ?? ??? ????? ??????? ??????? ???? ????? ???????? ??? ???? ??????? ?? ??????? ?? ????? ???????. ????????? ??? ?????? ????? ?????? ????? ????????? ?? ????? ??????? ?????? ??? ?? ??????? SET ON XACT_ABORT ???? ??? ??????? ????????.
    • ?? ???????? ??? ????? ?? ??? ??????? ?? ????? ???? ????? ?? ???? ? ?????? ?????? ???? ???. ??? ??? ???? ?? ???? ??? ????? ????????? ??????, ?? ???? ???? ????????? ?? ????? ?????. ?? ????? ?? ???? ?????? ??? ?????? ?????? ?? ????? ????? ??????? ??????? ??? ?? ?????? ???? ????? ????? ?? ?????? ?? ??? ???? ?????????.
  3. ??? ???? ?????? SPID ??? ????? ???? ???????? ???? ??? ???? ?????? ????? ??? ????????

    ??? ????? ??????? ??? ??????, ??? ??? ???? ????????? ????? ???? ?????? ?? ????? ?????? ??? ????????. ??? ????? ?? ??? ???? ?????? ???????, ???? ??? ??????? ??? ????? ??? ???????? ?????. ??? ??? ?????? ??? ??????? ??????? ?????? SQL ?????? ??? ?????? ??? ?? ??????? ??? ???? ?????? ???????. ??? ?? ??? ???? (??? ???? ?? ???? ????? ????) ? ?? ?? ????? ?? ?? ????? ???. ????? ??????? ????? ????? ???? ??? behaved ??????? ??? ???????? ?? ????? ?????? ??? ????.

    ???:

    ??? ?? ???? ??????? re-written ????? ???? ?????? ?? ??????? ??? ????????.
  4. ??? ???? ?????? Deadlock ??????/?????? ???????

    ???? ???? ??? ?????????? ???? ???? ??? ????? ??? detectable ???????? ????? ??????? RDBMS. ??? ????? ??? ?? ??? ??????? ???????? ?? ???? ???? ??? ??? ????? SQL Server. ?????? ????? ???? ???? ??? ?? ????? ???????? ?????? ???? ??? SQL Server ?? ???? ???? ??????. ???? ??? ????? ????? ??? ???? ?? ???? ??? ????? ??? ???????? ??????? ???? ????.

    1. ??????/?????? ??????? Deadlock ?? ???? ???? ???? ???????
      ??? ??? ?????? ??? ??????? ?????? ? ???? ????? ???? ???????, ?? ???? ???? ???? ??? ??????? ???????. ?????? ??? brevity ? ??? ??????? "dbproc" ??????? ???? ??? ???? ????? ??????.

       SPID1------blocked on lock------->SPID2
        /\                         (waiting to write results         
        |                           back to client)
        |                                 |
        |                                 |                      Server side
        | ================================|==================================
        |     <-- single thread -->       |                      Client side
        |                                 \/ 
       dbproc1   <-------------------   dbproc2
       (waiting to fetch             (effectively blocked on dbproc1, awaiting
        next row)                     single thread of execution to run)
      								
      ?? ???? ?????? ????? ??? ???? ????? ????? ???? ???? ??????? ??????. ???? ?????? ????? SQL ??? dbproc1 ???? ??? ??????. ???? ???? ??? ?? ????? ??? ????????? ?????? ??? ????????. ??????? ?? ???? ?????? ??? ????? SQL ??? dbproc2 ?? awaits ??????? ???? ?????? ???????? ???? ?? ???????. ??? ????? ???????? ??????? ????? (?? dbproc ????? ??????--????? ??? ?? dbproc1) ? ???? ??????? ??? ????? ???? ???????? ???????? ??? ??? dbproc ???????. ??? ??? ????? ?? dbproc1 ??? ???? ??? SPID1 ??? ????? ??????? SPID2 (??? ???? ??????????? ????? ???? ??? ?????? ??? ??????). ?? ??? ???????? dbproc1 ?????? ???? ??? ????? ?? ????????. ????? ????? SPID2 ???? ????? ????? ???????? ??? ?????? ?? dbproc2. ??? ???? dbproc2 ??????? ????? ??? dbproc1 ??? ???? ????????? ???? ????? ???? ??????? ??????? ??? ????????? ?????? dbproc1. ????? ?? ???? ???? ??? ???? ?? SQL Server ?? ?? ???? ??? ?? ??????? ???????? ??? ???? SQL Server.
    2. ??????/?????? ??????? Deadlock ?? ???? ????? ??? ?????

      ??? ?? ???? ???? ???? ????? ????? ??? ????? ??? ?????? ?? ???? ?? ???? ??????? ?? ???? ???? ??? ??? ??????? ??? ?? ???? ?????? ???????.

      SPID1------blocked on lock-------->SPID2
        /\                         (waiting on net write)        Server side
        |                                 |
        |                                 |
        | INSERT                          |SELECT
        | ================================|==================================
        |     <-- thread per dbproc -->   |                      Client side
        |                                 \/ 
       dbproc1   <-----data row-------   dbproc2
       (waiting on                     (blocked on dbproc1, waiting for it
        insert)                         to read the row from its buffer)
      								
      ???? ??? ?????? ?????? ?? ???????? dbproc2 ? SPID2 ???? ????? SELECT ?? ???? ?? ???? ????? ???? - ??? - - ?????? ?????? ??? ?? ?? ???? ???? ???? ??? dbproc1 ?????? ??? INSERT UPDATE, ?? DELETE ??????? ?? ??? ??????. SPID1 ??????, ???? ??? (????? INSERT ?? UPDATE ?? DELETE) ??? ????? ??????? SPID2 (????? SELECT). ???? SPID2 ?? ????? dbproc2 ??????. ????? Dbproc2 ?????? ???? ?? ???? ???? ??? dbproc1 ???? dbproc1 ?????? ????? (?? ???? ?????? SPID1 ?????? INSERT ?????? ???? ??? ??? ??? SPID2). ??? ??? ??????? ??? ??? dbproc2 ??? ???? ????????? ?????? dbproc1 SPID (SPID1) ???? ?? ??? ????? ????? ???????? ??? SPID2. ??? ????? ??? ????? ?? ???? ???? ??? ???? ?? SQL Server ?? ?? ???? ??? ?? ??????? ???????? ??? ???? SQL Server.
    ??????? ??? ? ?? ?? ???????? ???????? ???? ????? ????????? ??? ?? ???? ????? ???. ??? ?? ?????? ??? ????????? ?????? ??? ??????? ???? ?????.

    ???:

    ?????? ???????? ??????? ???? ??????? ?? ??????? ????.

    • ???? ?????????
      ????? ?? ????? ???? ??????? ?? ???? ???? ???? ???? ??? ??????? ? ???? ??? ???? ????? ??? ?? ???? ??????. ???? ????? - DB ?? ????? ODBC ?????? ??? ???? ?? ????????? ??? ??????? ???? ???????.
    • ??? ?????????
      ???? ??? ?????? ???? ??????? ?????? ???? ????? ????? ???? ????? ?????? ????? ???? ??? ??? ????????? ????? ?????. ?????? ??? ???? ?? ?????????? ???? ??????? "??????? ??????? ?????" ?? SQL Server 7.0 ????????.
  5. ??? ???? ?????? SPID ???? ??? "?????," ?? "?????????" ? ????

    ????? ??????? ?????? ???? ??? ??????? ?? ????? ???? ?????? ??????? ?? ??? ???????? ??? ??? ??????? ??. ????? ???? ?? ???? ??? ??????? ?????? ?? ????? ????? ????????? ?????? ???? ?? ???? ????? ???? ?????. ??????? ??????? ??????? ????? ????? ???? ???? ??? ??? ??? ??????? ??. ??????? ????? ?????? ?????? ?? ??? ??????? ?? ?? ???? ?? ??????? ?? ????? ?????????. ??? ???? ??????? ??? ??? ??? ????? ????? UPDATE ?? INSERT ?? DELETE ?? ??? ????, ?? ?????? ????? ???? ???????. ??? ?? ?????? ??????? ???? ????????? ???? ?? ??????? ??? ?? ???? ?????? ??????? ?? ????? ????? ????? ????????? ???????? ???? ?? ????? ????????. ???? ??? ?? ???? ??? SQL Server ?????? SPID ?? ???? "?????" ?? ?????? ??? ?????? ??????? (??? ???? ??? ?? ???? ????? ?? ?????? ?? ????? ???? ???? ???). ???? ????? ?????? ?? ???? ?????? ??????? sp_who, ????? ?? ???? ??? ????? ?????. ???? ??????sys.sysprocesses ???? ??? ???? ????? ????? ???? ????? ?? ????? sp_who ?? ?? ?????? ???? Studio ????? SQL Server.
    ???:

    ???? ???????? ??? SPID ?????? ???? ??????? ????????? ???? ?? ???????.

    ??? ?? ????? ?????? ????? midst ??? ??????? ????? ????? ???????? ?? ??? ????????? ??? ????? ????? ????? ??? ?????? ??? ??? ???? ??? ?????? ?????????. ??? ??????? ????????? ???? ??? ????? ?? ????? ??? ?????? ?? ????????? ??? ??????? ???? ????? ??? ???? ????? ???????? ????? ??? ??????. ????????? ??? ?????? ??? ????? SPID ?? ???? ????????? ?????? ???? counterproductive.

    ????? ??? ?????? ?? ??? ?????? ????? ?????? INSERT ? UPDATE, ?? DELETE ?????? ????? ????? ??????? ??? ????? OLTP. ??? ??? ??? ?????? ????? ?????? ??? ????? ????? ??????? ??????.
  6. ??? ???? ??? ????? ????????

    ?? ???? ????? ????? ???????? ????? ???? ?? ???? ??? ??????, ???? ??? ???? ??????? ??? ?????? ?? ?? ??? ????? ????? ??? ???????. ?? ????? ?????? ?????? ?????? ?????? ? ?? ???? ??? ???????? ????????? ???? ?? ?????? ????. ????? ?? ?????????? ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ?? Microsoft" (????? ?? ??? ???????? ??? ???? ?? ???? ??????? ??? ?? ??? ??? ?????? ??????????):
    137983????? ??????? ????? ??????? ???????? ?? SQL Server

    ???:

    ??? ?? ??? ????? ????? ?????? ???? ????? ???? ????? ?????? ???? ????? SPID ???????? ????? ????? ????? ". ???? ????? ????? ????? ???? SPID ??????. ??? ???? ??????? ????? SPID 9 ? ?????? ????? ????? ??????:

    KILL 9
    						

    ?????? ?? ?????? ????? ????? ????? 30 ????? ?????? ????? ?????? ?????? ??? ?????? ??? ????? ????? ?????.

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

?? ???? ???? ??????? ??????? ??????? ??? ???? ?????? ????? ??????? ??????? ?????? ??? ?????? ????? ???. ??? ????? ?? ???? ??? ???? ???? ??? ??? ?? ???? absorb ?????? ????? ???? ???? ?? ????? ?????? ? ????????? ???? ?????? ???? ??????. ??? ????? ?? ?? ?? ????? ??????? ??? ???? ????? ????????? ????? ?????? ?? ?????? ????? ??? nonetheless ???? ????? ?????? ????? ??? ????? ?? ?????? SQL ?????? ??????? ?? ??? ??????? ????? ??????? ?????? ???? ????? ??????? ?????????, ????? ??????? ??? ???? ?????? ???? ?????. ??? ??? ?? ???? ???? ????? ?????? ?????? ????? ??????? ? ??????? ?????????, ???? ?????????, ??? ??????? ??? ??? ???, ?? ?? ???? ????? ??????? ????. ??? ?? ???? ??? ??? ???????? ???? ???? ??? ????? ??? ????? ????? ????? ?? SQL Server ???? ?? ??? ????? OLTP ??????? ???????.

?? ???? exercised ?????? ????? ????? ????? ?????? ???? ????? ???????? ?????????? ??????. ???? ???? ??? ?? ??? ????? ??????? ??????? ????? ????? ???? ???? ?????? ?? ?????????. ??? ?? ???? ??? ??????? "?" ????????? ????? ??? ??? ???????. ??? ?? ???? exercised ????? ??? ????? ??????. ??? ?? ??? ??? ??? ?????? ?? ??????? ?? ???? ?? ???? ?????? ??????? ?? ??????? ?????? ?????????? ???? ?? ??? ?????? ???? ????? ???? ????? ?????????? ??? ????.

???????? ??????? ?????? ? ????? ????????? Microsoft SQL Server ????? ??? ??? ???? ?????? ?? ?????????? ????????? ??? ???? ???? ?? ??? ?????.

???????

???? ???????: 224453 - ????? ??? ??????: 21/?????/1430 - ??????: 5.1
????? ???
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Workgroup
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Workgroup
????? ??????: 
kbmt kbhowto kbtshoot kbexpertiseinter kbinfo KB224453 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????224453

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

 

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