在 SQL Server 7.0 中創(chuàng)建存儲過程時(shí)昔汉，過程的文本存儲在 syscomments 系統(tǒng)表中。 在用戶執(zhí)行過程時(shí)浓利，如果過程還不在高速緩存中挤庇，SQL Server 將從 syscomments（在早期版的 SQL Server 中為 sysprocedures）中加載過程，并編譯查詢計(jì)劃贷掖。 已編譯的計(jì)劃保存在高速緩存中嫡秕，并在以后調(diào)用存儲過程時(shí)再次使用，直到發(fā)生某個(gè)操作使計(jì)劃無效并強(qiáng)制進(jìn)行重新編譯為止苹威。 下列操作可能會引起存儲過程計(jì)劃的重新編譯。

• 在 CREATE PROCEDURE 或 EXECUTE 語句中使用 WITH RECOMPILE 子句掷酗。
• 對任何過程所引用對象的架構(gòu)進(jìn)行更改泻轰，包括添加或取消約束且轨、默認(rèn)值或規(guī)則旋奢。
• 對過程所引用的表運(yùn)行sp_recompile至朗。
• 恢復(fù)包含過程或過程引用的任意對象的數(shù)據(jù)庫（如果執(zhí)行的是跨數(shù)據(jù)庫操作）锹引。
• 太多的服務(wù)器活動(dòng)引起計(jì)劃在高速緩存中超時(shí)嫌变。

重新編譯存儲過程的所有這些原因在早期版本中確實(shí)存在初澎，并導(dǎo)致在開始執(zhí)行過程前重新編譯過程碑宴。 在 SQL Server 7.0 中延柠，引入了一個(gè)新功能，該功能使得可以在執(zhí)行期間重新編譯存儲過程贿条。 這種新功能可保證優(yōu)化器對過程內(nèi)的每個(gè)特定語句總是有最可行的計(jì)劃整以。 下列事件可能會引起存儲過程的運(yùn)行時(shí)重新編譯：

• 存儲過程引用的表中的大部分?jǐn)?shù)據(jù)發(fā)生了更改公黑，SQL Server 將重新編譯存儲過程以確保它有一個(gè)基于最新統(tǒng)計(jì)數(shù)據(jù)的計(jì)劃凡蚜。
• 過程交錯(cuò)執(zhí)行數(shù)據(jù)定義語言 (DDL) 和數(shù)據(jù)操作語言 (DML) 操作朝蜘。
• 過程在臨時(shí)表上執(zhí)行特定的操作谱醇。

在某些情況下，重新編譯存儲過程的開銷遠(yuǎn)遠(yuǎn)大于所帶來好處熔吗，對于大型過程尤其如此桅狠。 切記：啟動(dòng)重新編譯時(shí)中跌，整個(gè)批處理或過程均進(jìn)行重新編譯漩符。 這意味著性能的下降直接與過程或批處理的大小成比例钓丰。

事件探查器

SQL Server 事件探查器包括 SP:Recompile 事件，可以利用這個(gè)事件監(jiān)控重新編譯發(fā)生的次數(shù)描焰。 只要在執(zhí)行期間發(fā)生存儲過程重新編譯咐容，就會發(fā)生SP:Recompile 事件。

對數(shù)據(jù)庫使用事件探測器

• 菜單路徑：開始--程序--Microsoft SQL Server 2008--性能工具--SQL Server Profiler
• 文件--新建跟蹤：新建指定數(shù)據(jù)庫的跟蹤

Stored Procedures的事件

• SP:Recompile 事件類指示存儲過程虫啥、觸發(fā)器或用戶定義的函數(shù)已被重新編譯涂籽。 此事件類報(bào)告的重新編譯在語句級上發(fā)生。
• SP:Starting 事件類指示存儲過程將要開始執(zhí)行苔咪。
• SP:StmtStarting事件類指示已開始執(zhí)行存儲過程中的 Transact-SQL 語句团赏。
• SP:Completed 事件類指明存儲過程已執(zhí)行完畢舔清。
• SP:StmtCompleted 事件類指示存儲過程中的 Transact-SQL 語句已完成体谒。

根據(jù) Event Class 對“事件探查器”跟蹤進(jìn)行分組：

• 在文件菜單上抒痒，單擊屬性故响。

