兩種特殊情況

　　與計(jì)劃最優(yōu)性相關(guān)的重新編譯在下列兩種特殊情況中的處理方式有所不同。

　　sql server 2005 處理下列情況的方式不同于 sql server 2000。用戶創(chuàng)建了一個(gè)空表 t。然后又在 t 一個(gè)或多個(gè)列上創(chuàng)建了一個(gè)統(tǒng)計(jì) s。由于 t 為空，因此統(tǒng)計(jì)二進(jìn)制大對(duì)象（直方圖）為 null，但已經(jīng)在 t 上創(chuàng)建了統(tǒng)計(jì)。假設(shè)在查詢編譯期間已發(fā)現(xiàn) s 是“令人關(guān)注的”。根據(jù)重新編譯閾值的“500 行”規(guī)則，只有至少包含 500 行，t 才會(huì)在 sql server 2000 上導(dǎo)致重新編譯。所以，如果 t 包含的行不足 500，用戶可能使用欠優(yōu)化的計(jì)劃。

　　sql server 2005 可檢測(cè)到這種特殊情況，并以不同的方式進(jìn)行處理。在 sql server 2005 中，這種表或索引視圖的重新編譯閾值為 1。換句話說(shuō)，即使僅在 t 中插入一行，也可能導(dǎo)致重新編譯。發(fā)生這種重新編譯時(shí)，s 將被更新，同時(shí) s 的直方圖不再為 null。然而，這一重新編譯附帶了重新編譯閾值 (500 + 0.20 * n)的一般規(guī)則。

　　在 sql server 2005 中，即使發(fā)生下列情況，重新編譯閾值始終為 1：(1) t 沒(méi)有統(tǒng)計(jì)；或者 (2) t 沒(méi)有在查詢編譯期間被認(rèn)作是“令人關(guān)注的”統(tǒng)計(jì)。

　　導(dǎo)致重新編譯的與計(jì)劃最優(yōu)性相關(guān)的所有原因都適用于觸發(fā)器。另外，由于已插入或已刪除的表中的行數(shù)在不同的觸發(fā)器執(zhí)行間發(fā)生巨大變化，也會(huì)對(duì)觸發(fā)器產(chǎn)生與計(jì)劃最優(yōu)性相關(guān)的重新編譯。

　　回想一下，影響一行或多行的觸發(fā)器會(huì)被單獨(dú)緩存。已插入和已刪除的表中的行數(shù)通過(guò)觸發(fā)器的查詢計(jì)劃進(jìn)行保存。這些數(shù)字反映了導(dǎo)致計(jì)劃緩存的觸發(fā)器執(zhí)行的行數(shù)。如果后續(xù)的觸發(fā)器執(zhí)行產(chǎn)生了擁有“截然不同的”行數(shù)的已插入或已刪除的表，那么將對(duì)該觸發(fā)器進(jìn)行重新編譯（并緩存帶有新行數(shù)的全新的查詢計(jì)劃）。

　　在 sql server 2005 中，“截然不同”的定義如下：

　　其中 n 是已緩存查詢計(jì)劃中的已插入或已刪除表的行數(shù)，而 m 是當(dāng)前的觸發(fā)器執(zhí)行的對(duì)應(yīng)表的行數(shù)。如果同時(shí)存在“已插入”和“已刪除”的表，將對(duì)兩者分別執(zhí)行上面提到的測(cè)試。

　　舉一個(gè)計(jì)算示例，從 10 到 100 的行數(shù)更改不會(huì)導(dǎo)致重新編譯，而從 10 到 101 的更改則完全相反。

　　在 sql server 2000 中，“截然不同”的定義如下：

　　其中 n 和 m 的定義同上。請(qǐng)注意，根據(jù)這個(gè)公式，在 sql server 2000 中將已插入或已刪除的表的基數(shù)從 5 改為 95，將導(dǎo)致重新編譯，而從 5 到 94 的更改則不然。

　　可通過(guò)包含字符串“statistics changed”的事件探查器跟蹤（將在本文后面介紹）的“eventsubclass”列來(lái)識(shí)別與統(tǒng)計(jì)相關(guān)的重新編譯。

