SQLSERVER編譯與重編譯發生情境及重用的利弊介紹

編譯的含義
--------------------------------------------------------------------------------
當SQLSERVER收到任何一個指令，包括查詢（query）、批處理（batch）、預存程序、觸發器（trigger）
、先行編譯指令（prepared statement）和動態SQL語句（dynamic SQL Statement）要完成文法解釋、語句解釋，
然後再進行“編譯（compile）”，產生能夠啟動並執行“執行計畫（execution plan）”。在編譯的過程中，SQLSERVER會根據所涉及的對象的架構（schema）、統計資訊以及指令的具體內容，估算可能的執行計畫，以及他們的成本（cost），最後選擇一個SQLSERVER認為成本最低的執行計畫來執行。執行計畫產生之後，SQLSERVER通常會把他們緩衝在記憶體裡，術語統稱他們叫“plan cache”以後同樣的語句執行，SQLSERVER就可以使用同樣的執行計畫，而無須再做一次編譯。

這種行為叫“重用（reuse）或者叫重用執行計畫”。但是有時候，哪怕是一模一樣的語句，SQL下次執行還是要再做一次編譯。

這種行為叫“重編譯（recompile）”。執行計畫的編譯和重編譯都是要消耗資源的。
如果執行計畫能夠重用，那麼SQLSERVER就不需要再執行上面的過程，加快執行指令的速度，很多語句調優的文章裡提到資料庫重用執行計畫就是指這個意思

執行計畫重用的利弊
--------------------------------------------------------------------------------
執行計畫的好壞當然決定了語句最終的執行速度。對於同樣的一條語句，使用好的執行計畫可能會比差的要快幾百倍，甚至上千倍。
所以從這一個角度來講，每運行一條語句，都把他先編譯一遍當然是最好的。他能夠保證使用的執行計畫是SQLSERVER能找到的最優的。
但是SQLSERVER每秒鐘可能會運行成百上千的指令。如果每個都編譯一遍，是資源的一種浪費。所以SQLSERVER在這裡也試圖尋找一個平衡點，
使用有限的compile/recompile，得到最好的整體效能
運行下面的指令，就能夠看到SQLSERVER當前緩衝的執行計畫有哪些（請別在生產伺服器上直接運行因為上面往往有龐大的緩衝） 複製代碼 代碼如下:SELECT * FROM sys.[syscacheobjects]

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

1、當指令或者批處理所涉及的任何一個對象（表格或者視圖）發生了架構（schema）變化
例如，在表或者視圖上添加或刪除了一個欄位，添加或者刪除了一個索引，在表上添加或者刪除了一個約束條件（constraints）等。
定義發生了變化，原來的執行計畫就不一定正確了，當然要重編譯

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

3、有些動作會清除記憶體裡的所有執行計畫，迫使大家都要做重編譯
例如，下列動作會清除整個SQLSERVER伺服器緩衝的所有執行計畫：
(1)Detach一個資料庫
(2)對資料庫做了升級，在新的伺服器上，會發生執行計畫清空
(3)運行了DBCC freeproccache
(4)運行了reconfigure語句
(5)運行了alter database..collate語句修改了某個資料庫的字元集（collation）

下列動作會清除SQLSERVER伺服器緩衝的某個資料庫的執行計畫：
DBCC FLUSHPROCINDB
清除SQL Server 2000伺服器記憶體中的某個資料庫的預存程序緩衝內容 複製代碼 代碼如下:DECLARE @a INT
SELECT @a=DB_ID('gposdb')

DBCC flushprocindb(@a)ALTER DATABASE ...MODIFY NAME語句
ALTER DATABASE ...SET ONLINE語句
ALTER DATABASE...SET OFFLINE語句
ALTER DATABASE...SET EMERGENCY語句
DROP DATABASE 語句
當一個資料庫自動關閉時
DBCC CHECKDB語句結束時

4、當下面這些SET 開關值變化後，先前的那些執行計畫都不能重用 複製代碼 代碼如下:ansi_null_dflt_off,
ansi_null_dflt_on,
ansi_nulls,
_ansi_padding
ansi_warnings,
arithabort,
concat_null_yields_null,
datefirst,dateformat,
forceplan,
language,
no_browsetable,
numeric_roundabort,
quoted_identifier

這是因為這些SET開關會影響語句的執行的行為，甚至帶來不同的結果。他們發生變化了，SQLSERVER就要根據新的設定重做執行計畫

5、當表格或者視圖上的統計資訊發生變化後
當統計資訊被手動更新後，或者SQLSERVER發現某個統計資訊需要自動更新時，SQLSERVER會對所涉及的語句都做重編譯

需要說明的是，在SQLSERVER裡，執行計畫重用並不一定是一件好事，而編譯/重編譯也不一定是一件壞事。
計劃重用可以協助SQLSERVER節省編譯時間，對降低CPU使用率和減少阻塞都有好處，但是缺點是每次重用的計劃並不一定是最合適的計劃。

參數嗅探parameter sniffing就是典型的計劃重用帶來的負效應。編譯和重編譯當然能給當前啟動並執行語句帶來儘可能準確執行計畫， 但是對於經常啟動並執行語句，尤其是一些執行速度比較快的語句，可能其編譯時間占最後總時間的相當大比例。這對資源來講是一個很大的浪費

一般來說，SQLSERVER能夠很好地在編譯與重編譯之間做平衡，大部分情況下沒什麼問題的。