• 在事件選項(xiàng)選項(xiàng)卡上，只有已經(jīng)被選擇的事件才會被顯示伪冰。如果想顯示所有可供選擇的事件贮聂，則只需選中“顯示所有事件”單選框寂汇。要添加一個(gè)跟蹤事件骄瓣，在Event列中查找一個(gè)事件類下的事件榕栏，并單擊其左邊的檢查框扒磁；要?jiǎng)h除不需要的事件妨托，取消選中的事件選擇框。

  
  
  
  

  而在這里我們要添加的Events名為Stored Procedures(存儲過程)

• 單擊確定。

檢查 SP:Recompile 事件個(gè)數(shù)：
??可以展開 SP:Recompile 組以查看每次發(fā)生重新編譯時(shí)的詳細(xì)信息敦腔。事件的 Text 列指出重新編譯的存儲過程名稱符衔。
??如果多個(gè)過程正在引發(fā)重新編譯事件判族，則按發(fā)生的次數(shù)進(jìn)行存儲形帮。如果有大量的 SP:Recompile 事件沃缘，而且此時(shí) CPU 占用率很高槐臀，則集中解決重新編譯次數(shù)最多的過程。 如果沒有看到任何 SP:Recompile 事件得糜，但是仍然遇到性能問題朝抖，則要去解決SQL Server 中的應(yīng)用程序性能問題治宣。

找出引發(fā)重新編譯事件的語句：

• 在文件菜單上侮邀，單擊屬性绊茧。
• 在 Data Columns 選項(xiàng)卡上华畏，使用向下按鈕刪除 Groups 標(biāo)題下的所有其它列亡笑。
• 在 Events 選項(xiàng)卡上，刪除除 SP:Starting惜纸、SP:StmtStarting、SP:Recompile 和 SP:Completed 之外的所有其它事件祠够。 如果不捕獲 SP:StmtStarting 事件古瓤，可以替換為 SP:StmtCompleted 事件落君，但不能同時(shí)包含這兩個(gè)事件绎速，因?yàn)檫@樣會將需要查詢的信息量加倍纹冤。
• 如果已經(jīng)識別出要檢查的存儲過程重新編譯的特定實(shí)例，則通過使用 Filters 選項(xiàng)卡雁歌，可以將查看的數(shù)據(jù)限定為事件發(fā)生時(shí)的特定 SPID(用于該事件的SQL Server進(jìn)程標(biāo)識符)和時(shí)間范圍靠瞎。
• 單擊確定印蔗。

SP:Recompile 事件將在發(fā)生存儲過程語句引起重新編譯的 SP:StmtStarting事件之后立即發(fā)生丑勤。 重新編譯事件完成后法竞，將會看到 SP:StmtStarting事件重新出現(xiàn)，表明正在使用新生成的計(jì)劃執(zhí)行語句薛躬。

請考慮下列示例：

use pubs
go
drop procedure RecompProc 
go
create procedure RecompProc as
create table #t (a int)
select * from #t
go
exec RecompProc

如果在 Query Analyzer 中執(zhí)行該代碼并在“事件探查器”跟蹤中查看上述事件型宝，將看到下列序列：

您可以立即指出引起重新編譯的語句是：

select * from #t

因?yàn)樗霈F(xiàn)在 SP:Recompile 事件的前后趴酣。
??如果您僅捕獲 SP:StmtCompleted 事件岖寞，而沒有捕獲 SP:StmtStarting 事件柜蜈，SP:Recompile 事件將直接出現(xiàn)在引發(fā)該事件的語句之前淑履，如下所示：

可以看到 SP:Recompile 事件在 select * from #t 語句的 SP:StmtCompleted 事件之前發(fā)生狸吞，從而引起了重新編譯缆娃。 這是有道理的贯要，因?yàn)橹钡綖橹匦戮幾g生成了新查詢計(jì)劃之后，才能完成該語句字逗。

由于行修改引起的重新編譯

自從生成原始查詢計(jì)劃以來，如果由存儲過程所引用的表中的大多數(shù)數(shù)據(jù)已發(fā)生更改些举，SQL Server 將重新編譯存儲過程以確保它有一個(gè)基于最新統(tǒng)計(jì)數(shù)據(jù)的計(jì)劃户魏。 例如叼丑，請考慮下列存儲過程

drop procedure RowModifications 
go
create procedure RowModifications as
-- assume SomeTable exists with the same definition as #t, 
-- and has over 1000 rows
create table #t (a int, b char(10))
select * from #t
insert #t select * from SomeTable
select count(*) from #t  where a = 37
go
exec RowModifications
exec RowModifications