　　與本文檔的主題沒(méi)有直接相關(guān)的一個(gè)問(wèn)題是：給定的多個(gè)統(tǒng)計(jì)以相同的順序存在于一組相同的列中，那么在查詢優(yōu)化期間，查詢優(yōu)化器如何決定所要載入的統(tǒng)計(jì)呢？答案并不那么簡(jiǎn)單，但查詢優(yōu)化器采用如下原則：為最近的統(tǒng)計(jì)提供比較舊的統(tǒng)計(jì)更高的優(yōu)先權(quán)；為使用 fullscan 選項(xiàng)計(jì)算得出的統(tǒng)計(jì)提供比用樣例計(jì)算得出的統(tǒng)計(jì)更高的優(yōu)先權(quán)；等等。

　　與計(jì)劃最優(yōu)性相關(guān)的編譯、重新編譯和統(tǒng)計(jì)創(chuàng)建/更新間的“因果”關(guān)系可能會(huì)造成混淆。回想一下，統(tǒng)計(jì)可通過(guò)手動(dòng)或自動(dòng)方式創(chuàng)建或更新。只有編譯和重新編譯才會(huì)導(dǎo)致統(tǒng)計(jì)的自動(dòng)創(chuàng)建或更新。另一方面，當(dāng)（手動(dòng)或自動(dòng)）創(chuàng)建或更新一個(gè)統(tǒng)計(jì)時(shí)，重新編譯查詢計(jì)劃（可能會(huì)發(fā)現(xiàn)該統(tǒng)計(jì)“令人關(guān)注”）的概率將增大。

　　最佳實(shí)務(wù)

　　下面給出了四個(gè)用于減少與計(jì)劃最優(yōu)性相關(guān)的批處理重新編譯的最佳實(shí)務(wù)：

　　最佳實(shí)務(wù)：因?yàn)楸碜兞康幕鶖?shù)發(fā)生變化不會(huì)導(dǎo)致重新編譯，所以可考慮使用表變量來(lái)替代臨時(shí)表。然而，由于查詢優(yōu)化器不跟蹤表變量的基數(shù)，同時(shí)不在表變量上創(chuàng)建或維護(hù)統(tǒng)計(jì)，因此不可能產(chǎn)生最佳的查詢計(jì)劃。用戶必須確認(rèn)情況是否如此，并適當(dāng)?shù)丶右詸?quán)衡。

　　最佳實(shí)務(wù)：keep plan 查詢提示可改變臨時(shí)表的重新編譯閾值，使之與永久表的重新編譯閾值相同。所以，如果對(duì)臨時(shí)表的更改會(huì)導(dǎo)致大量的重新編譯，就可使用此查詢提示。可使用下列語(yǔ)法指定該提示：

　　最佳實(shí)務(wù)：為了完全避免因與計(jì)劃最優(yōu)性相關(guān)的（與統(tǒng)計(jì)更新相關(guān)的）原因而導(dǎo)致的重新編譯，可使用下列語(yǔ)法指定 keepfixed plan 查詢提示：

　　運(yùn)用這一選項(xiàng)，只有與正確性相關(guān)的原因（例如，語(yǔ)句更改所引用的表的架構(gòu)，或用 sp_recompile 過(guò)程標(biāo)記的表）才會(huì)導(dǎo)致重新編譯。

　　在 sql server 2005 中，下方所述的行為方式略有不同。假設(shè)帶有 option(keepfixed plan) 提示的查詢首次被編譯，而這一編譯會(huì)導(dǎo)致統(tǒng)計(jì)的自動(dòng)創(chuàng)建。如果 sql server 2005 可獲得一個(gè)特殊的“統(tǒng)計(jì)鎖”，那么就會(huì)發(fā)生重新編譯并自動(dòng)創(chuàng)建統(tǒng)計(jì)。如果無(wú)法獲得“統(tǒng)計(jì)鎖”，就會(huì)不產(chǎn)生重新編譯，并在沒(méi)有該統(tǒng)計(jì)的情況下編譯查詢。在 sql server 2000 中，出于與統(tǒng)計(jì)相關(guān)的原因，帶有 option(keepfixed plan) 的查詢從不會(huì)被重新編譯，所以在這種情況下，不會(huì)嘗試獲取“統(tǒng)計(jì)鎖”或自動(dòng)創(chuàng)建統(tǒng)計(jì)。

　　最佳實(shí)務(wù)：對(duì)表或索引視圖上定義的索引和統(tǒng)計(jì)關(guān)閉統(tǒng)計(jì)自動(dòng)更新，將確保因這些對(duì)象所導(dǎo)致的與計(jì)劃最優(yōu)性相關(guān)的重新編譯將停止。但是請(qǐng)注意，用這種方法關(guān)閉“自動(dòng)統(tǒng)計(jì)”功能通常并不是一個(gè)好辦法，因?yàn)椴樵儍?yōu)化器不再響應(yīng)這些對(duì)象中的數(shù)據(jù)變更，并可能導(dǎo)致次最佳查詢計(jì)劃。不到萬(wàn)不得已不要采用這種方法。

