¡Ò÷ӤÇÒÁà¢éÒã¨à¡ÕèÂǡѺ INF: áÅСÒÃá¡é䢻ѭËÒ¡ÒúÅçͤ¢Í§ SQL Server

• ¡ÒÃÅçÍ¡ã¹â»Ãá¡ÃÁ°Ò¹¢éÍÁÙÅ
• ÅçÍ¡¡Ó˹´àͧáÅСÒáÓ˹´ÃØ蹢ͧá¶Ç
• âËÁ´¡ÒÃÅçÍ¡
• ¤ÇÒÁà¢éҡѹä´é¢Í§ÅçÍ¡
• á¶Ç·Õè¢Öé¹ÍÂÙè¡Ñº¡ÒáÓ˹´ÃØ蹤ÇÒÁᡵèÒ§ËÒ¡ã¹â»Ãá¡ÃÁ°Ò¹¢éÍÁÙÅ
• ¡ÒäǺ¤ØÁ¡ÒøØáÃÃÁ (â»Ãá¡ÃÁ¢Í§°Ò¹¢éÍÁÙÅ)

• SPID ÁÕ¡ÒÃÅç͡㹪ش¢Í§·ÃѾÂÒ¡ÃÊÓËÃѺÃͺÃÐÂÐàÇÅÒ¡è͹·Õè¨Ð¹ÓÍÍ¡ãªéãËéà»ç¹áºº¢ÂÒ ¡ÒúÅçÍ¡ª¹Ô´¹Õéà¾×èÍá¡é䢵ÑÇàͧàÁ×èÍàÇÅÒ¼èÒ¹ä» áµèÍÒ¨·ÓãËéà¡Ô´¡ÒÃÅ´»ÃÐÊÔ·¸ÔÀÒ¾¡Ò÷ӧҹ
• SPID ÁÕ¡ÒÃÅç͡㹪ش¢Í§·ÃѾÂÒ¡Ã áÅÐäÁèà¤ÂÍÍ¡ä´é ¡ÒúÅçÍ¡ª¹Ô´¹ÕéäÁèÊÒÁÒöá¡é䢵ÑÇàͧ áÅлéͧ¡Ñ¹¡ÒÃà¢éÒ¶Ö§·ÃѾÂҡ÷Õèä´éÃѺ¼Å¡Ãзº indefinitely

¡ÓÅѧÃǺÃÇÁ¢éÍÁÙÅ¡ÒúÅçÍ¡

1. ÃÐºØ SPID (ËÁÒÂàÅ¢à«ÊªÑ¹) ã¹ËÑǢͧ¡ÅØèÁ·Õè¡ÒúÅçͤáÅÐÃÒ§ҹ¢Í§ SQL
   ¹Í¡à˹×ͨҡ¡ÒÃãªéʤÃÔ»µì¹Õé㹺·¤ÇÒÁ°Ò¹¤ÇÒÁÃÙé·ÕèÃкØäÇé¡è͹˹éÒ¹Õé ¤Ø³ÊÒÁÒöÃкØËÑǢͧ¡ÅØèÁ·Õè¡ÒúÅçͤ â´ÂãªéÅѡɳСÒ÷ӧҹ·ÕèãËé¼èÒ¹ Studio ¨Ñ´¡ÒÃà«ÔÃì¿àÇÍÃì SQL àÁ×è͵éͧ¡Ò÷Óàªè¹¹Õé ãËéãªéÇÔ¸Õ¡ÒÃã´ÇÔ¸Õ¡ÒÃ˹Ö觵èÍ仹Õé:
   • ¤ÅÔ¡¢ÇÒÇѵ¶Øà«ÔÃì¿àÇÍÃì ¢ÂÒÂÃÒ§ҹ:¢ÂÒÂÃÒ§ҹÁҵðҹáÅéÇ ¤ÅÔ¡¡Ô¨¡ÃÃÁ – ¸ØáÃÃÁ¡ÒúÅçÍ¡·Ñé§ËÁ´. ÃÒ§ҹ¹ÕéáÊ´§¸ØáÃÃÁ·ÕèÈÕÃÉТͧºÅçÍ¡¡ÒÃàª×èÍÁµèÍ If you expand the transaction, the report will show the transactions that are blocked by the head transaction. This report will also show the "Blocking SQL Statement" and the "Blocked SQL Statement."
   • Use DBCC INPUTBUFFER(<spid>) to find the last statement that was submitted by a SPID.</spid>
2. Find the transaction nesting level and process status of the blocking SPID.
   The transaction nesting level of a SPID is available in the @@TRANCOUNT global variable. However, it can be determined from outside the SPID by querying thesysprocessestable as follows:
   
   

   SELECT open_tran FROM master.sys.sysprocesses WHERE SPID=<blocking SPID number>
   go
   						

   The value returned is the @@TRANCOUNT value for the SPID. This shows the transaction nesting level for the blocking SPID, which in turn can explain why it is holding locks. For example, if the value is greater than zero, the SPID is in the midst of a transaction (in which case it is expected that it retains certain locks it has acquired, depending on the transaction isolation level).
   
   You can also check to see if any long-term open transaction exists in the database by using DBCC OPENTRANdatabase_name.

