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

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

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

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

• ?? ???? ???????? ??? ??? ??? ????????? ?? ??? ????? ?????? ?? ???, ???? ???????? ?????? ?? ?? hundred thousand ?????? ??????? ?? ??????? ?? ??? ????? ?????? ???? ?? ??, ?? ???? ??? ?? ???? ?? ?? ?? ???? ???????????? ?? ????? ???? ??? ????? ????? ?? ???? suppose:

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

  ?? ??? ??? ??? hundred ?? ?????? ?? ????? ?? ?? dramatically locks ?? ????? ????????? accumulate ?? ??????? ????? ?? ????? ?? ?????? ?? ?? ???? ???? ?????? ?? ???::

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

• ?????? ????? footprint ???? ?? ??? ??? ???? ?????? ???? ?? ?? ????? ????? ?? ??? ?? ???????? Lookups ?? ??? ?????? ??? ???? ?? ????? ????? ?? ??????? ??; ???? ??? ??, ?? deadlocks ?? ??????? ?? ?????? ??, ?? ????????: adversely ???????? concurrency ?? ???????????? ?? ?????? ?? ??????? ???? ?? ??? ??????? ????? ?? ???? ??, opportunities ????? ??????????? ?? ?????? ????? ?? ??????? ?? ??? ?? ??????????? ?? ?????? ?????? ?? ??? ??? ?????? ??????????? ??? ?????? ?? ??? ?? ??? ??????????? ????? ?? ??? ????? seeks ??? ?????? ?? ?? ?? ??? ????? ??????????? ???????? ?? ???? ?? ??? ?????? ???????? ?????? ????? ??? ????? ?? ????? ????? ??? ???? ?? ???, ???????????? ??,??????????? ???????? ???????SQL Server 2000 ?? ??? ?????????????? ?? ???????? ??????? SQL Server 7.0?
  
  ?? ???????????? ?? ?? ?????? ?? ??????????? ????? ?? ??? seeks ?? ??? ??? ??? ????????? ?? ???? ???? ?? ??? ??? ???????? Lookups (selectivity ????? ?????? ?? ??? ??????????? ?? ???) ?? ???? ?? ???? ?? ??? ???? ??? ??? SQL ????? ?? ?????? ????? ?? ?? ???? ???????? ????? ??????? ?????? ?? ????????? ???? ?? ???? ??, ?? ?? PREFETCH ???????? ????? ???? ?? ??? ????? ?? ???? ??? SQL Server PREFETCH ?? ????? ???? ???????? ????? ?? ???, ??? ?? ?????? ?? ?? ??? ?? ??? repeatable ???? ?? ??? ?????? ?? ?? ??? ?? ????????? isolation ???? ?? ??? ???? ?????? ???? ???? ?? ?? ?? ???? ??? ????? ?? ??? ???? isolation ???? ?? ?? ?? ??? ???? ??? ?? ??? ???? ?? ???? ?? ??????? ????? locks (????? ????? ??????????? ?? ?? ?? nonclustered ???????????), ????? ????? thresholds ?? ??? ???? ?? ??? ??? ??? ?????? ???? ?? ???? ??? ?????? ?? ?? ????? ?? ????? ??? ?? ?????????? ?? ??? ?? escalated ????? ?? ???? ?????? ?????, ??, ??? ??, ?? ???? ??????? ??? ?? ???? ??????? ????? ?? ??? ???? isolation ???? ?? ??????? ??? ??? ???????? ????? ?? ??? PREFETCH ??? ????? ???? ?? ??? ??, ?? ?????? ????? ??? ??????????? ????? ?? ??????????? ????? ??????? ?????? ???????? ????? ??????? ?????? ???? ??? ????? ???? ???? nonclustered ??????????? ?? ???????? ??????? ?? ?????? ?? ????? ????? Covering ??????????? (??????????? ?????? ??? ????? ??? ?? ???? ?????? ??? ??? ????? ????? ??? ??) ????? ?? ??? ???? ??, ?? ?? ?? ?? ?? ??????????? ?? ?? ????? ?????? ?? ????? ?? ??? ?? ???? ??? ??? ??? ??? ????? ???? ??? ????? ??? ??????? ?? covers 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)				

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