SqlServer編譯、重編譯與執行計劃重用原理

當 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 語句結束時

須說明的是，在 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" 這樣的查詢提示

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