Gathering SQL Server Profiler Trace Information

1. Open SQL Server Profiler.
2. 㹡ÒÃá¿éÁ:àÁ¹Ù ãËéªÕéä»·ÕèãËÁèáÅéÇ ¤ÅÔ¡Trace.
3. 㹡Ò÷ÑèÇä»tab, specify a trace name and a file name to capture the data to.
   
   ÊÔè§ÊӤѭThe trace file should be written to a fast local or shared disk. Avoid tracing to a slow disk or network drive. Also make sure Server processes trace data is selected.
4. 㹡ÒÃEvents Selectioná·çº ¤ÅÔ¡à¾×èÍàÅ×Í¡¹Ñé¹Show all eventsáÅÐShow all columns¡Åèͧ¡Òà¤Ã×èͧËÁÒÂäÇé
5. 㹡ÒÃEvents Selectiontab, add the Event types that are listed in Table 1 to your trace.
   
   Additionally, you may include the additional Event types that are listed in Table 2 for further information. If you are running in a high-volume production environment, you may decide to use only the events in Table 1, as they are typically sufficient to troubleshoot most blocking problems. Including the additional events in Table 2 may make it easier to quickly determine the source of a problem (or these events may be necessary to identify the culprit statement in a multi-statement procedure). However, including events in Table 2 will also add to the load on the system and increase the trace output size.

¡ÒÃÃкØáÅСÒÃá¡é䢷ÑèÇä»·Õè¡ÒúÅçÍ¡à˵ءÒóì

¡ÒôټÅÅѾ¸ì¢Í§Ê¤ÃÔ»µì¡ÒúÅçͤ

µÃǨÊͺsys.sysprocesses¼ÅÅѾ¸ì¡ÒõÃǨÊͺ heads ¢Í§ chains ·Õè¡ÒúÅçͤ

SPIDs at the head of blocking chains

µÃǨÊͺsys.sysprocesses¡ÒÃáÊ´§¼ÅÊÓËÃѺ¢éÍÁÙÅà¡ÕèÂǡѺ SPIDs ·ÕèËÑǢͧ¡ÅØèÁ·Õè¡ÒúÅçͤ

DBCC TRACEON ( 3604 )
DBCC PAGE ( 5 , 1 , 104 , 3 )

Examine the DBCC INPUTBUFFER output.

Viewing the Profiler Data

• ÊÔ觷Õè¤ÓÊÑè§ä´é SPID ·Õè·ÕèËÑǢͧ¡ÅØèÁ¡ÒúÅçͤ·Õè´Óà¹Ô¹¡ÒÃÀÒÂ㹸ØáÃÃÁ»Ñ¨¨ØºÑ¹ËÃ×ÍäÁè
  ¡Ãͧ¢éÍÁÙÅ¡ÒÃÊ׺¤é¹¡ÅѺÊÓËÃѺ SPID ੾ÒзÕèÍÂÙè·ÕèËÑǢͧ¡ÅØèÁ·Õè¡ÒúÅçͤ (ã¹¹Ñé¹á¿éÁ:àÁ¹Ù ¤ÅÔ¡¤Ø³ÊÁºÑµÔáÅéÇã¹¹Ñé¹µÑÇ¡Ãͧá·çºÃкؤèÒ SPID) ¤Ø³ÊÒÁÒöµÃǨÊͺ¤ÓÊÑ觷ÕèÁÕ¡Ò÷ӧҹ¡è͹·Õè¨Ðà»ç¹¤ÃÑ駷ÕèÁÕ¡ÒúÅçͤ¡Òà SPIDs Í×è¹ æ áÅéÇ ¶éҤسÁÕà˵ءÒóì·Ãҹ᫤ªÑ¹ ¾Ç¡à¢ÒÊÒÁÒöÃкØä´éâ´Â§èÒÂàÁ×èÍàÃÔèÁµé¹·Ãҹ᫤ªÑ¹ ÁԩйÑé¹ ¤Ø³ÊÒÁÒö¤é¹ËÒ¹Õé¢éͤÇÒÁ¤ÍÅÑÁ¹ìÊÓËÃѺ BEGIN, SAVE Êè§ ËÃ×͸ØáÃÃÁÂé͹¡ÅѺ¡ÒôÓà¹Ô¹§Ò¹ ãªéẺopen_tran¤èÒ¨Ò¡¹Ñé¹sysprocessesµÒÃÒ§à¾×èÍãËéá¹èã¨ÇèÒÊÒÁÒöãËé¤Ø³¨Ñ´¡ÒÃà˵ءÒóì¢Í§¸ØáÃÃÁ·Ñé§ËÁ´ ·ÃÒºÇèÒ¤ÓÊÑ觷Õè´Óà¹Ô¹¡ÒÃáÅкÃÔº·¸ØáÃÃÁ¨ÐªèÇÂãËé¤Ø³ÊÒÁÒö¡Ó˹´ÇèÒ·ÓäÁ SPID ¶Ù¡à¡çºÅçÍ¡
  
  â»Ã´¨ÓäÇé ¤Ø³ÊÒÁÒöźà˵ءÒóìáÅТéÍÁÙŤÍÅÑÁ¹ì á·¹·Õè¡Òäé¹ËÒ·ÕèàÃÔèÁµé¹·Ñé§Êͧ áÅÐàÅ×Í¡à˵ءÒóì·ÕèàÊÃç¨ÊÁºÙóì ˹Öè§ ËÒ¡ SPIDs ·Õè¡ÒúÅçͤ¨ÐäÁè¡Ãкǹ§Ò¹·Õèà¡çºäÇé Í͡ẺSP: àÃÔèÁµé¹ËÃ×ÍSP: àÊÃç¨ÊÁºÙóìáÅéÇà˵ءÒóìSQLBatcháÅÐrpcà˵ءÒóì·Õè¨ÐáÊ´§¡ÒÃàÃÕ¡¢Ñ鹵͹ ´Ùà˵ءÒóì SP àÁ×èͤسµéͧ¡ÒôÙÇèÒÃдѺ¢Í§ÃÒÂÅÐàÍÕ´à·èÒ¹Ñé¹
