SqlServer編譯、重編譯與執(zhí)行計(jì)劃重用原理

當(dāng) Sql Server 收到任何一個(gè)指令，包括：查詢、批處理、存儲(chǔ)過(guò)程、觸發(fā)器、預(yù)編譯指令和動(dòng)態(tài)SQL Server語(yǔ)句，要完成語(yǔ)法解析、語(yǔ)義分析，然后再進(jìn)行"編譯"，生成能夠運(yùn)行的"執(zhí)行計(jì)劃"。在編譯的過(guò)程中，SQL Server 會(huì)根據(jù)所涉及的對(duì)象的架構(gòu)、統(tǒng)計(jì)信息，以及指令的具體內(nèi)容，估算可能的執(zhí)行計(jì)劃，以及它們的成本，最后選擇一個(gè)SQL Server認(rèn)為成本最低的語(yǔ)句。

執(zhí)行計(jì)劃生成之后，SQL Server 通常會(huì)把它們緩存到內(nèi)存里，術(shù)語(yǔ)統(tǒng)稱它們叫“Plane Cache”。以后同樣的語(yǔ)句執(zhí)行，SQL Server就可以使用同樣的執(zhí)行計(jì)劃，而無(wú)須再做一次編譯。這種行為，叫做“重用”。但是有時(shí)候，哪怕是一模一樣的語(yǔ)句，SQL Server 下次執(zhí)行還是要再做一次編譯。這種行為叫“重編譯”。執(zhí)行計(jì)劃的編譯和重編譯都是要耗費(fèi)資源的。

執(zhí)行計(jì)劃的好壞當(dāng)然決定了語(yǔ)句的執(zhí)行速度。對(duì)于同樣一條語(yǔ)句，使用好的執(zhí)行計(jì)劃可能會(huì)比差的要快幾百倍，甚至幾千倍。所以從這一角度上來(lái)講，沒(méi)運(yùn)行一條語(yǔ)句，都把它先編譯一遍當(dāng)然是最好的。它能夠保證使用的執(zhí)行計(jì)劃是 SQL Server 能找到的最優(yōu)的。但是 SQL Server 每秒鐘可能會(huì)運(yùn)行成百上千的指令。如果每個(gè)都編譯一遍，是資源的嚴(yán)重浪費(fèi)。所以 SQL Server 在這里也試圖尋找一個(gè)平衡點(diǎn)，使用優(yōu)先的 complie/recomplie，得到最好的 整體性能。

查看 SQL Server 緩存的執(zhí)行計(jì)劃，可以運(yùn)行下面這條語(yǔ)句：Select＊From Sys.syscacheobjects

對(duì)不同的指令調(diào)用方法， SQL Server 做執(zhí)行計(jì)劃緩存和重用機(jī)制也有所不同。下面介紹最常見(jiàn)的幾種：

1. Adhoc 語(yǔ)句

一組包含 Select、Insert、Update、Delete 的批處理指令。對(duì)這樣的指令，只有前后完全一直，包括字母的大小寫(xiě)、空格、回車換行都一致， SQL Server 才認(rèn)為是兩條一樣的語(yǔ)句，才能夠重用執(zhí)行計(jì)劃。所以這個(gè)要求還是挺高的。

2. 用 Exec() 的方式運(yùn)行動(dòng)態(tài) SQL Server 語(yǔ)句

有些應(yīng)用程序?yàn)榱碎_(kāi)發(fā)上的靈活程度，在程序運(yùn)行過(guò)程中，動(dòng)態(tài)地拼接成一個(gè)語(yǔ)句字符串，然后用 Exec() 的方式執(zhí)行。這種調(diào)用方法被稱為“dynamic SQL”。它的好處就是很靈活，可以根據(jù)客戶的選擇，動(dòng)態(tài)生成指令，而不僅限于預(yù)定義的那幾種。但是它的缺點(diǎn)也是太靈活了，客戶發(fā)過(guò)來(lái)的語(yǔ)句每次都不一樣，或者語(yǔ)句主體部分是一樣的，但是參數(shù)不一樣， SQL Server 都要做編譯。這點(diǎn)和 adhoc 語(yǔ)句是一樣的。

3. 自動(dòng)參數(shù)化查詢

對(duì)于一些比較簡(jiǎn)單的查詢， SQL Server 2005 自己就可以做自動(dòng)參數(shù)化，把語(yǔ)句里的參數(shù)用一個(gè)變量代替，但是這僅限于很簡(jiǎn)單的查詢。

4. 用 sp_executesql 的方式調(diào)用的指令

查詢自動(dòng)參數(shù)化在很多種條件下是不支持的，而且它還是要為每句查詢生成一個(gè) adhoc 的執(zhí)行計(jì)劃。所以它并不是減少比哪一的最有手段。改用 sp_executesql 能夠更有效地增加執(zhí)行計(jì)劃重用。

5. 存儲(chǔ)過(guò)程

對(duì)用戶經(jīng)常要調(diào)用的指令，把他們做成存儲(chǔ)過(guò)程，既方便管理、規(guī)范腳本，又能夠大大提高執(zhí)行計(jì)劃調(diào)用，是值得推薦的一種做法。從 SQL Server 的角度，最好絕大多數(shù)指令都能夠以存儲(chǔ)過(guò)程的方式調(diào)用，盡量少使用 Dynamic SQL 的方式。

