วิธีการแก้ปัญหาการบล็อคที่เกิดจากการจัดการล็อกใน SQL Server

การแปลบทความ การแปลบทความ
หมายเลขบทความ (Article ID): 323630 - ผลิตภัณฑ์ที่เกี่ยวข้องในบทความนี้
ขยายทั้งหมด | ยุบทั้งหมด

เนื้อหาบนหน้านี้

สรุป

จัดการล็อกคือ กระบวนการแปลงล็อก fine-grained มาก (เช่นล็อกแถวหรือเพ) ลงในตารางล็อก Microsoft SQL Server แบบไดนามิกกำหนดเมื่อทำการจัดการล็อก เมื่อทำการตัดสินใจนี้ SQL Server จะเข้าสู่บัญชีจำนวนที่ใช้ในการล็อกที่เก็บไว้ในการสแกนเฉพาะ จำนวนที่ใช้ในการล็อกที่เก็บไว้ โดยธุรกรรมทั้งหมด และหน่วยความจำที่ถูกใช้สำหรับการล็อกในระบบเป็นจำนวนเต็ม โดยทั่วไป การทำงานดีฟอลต์ของ SQL Server ส่งผลในการจัดการล็อกเกิดขึ้นที่จุดเหล่านั้นเท่านั้นที่คุณจะปรับปรุงประสิทธิภาพหรือเมื่อคุณต้องลดหน่วยความจำในการล็อกระบบมากเกินไปเป็นระดับที่เหมาะสมมากขึ้น อย่างไรก็ตาม ออกแบบโปรแกรมประยุกต์หรือแบบสอบถามบางอย่างอาจทริกเกอร์จัดการล็อกในเวลาเมื่อไม่ช และล็อกตาราง escalated อาจบล็อกการผู้ใช้อื่น บทความนี้อธิบายถึงวิธีการตรวจสอบว่า ล็อกจัดสาเหตุการบล็อคและวิธีการจัดการกับการจัด undesirable ล็อก

ข้อมูลเพิ่มเติม

วิธีการระบุว่าว่า ล็อกจัดเป็นสาเหตุการบล็อค

จัดการล็อกไม่ทำให้เกิดปัญหาการบล็อคที่มากที่สุด การตรวจสอบว่า จัดการล็อกเกิดรอบเวลาเมื่อคุณพบปัญหาการบล็อค เริ่มต้นการสืบค้นกลับ Profiler SQL ซึ่งประกอบด้วยล็อก: จัดเหตุการณ์ ถ้าคุณไม่เห็นใด ๆล็อก: จัดเหตุการณ์ จัดล็อกไม่เกิดบนเซิร์ฟเวอร์ของคุณ และข้อมูลในบทความนี้ไม่ได้ประยุกต์ใช้กับสถานการณ์ของคุณ

ถ้ามีการจัดการล็อกเกิดขึ้น ตรวจสอบว่า ล็อกตาราง escalated การบล็อคผู้ใช้อื่น

สำหรับข้อมูลเพิ่มเติมเกี่ยวกับวิธีการระบุ ตัวบล็อกหลักและวิธีการระบุการล็อกทรัพยากรที่เก็บไว้ โดยตัวบล็อกการหลักที่กำลังบล็อกการประมวลผลรหัส (SPIDs), เซิร์ฟเวอร์อื่น คลิกหมายเลขบทความต่อไปนี้เพื่อดูบทความในฐานความรู้ของ Microsoft:
224453การศึกษา และการแก้ไขปัญหาการบล็อคของ 2000 หรือ SQL Server 7.0
ถ้ามีการล็อกที่มีการบล็อคผู้ใช้รายอื่น ๆ เป็นสิ่งอื่นที่ไม่ใช่'ล็อก' (ระดับตาราง) TAB โหมดล็อก S (ที่ใช้ร่วมกัน), หรือ X (exclusive), จัดล็อกไม่ได้ปัญหา โดยเฉพาะ ถ้าล็อก TAB ล็อกการปรับค่า (เช่นโหมดการล็อกของ IS, IU หรือ IX), ซึ่งไม่ได้ผลของการจัดการล็อก หากเกิดปัญหาของคุณบล็อกไม่มีการจากการล็อกจัด ดูบทความ Q224453 ขั้นตอนการแก้ไขปัญหา

วิธีการป้องกันการจัดการล็อก

วิธีที่ง่ายที่สุด และ safest เพื่อป้องกันการจัดการล็อกมี การเก็บธุรกรรมที่สั้น และ เพื่อลด footprint ล็อกของแบบสอบถามแพงเพื่อที่จะไม่มีเกิน thresholds จัดการล็อก มีหลายวิธีเพื่อขอรับค่าเป้าหมายนี้ หลายอย่างที่แสดง:
  • แบ่งค่าดำเนินการชุดงานที่มีขนาดใหญ่ในหลาย ๆ การดำเนินงานขนาดเล็กลง ตัวอย่างเช่น สมมติคุณรันการสอบถามต่อไปนี้เพื่อลบระเบียนเก่า 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::
    260652Nested 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).
