SQLSERVER編譯與重編譯發(fā)生場景及重用的利弊介紹
更新時間:2013年01月18日 14:04:38 作者:
本文將介紹編譯的含義;執(zhí)行計劃重用的利弊以及重編譯的發(fā)生場景等等,為您學習SQLSERVER編譯與重編譯打下很好的基礎,感興趣的朋友可以了解下
編譯的含義
--------------------------------------------------------------------------------
當SQLSERVER收到任何一個指令,包括查詢(query)、批處理(batch)、存儲過程、觸發(fā)器(trigger)
、預編譯指令(prepared statement)和動態(tài)SQL語句(dynamic SQL Statement)要完成語法解釋、語句解釋,
然后再進行“編譯(compile)”,生成能夠運行的“執(zhí)行計劃(execution plan)”。在編譯的過程中,SQLSERVER會根據所涉及的對象的架構(schema)、統(tǒng)計信息以及指令的具體內容,估算可能的執(zhí)行計劃,以及他們的成本(cost),最后選擇一個SQLSERVER認為成本最低的執(zhí)行計劃來執(zhí)行。執(zhí)行計劃生成之后,SQLSERVER通常會把他們緩存在內存里,術語統(tǒng)稱他們叫“plan cache”以后同樣的語句執(zhí)行,SQLSERVER就可以使用同樣的執(zhí)行計劃,而無須再做一次編譯。
這種行為叫“重用(reuse)或者叫重用執(zhí)行計劃”。但是有時候,哪怕是一模一樣的語句,SQL下次執(zhí)行還是要再做一次編譯。
這種行為叫“重編譯(recompile)”。執(zhí)行計劃的編譯和重編譯都是要消耗資源的。
如果執(zhí)行計劃能夠重用,那么SQLSERVER就不需要再執(zhí)行上面的過程,加快執(zhí)行指令的速度,很多語句調優(yōu)的文章里提到數據庫重用執(zhí)行計劃就是指這個意思
執(zhí)行計劃重用的利弊
--------------------------------------------------------------------------------
執(zhí)行計劃的好壞當然決定了語句最終的執(zhí)行速度。對于同樣的一條語句,使用好的執(zhí)行計劃可能會比差的要快幾百倍,甚至上千倍。
所以從這一個角度來講,每運行一條語句,都把他先編譯一遍當然是最好的。他能夠保證使用的執(zhí)行計劃是SQLSERVER能找到的最優(yōu)的。
但是SQLSERVER每秒鐘可能會運行成百上千的指令。如果每個都編譯一遍,是資源的一種浪費。所以SQLSERVER在這里也試圖尋找一個平衡點,
使用有限的compile/recompile,得到最好的整體性能
運行下面的指令,就能夠看到SQLSERVER當前緩存的執(zhí)行計劃有哪些(請別在生產服務器上直接運行因為上面往往有龐大的緩存)
SELECT * FROM sys.[syscacheobjects]
重編譯的發(fā)生場景
--------------------------------------------------------------------------------
但是有些時候,SQLSERVER為了確保返回正確的值,或者有性能上的顧慮,有意不重用緩存在內存里的執(zhí)行計劃,而現場編譯一份。
這種行為,被稱為重編譯(recompile)。下面是比較常見的會發(fā)生重編譯的情形:
1、當指令或者批處理所涉及的任何一個對象(表格或者視圖)發(fā)生了架構(schema)變化
例如,在表或者視圖上添加或刪除了一個字段,添加或者刪除了一個索引,在表上添加或者刪除了一個約束條件(constraints)等。
定義發(fā)生了變化,原來的執(zhí)行計劃就不一定正確了,當然要重編譯
2、運行過sp_recompile
當用戶在某個存儲過程或者觸發(fā)器上運行過sp_recompile后,下一次運行他們就會發(fā)生一次重編譯。
如果用戶在某個表或者視圖上運行了sp_recompile,那么所有引用到這張表(或者視圖)的存儲過程在下一次運行前,都要做重編譯
3、有些動作會清除內存里的所有執(zhí)行計劃,迫使大家都要做重編譯
例如,下列動作會清除整個SQLSERVER服務器緩存的所有執(zhí)行計劃:
(1)Detach一個數據庫
(2)對數據庫做了升級,在新的服務器上,會發(fā)生執(zhí)行計劃清空
(3)運行了DBCC freeproccache
(4)運行了reconfigure語句
(5)運行了alter database..collate語句修改了某個數據庫的字符集(collation)
下列動作會清除SQLSERVER服務器緩存的某個數據庫的執(zhí)行計劃:
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 開關值變化后,先前的那些執(zhí)行計劃都不能重用
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開關會影響語句的執(zhí)行的行為,甚至帶來不同的結果。他們發(fā)生變化了,SQLSERVER就要根據新的設置重做執(zhí)行計劃
5、當表格或者視圖上的統(tǒng)計信息發(fā)生變化后
當統(tǒng)計信息被手動更新后,或者SQLSERVER發(fā)現某個統(tǒng)計信息需要自動更新時,SQLSERVER會對所涉及的語句都做重編譯
需要說明的是,在SQLSERVER里,執(zhí)行計劃重用并不一定是一件好事,而編譯/重編譯也不一定是一件壞事。
計劃重用可以幫助SQLSERVER節(jié)省編譯時間,對降低CPU使用率和減少阻塞都有好處,但是缺點是每次重用的計劃并不一定是最合適的計劃。
參數嗅探parameter sniffing就是典型的計劃重用帶來的負效應。編譯和重編譯當然能給當前運行的語句帶來盡可能準確執(zhí)行計劃, 但是對于經常運行的語句,尤其是一些執(zhí)行速度比較快的語句,可能其編譯時間占最后總時間的相當大比例。這對資源來講是一個很大的浪費
一般來說,SQLSERVER能夠很好地在編譯與重編譯之間做平衡,大部分情況下沒什么問題的。
--------------------------------------------------------------------------------
當SQLSERVER收到任何一個指令,包括查詢(query)、批處理(batch)、存儲過程、觸發(fā)器(trigger)
、預編譯指令(prepared statement)和動態(tài)SQL語句(dynamic SQL Statement)要完成語法解釋、語句解釋,
然后再進行“編譯(compile)”,生成能夠運行的“執(zhí)行計劃(execution plan)”。在編譯的過程中,SQLSERVER會根據所涉及的對象的架構(schema)、統(tǒng)計信息以及指令的具體內容,估算可能的執(zhí)行計劃,以及他們的成本(cost),最后選擇一個SQLSERVER認為成本最低的執(zhí)行計劃來執(zhí)行。執(zhí)行計劃生成之后,SQLSERVER通常會把他們緩存在內存里,術語統(tǒng)稱他們叫“plan cache”以后同樣的語句執(zhí)行,SQLSERVER就可以使用同樣的執(zhí)行計劃,而無須再做一次編譯。
這種行為叫“重用(reuse)或者叫重用執(zhí)行計劃”。但是有時候,哪怕是一模一樣的語句,SQL下次執(zhí)行還是要再做一次編譯。
這種行為叫“重編譯(recompile)”。執(zhí)行計劃的編譯和重編譯都是要消耗資源的。
如果執(zhí)行計劃能夠重用,那么SQLSERVER就不需要再執(zhí)行上面的過程,加快執(zhí)行指令的速度,很多語句調優(yōu)的文章里提到數據庫重用執(zhí)行計劃就是指這個意思
執(zhí)行計劃重用的利弊
--------------------------------------------------------------------------------
執(zhí)行計劃的好壞當然決定了語句最終的執(zhí)行速度。對于同樣的一條語句,使用好的執(zhí)行計劃可能會比差的要快幾百倍,甚至上千倍。
所以從這一個角度來講,每運行一條語句,都把他先編譯一遍當然是最好的。他能夠保證使用的執(zhí)行計劃是SQLSERVER能找到的最優(yōu)的。
但是SQLSERVER每秒鐘可能會運行成百上千的指令。如果每個都編譯一遍,是資源的一種浪費。所以SQLSERVER在這里也試圖尋找一個平衡點,
使用有限的compile/recompile,得到最好的整體性能
運行下面的指令,就能夠看到SQLSERVER當前緩存的執(zhí)行計劃有哪些(請別在生產服務器上直接運行因為上面往往有龐大的緩存)
復制代碼 代碼如下:
SELECT * FROM sys.[syscacheobjects]
重編譯的發(fā)生場景
--------------------------------------------------------------------------------
但是有些時候,SQLSERVER為了確保返回正確的值,或者有性能上的顧慮,有意不重用緩存在內存里的執(zhí)行計劃,而現場編譯一份。
這種行為,被稱為重編譯(recompile)。下面是比較常見的會發(fā)生重編譯的情形:
1、當指令或者批處理所涉及的任何一個對象(表格或者視圖)發(fā)生了架構(schema)變化
例如,在表或者視圖上添加或刪除了一個字段,添加或者刪除了一個索引,在表上添加或者刪除了一個約束條件(constraints)等。
定義發(fā)生了變化,原來的執(zhí)行計劃就不一定正確了,當然要重編譯
2、運行過sp_recompile
當用戶在某個存儲過程或者觸發(fā)器上運行過sp_recompile后,下一次運行他們就會發(fā)生一次重編譯。
如果用戶在某個表或者視圖上運行了sp_recompile,那么所有引用到這張表(或者視圖)的存儲過程在下一次運行前,都要做重編譯
3、有些動作會清除內存里的所有執(zhí)行計劃,迫使大家都要做重編譯
例如,下列動作會清除整個SQLSERVER服務器緩存的所有執(zhí)行計劃:
(1)Detach一個數據庫
(2)對數據庫做了升級,在新的服務器上,會發(fā)生執(zhí)行計劃清空
(3)運行了DBCC freeproccache
(4)運行了reconfigure語句
(5)運行了alter database..collate語句修改了某個數據庫的字符集(collation)
下列動作會清除SQLSERVER服務器緩存的某個數據庫的執(zhí)行計劃:
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 開關值變化后,先前的那些執(zhí)行計劃都不能重用
復制代碼 代碼如下:
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開關會影響語句的執(zhí)行的行為,甚至帶來不同的結果。他們發(fā)生變化了,SQLSERVER就要根據新的設置重做執(zhí)行計劃
5、當表格或者視圖上的統(tǒng)計信息發(fā)生變化后
當統(tǒng)計信息被手動更新后,或者SQLSERVER發(fā)現某個統(tǒng)計信息需要自動更新時,SQLSERVER會對所涉及的語句都做重編譯
需要說明的是,在SQLSERVER里,執(zhí)行計劃重用并不一定是一件好事,而編譯/重編譯也不一定是一件壞事。
計劃重用可以幫助SQLSERVER節(jié)省編譯時間,對降低CPU使用率和減少阻塞都有好處,但是缺點是每次重用的計劃并不一定是最合適的計劃。
參數嗅探parameter sniffing就是典型的計劃重用帶來的負效應。編譯和重編譯當然能給當前運行的語句帶來盡可能準確執(zhí)行計劃, 但是對于經常運行的語句,尤其是一些執(zhí)行速度比較快的語句,可能其編譯時間占最后總時間的相當大比例。這對資源來講是一個很大的浪費
一般來說,SQLSERVER能夠很好地在編譯與重編譯之間做平衡,大部分情況下沒什么問題的。
相關文章
關于SQL數據庫 msdb.dbo.sp_send_dbmail 函數發(fā)送郵件的場景分析
這篇文章主要介紹了關于SQL數據庫 msdb.dbo.sp_send_dbmail 函數發(fā)送郵件的場景分析,需要的朋友可以參考下2018-10-10SQL 判斷給定日期值(或時間段)所在星期的星期一和星期天的日期
最近報表要用到一項功能,需要把數據源根據記錄發(fā)生日期所在的星期序列進行分組。因此就寫了兩個相關SQL Function進行調用。2011-10-10CREATE FUNCTION sqlserver用戶定義函數
創(chuàng)建用戶定義函數,它是返回值的已保存的 Transact-SQL 例程。用戶定義函數不能用于執(zhí)行一組修改全局數據庫狀態(tài)的操作。與系統(tǒng)函數一樣,用戶定義函數可以從查詢中喚醒調用。也可以像存儲過程一樣,通過 EXECUTE 語句執(zhí)行2012-07-07清除SQL SERVER錯誤日志出現操作系統(tǒng)錯誤的解決方法
SQL Server 外部的進程可能會阻止 SQL Server 讀取這些文件。因此,錯誤日志條目可能已丟失,并且或許不可能查看某些 SQL Server 錯誤日志。請確保任何其他進程都未將該文件鎖定為只寫訪問2013-08-08