本文闡述了批處理在 sql server 2005 中的緩存和重用方式，并就最大限度重用已緩存的計劃提供了相應的最佳實務。另外，文中還說明了一些重新編譯批處理的方案，并針對減少或消除不必要的重新編譯，給出了最佳實務。
一、本白皮書的目的
此白皮書的目的有幾個。闡述了批處理在 sql server 2005 中的緩存和重用方式，并就最大限度重用已緩存的計劃提供了相應的最佳實務。另外，文中還說明了一些重新編譯批處理的方案，并針對減少或消除不必要的重新編譯，給出了最佳實務。本白皮書闡述了 sql server 2005 的“語句級重新編譯”功能。另外，還介紹了許多工具及實用程序，它們可在查詢編譯、查詢重新編譯、計劃緩存和計劃重用過程中充當很有用的觀測工具。我們在整篇文章中對比了 sql server 2000 和 sql server 2005 的不同表現，以便讀者能夠更好地了解文中的內容。本文檔所列舉的語句均適用于 sql server 2000 和 sql server 2005。同時，明確指出了這兩個 sql server 版本在行為上的差異。
本文面向三類讀者：
用戶：使用、維護并為 sql server 開發應用程序的人員。初識 sql server 2005 的用戶及正從 sql server 2000 進行遷移的人員將在這里找到有用的信息。
開發人員：sql server 開發人員將在這里找到有用的背景信息。
測試人員和項目經理：本文檔將對“sql server 2005 中的編譯和重新編譯”功能提供說明。
二、重新編譯：定義
在查詢、批處理、存儲過程、觸發器、被準備語句或動態 sql 語句（以下稱為“批處理”）在 sql server 上開始執行以前，批處理將被編譯成計劃。然后，將執行該計劃以便發揮其效力或生成結果。
|||一個批處理可包含一個或多個 select、insert、update 和 delete 語句、存儲過程調用（t-sql“粘連”或控制結構（比如：set、if、while、declare）可能使其產生交錯）、ddl 語句（比如：create、drop）以及與權限相關的語句（比如：grant、deny 和 revoke）。批處理還可包含 clr 構造的定義和用法（比如：用戶定義的類型、函數、過程和聚合）。
已編譯的計劃被保存到 sql server 的一部分內存中，這部分內存稱為計劃緩存。將搜索計劃緩存以獲得重用計劃的機會。如果對某個批處理重用計劃，就可避免編譯工作。請注意，在有關 sql server 的文獻中，過去所用的“過程緩存”一詞在本文中被稱為“計劃緩存”。“計劃緩存”用詞更準確，因為計劃緩存不僅僅保存存儲過程的查詢計劃。
在涉及 sql server 的用語中，上段所提到的編譯過程有時會被誤認為是“重新編譯”，但該過程僅涉及“編譯”。
重新編譯的定義：假設某個批處理被編譯成一個或多個查詢計劃的集合。在 sql server 開始執行任何單獨的查詢計劃之前，服務器將檢查該查詢計劃的有效性（正確性）和最優性。如果某個檢查失敗了，將重新編譯相應查詢計劃所對應的語句或整個批處理，并可能生成一個不同的查詢計劃。這種編譯稱為“重新編譯”。
請特別注意，不必預先緩存該批處理的查詢計劃。實際上，某些批處理類型從不被緩存，但仍能引發重新編譯。舉個例，有個批處理包含一個大于 8 kb 的文本。假設該批處理創建了一個臨時表，并在表中插入了 20 行。所插入的第七行將導致重新編譯，但由于其包含的文本較大，將不緩存該批處理。
在 sql server 中執行的多數重新編譯都是有根據的。有些是為了確保語句的正確性；另一些是為了在 sql server 數據庫中的數據發生變化時，獲得最佳的查詢執行計劃。然而，重新編譯有時會大大延緩批處理執行的速度。這時，就有必要減少進行重新編譯的次數。
|||三、比較 sql server 2000 和 sql server 2005 中的重新編譯
在 sql server 2000 中重新編譯批處理時，將重新編譯批處理中的所有語句，而不僅僅是觸發重新編譯的語句。sql server 2005 在該行為上做了改進，只編譯導致重新編譯的語句，而不是整個批處理。與 sql server 2000 相比，這一“語句級重新編譯”功能將改善 sql server 2005 的重新編譯行為。尤其，在批處理重新編譯過程中，sql server 2005 所用的 cpu 時間和內存更少，而得到的編譯塊也更少。
語句級重新編譯有一個優點顯而易見：不必再只是為了減少較長的存儲過程的重新編譯罰點，而將其分成多個較短的存儲過程。
四、計劃緩存
在處理重新編譯問題之前，本文將用較大的篇幅來討論查詢計劃緩存的相關及重要的主題。緩存計劃以便重用。如果未緩存查詢計劃，重用機會將為零。這種計劃將在每次執行時被編譯，從而導致性能低下。只有在極少數情況下，才不需要進行緩存。本文將在后面指出這些情況。
sql server 可緩存許多批處理類型的查詢計劃。以下列舉了這些類型。對于每種類型，我們都說明了重用計劃的必要條件。請注意，這些條件不一定充分。稍后，讀者將在本文中看到完整的相關內容。
1.
特殊查詢。特殊查詢是一種包含 select、insert、update 或 delete 語句的批處理。sql server 要求兩個特殊查詢的文本完全匹配。文本必須在大小寫和空格上都匹配。例如，下列這兩個查詢不共享相同的查詢計劃。（出現在本白皮書中的所有 t-sql 代碼段都在 sql server 2005 的 adventureworks 數據庫中。）
select productid
from sales.salesorderdetail
group by productid
having avg(orderqty) > 5
order by productid
select productid
from sales.salesorderdetail
group by productid
having avg(orderqty) > 5
order by productid |||2.
自動參數化查詢。對于某些查詢，sql server 2005 將常量文本值替換為變量，并編譯查詢計劃。如果后續的查詢僅在常量的值上有所不同，那么其將與自動參數化查詢相匹配。通常，sql server 2005 會對那些查詢計劃不取決于常量文本的特定值的查詢進行自動參數化處理。
附錄 a 包含一個語句類型列表，sql server 2005 將不對其上列出的語句類型進行自動參數化處理。
作為 sql server 2005 中自動參數化的一個示例，下列這兩個查詢可重用查詢計劃：
select productid, salesorderid, linenumber
from sales.salesorderdetail
where productid > 1000
order by productid
select productid, salesorderid, linenumber
from sales.salesorderdetail
where productid > 2000
order by productid上方查詢的自動參數化形式為：
select productid, salesorderid, linenumber
from sales.salesorderdetail
where productid > @p1
order by productid當出現在查詢中的常量文本的值會影響查詢計劃時，將不對該查詢進行自動參數化處理。這類查詢的查詢計劃將被緩存，但同時會插入常量，而不是占位符（比如：@p1）。
sql server 的“showplan”功能可用于確定是否已對查詢進行了自動參數化處理。例如，可在“set showplan_xml on”模式下提交查詢。如果結果 showplan 包含諸如 @p1 和 @p2 等占位符，那么將對查詢進行自動參數化處理；否則將不進行自動參數化處理。xml 格式的 sql server showplan 還包含在編譯時（‘showplan_xml’和‘statistics xml’模式）和執行時（僅‘statistics xml’模式）的參數值的相關信息。
|||，歡迎訪問網頁設計愛好者web開發。3.
sp_executesql 過程。這是促進查詢計劃重用的方法之一。當使用 sp_executesql 時，用戶或應用程序將明確地確定參數。例如：
exec sp_executesql n'select p.productid, p.name, p.productnumber
from production.product p
inner join production.productdescription pd
on p.productid = pd.productdescriptionid
where p.productid = @a', n'@a int', 170
exec sp_executesql n'select p.productid, p.name, p.productnumber
from production.product p inner join production.productdescription pd
on p.productid = pd.productdescriptionid
where p.productid = @a', n'@a int', 1201可通過逐一列舉來指定多個參數。實際的參數值遵照相應的參數定義。根據查詢文本（sp_executesql 后的第一個參數）的匹配情況以及查詢文本（上例中的 n'@a int'）后面所帶的所有參數來預測計劃重用機會。不考慮參數值（170 和 1201）的文本是否匹配。因此，在上述例子中，兩個 sp_executesql 語句會發生計劃重用。
4.
預備的查詢.該方法——類似于 sp_executesql 方法——還促進了查詢計劃重用。在“準備”時發送批處理文本。sql server 2005 通過返回一個句柄（可用于在執行時調用批處理）進行響應。在執行時，一個句柄和一些參數值會被發送到服務器。odbc 和 ole db 通過 sqlprepare/sqlexecute 和 icommandprepare 顯露該功能。例如，使用 odbc 的代碼段可能如下所示：
sqlprepare(hstmt, "select salesorderid, sum(linetotal) as subtotal
from sales.salesorderdetail sod
where salesorderid < ?
group by salesorderid
order by salesorderid", sql_nts)
sqlexecute(hstmt) |||國內最大的酷站演示中心!5.
存儲過程（含觸發器）。存儲過程設計用于促進計劃重用。計劃重用基于存儲過程或觸發器的名稱。（但是，無法直接調用觸發器。）sql server 在內部將存儲過程的名稱轉化為 id，而隨后的計劃重用將根據該 id 的值來進行。觸發器的計劃緩存和重新編譯行為與存儲過程略有不同。我們將在本文檔的適當位置指出這些不同之處。
當首次編譯一個存儲過程時，執行調用所提供的參數的值被用于優化該存儲過程中的語句。這個過程被稱為“參數嗅探”。如果這些值都是典型的，那么針對該存儲過程的所有調用將從一個高效的查詢計劃中獲益。本文隨后將討論可用于防止緩存帶有非典型的存儲過程參數值的查詢計劃。
6.
批處理.如果批處理文本完全匹配，那么將對相應的批處理進行查詢計劃重用。文本必須在大小寫和空格上都匹配。
7.
通過 exec ( ...) 執行查詢。sql server 2005 可緩存通過 exec 提交的字符串以便執行。這些字符串稱為“動態 sql”。例如：
exec ( 'select *' + ' from production.product pr
inner join production.productphoto ph' + '
on pr.productid = ph.productphotoid' +
' where pr.makeflag = ' + @mkflag )計劃重用基于在執行語句時將變量（比如：上例中的 @mkflag ）替換為其實際值后得到的連鎖字符串。
多級緩存
認識到多“級”緩存匹配將獨立進行，這一點很重要。舉個例子。假設批處理 1（非存儲過程）包含下列語句（及其他）：
exec dbo.proca批處理 2（也不是存儲過程）與批處理 1 在文本上不相匹配，但包含引用相同存儲過程的 “exec dbo.proca”。這里，批處理 1 和批處理 2 的查詢計劃不相匹配。然而，只要在這兩個批處理的一個中執行 “exec dbo.proca”，同時在執行當前批處理之前執行了另一個批處理，而 proca 的查詢計劃仍存在于計劃緩存中，就有可能實現 proca 的查詢計劃重用。但是，每次單獨執行 proca 都會得到執行上下文。該執行上下文要么剛剛生成（如果正在使用所有現有的執行上下文），要么被重用（如果未使用的執行上下文可用）。即使使用 exec 執行了動態 sql，或者在批處理 1 和批處理 2 內部執行了自動參數化語句，也有可能產生某種類型的重用。總之，下列三類批處理會啟動他們自己的“級別”（無論任何包含級別中是否存在緩存匹配，都會在這些級別中產生緩存匹配）：
|||商業源碼熱門下載www.html.org.cn
• 存儲過程執行（比如：“exec dbo.stored_proc_name”）
• 動態 sql 執行（比如：“exec query_string”）
• 自動參數化查詢
對于上述規則，存儲過程是一個例外。例如，如果兩個不同的存儲過程都包含“exec proca”語句，那么就不會產生 proca 的查詢計劃和執行上下文重用。
查詢計劃和執行上下文
當一個可緩存的批處理被提交給 sql server 2005 進行執行時，該批處理會被編譯，而它的一個查詢計劃會被放到計劃緩存中。查詢計劃是一種只讀的可重入結構（由多個用戶共享）。任何時候，查詢計劃在計劃緩存中最多只能有兩個實例：一個用于所有的串行執行，另一個用于所有的并行執行。并行執行的副本適用于所有的并行級別。（嚴格說來，如果相同的用戶使用帶有相同會話選項的兩個不同會話設置的兩個相同的查詢同時達到 sql server 2005，在執行時將存在兩個查詢計劃。但是，當執行結束時，僅有一個查詢計劃會保留在計劃緩存中。）
執行上下文是從查詢計劃中派生的。執行上下文是為生成查詢結果而“執行”的。執行上下文也被緩存和重用。當前執行批處理的每位用戶將擁有一個執行上下文，其中保存了特定于其執行的數據（比如：參數值）。雖然被重用，但是執行上下文并不是可重入的（例如，它們是單線程的）。也就是說，在任何時候，一個執行上下文只能執行一個由會話提交的批處理，而在執行時，相應的上下文不會提供給任何其他會話或用戶。
查詢計劃與從中派生的執行上下文之間的關系如下圖所示。其中，有一個查詢計劃，從中派生了三個執行上下文。這些執行上下文包含參數值和特定于用戶的信息。對于參數值和特定于用戶的信息而言，查詢計劃都不是明確的。
|||
在計劃緩存中，一個查詢計劃和多個相關聯的執行上下文可以共存。然而，單個執行上下文（如果沒有相關聯的查詢計劃）無法存在于計劃緩存中。只要從計劃緩存中刪除了查詢計劃，所有相關聯的執行上下文也將隨之被刪除。
當搜索計劃緩存以尋找計劃重用的機會時，將比較各個查詢計劃，而不是各個執行上下文。一旦找到了可重用的查詢計劃，就能找到（導致執行上下文重用）或新生成可用的執行上下文。所以，查詢計劃重用不一定會導致執行上下文重用。
執行上下文是在“匆忙中 (on the fly)”派生的，其間一個主干執行上下文會在批處理執行開始之前生成。隨著執行的進行，將生成必要的執行上下文片斷并放入該主干中。這意味著，即便從中刪除了特定于用戶的信息和查詢參數，兩個執行上下文也不必完全相同。由于派生自相同查詢計劃的執行上下文的結構可以彼此不同，因此用于特定執行的執行上下文對性能有輕微的影響。隨著計劃緩存變“熱”并達到穩定狀態，這種性能差異的影響會越來越小。
例如：假設批處理 b 包含一個“if”語句。當 b 開始執行時，就會為其生成一個執行上下文。假設在首次執行時，提取了“if”的“true”分支。此外，假設在首次執行時，b 再次由另一個連接提交。因為當時唯一存在的執行上下文正被使用，所以將生成第二個執行上下文，并提供給第二個連接。假設第二個執行上下文提取了“if”的“false”分支。當這兩個執行都完成之后，將有第三個連接提交 b。假設 b 的第三個執行選擇了“true”分支，如果 sql server 為該連接選擇了 b 的第一個執行上下文而非第二個執行上下文，那么完成該執行的速度將稍快一些。
可重用批處理 s 的執行上下文，即使 s 的調用順序有所不同。例如，調用順序可以是“存儲過程 1 --> 存儲過程 2 --> s”，而第二個調用順序可以是“存儲過程 3 --> s”。可對 s 的第二次執行重用其第一次執行的執行上下文。
如果批處理執行生成了嚴重級別高達 11 或更高的錯誤，那么其執行上下文會被破壞。如果批處理執行生成了一個警告（嚴重級別為 10），那么執行上下文就不會被破壞。因此，即便沒有內存方面的壓力——會導致計劃緩存縮小，計劃緩存中所緩存的（給定查詢計劃的）執行上下文的數量也會起伏不定。
不緩存并行計劃的執行上下文。sql server 編譯并行查詢計劃的一個必備條件是：滿足了處理器關聯掩碼和“最高程度的并行”服務器級選項的值（可能是用“sp_configure”存儲過程設置）后所剩下的處理器的最低數量大于 1。即使編譯了并行查詢計劃，sql server 的“查詢執行”組件也可能會從中生成一個串行執行上下文。不緩存派生自并行計劃的任何執行上下文——串行或并行。但是，會緩存并行查詢計劃。
新聞熱點
疑難解答