對于 RowModifications 過程的第二次執(zhí)行鸠信，在“事件探查器”中將看到下列事件：

使用以下方法消除由于行修改引起的重新編譯：

• 使用 sp_executesql 執(zhí)行語句咆课。
  ??這是首選的方法书蚪。使用 sp_executesql 存儲過程執(zhí)行的語句并沒有作為存儲過程計(jì)劃的一部分進(jìn)行編譯迅栅。 因此读存，執(zhí)行該語句時(shí)让簿，SQL Server 將可以隨意選擇使用語句高速緩存中的現(xiàn)有計(jì)劃尔当，或者在運(yùn)行時(shí)創(chuàng)建新的計(jì)劃椭迎。 不管哪一種情況下畜号，正在調(diào)用的存儲過程的計(jì)劃不會受到影響弄兜，因而無須進(jìn)行重新編譯。
  ??EXECUTE 語句有同樣的效果,但是并不推薦使用它贸典。使用EXECUTE 語句的效率沒有使用 sp_executesql 的高踱卵，因?yàn)樗辉试S進(jìn)行參數(shù)化查詢惋砂。
  ??上述所給出的 RowModifications 過程可以使用 sp_executesql 進(jìn)行編寫西饵，如下所示：

drop procedure RowModifications2
go
create procedure RowModifications2 as
set nocount on
-- assume SomeTable exists with the same definition as #t, 
-- and has over 1000 rows
create table #t (a int, b char(10))
select * from #t
insert #t select * from SomeTable
exec sp_executesql N'select count(*) from #t where a = @a', 
                   N'@a int', @a =  37
go
exec RowModifications2
exec RowModifications2

• 使用子過程執(zhí)行引起重新編譯的語句期虾。
  ??在這種情況下，該語句可能還會引起重新編譯镶苞，但不是重新編譯大型調(diào)用存儲過程，而只是重新編譯小型子過程剃幌。
• 使用 KEEP PLAN 選項(xiàng)锥忿。
  ??臨時(shí)表對于重新編譯有特殊規(guī)則敬鬓，在某些情況下钉答，這些規(guī)則比默認(rèn)的重新編譯算法要嚴(yán)格得多数尿。 可以使用 KEEP PLAN 選項(xiàng)將臨時(shí)表的閾值放寬到默認(rèn)算法惶楼。

由于交錯(cuò)執(zhí)行數(shù)據(jù)定義語句 (DDL) 和數(shù)據(jù)操作語句 (DML) 操作引起重新編譯

如果 DDL 操作是在一個(gè)過程或批處理內(nèi)執(zhí)行的右蹦，則過程或批處理在遇到隨之發(fā)生的影響 DDL 中涉及的表的第一個(gè) DML 操作時(shí)將進(jìn)行重新編譯诊杆。
??請考慮下列示例存儲過程：

drop procedure Interleave 
go
create procedure Interleave as
-- DDL
create table #t1 (a int)
-- DML
select * from #t1
-- DDL
create index idx_#t1 on #t1(a)
-- DML
select * from #t1
-- DDL
create table #t2 (a int)
-- DML
select * from #t2
go
exec Interleave

如果在 Query Analyzer 中執(zhí)行該代碼并在“事件探查器”跟蹤中查看上述事件，將看到下列序列：

