編譯鎖定所造成的封鎖疑難排解

本文說明如何針對編譯鎖定所造成的封鎖進行疑難排解。

原始產品版本:   Sqlserver
原始 KB 編號:   263889

摘要

在 Microsoft SQL Server 中,一次只能有一個預存處理排程複本在快取中。 強制執行這項作業需要序列化編譯器的某些部分,而且此同步處理是透過使用編譯鎖定進行部分完成。 如果有許多連線同時執行相同的預存程式,且每次執行該預存程式必須取得一個編譯鎖定,則 session IDs (Spid) 每次嘗試取得物件的獨佔編譯鎖定時,就會開始相互封鎖。

以下是一些編譯封鎖的常見特徵,可在封鎖輸出中加以觀察:

  • waittype 針對封鎖和 (,通常是) 封鎖會話 Spid LCK_M_X (獨佔) ,其 waitresource 格式為 OBJECT: dbid: object_id [[COMPILE]] ,其中 object_id 是預存程式的物件識別碼。

  • 封鎖封鎖 waittype 有空,狀態可供使用。 Blockees 具有 waittype LCK_M_X (獨佔鎖定) ,狀態為 [休眠]。

  • 雖然封鎖事件的持續時間可能很長,但沒有一部 SPID 很長時間封鎖其他 Spid。 有滾動封鎖。 一個編譯完成後,另一個 SPID 會在幾秒或更少的時間內接管標頭封鎖的角色,依此類推。

下列資訊來自于 sys.dm_exec_requests 這種封鎖期間的快照:

session_id   blocking_session_id   wait_type   wait_time   waitresource ---------- ------------------- --------- --------- ----------------------------
221           29                   LCK_M_X     2141        OBJECT: 6:834102
[[COMPILE]]
228           29                   LCK_M_X     2235        OBJECT: 6:834102
[[COMPILE]]
29            214                  LCK_M_X     3937        OBJECT: 6:834102
[[COMPILE]]
13            214                  LCK_M_X     1094        OBJECT: 6:834102
[[COMPILE]]
68            214                  LCK_M_X     1968        OBJECT: 6:834102
[[COMPILE]]
214           0                    LCK_M_X     0           OBJECT: 6:834102
[[COMPILE]]

waitresource 欄 (6:834102) 中,6是資料庫識別碼,834102是物件識別碼。 這個物件識別碼屬於某個預存程式,而非 table。

其他相關資訊

預存程式的重新編譯是在預存程式或觸發器上編譯鎖定的一個說明。 在此案例中,方案是減少或消除重新編譯。

導致編譯鎖定的其他案例

  1. 預存程式執行時不需完全限定名稱

    • 執行預存程式的使用者不是程式的擁有者。
    • 預存程式名稱並未完全限定物件擁有者的名稱。

    例如,如果使用者 dbo 擁有物件 dbo.mystoredproc 及另一個使用者, Harry 請使用命令來執行這個預存程式, exec mystoredproc 根據物件名稱的初始快取查閱會失敗,因為物件不是擁有者限定的。 (它尚未知道是否有另一個名為的預存程式 Harry.mystoredproc 存在。 因此,SQL Server 無法確定所要執行的快取方案 dbo.mystoredproc 。 ) SQL Server 會在程式中取得獨佔編譯鎖定,並進行編譯器的準備過程。 這包括將物件名稱解析為物件識別碼。 在 SQL Server 編譯計畫之前,SQL Server 會使用此物件識別碼來執行程式快取的更精確搜尋,也可以找出先前編譯的計畫,甚至不需要擁有者資格。

    如果找到現有的計畫,則 SQL Server 會重複使用快取的計畫,而且不會實際編譯該預存程式。 不過,缺乏擁有者資格的強制會強制 SQL Server 執行第二次快取查閱,並取得獨佔編譯鎖定,程式才會決定現有的快取執行計畫可以重複使用。 取得鎖定和執行查閱,以及達到此點所需的其他工作,可能會引入導致封鎖的編譯鎖定延遲。 如果許多不是預存程式擁有者的使用者同時執行該程式,而不提供擁有者的名稱,就會特別如此。 即使您沒有看到在等待編譯鎖定的 Spid,缺乏擁有者限定也會在儲存過程執行中引入延遲,並導致高 CPU 使用率。

    發生此問題時,會在 SQL Server 延伸的事件會話中記錄下列的事件順序。

    事件名稱 文字
    rpc_starting mystoredproc
    sp_cache_miss mystoredproc
    sql_batch_starting mystoredproc
    sp_cache_hit mystoredproc
    ... ...

    sp_cache_miss 當以名稱進行的快取查閱失敗時發生,但在將不明確的物件名稱解析為物件識別碼且有事件時,最後在快取中找到符合的快取方案 sp_cache_hit

    此編譯鎖定問題的解決方案是確定對預存程式的參照是擁有者限定的。 (,而不是 exec mystoredproc ,請使用 exec dbo.mystoredproc 。 ) 出於效能考慮,「擁有者」是很重要的,您不需要使用資料庫名稱來限定儲存的處理常式,就可以避免進行其他的快取查閱。

    使用標準封鎖疑難排解方法,可以偵測出由編譯鎖定所造成的封鎖。

  2. 預存程式的首碼為 sp_

    如果您的預存程式名稱是以前置詞開頭, sp_ 而不是在 master 資料庫中,則會在每次執行的快取命中之前看到 sp_cache_miss ,即使您擁有者限定預存程式也是一樣。 這是因為 sp_ 首碼會告訴 SQL Server 預存程式是系統預存程式,而系統預存程式會有不同的名稱解析規則。 (偏好的位置位於 master 資料庫中。 ) 使用者建立的預存程式名稱不應該以開頭 sp_

  3. 預存程式是使用不同的大小寫 (上/lower)

    如果使用不同的 case 來執行擁有者資格的程式 () 中所用的大小寫,此程式就可以觸發 CacheMiss 事件或要求編譯鎖定。 最後,程式會使用快取的計畫,而不會重新編譯。 不過,如果有許多 Spid 嘗試使用與用來建立相同的程式時所用的 case 不同,則編譯鎖定的要求有時會造成 封鎖鏈 的情況。 不論是在伺服器上或資料庫上使用的排序次序或排序規則為何,都是如此。 這種行為的原因是,用來尋找快取中之程式的演算法是根據效能) (的雜湊值,而且如果 case 不同,雜湊值也會變更。

    解決方法是使用與應用程式執行程式時所使用的相同大小寫方式,來丟棄並建立程式。 您也可以使用正確的大小寫 (upper 或小寫) ,以確定是否要從所有應用程式執行該程式。

  4. 預存程式是以語言事件的方式調用

    如果您嘗試將預存程式當做語言事件執行,而不是以 RPC 的身分執行,則 SQL Server 必須剖析及編譯語言事件查詢,判斷查詢正嘗試執行特定程式,然後嘗試在快取中尋找該程式的計畫。 若要避免出現 SQL Server 必須剖析及編譯語言事件的情況,請確定查詢以 RPC 形式傳送至 SQL。

    如需詳細資訊,請參閱線上叢書文章中 建立預存 程式的「系統儲存程式」一節。

參考

開啟對稱金鑰命令可避免查詢方案快取