• ÃÐÂÐàÇÅҢͧ¡ÒÃÊͺ¶ÒÁÊÓËÃѺ SPIDs ·ÕèÈÕÃÉТͧºÅçÍ¡ chains ¤×ÍÍÐäÃ
  ¶éҤسÁÕ¡ÒÃãËéàÊÃç¨ÊÁºÙóìà˵ءÒóì¢éÒ§µé¹ ¡ÒÃÃÐÂÐàÇÅÒ¤ÍÅÑÁ¹ì¨ÐáÊ´§àÇÅÒ¡ÒôÓà¹Ô¹¡ÒÃẺÊͺ¶ÒÁ «Öè§ÊÒÁÒöªèǤسÃкØẺÊͺ¶ÒÁ·ÕèÃѹà»ç¹àÇÅÒ¹Ò¹·Õèà»ç¹ÊÒà˵ءÒúÅçͤ ¡ÒõÃǨÊͺÊÒà˵ØẺÊͺ¶ÒÁ¡ÓÅѧ·Ó§Ò¹ªéÒËÃ×ÍäÁè ãËé´ÙCPU,ReadáÅÐà¢Õ¹¤ÍÅÑÁ¹ì àªè¹à´ÕÂÇ ¡Ñºá¼¹¡ÒôÓà¹Ô¹¡ÒÃà˵ءÒóì

categorizing à˵ءÒóì¨ÓÅͧ¡ÒúÅçͤ·Õè·ÑèÇä»

à˵ءÒóì¨ÓÅͧ·ÕèÁÕ¡ÒúÅçÍ¡·Õ辺â´Â·ÑèÇä»áÅÐÇÔ¸Õá¡éä¢

