SQL Server 會封鎖因編譯鎖定的描述

文章翻譯 文章翻譯
文章編號: 263889 - 檢視此文章適用的產品。
全部展開 | 全部摺疊

在此頁中

結論

在 Microsoft SQL Server,只有一個複本的預存程序計劃通常是在快取中一次。 強制執行這需要編譯] 程序的某些部分的序列化,而且這項同步處理完成部分,藉由使用編譯的鎖定。 如果許多連線同時執行相同的預存程序,並產生編譯鎖定必須取得該預存程序的執行每次系統處理序識別碼 (SPID) 可能會開始封鎖另一個,如同它們各自嘗試取得物件的編譯獨占鎖定。

其他相關資訊

預存程序重新編譯是一個說明供編譯預存程序或觸發程序上的鎖定。 減少或消除在重新編譯的次數,在這種情況下是方案。 最常見的原因,預存程序可能需要重新編譯的說明和一些有用的資訊上減少重新編譯的頻率,請參閱下列的 「 Microsoft 知識庫 」 文件:
243586預存程序重新編譯的疑難排解
編譯鎖定發生的另一種情況是,當下列情況成立時:
  • 在執行預存程序的使用者不是程序的擁有者。
  • 預存程序名稱不是完整的物件擁有者的名稱。
範例如果使用者"dbo"擁有物件dbo.mystoredproc與另一位使用者,"Harry],使用 [命令] exec mystoredproc,"初始的快取查閱的物件名稱失敗,因為物件不是擁有者限定執行此預存程序。 (它還不知道是否存在於另一個名為 Harry.mystoredproc 的預存程序。 因此,SQL Server 無法確定 dbo.mystoredproc 快取的計劃是正確的一個執行)。 然後 SQL Server 取得程序的編譯獨占鎖定,並使編譯程序的準備工作。 這包括物件名稱解析物件 ID。 SQL Server 會編譯計劃之前先 SQL Server 會使用這個物件識別碼來執行更精確的搜尋的程序快取,而且可以找出先前已編譯的計劃,即使沒有擁有者限定性條件。

如果找到現有的計劃,SQL Server 就會重複使用快取的計劃,並不會實際編譯預存程序。 但是,擁有者限定性條件缺乏會強制執行第二個的快取查閱並取得獨占式編譯鎖定之前,程式會決定可以重複使用現有的快取的執行計劃的 SQL Server。 取得鎖定,並執行查詢及其他達到這一點所需的工作可能會造成編譯鎖定的延遲會導致封鎖。 這是尤其如果許多使用者不是預存程序的擁有者,同時執行此程序,而不提供擁有者的名稱。 要注意即使您沒有等候編譯鎖定的 SPID 缺乏擁有者限定性條件可以介紹執行預存程序中的延遲,並造成不必要地高的 CPU 使用率。

發生這個問題時,以下的事件順序都會記錄在 SQL Server Profiler 追蹤。 (若要追蹤與快取相關的事件,您必須啟用進階的事件。 若要執行此動作按一下選項工具功能表,然後再選取所有的事件類別。)

摺疊此表格展開此表格
事件類別文字
RPC: 啟動mystoredproc
SP:CacheMissmystoredproc
SP:ExecContextHitmystoredproc
預存程序: 啟動mystoredproc
......

SP:CacheMiss快取查閱,依名稱失敗時,就會發生。 下列SP:ExecContextHit指出符合快取的計劃最後找到在快取中之後模稜兩可的物件名稱被解析為物件 ID。 在的情況下而定SP:CacheHit可能會出現代替SP:ExecContextHit.

這個問題的編譯鎖定解決方案是確定預存程序的參考都是限定為擁有者。 (而非exec mystoredproc使用 execdbo.mystoredproc.) 當擁有者限定性條件重要效能方面的原因時並不需要限定預存程序,以避免其他的快取查閱的資料庫名稱。

封鎖,是由鎖定可以使用如下列的 「 Microsoft 知識庫 」 文件中所定義的封鎖指令碼偵測到的編譯所造成:
251004INF: 如何監視 SQL Server 7.0 封鎖
271509INF: 如何監視 SQL Server 2000 封鎖
The following are some typical characteristics of compile blocking that can be observed in the blocking script output:
  • lastwaittypefor the blocked and (usually) blocking SPIDs is LCK_M_X (exclusive) andwaitresourceis of the form "TAB: dbid:object_id[[COMPILE]]," where "object_id" is the object ID of the stored procedure.
  • Blockers havewaittype0x0000, status runnable. Blockees havewaittype0x000e (exclusive lock), status sleeping.
  • Although the duration of the blocking incident may be long, there is no single SPID that is blocking the other SPIDs for a long time. There is rolling blocking. As soon as one compilation is complete, another SPID takes over the role of head blocker for a?several seconds or less, and so on.
