Stored Procedure 里的 WITH RECOMPILE 到底是干麻的？ 原文:Stored Procedure 里的 WITH RECOMPILE 到底是干麻的？

在 SQL Server 創(chuàng)建或修改「存儲(chǔ)過(guò)程(stored procedure)」時(shí)，可加上 WITH RECOMPILE 選項(xiàng)，但多數(shù)文檔或書籍都寫得語(yǔ)焉不詳，或只解釋為「每次執(zhí)行此存儲(chǔ)過(guò)程時(shí)，都要重新編譯」。事實(shí)上，是指執(zhí)行此一存儲(chǔ)過(guò)程時(shí)，要強(qiáng)制重新產(chǎn)生「執(zhí)行計(jì)劃(execution plan)」，而不要從「緩存(cache)」去取得舊的「執(zhí)行計(jì)劃」。

SQL Server 在評(píng)估與產(chǎn)生「執(zhí)行計(jì)劃」時(shí)，非常耗 CPU 資源，因此，如何讓其正確地從 cache 中，重復(fù)使用舊的「執(zhí)行計(jì)劃」就很重要；但是，若誤用舊的「執(zhí)行計(jì)劃」，導(dǎo)致 SELECT 查詢的性能大幅下降，則更得不償失。

一般的 SQL 查詢，兩次或多次執(zhí)行的 SQL 語(yǔ)句中，內(nèi)容必須完全符合，才能延用舊的「執(zhí)行計(jì)劃」，包含: 大小寫、換行、空白。如下圖 1，因?yàn)閮纱螆?zhí)行的 SQL 語(yǔ)句，差了一個(gè)「半形空格」，導(dǎo)致產(chǎn)生了兩次「執(zhí)行計(jì)劃」，而無(wú)法重復(fù)使用舊的「執(zhí)行計(jì)劃」。

圖 1　浪費(fèi)資源產(chǎn)生了兩次「執(zhí)行計(jì)劃」

1 DBCC FREEPROCCACHE 2 3 SELECT * FROM Customers SELECT * FROM Orders 4 GO 5 SELECT * FROM Customers SELECT * FROM Orders 6 7 SELECT cacheobjtype, objtype, usecounts, sql FROM sys.syscacheobjects 8 WHERE sql NOT LIKE '%cache%' AND sql NOT LIKE '%sys.%' View Code

若是改用「參數(shù)化查詢」，如下 :
SELECT * FROM Customers WHERE CustomerID=@CustomerID
即可避免因參數(shù)值不同，一直產(chǎn)生新的「執(zhí)行計(jì)劃」，亦可避免 SQL Injection 攻擊。

而存儲(chǔ)過(guò)程，相對(duì)于一般 SQL 語(yǔ)句，其在性能上的優(yōu)勢(shì)，除了已事先編譯外，存儲(chǔ)過(guò)程也可提升「執(zhí)行計(jì)劃」的重用性(復(fù)用性)，避免產(chǎn)生新的「執(zhí)行計(jì)劃」、消耗 CPU 資源。如下圖 2，兩次調(diào)用同一個(gè)存儲(chǔ)過(guò)程時(shí)，但傳入不同的參數(shù)，SQL Server 會(huì)重復(fù)使用同一個(gè)「執(zhí)行計(jì)劃」，如同上述的「參數(shù)化查詢」一樣，不會(huì)浪費(fèi)資源產(chǎn)生新的「執(zhí)行計(jì)劃」。

圖 2　「執(zhí)行計(jì)劃」被重復(fù)使用，避免浪費(fèi)資源

1 CREATE PROC spCust1 @CustID NVARCHAR(5) 2 AS 3 SELECT * FROM dbo.Customers 4 WHERE CustomerID=@CustID 5 GO 6 7 EXEC spCust1 'ALFKI' 8 EXEC spCust1 'BERGS' 9 10 --DBCC FREEPROCCACHE 11 SELECT cacheobjtype, objtype, usecounts, sql FROM sys.syscacheobjects 12 WHERE sql NOT LIKE '%cache%' AND sql NOT LIKE '%sys.%' View Code

但若存儲(chǔ)過(guò)程「數(shù)據(jù)內(nèi)容分布不平均」，例如某個(gè) Table，里面有個(gè) Int 類型的字段，大量記錄里所存儲(chǔ)的值依序?yàn)?1~100，但只有某一條記錄存的是 10000。亦即符合過(guò)濾條件的記錄有時(shí)極多 (「執(zhí)行計(jì)劃」適合用「索引掃描」)，但有時(shí)符合的只有一兩條 (「執(zhí)行計(jì)劃」適合用「索引查找」)。而未來(lái)在調(diào)用此存儲(chǔ)過(guò)程時(shí)，兩種情境都有可能出現(xiàn)，因此我們希望此一存儲(chǔ)過(guò)程，在執(zhí)行時(shí)「不要 cache 執(zhí)行計(jì)劃」，亦即讓此存儲(chǔ)過(guò)程在每次執(zhí)行時(shí)，都重新評(píng)估、產(chǎn)生最適當(dāng)?shù)摹笀?zhí)行計(jì)劃」，此時(shí)就可加上 WITH RECOMPILE 選項(xiàng)。或者如下圖 3，丟給前端應(yīng)用程序去決定，亦即 AP 在調(diào)用此存儲(chǔ)過(guò)程時(shí)，再?zèng)Q定是否加上 WITH RECOMPILE 參數(shù)。

圖 3