Additionally, you can disable lock escalation by enabling trace flag 1211. However, this trace flag disables all lock escalation globally in the instance of SQL Server. Lock escalation serves a very useful purpose in SQL Server by maximizing the efficiency of queries that are otherwise slowed down by the overhead of acquiring and releasing several thousands of locks. Lock escalation also helps to minimize the required memory to keep track of locks. The memory that SQL Server can dynamically allocate for lock structures is finite, so if you disable lock escalation and the lock memory grows large enough, attempts to allocate additional locks for any query may fail and the following error occurs:

Error: 1204, Severity: 19, State: 1
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.
หมายเหตุ:When a "1204" error occurs, it stops the processing of the current statement and causes a rollback of the active transaction. The rollback itself may block users or lead to a long database recovery time if you restart the SQL Server service.

เฉพาะโดยใช้คำแนะนำ'ล็อก'เช่น ROWLOCK alters แผนการล็อกเริ่มต้น คำแนะนำในการล็อกไม่ป้องกันการจัดการล็อก

ในวิธีการอื่น ๆ ของการป้องกันการจัดการล็อกที่กล่าวถึงก่อนหน้าในบทความนี้มีตัวเลือกที่ดีกว่าการเปิดใช้งานการตั้งค่าสถานะการสืบค้นกลับ นอกจากนี้ วิธีอื่น ๆ โดยทั่วไปจะทำประสิทธิภาพที่ดีขึ้นสำหรับแบบสอบถามนอกเหนือจากการปิดใช้งานการจัดการล็อกสำหรับอินสแตนซ์ทั้งหมด Microsoft แนะนำในการเปิดใช้งานสถานะนี้สืบค้นกลับเท่านั้นเพื่อลดการบล็อกรุนแรงที่เกิดจากการจัดการล็อกในขณะที่ตัวเลือกอื่น เช่นเหล่านั้น discussed ก่อนหน้าในบทความนี้ กำลังถูก investigated เมื่อต้องการเปิดใช้งานการสืบค้นกลับค่าสถานะเพื่อให้เปิดอยู่ เมื่อใดก็ตาม SQL Server จะเริ่มต้น เพิ่มเป็นพารามิเตอร์การเริ่มต้นเซิร์ฟเวอร์

เมื่อต้องการเพิ่มพารามิเตอร์การเริ่มต้นเซิร์ฟเวอร์ คลิกขวาในตัวจัดการองค์กรของ SQL เซิร์ฟเวอร์ คลิกคุณสมบัติและจากนั้นในการทั่วไปแท็บ คลิกพารามิเตอร์การเริ่มต้นแล้ว เพิ่มพารามิเตอร์ต่อไปนี้ (อย่างแน่นอนดังที่แสดง):
-t1211
คุณต้องวนบริการ SQL Server สำหรับพารามิเตอร์เริ่มต้นระบบใหม่เพื่อที่มีผล หากคุณรันการสอบถามต่อไปนี้ในตัววิเคราะห์คำถาม สถานะการสืบค้นกลับจะมีผลบังคับทันที:
DBCC TRACEON (1211, -1)				
อย่างไรก็ตาม ถ้าคุณไม่ต้องเพิ่ม-t1211พารามิเตอร์การเริ่มต้น ผลของตัวtraceonคำสั่งจะสูญหายไปเมื่อบริการ SQL Server จะ cycled เปิดการตั้งค่าสถานะการสืบค้นกลับ escalations ล็อกในอนาคตใด ๆ ที่ป้องกันไม่ให้ แต่ไม่กลับใด ๆ escalations ล็อกที่ได้เกิดขึ้นในธุรกรรมการใช้งานอยู่

คุณสมบัติ

หมายเลขบทความ (Article ID): 323630 - รีวิวครั้งสุดท้าย: 13 มกราคม 2554 - Revision: 5.0
ใช้กับ
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL 2005 Server Enterprise
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL 2005 Server Workgroup
Keywords: 
kbinfo kbmt KB323630 KbMtth
แปลโดยคอมพิวเตอร์
ข้อมูลสำคัญ: บทความนี้แปลโดยซอฟต์แวร์การแปลด้วยคอมพิวเตอร์ของ Microsoft แทนที่จะเป็นนักแปลที่เป็นบุคคล Microsoft มีบทความที่แปลโดยนักแปลและบทความที่แปลด้วยคอมพิวเตอร์ เพื่อให้คุณสามารถเข้าถึงบทความทั้งหมดในฐานความรู้ของเรา ในภาษาของคุณเอง อย่างไรก็ตาม บทความที่แปลด้วยคอมพิวเตอร์นั้นอาจมีข้อบกพร่อง โดยอาจมีข้อผิดพลาดในคำศัพท์ รูปแบบการใช้ภาษาและไวยากรณ์ เช่นเดียวกับกรณีที่ชาวต่างชาติพูดผิดเมื่อพูดภาษาของคุณ Microsoft ไม่มีส่วนรับผิดชอบต่อความคลาดเคลื่อน ความผิดพลาดหรือความเสียหายที่เกิดจากการแปลเนื้อหาผิดพลาด หรือการใช้บทแปลของลูกค้า และ Microsoft มีการปรับปรุงซอฟต์แวร์การแปลด้วยคอมพิวเตอร์อยู่เป็นประจำ
ต่อไปนี้เป็นฉบับภาษาอังกฤษของบทความนี้:323630

ให้ข้อเสนอแนะ

 

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