The following information is from a snapshot ofsysprocessesduring this kind of blocking:
   spid  blocked  waittype  waittime  lastwaittype  waitresource
   ----  -------  --------  --------  ------------  -------------------------
   
   221    29      0x000e    2141      LCK_M_X       TAB: 6:834102 [[COMPILE]]
   228    29      0x000e    2235      LCK_M_X       TAB: 6:834102 [[COMPILE]]
    29   214      0x000e    3937      LCK_M_X       TAB: 6:834102 [[COMPILE]]
    13   214      0x000e    1094      LCK_M_X       TAB: 6:834102 [[COMPILE]]
    68   214      0x000e    1968      LCK_M_X       TAB: 6:834102 [[COMPILE]]
   214     0      0x0000       0      LCK_M_X       TAB: 6:834102 [[COMPILE]]
waitresourcecolumn ("6:834102"), 6 is the database ID and 834102 is the object ID. Be aware that this object ID belongs to a stored procedure, not to a table (despite the "TAB" lock type).

備忘稿
  • If you are using SQL Server 2005, many of the system tables from SQL Server 2000 are now implemented as a set of views. These views are known as compatibility views, and they are meant for backward compatibility only. The compatibility views expose the same metadata that was available in SQL Server 2000. For more information about mapping between the SQL Server 2000 system tables and the SQL Server 2005 system views, see the "Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views" topic in SQL Server 2005 Books Online.
  • If your stored procedure name starts with the "sp_" prefix and is not in the master database, you seeSP:CacheMissbefore the cache hit for each execution even if you owner-qualify the stored procedure. This is because the sp_ prefix tells SQL Server that the stored procedure is a system stored procedure, and system stored procedures have different name resolution rules. (The "preferred" location is in the master database.) The names of user-created stored procedures should not start with "sp_".
  • If an owner-qualified procedure is executed with a different case than the owner-qualified procedure was created as, the owner-qualified procedure can obtain aCacheMissor request a COMPILE lock but eventually use the cached plan. Therefore, this would not actually recompile the procedure and should not cause much of an overhead. But in certain situations, the request for a COMPILE lock can cause a "blocking chain" situation if there are many SPIDs trying to execute the same procedure with a different case than the procedure was created as. This is true regardless of the sort order or collation that is being used on the server or on the database. The reason for this behavior is that the algorithm that is being used to find the procedure in cache is based on hash values (for performance reasons), which can change if the case is different.

    The workaround is to drop and create the procedure with the same case as?the procedure is executed by the application. You can also make sure that the procedure is executed from all applications that use the same case.
  • If you try to execute a stored procedure as a Language Event instead of as an RPC, SQL Server must parse and compile the language event query, determine that the query is trying to execute the particular procedure, and then try to find a plan in cache for that procedure. To avoid this situation in which SQL Server must parse and compile the language event, make sure that the query is sent to SQL as an RPC.

    For more information, see the "System Stored Procedures" section in the Books Online article "Creating a Stored Procedure."


Known issues

Here are some known issues that can prevent plan caching:
  • You use BLOB variables as a Stored Procedure parameter. 如需詳細資訊,請按一下下列的文件編號,檢視 「 Microsoft 知識庫 」 中的文件::
    2380435FIX: The query plan for a stored procedure is not cached if the stored procedure uses a BLOB variable and the variable is used in a string function in Microsoft SQL Server 2008
  • You use OPEN SYMMETRIC KEY in a Stored Procedure/Query Batch. For more information, see the following MSDN blog entry:
    http://blogs.msdn.com/b/sqlserverfaq/archive/2010/09/08/open-symmetric-key-command-prevents-query-plan-caching.aspx

屬性

文章編號: 263889 - 上次校閱: 2010年11月24日 - 版次: 1.0
這篇文章中的資訊適用於:
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Express Edition
  • Microsoft SQL Server 2005 Workgroup Edition
關鍵字:?
kbinfo kbmt KB263889 KbMtzh
機器翻譯
重要:本文是以 Microsoft 機器翻譯軟體翻譯而成,而非使用人工翻譯而成。Microsoft 同時提供使用者人工翻譯及機器翻譯兩個版本的文章,讓使用者可以依其使用語言使用知識庫中的所有文章。但是,機器翻譯的文章可能不盡完美。這些文章中也可能出現拼字、語意或文法上的錯誤,就像外國人在使用本國語言時可能發生的錯誤。Microsoft 不為內容的翻譯錯誤或客戶對該內容的使用所產生的任何錯誤或損害負責。Microsoft也同時將不斷地就機器翻譯軟體進行更新。
按一下這裡查看此文章的英文版本:263889
Microsoft及(或)其供應商不就任何在本伺服器上發表的文字資料及其相關圖表資訊的恰當性作任何承諾。所有文字資料及其相關圖表均以「現狀」供應,不負任何擔保責任。Microsoft及(或)其供應商謹此聲明,不負任何對與此資訊有關之擔保責任,包括關於適售性、適用於某一特定用途、權利或不侵權的明示或默示擔保責任。Microsoft及(或)其供應商無論如何不對因或與使用本伺服器上資訊或與資訊的實行有關而引起的契約、過失或其他侵權行為之訴訟中的特別的、間接的、衍生性的損害或任何因使用而喪失所導致的之損害、資料或利潤負任何責任。

提供意見

 

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