　　在 sql server 2000 中，存儲(chǔ)過(guò)程、觸發(fā)器和動(dòng)態(tài) sql 的編譯和重新編譯均被串行化。例如，假定使用“exec dbo.sp1”提交了一個(gè)存儲(chǔ)過(guò)程用以執(zhí)行。并假設(shè)當(dāng) sql server 編譯 sp1 時(shí)，收到了另一個(gè)引用相關(guān)存儲(chǔ)過(guò)程的請(qǐng)求“exec dbo.sp1”。第二個(gè)請(qǐng)求將等到第一個(gè)請(qǐng)求完成 sp1 的編譯，然后嘗試重用結(jié)果查詢計(jì)劃。在 sql server 2005 中，編譯被串行化，而重新編譯則不會(huì)。換句話說(shuō)，相同存儲(chǔ)過(guò)程的兩個(gè)并發(fā)重新編譯可能會(huì)繼續(xù)。最后結(jié)束的重新編譯請(qǐng)求將替代由另一個(gè)請(qǐng)求生成的查詢計(jì)劃。

　　“參數(shù)嗅探”是一個(gè)過(guò)程，通過(guò)這一過(guò)程，sql server 的執(zhí)行環(huán)境可在編譯或重新編譯時(shí)“嗅探”當(dāng)前參數(shù)值，并將之傳遞給查詢優(yōu)化器，以用于生成更快的查詢執(zhí)行計(jì)劃。“當(dāng)前”一詞指導(dǎo)致編譯或重新編譯的語(yǔ)句調(diào)用中所存在的參數(shù)值。在 sql server 2000 和 sql server 2005 中，將在編譯或重新編譯下列批處理類(lèi)型時(shí)嗅探參數(shù)值：

　　在 sql server 2005 中，這一操作被擴(kuò)展到使用 option(recompile) 查詢提示提交的查詢上。對(duì)于這種查詢（可以是 select、insert、update 或 delete），將同時(shí)嗅探本地變量的參數(shù)值和當(dāng)前值。在批處理中，所嗅探到的（參數(shù)和本地變量的）值后面緊跟著帶有 option(recompile) 提示的語(yǔ)句。尤其對(duì)于參數(shù)來(lái)說(shuō)，不會(huì)嗅探批處理調(diào)用所附帶的值。

　　十、識(shí)別重新編譯

　　sql server 的事件探查器使得識(shí)別導(dǎo)致重新編譯的批處理變得很簡(jiǎn)單。啟動(dòng)一個(gè)新的事件探查器跟蹤，并在存儲(chǔ)過(guò)程事件類(lèi)別下，選擇下列事件。（為了減少所生成的數(shù)據(jù)量，建議用戶取消選定其他事件。）

　　此外，為了檢測(cè)與統(tǒng)計(jì)更新相關(guān)的重新編譯，可選擇“對(duì)象”類(lèi)別下的“自動(dòng)統(tǒng)計(jì)”事件。

　　現(xiàn)在，啟動(dòng) sql server 2005 management studio，并執(zhí)行下列 t-sql 代碼：

　　暫停事件探查器跟蹤，并將看到下列事件。

　　該事件序列指示“select * from #t1”為導(dǎo)致重新編譯的語(yǔ)句。eventsubclass 列指出了進(jìn)行重新編譯的原因。在這種情況下，當(dāng) demoproc1 在開(kāi)始執(zhí)行之前被編譯，就可對(duì)“create table”語(yǔ)句進(jìn)行變異。后續(xù)的“select”語(yǔ)句可能不會(huì)被編譯，因?yàn)槠湟昧艘粋€(gè)在初始編譯時(shí)不存在的臨時(shí)表 #t1。因此，demoproc1 的已編譯計(jì)劃是不完整的。當(dāng) demoproc1 開(kāi)始執(zhí)行時(shí)，隨即創(chuàng)建了 #t1，然后就可以對(duì)“select”語(yǔ)句進(jìn)行編譯。由于 demoproc1 已經(jīng)執(zhí)行，因此根據(jù)我們對(duì)重新編譯的定義，這一編譯可視為重新編譯。此重新編譯的真正原因是“延遲編譯”。

