Sql Server 編譯、重編譯與執行計畫重用原理

當 Sql Server 收到任何一個指令，包括：查詢、批處理、預存程序、觸發器、先行編譯指令和動態SQL Server語句，要完成文法解析、語義分析，然後再進行"編譯"，產生能夠啟動並執行"執行計畫"。在編譯的過程中，SQL Server 會根據所涉及的對象的架構、統計資訊，以及指令的具體內容，估算可能的執行計畫，以及它們的成本，最後選擇一個SQL Server認為成本最低的語句。

執行計畫產生之後，SQL Server 通常會把它們緩衝到記憶體裡，術語統稱它們叫“Plane Cache”。以後同樣的語句執行，SQL Server就可以使用同樣的執行計畫，而無須再做一次編譯。這種行為，叫做“重用”。但是有時候，哪怕是一模一樣的語句，SQL Server 下次執行還是要再做一次編譯。這種行為叫“重編譯”。執行計畫的編譯和重編譯都是要耗費資源的。

執行計畫的好壞當然決定了語句的執行速度。對於同樣一條語句，使用好的執行計畫可能會比差的要快幾百倍，甚至幾千倍。所以從這一角度上來講，沒運行一條語句，都把它先編譯一遍當然是最好的。它能夠保證使用的執行計畫是 SQL Server 能找到的最優的。但是 SQL Server 每秒鐘可能會運行成百上千的指令。如果每個都編譯一遍，是資源的嚴重浪費。所以 SQL Server 在這裡也試圖尋找一個平衡點，使用優先的 complie/recomplie，得到最好的 整體效能。

查看 SQL Server 緩衝的執行計畫，可以運行下面這條語句：Select＊From Sys.syscacheobjects

對不同的指令調用方法， SQL Server 做執行計畫緩衝和重用機制也有所不同。下面介紹最常見的幾種：

1. Adhoc 語句

一組包含 Select、Insert、Update、Delete 的批處理指示。對這樣的指令，只有前後完全一直，包括字母的大小寫、空格、斷行符號換行都一致， SQL Server 才認為是兩條一樣的語句，才能夠重用執行計畫。所以這個要求還是挺高的。

2. 用 Exec() 的方式運行動態  SQL Server 語句

有些應用程式為了開發上的靈活程度，在程式運行過程中，動態地拼接成一個語句字串，然後用 Exec() 的方式執行。這種調用方法被稱為“dynamic SQL”。它的好處就是很靈活，可以根據客戶的選擇，動態產生指令，而不僅限於預定義的那幾種。但是它的缺點也是太靈活了，客戶發過來的語句每次都不一樣，或者語句主體部分是一樣的，但是參數不一樣， SQL Server 都要做編譯。這點和 adhoc 語句是一樣的。

3. 自動參數化查詢

對於一些比較簡單的查詢， SQL Server 2005 自己就可以做自動參數化，把語句裡的參數用一個變數代替，但是這僅限於很簡單的查詢。

4. 用 sp_executesql 的方式調用的指令

查詢自動參數化在很多種條件下是不支援的，而且它還是要為每句查詢產生一個 adhoc 的執行計畫。所以它並不是減少比哪一的最有手段。改用 sp_executesql 能夠更有效地增加執行計畫重用。

5. 預存程序

對使用者經常要調用的指令，把他們做成預存程序，既方便管理、規範指令碼，又能夠大大提高執行計畫調用，是值得推薦的一種做法。從 SQL Server 的角度，最好絕大多數指令都能夠以預存程序的方式調用，盡量少使用 Dynamic SQL 的方式。

但是有些時候， SQL Server 為了確保返回正確的值，或者有效能上的顧慮，有意不重用緩衝在記憶體裡的執行計畫，而現場編譯一份。這種行為，被成為重編譯。下面是比較常見的會發生重編譯的情形：

1. 當指令或批處理所涉及的任何一個對象（表格或者試圖）發生了架構（schema）變化

例如，在表或者視圖上添加或刪除另一個欄位、添加或刪除了一個索引，在表上添加或者刪除了一個約束條件等。定義發生了變化，原來的執行計畫就不一定正確了，當然要重編譯。

2. 運行過 sp_recomplie 後

當使用者在某個預存程序或者觸發器上運行過 sp_recomplie 後，下一次運行它們就會發生一次重編譯。如果使用者在某個表或試圖上運行了 sp_recomplie  ,那麼所有引用到這張表或視圖的預存程序在下一次運行前，都要做重編譯

3. 有些動作會清除記憶體裡的所有執行計畫，迫使大家都要做重編譯

• Detach 一個資料庫
• 對資料庫做了一個升級，在新的伺服器上會發生執行計畫清空
• 運行了 DBCC FreeProccache 語句
• 運行了 ReConfigure 語句
• 運行了 Alter DataBase ... Modify FileGroup 語句
• 用 Alter DataBase ... Collate 語句修改了某個資料庫的字元集

下列動作會清楚  SQL Server 服務器緩衝的某個資料庫的執行計畫：

• DBCC FlushProcinDB 語句
• Alter DataBase ... Modify Name 語句
• Alter  DataBase ... Set Online 語句
• Alter  DataBase ... Set Offline 語句
• Alter  DataBase ... Set EmerGency 語句
• Drop  DataBase 語句
• 當一個資料庫自動關閉時
• DBCC CheckDB 語句結束時

4. 當一些 Set 開關值變化後，先前的那些執行計畫都不能重用

5. 當表格或試圖上的統計資訊發生變化後

當統計資訊被手動跟新後，或者 SQL Server 發現某個統計資訊需要自動更新時， SQL Server 會對所涉及的語句都做重編譯。

  須說明的是，在 Sql  Server 裡，執行計畫重用並不一定是一件好事，而編譯重編譯也不一定是一件壞事。在 Sql Server 裡，能對計劃重用和編譯/重編譯產生影響的功能主要有：  1. 使用預存程序，或者 sp_executesql 的方式調用會被重複使用的語句，而不要直接用 ad-hoc 語句或者 dynamic SQL  。
2. 在語句裡引用對象（表、視圖、預存程序等），到帶上它的 schema 名字，而不光是對象自己的名字。

3. 將 資料庫 Parameterization 屬性設定成 Forced 這個屬性是開啟資料庫強制參數化。也就是說，對於在這個資料庫下啟動並執行大部分語句，SQL Server 都會先參數化，再運行。如果應用經常用 adhoc 的方式調用一樣的語句，強制參數化可能會有所協助 4. 統計資訊更新 統計資訊手工或者自動更新後，對和它有關的執行計畫都不再能重用，而會產生重編譯。

5. Create Procedure ...   with Recompile 選項 和 Exce ... with Recomplie 選項 在重建或者調用預存程序的時候使用 "with Recomplie"，會強制  Sql Server 在調用這個預存程序的時候，永遠都要先編譯，再運行。 6. 使用者使用了 sp_recomplie 
7. 使用者在調用語句的時候，使用了 "Keep Plan" 或者 "KeepFixed Plan" 這樣的查詢提示