ÇÔ¸Õ¡ÒÃá¡é»Ñ­ËÒ¡ÒúÅçͤ·Õèà¡Ô´¨Ò¡¡ÒèѴ¡ÒÃÅçÍ¡ã¹ SQL Server

ÇÔ¸Õ¡ÒÃÃкØÇèÒÇèÒ ÅçÍ¡¨Ñ´à»ç¹ÊÒà˵ءÒúÅçͤ

¡ÅѺ仴éÒ¹º¹

ÇÔ¸Õ¡Òûéͧ¡Ñ¹¡ÒèѴ¡ÒÃÅçÍ¡

• áºè§¤èÒ´Óà¹Ô¹¡Òêش§Ò¹·ÕèÁÕ¢¹Ò´ãË­èã¹ËÅÒÂ æ ¡ÒôÓà¹Ô¹§Ò¹¢¹Ò´àÅç¡Å§ µÑÇÍÂèÒ§àªè¹ ÊÁÁµÔ¤Ø³Ãѹ¡ÒÃÊͺ¶ÒÁµèÍ仹Õéà¾×èÍźÃÐàºÕ¹à¡èÒ hundred thousand ËÅÒ¨ҡµÒÃÒ§¡ÒõÃǨÊͺ áÅШҡ¹Ñé¹ ¤Ø³¾ºÇèÒ à¡Ô´¡ÒèѴ¡ÒÃÅçÍ¡·Õè¶Ù¡ºÅçͤ¼ÙéãªéÍ×è¹:

  DELETE FROM LogMessages WHERE LogDate < '2/1/2002'						

  ´éÇ¡ÒÃźàá¤ÍÃì´àËÅèÒ¹Õé hundred ºÒ§¤ÃÑé§ ¤Ø³ÊÒÁÒöŴ¨Ó¹Ç¹·Õèãªé㹡ÒÃÅçÍ¡·Õè accumulate µè͸ØáÃÃÁ áÅлéͧ¡Ñ¹äÁèãËé¨Ñ´ÅçÍ¡¨¨Ð µÑÇÍÂèÒ§::

  SET ROWCOUNT 500
  delete_more:
       DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
  IF @@ROWCOUNT > 0 GOTO delete_more
  SET ROWCOUNT 0