　　請(qǐng)注意，有趣的一點(diǎn)是：當(dāng)再次執(zhí)行 demoproc1 時(shí)，查詢計(jì)劃將不再是不完整的。重新編譯已經(jīng)將 demoproc1 的一個(gè)完整的查詢計(jì)劃插入計(jì)劃緩存中。所以，第二次執(zhí)行過(guò)程中未發(fā)生任何重新編譯。

　　sql server 2000 在這方面的情況也相同。

　　通過(guò)選擇下列這組跟蹤事件，也可以識(shí)別導(dǎo)致重新編譯的批處理。

　　如果在選擇了這組新的跟蹤事件后執(zhí)行了剛才所述的例子，那么跟蹤輸出將如下所示。

　　在此請(qǐng)注意，導(dǎo)致重新編譯的語(yǔ)句將在 sp:recompile 事件后被輸出。這種方法不如第一種方法直接了當(dāng)。因此，之后應(yīng)跟蹤第一組事件探查器跟蹤事件。

　　為了看到所有針對(duì) sp:recompile 事件報(bào)告的可能導(dǎo)致重新編譯的原因，請(qǐng)?jiān)?sql server 2005 上發(fā)出下列查詢：

　　上述查詢的輸出如下。（僅輸出不帶有陰影的列；帶有陰影的列用于提供其他詳細(xì)信息。）

　　在 sql server 2000 中，eventsubclass 列包含從 1 到 6 的整數(shù)值，意義與上表所列的內(nèi)容相同。sql server 2005 新增了最后兩個(gè)類(lèi)別。

　　對(duì)本節(jié)所述的兩個(gè)例子，sql server 2000 上的跟蹤輸出與 sql server 2005 相同，除了在 sql server 2000 上，eventsubclass 列包含“3”而非字符串“deferred compile”。從內(nèi)部來(lái)說(shuō)，語(yǔ)句級(jí)重新編譯發(fā)生在 sql server 2005 上，因此，僅有“select * from #t1”在 sql server 2005 上進(jìn)行重新編譯，而在 sql server 2000 上，整個(gè) demoproc1 都將被重新編譯。

　　因混用 ddl 和 dml 而導(dǎo)致重新編譯

　　在批處理或存儲(chǔ)過(guò)程中混用數(shù)據(jù)定義語(yǔ)言 (ddl) 和數(shù)據(jù)操作語(yǔ)言 (dml) 語(yǔ)句并不是一個(gè)好辦法，因?yàn)檫@會(huì)引起不必要的重新編譯。下面這個(gè)例子運(yùn)用存儲(chǔ)過(guò)程闡述了這一點(diǎn)。（批處理也會(huì)發(fā)生同樣的情況。但是，由于 sql server 2005 事件探查器沒(méi)有提供必要的跟蹤事件，因此無(wú)法對(duì)其進(jìn)行實(shí)時(shí)觀測(cè)。）創(chuàng)建下列存儲(chǔ)過(guò)程。

　　在事件探查器跟蹤輸出中，可觀測(cè)到下列事件。

　　這里說(shuō)明了 mixddldml 是如何編譯的。

　　總之，在這個(gè)例子中，sql server 2000 和 sql server 2005 中都發(fā)生了三次重新編譯。但是，sql server 2005 的重新編譯成本要低于 sql server 2000，因?yàn)榍罢邔儆谡Z(yǔ)句級(jí)而非存儲(chǔ)過(guò)程級(jí)重新編譯。

　　如果根據(jù)下方所示來(lái)編寫(xiě)存儲(chǔ)過(guò)程，那么將觀察到有趣的現(xiàn)象。

　　在事件探查器跟蹤輸出中，可觀察到下列事件。

　　在 sql server 2005 中，出于語(yǔ)句級(jí)重新編譯，仍會(huì)發(fā)生這三次重新編譯。與 mixddldml 存儲(chǔ)過(guò)程相比，重新編譯的次數(shù)并沒(méi)有減少。如果在 sql server 2000 上嘗試相同的例子，重新編譯的次數(shù)將從 3 次減少到 1 次。在 sql server 2000 中，重新編譯在存儲(chǔ)過(guò)程級(jí)上進(jìn)行，因而可以一次性編譯三個(gè)“select”語(yǔ)句。總之，與 sql server 2000 相比，sql server 2005 的重新編譯工作量沒(méi)有增加，但是重新編譯的次數(shù)卻增多了。