1 Exec select_Proc1 @Key1=5 --自動(dòng)選用高效能的「執(zhí)行計(jì)畫」 2 Exec select_Proc1 @Key1=10000 --從 cache 延用舊的「執(zhí)行計(jì)畫」，因不適用，反而導(dǎo)致效能不佳 3 Exec select_Proc1 @Key1=10000 WITH RECOMPILE --強(qiáng)制重新產(chǎn)生新的、高效能「執(zhí)行計(jì)畫」 View Code

還有其他進(jìn)階的選項(xiàng)應(yīng)用，像是可以在創(chuàng)建存儲(chǔ)過(guò)程時(shí)，使用 OPTIMIZE FOR 選項(xiàng)，只針對(duì)特定某一個(gè)參數(shù)值來(lái)做 cache，來(lái)產(chǎn)生固定一種、平均對(duì)性能影響最小的「執(zhí)行計(jì)劃」，又能避免一直重復(fù)產(chǎn)生新的「執(zhí)行計(jì)劃」而浪費(fèi) CPU 資源。

---

案例分析 - 同樣的語(yǔ)法在存儲(chǔ)過(guò)程內(nèi)跑很慢，單獨(dú)跑很快 (胡百敬, 繁體中文) :
http://byronhu.wordpress.com/2010/07/15/with-recompile/

引用該文部分內(nèi)容 :

朋友問(wèn)了一個(gè)有趣的問(wèn)題：同樣的語(yǔ)句，在存儲(chǔ)過(guò)程內(nèi)跑很慢，單獨(dú)跑很快。

存儲(chǔ)過(guò)程會(huì)緩存執(zhí)行計(jì)劃 (若未加上 WITH RECOMPILE)，一般來(lái)說(shuō)可以省掉 CPU 耗費(fèi)。但若兩次執(zhí)行此存儲(chǔ)過(guò)程的期間，所引用的記錄數(shù)量差異很大，則第二次執(zhí)行時(shí)沿用舊的執(zhí)行計(jì)劃，性能會(huì)變得很差。可以觀察以下現(xiàn)象：

觀察執(zhí)行后的執(zhí)行計(jì)劃，傳回大量記錄卻是用「索引查找」。

透過(guò) Profiler 觀察存儲(chǔ)過(guò)程內(nèi)的語(yǔ)法，和單獨(dú)執(zhí)行的語(yǔ)法，所耗的 IO/CPU/Duration 的數(shù)值。若將某句的語(yǔ)法單獨(dú)拿到 Management Studio 執(zhí)行的性能，遠(yuǎn)好于該句語(yǔ)法在存儲(chǔ)過(guò)程內(nèi)執(zhí)行，就有可能是上述原因。

簡(jiǎn)單的解法，是在執(zhí)行或創(chuàng)建存儲(chǔ)過(guò)程時(shí)，搭配 WITH RECOMPILE 選項(xiàng)。

...中間略...

存儲(chǔ)過(guò)程的執(zhí)行情境可以分 80-20 定律，若少數(shù)執(zhí)行狀況 AP 自己知道，則 AP 可以判讀是否要下 with recompile 或是撰寫存儲(chǔ)過(guò)程直接搭配 Option(Optimize for (參數(shù)定義))

但在一些狀況，例如使用者下 Range 查詢，或是「財(cái)務(wù)滾算」數(shù)據(jù)，會(huì)大量刪除、插入中繼表內(nèi)的數(shù)據(jù)，developer 無(wú)法預(yù)先評(píng)估可能的數(shù)據(jù)量大小，則在存儲(chǔ)過(guò)程創(chuàng)建時(shí)，直接搭配 with recompile，可得到較穩(wěn)定的執(zhí)行性能。

---

結(jié)語(yǔ)： 我自己早年寫 AP 時(shí)，一直查不到 WITH RECOMPILE 是干麻的，當(dāng)時(shí)我寫用來(lái)「分頁(yè)(換頁(yè))」的存儲(chǔ)過(guò)程時(shí) (雙 TOP 夾擊、或 ROW_NUMBER 函數(shù))，就一律加上 WITH RECOMPILE 選項(xiàng)。現(xiàn)在回想起來(lái)，其實(shí)是不必加的，因?yàn)橹貜?fù)用舊的「執(zhí)行計(jì)劃」即可 (可節(jié)省許多數(shù)據(jù)庫(kù)伺服器上的資源)，丟入的參數(shù)也都差不多 (用戶目前所在頁(yè)數(shù)、每頁(yè)要傳回幾條記錄)。

1 CREATE PROCEDURE [dbo].[GridView_pager] 2 @StartRowIndex int, 3 @PageSize int, 4 @tableName nvarchar(50), 5 @columnName nvarchar(100), 6 @sqlWhere nvarchar(1000), 7 @groupBy nvarchar(100), 8 @orderBy nvarchar(100), 9 @rowCount int output 10 WITH RECOMPILE 11 AS View Code

---

相關(guān)文章?:

談一談 SQL Server 中的執(zhí)行計(jì)劃緩存
http://www.cnblogs.com/CareySon/archive/2013/05/04/3058592.html
http://www.cnblogs.com/CareySon/archive/2013/05/04/PlanCacheInSQLServerPart2.html

---

posted on 2014-11-12 00:02 NET未來(lái)之路 閱讀(...) 評(píng)論(...) 編輯 收藏

轉(zhuǎn)載于:https://www.cnblogs.com/lonelyxmas/p/4090896.html

總結(jié)

以上是生活随笔為你收集整理的Stored Procedure 里的 WITH RECOMPILE 到底是干麻的？的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。