• Å´ footprint ÅçÍ¡¢Í§áººÊͺ¶ÒÁ â´Â¡Ò÷ÓãËéẺÊͺ¶ÒÁÁÕ»ÃÐÊÔ·¸ÔÀÒ¾ÁÒ¡·ÕèÊØ´ ¡ÒÃÊ᡹·ÕèÁÕ¢¹Ò´ãË­èËÃ×͵ÑÇàÅ¢·ÕèÁÕ¢¹Ò´ãË­è¢Í§¡Òäé¹ËÒ·Õè¤Ñè¹Ë¹éÒÍÒ¨à¾ÔèÁâÍ¡Òʢͧ¡ÒèѴ¡ÒÃÅçÍ¡ ¹Í¡¨Ò¡¹Õé Áѹà¾ÔèÁâÍ¡ÒÊ deadlocks áÅÐÁÕ¼Åâ´Â·ÑèÇä»ÁÒ¡µèÍ¡ÒÃà¡Ô´¾ÃéÍÁ¡Ñ¹áÅлÃÐÊÔ·¸ÔÀÒ¾¡Ò÷ӧҹ ËÅѧ¨Ò¡·Õè¤Ø³¤é¹ËÒẺÊͺ¶ÒÁ ·Õè·ÓãËéà¡Ô´¡ÒÃÅçÍ¡¨Ñ´ seeks ÅѡɳÐÊÓËÃѺâÍ¡ÒÊã¹ ¡ÒÃÊÃéÒ§´Ñª¹ÕãËÁè ËÃ×Í à¾×èÍà¾ÔèÁ¤ÍÅÑÁ¹ìŧ㹴Ѫ¹Õ·ÕèÁÕÍÂÙè à¾×èÍàÍҴѪ¹ÕËÃ×͵ÒÃÒ§¡ÒÃÊ᡹ áÅÐ à¾×èÍà¾ÔèÁ»ÃÐÊÔ·¸ÔÀÒ¾¢Í§´Ñª¹Õ ãËé¾Ô¨ÒóҡÒÃÇҧẺÊͺ¶ÒÁŧã¹Ë¹éÒµèÒ§¡ÒÃÊͺ¶ÒÁµÑÇÇÔà¤ÃÒÐËì¤Ó¶ÒÁà¾×èÍ·Ó¡ÒÃÇÔà¤ÃÒÐËì´Ñª¹ÕÍѵâ¹ÁѵԹÑé¹ àÁ×è͵éͧ¡Ò÷Óàªè¹¹Ñé¹ ã¹¡ÒÃẺÊͺ¶ÒÁàÁ¹Ù ¤ÅÔ¡µÑǪèÇÂÊÃéÒ§¡ÒûÃѺáµè§¡ÒèѴ·Ó´Ñª¹Õin SQL Server 2000, or clickPerform Index Analysisin SQL Server 7.0.
  
  One goal of this optimization is to make index seeks return as few rows as possible to minimize the cost of Bookmark Lookups (maximize the selectivity of the index for the particular query). If SQL Server estimates that a Bookmark Lookup logical operator may return many rows, it may use a PREFETCH to perform the bookmark lookup. If SQL Server does use PREFETCH for a bookmark lookup, it must increase the transaction isolation level of a portion of the query to repeatable read for a portion of the query. This means that what may look similar to a SELECT statement at a read-committed isolation level may acquire many thousands of key locks (on both the clustered index and one nonclustered index), which can cause such a query to exceed the lock escalation thresholds. This is especially important if you find that the escalated lock is a shared table lock, which, however, is not commonly seen at the default read-committed isolation level. If a Bookmark Lookup WITH PREFETCH clause is causing the escalation, consider adding additional columns to the nonclustered index that appears in the Index Seek or the Index Scan logical operator below the Bookmark Lookup logical operator in the query plan. It may be possible to create a covering index (an index that includes all columns in a table that were used in the query), or at least an index that covers the columns that were used for join criteria or in the WHERE clause if including everything in the select column list is impractical.
  
  A Nested Loop join may also use PREFETCH, and this causes the same locking behavior.
  
  ÊÓËÃѺ¢éÍÁÙÅà¾ÔèÁàµÔÁ ãËé¤ÅÔ¡ËÁÒÂàÅ¢º·¤ÇÒÁµèÍ仹Õé à¾×èÍ´Ùº·¤ÇÒÁã¹°Ò¹¤ÇÒÁÃÙé¢Í§ Microsoft::
  260652  (http://support.microsoft.com/kb/260652/ ) Nested loop join that uses a "BOOKMARK LOOKUP ...WITH PREFETCH" may hold locks longer
• Lock escalation cannot occur if a different SPID is currently holding an incompatible table lock. Lock escalation always escalates to a table lock, and never to page locks. Additionally, if a lock escalation attempt fails because another SPID holds an incompatible TAB lock, the query that attempted escalation does not block while waiting for a TAB lock. Instead, it continues to acquire locks at its original, more granular level (row, key, or page), periodically making additional escalation attempts. Therefore, one method to prevent lock escalation on a particular table is to acquire and to hold a lock on a different connection that is not compatible with the escalated lock type. An IX (intent exclusive) lock at the table level does not lock any rows or pages, but it is still not compatible with an escalated S (shared) or X (exclusive) TAB lock. For example, assume that you must run a batch job that modifies a large number of rows in themytabletable and that has caused blocking that occurs because of lock escalation. If this job always completes in less than an hour, you might create a Transact-SQL job that contains the following code, and schedule the new job to start several minutes before the batch job's start time:

  BEGIN TRAN
  SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
  WAITFOR DELAY '1:00:00'
  COMMIT TRAN				

  This query acquires and holds an IX lock onmytablefor one hour, which prevents lock escalation on the table during that time. This batch does not modify any data or block other queries (unless the other query forces a table lock with the TABLOCK hint or if an administrator has disabled page or row locks by using ansp_indexoptionstored procedure).

DBCC TRACEON (1211, -1)				

¡ÅѺ仴éÒ¹º¹