??在這種情況下何陆，存儲過程在執(zhí)行期間重新編譯三次晨汹。 要了解發(fā)生這種情況的原因，請考慮優(yōu)化器為存儲過程開發(fā)計(jì)劃的方式：
1贷盲、在過程的初始編譯過程中淘这，并不存在臨時(shí)表 #t1 和 #t2。 因此巩剖，沒有創(chuàng)建引用這些表的查詢的計(jì)劃铝穷。 這些計(jì)劃必須是在執(zhí)行期間生成的。
2筹陵、在過程第一次執(zhí)行時(shí)庐氮，第一步是創(chuàng)建表 #t1。 第二步是從表 #t1 中進(jìn)行選擇，該選擇還沒有計(jì)劃寸士。 因此婶博，此時(shí)重新編譯過程來為 SELECT 語句生成計(jì)劃名党。 為表 #t1 中的當(dāng)前選擇生成計(jì)劃忠荞，也為創(chuàng)建索引后表 #t1 中的選擇生成計(jì)劃堂油。 沒有為表 #t2 中的選擇生成計(jì)劃迫靖，因?yàn)楸?#t2 還不存在盹牧。
3、下一步是對表 #t1 創(chuàng)建索引。 隨后毛好，在表 #t1 上執(zhí)行另一個(gè)選擇，現(xiàn)在它有來自第一次重新編譯的一個(gè)計(jì)劃旨剥。 但是蚌父，因?yàn)楸?#t1 的架構(gòu)自從生成計(jì)劃后已更改膏秫，因此必須再次重新編譯過程來為表 #t1 中的選擇生成新計(jì)劃吹榴。 由于表 #t2 還不存在婿斥，還不可以為表 #t2 中的選擇生成計(jì)劃活鹰。
4、下一步，創(chuàng)建表 #t2娃胆，并執(zhí)行表 #t2 中的選擇。 由于沒有用于該語句的計(jì)劃别厘，最終一次重新編譯過程。

在每次執(zhí)行存儲過程時(shí)都發(fā)生這些重新編譯没讲。 要減少重新編譯的次數(shù)，請將過程修改為先執(zhí)行所有 DDL 操作卦溢，然后接著執(zhí)行 DML 操作

由于某些臨時(shí)表操作引起的重新

在存儲過程中使用臨時(shí)表可能會導(dǎo)致在每次執(zhí)行過程時(shí)重新編譯存儲過程。為避免這種情況壹甥，請更改存儲過程以符合下列要求：

• 創(chuàng)建臨時(shí)表（如 CREATE TABLE 或 SELECT... INTO）的語句不出現(xiàn)在流控制語句如 IF... ELSE 或 WHILE 中帽哑。
• 包含任意臨時(shí)表名稱的所有語句出現(xiàn)在引用臨時(shí)表的任何 DROP TABLE 語句之前。在存儲過程中創(chuàng)建的臨時(shí)表不需要 DROP TABLE 語句敷钾，在過程執(zhí)行完畢時(shí)羊壹，這些表自動(dòng)消失。

使用 KEEP PLAN 選項(xiàng)避免重新編譯

在存儲過程中使用臨時(shí)表使得查詢優(yōu)化程序變得有些復(fù)雜丐箩。 表的行計(jì)數(shù)和統(tǒng)計(jì)信息在存儲過程執(zhí)行的整個(gè)期間變化非常大犀概。 為確保優(yōu)化器在所有涉及臨時(shí)表的情況下使用最優(yōu)計(jì)劃曾沈，已開發(fā)了一種特殊算法，該算法對于重新編譯更具有主動(dòng)性粘我。 該算法的原理是：如果對存儲過程創(chuàng)建的臨時(shí)表已更改六次以上搁料，將在下一個(gè)語句引用臨時(shí)表時(shí)重新編譯過程庐杨。

請考慮下列示例：

drop procedure useKeepPlan 
go
create procedure useKeepPlan as
create table #t (a int, b char(3))
select * from #t
-- Make greater than 6 changes to #t
insert #t values (1, 'abc')
insert #t values (2, 'abc')
insert #t values (3, 'abc')
insert #t values (4, 'abc')
insert #t values (5, 'abc')
insert #t values (6, 'abc')
insert #t values (7, 'abc')
-- Now reference #t
select count(*) from #t 
-- 使用KEEP PLAN
option (KEEP PLAN)
go
exec useKeepPlan
exec useKeepPlan

最優(yōu)準(zhǔn)則

當(dāng)作為非所有者執(zhí)行某個(gè)過程時(shí)匪凉，最好完全限定存儲過程名稱。這樣做會更明確且更易于當(dāng)前用戶重新使用現(xiàn)有執(zhí)行計(jì)劃萨脑。 例如渤早，如果一個(gè)非數(shù)據(jù)庫所有者 (dbo) 用戶執(zhí)行了數(shù)據(jù)庫所有者 (dbo) 所擁有的 pubs 數(shù)據(jù)庫中的存儲過程（在本例中為 myProc）骂蓖，則應(yīng)當(dāng)使用下面的語句：

exec pubs.dbo.myProc

這種技術(shù)從編碼和維護(hù)的角度被芳，消除了不同所有者使用不同過程版本的混亂狀況，同時(shí)也允許 SQL Server 更直接地訪問特定過程的執(zhí)行計(jì)劃。