針對編譯鎖定所造成的封鎖問題進行疑難解答

本文說明如何針對編譯鎖定所造成的封鎖問題進行疑難解答和解決。

原始產品版本: SQL S
原始 KB 編號: 263889

摘要

在 Microsoft SQL Server 中,一次通常只會有一份預存程式計劃複本在快取中。 強制執行此動作需要串行化編譯程式的某些部分,而此同步處理部分是使用編譯鎖定來完成。 如果許多連線同時執行相同的預存程式,而且每次執行時都必須取得該預存程式的編譯鎖定,則會話標識符 (SPID) 可能會開始互相封鎖,因為其每次嘗試取得對象的獨占編譯鎖定。

以下是編譯封鎖的一些典型特性,可在封鎖輸出中觀察到:

  • waittype對於已封鎖和 (,通常) 封鎖會話 SPID 會 LCK_M_X (獨佔) ,格式waitresourceOBJECT: dbid: object_id [[COMPILE]]為 ,其中 object_id 是預存程式的物件標識符。

  • 封鎖程式具有 waittype 可執行的NULL狀態。 封鎖者 (waittypeLCK_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是對象識別碼。 此物件識別碼屬於預存程式,而不是數據表。

其他相關資訊

預存程式重新編譯是在預存程式或觸發程式上編譯鎖定的說明之一。 在此情況下,解決方法是減少或消除重新編譯。

導致編譯鎖定的其他案例

  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 擴充事件會話中。

    事件名稱 Text
    rpc_starting mystoredproc
    sp_cache_miss mystoredproc
    sql_batch_starting mystoredproc
    sp_cache_hit mystoredproc
    ... ...

    sp_cache_miss 會在依名稱快取查閱失敗時發生,但在將模棱兩可的物件名稱解析為對象標識元且有 sp_cache_hit 事件之後,最終在快取中找到相符的快取計劃時發生。

    此編譯鎖定問題的解決方案是確定預存程序的參考符合擁有者資格。 (不要執行 mystoredproc,請使用 exec dbo.mystoredproc.) 雖然擁有者資格對於效能考慮很重要,但您不需要使用資料庫名稱來限定預存程式,以防止額外的快取查閱。

    您可以使用標準封鎖疑難解答方法來偵測編譯鎖定所造成的封鎖。

  2. 預存程式前面會加上 sp_

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

  3. 預存程式是使用不同的大小寫來叫用, (上 /下)

    如果使用不同案例來執行擁有者限定程式, (與用來建立它的大小寫不同的大小寫) ,則程式可以觸發 CacheMiss 事件或要求 COMPILE 鎖定。 最後,程式會使用快取的計劃,而且不會重新編譯。 但是,如果有許多SPID嘗試使用與用來建立它之案例不同的案例來執行相同的程式,則 COMPILE 鎖定的要求有時可能會造成 封鎖鏈 結狀況。 不論伺服器或資料庫上使用的排序順序或定序為何,都是如此。 此行為的原因是用來在快取中尋找程序的演算法是以效能) (哈希值為基礎,而哈希值可能會在案例不同時變更。

    因應措施是卸除並建立程式,方法是使用與應用程式執行程式時所使用的相同案例。 您也可以使用正確的大小寫 (上下) ,確定程式是從所有應用程式執行。

  4. 預存程式會叫用為語言事件

    如果您嘗試以語言事件而不是 RPC 形式執行預存程式,SQL Server 必須剖析並編譯語言事件查詢、判斷查詢嘗試執行特定程式,然後嘗試在該程式的快取中尋找計劃。 若要避免 SQL Server 必須剖析和編譯語言事件的情況,請確定查詢會以 RPC 形式傳送至 SQL。

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

參考資料

OPEN SYMMETRIC KEY 命令可防止查詢計劃快取