1. â´Â·Õè¡ÓÅѧ·Óà»ç¹»¡µÔ§Ò¹¡ÒúÅçͤà¡Ô´¨Ò¡áººÊͺ¶ÒÁ ´éÇ¡Òû¯ÔºÑµÔ§Ò¹àÇÅÒ·ÕèÂÒÇ
   
   ¡ÒÃá¡éä¢:
   ¡ÒÃá¡é䢻ѭËÒ¡ÒúÅçÍ¡ª¹Ô´¹Õé¶Ù¡ãªéà¾×èÍ ¤é¹ËÒÇÔ¸Õ㹡ÒûÃѺ¡ÒõÑ駤èÒ¡ÒÃÊͺ¶ÒÁ ¨ÃÔ§ ¤ÅÒʹÕé¢Í§»Ñ­ËÒ¡ÒúÅçÍ¡ÍÒ¨à¾Õ§ÁջѭËÒ»ÃÐÊÔ·¸ÔÀÒ¾¡Ò÷ӧҹ áÅФسÍÒ¨µéͧ pursue à»ç¹àªè¹¹Ñé¹ ÊÓËÃѺ¢éÍÁÙÅà¡ÕèÂǡѺ¡ÒÃá¡é䢻ѭËÒẺÊͺ¶ÒÁ·ÕèÃѹªéÒ੾ÒÐ ´Ùº·¤ÇÒÁ°Ò¹¤ÇÒÁÃÙé¢Í§ Microsoft µèÍ仹Õé:
   243589

   (http://support.microsoft.com/kb/243589/ )

   How to troubleshoot slow-running queries on SQL Server 7.0 or on later versions
   For overall application performance troubleshooting, see the following Knowledge Base article:
   224587

   (http://support.microsoft.com/kb/224587/ )

   HOW TO: Troubleshoot Application Performance with SQL Server
   ÊÓËÃѺ¢éÍÁÙÅà¾ÔèÁàµÔÁ ãËé´ÙPerformance Monitoring and Tuning How-to TopicsSQL Server 2008 Books Online topic on the following MSDN Web site:
   http://msdn.microsoft.com/en-us/library/ms187830.aspx

   (http://msdn.microsoft.com/en-us/library/ms187830.aspx)
   If you have a long-running query that is blocking other users and cannot be optimized, consider moving it from an OLTP environment to a decision support system.
2. Blocking Caused by a Sleeping SPID That Has Lost Track of the Transaction Nesting Level
   
   This type of blocking can often be identified by a SPID that is sleeping or awaiting a command, yet whose transaction nesting level (@@TRANCOUNT,open_tran¨Ò¡sysprocesses) is greater than zero. This can occur if the application experiences a query timeout, or issues a cancel without also issuing the required number of ROLLBACK and/or COMMIT statements. When a SPID receives a query timeout or cancel, it will terminate the current query and batch, but does not automatically roll back or commit the transaction. The application is responsible for this, as SQL Server cannot assume that an entire transaction must be rolled back simply due to a single query being canceled. The query timeout or cancel will appear as an ATTENTION signal event for the SPID in the Profiler trace.
   
   To demonstrate this, issue the following simple query from Query Analyzer:
   
   

   BEGIN TRAN 
   SELECT * FROM SYSOBJECTS S1, SYSOBJECTS S2
   
   -- Issue this after canceling query
   SELECT @@TRANCOUNT
   ROLLBACK TRAN
   						

   While the query is executing, click the red¡àÅÔ¡»ØèÁ After the query is canceled, SELECT @@TRANCOUNT indicates that the transaction nesting level is one. Had this been a DELETE or an UPDATE query, or had HOLDLOCK been used on the SELECT, all the locks acquired would still be held. Even with the query above, if another query had acquired and held locks earlier in the transaction, they would still be held when the above SELECT was canceled.
   
   Resolutions:
   
   
   • Applications must properly manage transaction nesting levels, or they may cause a blocking problem following the cancellation of the query in this manner. This can be accomplished in one of several ways:
     1. ã¹µÑǨѴ¡ÒâéͼԴ¾ÅÒ´¢Í§á;ÅÔपѹä¤Åà͹µì Ê觡Òà IF @@ TRANCOUNT > 0 TRAN Âé͹¡ÅѺµÒÁ¢éͼԴ¾ÅÒ´ã´ æ ¶Ö§áÁéÇèÒâ»Ãá¡ÃÁ»ÃÐÂØ¡µì¢Í§ä¤ÅàÍç¹µìäÁèÁÑè¹ã¨ÇèÒ¸ØáÃÃÁ¨Ðà»Ô´¢Öé¹ ¹Õè¤×͵éͧ à¹×èͧ¨Ò¡¡Ãкǹ§Ò¹à¡çºäÇé·ÕèàÃÕ¡ÇèÒÃÐËÇèÒ§ªØ´§Ò¹äÁèä´éàÃÔèÁ·Ãҹ᫤ªÑ¹äÁè ÁÕ¤ÇÒÁÃÙé¢Í§â»Ãá¡ÃÁ»ÃÐÂØ¡µìä¤Åà͹µì ËÁÒÂà˵ØÇèÒ à§×è͹䢺ҧ àªè¹¡ÒáàÅԡẺÊͺ¶ÒÁ »éͧ¡Ñ¹¢Ñ鹵͹¡ÒôÓà¹Ô¹¡ÒÃã¹Í´Õµ§º»Ñ¨¨ØºÑ¹ ´Ñ§¹Ñé¹áÁéÇèÒ¢Ñ鹵͹´Ñ§¡ÅèÒÇÁÕµÃáСÒõÃǨÊͺ IF ¢éͼԴ¾ÅÒ´ @@ <> 0 áÅСàÅÔ¡¸ØáÃÃÁ ¹ÕéÂé͹¡ÅѺ¨ÐäÁèÊÒÁÒöàÃÕ¡ãªéÃËÑÊ㹡óÕàªè¹¡Ñ¹
     2. ãªé SET ON XACT_ABORT ÊÓËÃѺ¡ÒÃàª×èÍÁµèÍ ËÃ×ÍÍÂÙè ã¹¢Ñ鹵͹¡ÒèѴà¡çºäÇéã´ æ «Ö觸ØáÃÃÁ·ÕèàÃÔèÁµé¹ áÅШÐäÁèÅéÒ§¢éͼԴ¾ÅҴ㹡ÒõèÍ仹Õé 㹡óբͧ¢éͼԴ¾ÅÒ´¢³Ð·Ó§Ò¹ ¡ÒõÑ駤èÒ¹Õé¨Ð¡àÅÔ¡¸ØáÃÃÁ·Õèà»Ô´ áÅСÅѺ¡ÒäǺ¤ØÁä»Âѧä¤ÅàÍç¹µì â»Ã´ÊѧࡵÇèÒ ¤ÓÊÑè§ T SQL ·ÕèµÒÁãºá¨é§ÂÍ´«Ö觷ÓãËéà¡Ô´¢éͼԴ¾ÅÒ´¨ÐäÁèÊÒÁÒö´Óà¹Ô¹¡ÒÃ
     3. ¶éÒÁÕ¡ÒÃàª×èÍÁµèÍÃèÇÁ¡Ñ¹¶Ù¡ãªéã¹â»Ãá¡ÃÁ»ÃÐÂØ¡µì·Õèà»Ô´¡ÒÃàª×èÍÁµèÍ áÅÐàÃÕ¡ãªé¨Ó¹Ç¹áººÊͺ¶ÒÁàÅç¡ æ ¡è͹·Õè¨Ð¹ÓÍÍ¡ãªé¡ÒÃàª×èÍÁµè͡Ѻ¾ÙÅâ»Ãá¡ÃÁ àªè¹á;¾ÅÔपѹ·Õè¢Öé¹ÍÂÙè¡ÑºàÇçº ¡ÅÑºä» à»ç¹¡ÒêÑèǤÃÒǻԴãªé§Ò¹¡ÒÃàª×èÍÁµèÍÃèÇÁ¡Ñ¹ÍÒ¨ªèÇ alleviate »Ñ­ËÒ¨¹¡ÇèÒ¨ÐÁÕ¡ÒûÃѺà»ÅÕè¹á;ÅÔपѹä¤Åà͹µìà¾×èͨѴ¡ÒâéͼԴ¾ÅÒ´ÍÂèÒ§àËÁÒÐÊÁ â´Â¡ÒûԴãªé§Ò¹¡ÒÃàª×èÍÁµèÍÃèÇÁ¡Ñ¹ ¹ÓÍÍ¡ãªé¡ÒÃàª×èÍÁµèͨзÓãËéà¡Ô´ logout ·ÕèÁÕÍÂÙè¨ÃÔ§¢Í§¡ÒÃàª×èÍÁµèÍ SQL Server Íѹà»ç¹¼Åã¹à«ÔÃì¿àÇÍÃì·Õè¹Ó¡ÅѺ¸ØáÃÃÁ·Õèà»Ô´ÍÂÙè
     4. ¶éÒÁÕ¡ÒÃàª×èÍÁµèÍÃèÇÁ¡Ñ¹¶Ù¡à»Ô´ãªé§Ò¹ áÅÐà«ÔÃì¿àÇÍÃì»ÅÒ·ҧà»ç¹ SQL Server 2000 ¡ÒûÃѺÃØ蹤ÍÁ¾ÔÇàµÍÃìä¤Åà͹µì à¾×èÍ MDAC 2.6 ËÃ×ÍãËÁè¡ÇèÒÍÒ¨ beneficial ¤ÍÁâ¾à¹¹µì¢Í§ MDAC ÃØè¹¹Õéà¾ÔèÁÃËÑÊä»Âѧâ»Ãá¡ÃÁ¤Çº¤ØÁ ODBC áÅмÙéãËéºÃÔ¡Òà OLE DB à¾×èÍãËé¡ÒõÑ駤èÒ¡ÒÃàª×èÍÁµèͨж١ "ãËÁè" ¡è͹·Õè¨Ð reused àÃÕ¡¢éÍÁÙŹÕéà¾×èÍ sp_reset_connection aborts äËÇàÃÔèÁµé¹à«ÔÃì¿àÇÍÃìã´ æ (DTC ¸ØáÃÃÁ·ÕèàÃÔèÁµé¹ â´Ââ»Ãá¡ÃÁ»ÃÐÂØ¡µì¢Í§ä¤ÅàÍç¹µìäÁèä´éÃѺ¨Ò¡) ¡ÒõÑ駤èÒ°Ò¹¢éÍÁÙÅàÃÔèÁµé¹ µÑÇàÅ×Í¡ SET áÅÐ forth Í×è¹ æ â»Ã´ÊѧࡵÇèÒ ¡ÒÃàª×èÍÁµèͶ١ÃÕäÁèà«çµ¨¹¡ÇèÒ¨ÐÁÕ reused ¨Ò¡»ÃÐàÀ·¡ÒÃàª×èÍÁµèÍ ´Ñ§¹Ñé¹à»ç¹ä»ä´éÇèÒ ¼ÙéãªéÊÒÁÒöà»Ô´·Ãҹ᫤ªÑ¹ áÅСÒÃàª×èÍÁµè͡Ѻ»ÃÐàÀ·¡ÒÃàª×èÍÁµèÍ·Õè¹ÓÍÍ¡ãªéáÅéÇ áµèÍÒ¨äÁèÁÕ reused ¢éÍÁÙŴѧ¡ÅèÒÇÊÓËÃѺàÇÅÒËÅÒÂ¹Ò·Õ ã¹ªèǧàÇÅÒ«Ö觸ØáÃÃÁ¨Ðà»Ô´·Ôé§äÇé ¶éÒ¡ÒÃàª×èÍÁµèÍäÁè¶Ù¡ reused ¸ØáÃÃÁ¨Ð¶Ù¡Â¡àÅÔ¡àÁ×èÍ¡ÒÃàª×èÍÁµèÍËÁ´àÇÅÒ áÅж١źÍÍ¡¨Ò¡»ÃÐàÀ·¡ÒÃàª×èÍÁµèÍ «Öè§ ¨Ð´Õ·ÕèÊØ´ÊÓËÃѺá;ÅÔपѹä¤Åà͹µìà¾×èÍ¡àÅÔ¡¸ØáÃÃÁ㹡ÒèѴ¡ÒâéͼԴ¾ÅÒ´¢Í§µ¹àͧ ËÃ×Íãªé SET ON XACT_ABORT à¾×èÍËÅÕ¡àÅÕ觡ÒÃ˹èǧàÇÅÒ¹ÕéÍÒ¨à¡Ô´¢Öé¹
   • ¨ÃÔ§ ¤ÅÒʹÕé»Ñ­ËÒ¡ÒúÅçͤÍÒ¨ÂѧÁջѭËÒ»ÃÐÊÔ·¸ÔÀÒ¾¡Ò÷ӧҹ áÅФسÍÒ¨µéͧ pursue à»ç¹àªè¹¹Ñé¹ ¶éÒàÇÅÒ¡ÒôÓà¹Ô¹¡ÒÃẺÊͺ¶ÒÁÊÒÁÒöÁÕ diminished ¡ÒÃËÁ´àÇÅÒ¡ÒÃÊͺ¶ÒÁËÃ×Í¡àÅÔ¡¨ÐäÁèà¡Ô´¢Öé¹ äÁèÊӤѭÇèÒ â»Ãá¡ÃÁ»ÃÐÂØ¡µìäÁèÊÒÁÒö¨Ñ´¡ÒáÒÃËÁ´àÇÅÒ ËÃ×Íʶҹ¡Òóì¡ÒáàÅÔ¡à¡Ô´¤ÇèТÖé¹ áµè¤Ø³ÂѧÍÒ¨ä´éÃѺ»ÃÐ⪹ì¨Ò¡¡ÒõÃǨÊͺ»ÃÐÊÔ·¸ÔÀÒ¾¡Ò÷ӧҹ¢Í§áººÊͺ¶ÒÁ
3. ºÅçÍ¡·Õèà¡Ô´¨Ò¡ SPID ·ÕèäÁèÁÕá;ÅÔपѹä¤Åà͹µìÊÍ´¤Åéͧ¡Ñ¹ä´é´Ö§á¶Ç¼Å·Ñé§ËÁ´ãËéàÊÃç¨ÊÁºÙóì
   
   ËÅѧ¨Ò¡¡ÒÃÊè§áººÊͺ¶ÒÁä»Âѧà«ÔÃì¿àÇÍÃì â»Ãá¡ÃÁ»ÃÐÂØ¡µì·Ñé§ËÁ´µéͧ·Ñ¹·Õ´Ö§á¶Ç¼ÅÅѾ¸ì·Ñé§ËÁ´ãËéàÊÃç¨ÊÁºÙÃ³ì ¶éÒâ»Ãá¡ÃÁ»ÃÐÂØ¡µì´Ö§á¶Ç¼ÅÅѾ¸ì·Ñé§ËÁ´ äÁèÅçÍ¡ÊÒÁÒö¶Ù¡»Åèͺ¹µÒÃÒ§ ¡ÒúÅçÍ¡¼ÙéãªéÍ×è¹ ¶éҤس¡ÓÅѧãªéâ»Ãá¡ÃÁ»ÃÐÂØ¡µì·ÕèÊè§ SQL transparently §ºä»Âѧà«ÔÃì¿àÇÍÃì â»Ãá¡ÃÁ»ÃÐÂØ¡µìµéͧ´Ö§á¶Ç¼ÅÅѾ¸ì·Ñé§ËÁ´ ¶éҤسäÁè (áÅÐ ¶éҤسäÁèÊÒÁÒö¡Ó˹´¤èÒ¡Ò÷ӧҹ), ¤Ø³ÍÒ¨äÁèÊÒÁÒöá¡é»Ñ­ËÒ¡ÒúÅçͤä´é à¾×èÍËÅÕ¡àÅÕ觻ѭËÒ ¤Ø³ÊÒÁÒö¨Ó¡Ñ´â»Ãá¡ÃÁ»ÃÐÂØ¡µì·Õè poorly behaved à¾×èÍ¡ÒÃÃÒ§ҹËÃ×Í°Ò¹¢éÍÁÙÅʹѺʹعµÑ´ÊÔ¹ã¨
   
   ¡ÒÃá¡éä¢:
   
   â»Ãá¡ÃÁ»ÃÐÂØ¡µìµéͧÁÕ re-written à¾×èÍ´Ö§á¶Ç·Ñé§ËÁ´¢Í§¼Å¡ÒôÓà¹Ô¹¡ÒÃàÊÃç¨ÊÁºÙóì
4. ºÅçÍ¡·Õèà¡Ô´¨Ò¡ Deadlock ä¤ÅàÍç¹µì/à«ÔÃì¿àÇÍÃì·ÕèÇÒ§¨Ó˹èÒÂ
   
   «Öè§áµ¡µèÒ§¨Ò¡ deadlock ·Õ軡µÔ deadlock ·ÕèÇÒ§¨Ó˹èÒÂäÁèä´éãªéµÑǨѴ¡ÒáÒÃÅçÍ¡ RDBMS detectable ¹Õè¤×Í due to fact ÇèÒà¾Õ§˹Ö觷ÃѾÂÒ¡Ãà¡ÕèÂÇ¢éͧ㹠deadlock ÁÕ¡ÒÃÅçÍ¡ SQL Server ´éÒ¹Í×è¹ æ ¢Í§ deadlock ÍÂÙè·ÕèÃдѺä¤Åà͹µìá;ÅÔपѹ ¤Òº SQL Server ä´éäÁèÁÕµÑǤǺ¤ØÁ µèÍ仹Õé¤×Í µÑÇÍÂèÒ§·ÕèÊͧ¢Í§ÇÔ¸Õ¹ÕéÍÒ¨à¡Ô´¢Öé¹ áÅÐÇÔ¸Õ·Õèà»ç¹ä»ä´éá;ÅÔपѹÊÒÁÒöËÅÕ¡àÅÕè§
   
   
   1. ¡ÒáÃШÒÂẺä¤ÅàÍç¹µì/à«ÔÃì¿àÇÍÃì Deadlock ¡Ñºä¤Åà͹µì·Õè˹Öè§à¸Ã´
      ¶éÒä¤Åà͹µìÁÕ¡ÒÃàª×èÍÁµèÍ·Õèà»Ô´ËÅÒ áÅÐà¸Ã´à´ÕÂǢͧ¡ÒôÓà¹Ô¹¡Òà deadlock ·Õèᨡ¨èÒµèÍ仹ÕéÍÒ¨à¡Ô´¢Öé¹ For brevity, the term "dbproc" used here refers to the client connection structure.
      
      

       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)
      								

      In the case shown above, a single client application thread has two open connections. It asynchronously submits a SQL operation on dbproc1. This means it does not wait on the call to return before proceeding. The application then submits another SQL operation on dbproc2, and awaits the results to start processing the returned data. When data starts coming back (whichever dbproc first responds -- assume this is dbproc1), it processes to completion all the data returned on that dbproc. It fetches results from dbproc1 until SPID1 gets blocked on a lock held by SPID2 (because the two queries are running asynchronously on the server). At this point, dbproc1 will wait indefinitely for more data. SPID2 is not blocked on a lock, but tries to send data to its client, dbproc2. However, dbproc2 is effectively blocked on dbproc1 at the application layer as the single thread of execution for the application is in use by dbproc1. This results in a deadlock that SQL Server cannot detect or resolve because only one of the resources involved is a SQL Server resource.
   2. Client/Server Distributed Deadlock with a Thread per Connection
      
      Even if a separate thread exists for each connection on the client, a variation of this distributed deadlock may still occur as shown by the following.
      
      

      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)
      								

      This case is similar to Example A, except dbproc2 and SPID2 are running a SELECT statement with the intention of performing row-at-a-time processing and handing each row through a buffer to dbproc1 for an INSERT, UPDATE, or DELETE statement on the same table. Eventually, SPID1 (performing the INSERT, UPDATE, or DELETE) becomes blocked on a lock held by SPID2 (performing the SELECT). SPID2 writes a result row to the client dbproc2. Dbproc2 then tries to pass the row in a buffer to dbproc1, but finds dbproc1 is busy (it is blocked waiting on SPID1 to finish the current INSERT, which is blocked on SPID2). At this point, dbproc2 is blocked at the application layer by dbproc1 whose SPID (SPID1) is blocked at the database level by SPID2. Again, this results in a deadlock that SQL Server cannot detect or resolve because only one of the resources involved is a SQL Server resource.
   Both examples A and B are fundamental issues that application developers must be aware of. They must code applications to handle these cases appropriately.
   
   Resolutions:
   
   Two reliable solutions are to use either a query timeout or bound connections.
   
   
   • Query Timeout
     When a query timeout has been provided, if the distributed deadlock occurs, it will be broken when then timeout happens. See the DB-Library or ODBC documentation for more information on using a query timeout.
   • Bound Connections
     This feature allows a client having multiple connections to bind them into a single transaction space, so the connections do not block each other. For more information, see the "Using Bound Connections" topic in SQL Server 7.0 Books Online.
5. Blocking Caused by a SPID That Is in a "Golden," or Rollback, State
   
   A data modification query that is KILLed, or canceled outside of a user-defined transaction, will be rolled back. This can also occur as a side effect of the client computer restarting and its network session disconnecting. Likewise, a query selected as the deadlock victim will be rolled back. A data modification query often cannot be rolled back any faster than the changes were initially applied. For example, if a DELETE, INSERT, or UPDATE statement had been running for an hour, it could take at least an hour to roll back. This is expected behavior, because the changes made must be completely rolled back, or transactional and physical integrity in the database would be compromised. Because this must happen, SQL Server marks the SPID in a "golden" or rollback state (which means it cannot be KILLed or selected as a deadlock victim). This can often be identified by observing the output ofsp_who, which may indicate the ROLLBACK command. ¡Ãкǹ¡ÒÃʶҹÐ:column ofsys.sysprocesseswill indicate a ROLLBACK status, which will also appear insp_whooutput or in SQL Server Management Studio Activity Monitor.
   ¡ÒÃá¡éä¢:
   
   You must wait for the SPID to finish rolling back the changes that were made.
   
   ¶éÒà«ÔÃì¿àÇÍÃì¡ÓÅѧ»Ô´Å§ã¹ midst ¢Í§¡ÒôÓà¹Ô¹¡ÒùÕé °Ò¹¢éÍÁÙŨÐÍÂÙèã¹âËÁ´¡ÒáÙé¤×¹àÁ×èÍàÃÔèÁãËÁè áÅФس¨ÐäÁèÊÒÁÒöà¢éÒ¶Ö§¨¹¡ÇèÒ·Ñé§ËÁ´à»Ô´ ¸ØáÃÃÁ¨Ð¶Ù¡»ÃÐÁÇżŠàÃÔèÁµé¹Ãкº¡Ùé¤×¹ essentially à´ÕÂǡѹ¡ÑºÃÐÂÐàÇÅÒÊÓËÃѺáµèÅиØáÃÃÁà»ç¹¡ÒáÙé¤×¹¢Í§àÇÅÒ·Ó§Ò¹·ÕèãªéàÇÅÒ áÅаҹ¢éÍÁÙŨÐäÁèÊÒÁÒöà¢éÒ¶Ö§ã¹ÃÐËÇèÒ§ÃͺÃÐÂÐàÇÅÒ¡Ò÷ӧҹ¹Õé «Öè§ à«ÔÃì¿àÇÍÃìŧà¾×èÍá¡éä¢¤Ó SPID ã¹Ê¶Ò¹ÐÂé͹¡ÅѺ¡Òúѧ¤ÑºãËéÁÑ¡¨Ð counterproductive
   
   àÁ×è͵éͧ¡ÒÃËÅÕ¡àÅÕè§ʶҹ¡Òóìàªè¹¹Õé äÁè·ÓãË­èªØ´ INSERT, UPDATE ËÃ×Íź¡ÒôÓà¹Ô¹¡ÒÃÃÐËÇèÒ§ªÑèÇâÁ§ÇèÒ§ OLTP Ãкº ¶éÒà»ç¹ä»ä´é ·Ó¡ÒôÓà¹Ô¹¡ÒÃàªè¹ã¹ÃÐËÇèÒ§ÃͺÃÐÂÐàÇÅҢͧ¡Ô¨¡ÃÃÁ·ÕèµèÓ
6. ºÅçÍ¡·Õèà¡Ô´¨Ò¡¡ÒÃàª×èÍÁµèÍ Orphaned
   
   àÃÔèÁµé¹¶éÒ traps ¢Í§á;ÅÔपѹä¤Åà͹µìËÃ×ÍàÇÔÃì¡Ê൪ѹ¢Í§ä¤ÅàÍç¹µì¶Ù¡ãËÁè à«ÊªÑ¹¢Í§à¤Ã×Í¢èÒÂä»Âѧà«ÔÃì¿àÇÍÃìÍÒ¨äÁèÁÕ¡Ò÷ѹ·Õ¡ÀÒÂãµéà§×è͹䢺ҧÍÂèÒ§ä´é ¨Ò¡ÁØÁÁͧ¢Í§à«ÔÃì¿àÇÍÃì ä¤Åà͹µìÂѧ¤§ÁÕÍÂÙè áÅÐÂѧÍÒ¨¶Ù¡ÃÑ¡ÉÒã´ æ ä´éÃѺ¡ÒÃÅçÍ¡ÊÓËÃѺ¢éÍÁÙÅà¾ÔèÁàµÔÁ ãËé¤ÅÔ¡ËÁÒÂàÅ¢º·¤ÇÒÁµèÍ仹Õé à¾×èÍ´Ùº·¤ÇÒÁã¹°Ò¹¤ÇÒÁÃÙé¢Í§ Microsoft::
   137983

   (http://support.microsoft.com/kb/137983/ )

   ÇÔ¸Õ¡ÒÃá¡é䢻ѭËÒ¡ÒÃàª×èÍÁµèÍ orphaned ã¹ SQL Server
   
   ¡ÒÃá¡éä¢:
   
   ¶éÒá;ÅÔपѹä¤Åà͹µìä´éÁÕ¡ÒÃàª×èÍÁµèÍ â´ÂäÁèÁÕ¡ÒÃÅéÒ§¢éÍÁÙŢͧ·ÃѾÂÒ¡ÃÍÂèÒ§àËÁÒÐÊÁ ¤Ø³ÊÒÁÒöÊÔé¹ÊØ´ SPID ·Õè â´Âãªé¤ÓÊÑ觷ÓÅÒ ¤ÓÊÑ觷ÓÅÒÂãªé¤èÒ SPID à»ç¹ÍÔ¹¾Øµ µÑÇÍÂèÒ§àªè¹ ¡Ò÷ÓÅÒ SPID 9 à¾Õ§á¤èÍÍ¡¤ÓÊÑ觵èÍ仹Õé:
   
   

   KILL 9
   						

   
   ËÁÒÂà˵Ø:¤ÓÊÑ觷ÓÅÒÂÍÒ¨ãªéàÇÅÒ¶Ö§ 30 ÇÔ¹Ò·ÕãËéàÊÃç¨ÊÁºÙóì à¹×èͧ¨Ò¡¶Ö§ªèǧàÇÅÒÃÐËÇèÒ§¡ÒõÃǨÊͺÊÓËÃѺ¤ÓÊÑ觷ÓÅÒÂ

â»Ãá¡ÃÁ»ÃÐÂØ¡µì Involvement 㹻ѭËÒ¡ÒúÅçÍ¡

¡ÅѺ仴éÒ¹º¹ | ãËé¢éÍàʹÍá¹Ð