但是有些時(shí)候， SQL Server 為了確保返回正確的值，或者有性能上的顧慮，有意不重用緩存在內(nèi)存里的執(zhí)行計(jì)劃，而現(xiàn)場(chǎng)編譯一份。這種行為，被成為重編譯。下面是比較常見(jiàn)的會(huì)發(fā)生重編譯的情形：

1. 當(dāng)指令或批處理所涉及的任何一個(gè)對(duì)象（表格或者試圖）發(fā)生了架構(gòu)（schema）變化

例如，在表或者視圖上添加或刪除另一個(gè)字段、添加或刪除了一個(gè)索引，在表上添加或者刪除了一個(gè)約束條件等。定義發(fā)生了變化，原來(lái)的執(zhí)行計(jì)劃就不一定正確了，當(dāng)然要重編譯。

2. 運(yùn)行過(guò) sp_recomplie 后

當(dāng)用戶在某個(gè)存儲(chǔ)過(guò)程或者觸發(fā)器上運(yùn)行過(guò) sp_recomplie 后，下一次運(yùn)行它們就會(huì)發(fā)生一次重編譯。如果用戶在某個(gè)表或試圖上運(yùn)行了 sp_recomplie ,那么所有引用到這張表或視圖的存儲(chǔ)過(guò)程在下一次運(yùn)行前，都要做重編譯

3. 有些動(dòng)作會(huì)清除內(nèi)存里的所有執(zhí)行計(jì)劃，迫使大家都要做重編譯

• Detach 一個(gè)數(shù)據(jù)庫(kù)
• 對(duì)數(shù)據(jù)庫(kù)做了一個(gè)升級(jí)，在新的服務(wù)器上會(huì)發(fā)生執(zhí)行計(jì)劃清空
• 運(yùn)行了 DBCC FreeProccache 語(yǔ)句
• 運(yùn)行了 ReConfigure 語(yǔ)句
• 運(yùn)行了 Alter DataBase ... Modify FileGroup 語(yǔ)句
• 用 Alter DataBase ... Collate 語(yǔ)句修改了某個(gè)數(shù)據(jù)庫(kù)的字符集

下列動(dòng)作會(huì)清楚 SQL Server 服務(wù)器緩存的某個(gè)數(shù)據(jù)庫(kù)的執(zhí)行計(jì)劃：

• DBCC FlushProcinDB 語(yǔ)句
• Alter DataBase ... Modify Name 語(yǔ)句
• Alter DataBase ... Set Online 語(yǔ)句
• Alter DataBase ... Set Offline 語(yǔ)句
• Alter DataBase ... Set EmerGency 語(yǔ)句
• Drop DataBase 語(yǔ)句
• 當(dāng)一個(gè)數(shù)據(jù)庫(kù)自動(dòng)關(guān)閉時(shí)
• DBCC CheckDB 語(yǔ)句結(jié)束時(shí)

須說(shuō)明的是，在 Sql Server 里，執(zhí)行計(jì)劃重用并不一定是一件好事，而編譯重編譯也不一定是一件壞事。在 Sql Server 里，能對(duì)計(jì)劃重用和編譯/重編譯產(chǎn)生影響的功能主要有：

1. 使用存儲(chǔ)過(guò)程，或者 sp_executesql 的方式調(diào)用會(huì)被重復(fù)使用的語(yǔ)句，而不要直接用 ad-hoc 語(yǔ)句或者 dynamic SQL 。

2. 在語(yǔ)句里引用對(duì)象（表、視圖、存儲(chǔ)過(guò)程等），到帶上它的 schema 名字，而不光是對(duì)象自己的名字。

3. 將 數(shù)據(jù)庫(kù) Parameterization 屬性設(shè)置成 Forced

這個(gè)屬性是開(kāi)啟數(shù)據(jù)庫(kù)強(qiáng)制參數(shù)化。也就是說(shuō)，對(duì)于在這個(gè)數(shù)據(jù)庫(kù)下運(yùn)行的大部分語(yǔ)句，SQL Server 都會(huì)先參數(shù)化，再運(yùn)行。如果應(yīng)用經(jīng)常用 adhoc 的方式調(diào)用一樣的語(yǔ)句，強(qiáng)制參數(shù)化可能會(huì)有所幫助

4. 統(tǒng)計(jì)信息更新

統(tǒng)計(jì)信息手工或者自動(dòng)更新后，對(duì)和它有關(guān)的執(zhí)行計(jì)劃都不再能重用，而會(huì)產(chǎn)生重編譯。

5. Create Procedure ... with Recompile 選項(xiàng) 和 Exce ... with Recomplie 選項(xiàng)

在重建或者調(diào)用存儲(chǔ)過(guò)程的時(shí)候使用 "with Recomplie"，會(huì)強(qiáng)制 Sql Server 在調(diào)用這個(gè)存儲(chǔ)過(guò)程的時(shí)候，永遠(yuǎn)都要先編譯，再運(yùn)行。

6. 用戶使用了 sp_recomplie

7. 用戶在調(diào)用語(yǔ)句的時(shí)候，使用了 "Keep Plan" 或者 "KeepFixed Plan" 這樣的查詢提示

生活不易，碼農(nóng)辛苦
如果您覺(jué)得本網(wǎng)站對(duì)您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈(zèng)