在 SQL Server 創(chuàng)建或修改「存儲過程(stored procedure)」時，可加上 WITH RECOMPILE 選項，但多數(shù)文檔或書籍都寫得語焉不詳，或只解釋為「每次執(zhí)行此存儲過程時，都要重新編譯」。事實上，是指執(zhí)行此一存儲過程時，要強(qiáng)制重新產(chǎn)生「執(zhí)行計劃(execution plan)」，而不要從「緩存(cache)」去取得舊的「執(zhí)行計劃」。
SQL Server 在評估與產(chǎn)生「執(zhí)行計劃」時，非常耗 CPU 資源，因此，如何讓其正確地從 cache 中，重復(fù)使用舊的「執(zhí)行計劃」就很重要；但是，若誤用舊的「執(zhí)行計劃」，導(dǎo)致 SELECT 查詢的性能大幅下降，則更得不償失。
一般的 SQL 查詢，兩次或多次執(zhí)行的 SQL 語句中，內(nèi)容必須完全符合，才能延用舊的「執(zhí)行計劃」，包含: 大小寫、換行、空白。如下圖 1，因為兩次執(zhí)行的 SQL 語句，差了一個「半形空格」，導(dǎo)致產(chǎn)生了兩次「執(zhí)行計劃」，而無法重復(fù)使用舊的「執(zhí)行計劃」。
圖 1 浪費(fèi)資源產(chǎn)生了兩次「執(zhí)行計劃」
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.%
'
若是改用「參數(shù)化查詢」，如下 :
SELECT * FROM Customers WHERE CustomerID=
@CustomerID
即可避免因參數(shù)值不同，一直產(chǎn)生新的「執(zhí)行計劃」，亦可避免 SQL Injection 攻擊。
而存儲過程，相對于一般 SQL 語句，其在性能上的優(yōu)勢，除了已事先編譯外，存儲過程也可提升「執(zhí)行計劃」的重用性(復(fù)用性)，避免產(chǎn)生新的「執(zhí)行計劃」、消耗 CPU 資源。如下圖 2，兩次調(diào)用同一個存儲過程時，但傳入不同的參數(shù)，SQL Server 會重復(fù)使用同一個「執(zhí)行計劃」，如同上述的「參數(shù)化查詢」一樣，不會浪費(fèi)資源產(chǎn)生新的「執(zhí)行計劃」。
圖 2 「執(zhí)行計劃」被重復(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.%
'
但若存儲過程「數(shù)據(jù)內(nèi)容分布不平均」，例如某個 Table，里面有個 Int 類型的字段，大量記錄里所存儲的值依序為 1~100，但只有某一條記錄存的是 10000。亦即符合過濾條件的記錄有時極多 (「執(zhí)行計劃」適合用「索引掃描」)，但有時符合的只有一兩條 (「執(zhí)行計劃」適合用「索引查找」)。而未來在調(diào)用此存儲過程時，兩種情境都有可能出現(xiàn)，因此我們希望此一存儲過程，在執(zhí)行時「不要 cache 執(zhí)行計劃」，亦即讓此存儲過程在每次執(zhí)行時，都重新評估、產(chǎn)生最適當(dāng)?shù)摹笀?zhí)行計劃」，此時就可加上 WITH RECOMPILE 選項。或者如下圖 3，丟給前端應(yīng)用程序去決定，亦即 AP 在調(diào)用此存儲過程時，再決定是否加上 WITH RECOMPILE 參數(shù)。
圖 3
1
Exec
select_Proc1
@Key1
=
5
--
自動選用高效能的「執(zhí)行計畫」
2
Exec
select_Proc1
@Key1
=
10000
--
從 cache 延用舊的「執(zhí)行計畫」，因不適用，反而導(dǎo)致效能不佳
3
Exec
select_Proc1
@Key1
=
10000
WITH
RECOMPILE
--
強(qiáng)制重新產(chǎn)生新的、高效能「執(zhí)行計畫」
還有其他進(jìn)階的選項應(yīng)用，像是可以在創(chuàng)建存儲過程時，使用 OPTIMIZE FOR 選項，只針對特定某一個參數(shù)值來做 cache，來產(chǎn)生固定一種、平均對性能影響最小的「執(zhí)行計劃」，又能避免一直重復(fù)產(chǎn)生新的「執(zhí)行計劃」而浪費(fèi) CPU 資源。
案例分析 - 同樣的語法在存儲過程內(nèi)跑很慢，單獨(dú)跑很快 (胡百敬, 繁體中文) :
http://byronhu.wordpress.com/2010/07/15/with-recompile/
引用該文部分內(nèi)容 :
朋友問了一個有趣的問題：同樣的語句，在存儲過程內(nèi)跑很慢，單獨(dú)跑很快。
存儲過程會緩存執(zhí)行計劃 (若未加上 WITH RECOMPILE)，一般來說可以省掉 CPU 耗費(fèi)。但若兩次執(zhí)行此存儲過程的期間，所引用的記錄數(shù)量差異很大，則第二次執(zhí)行時沿用舊的執(zhí)行計劃，性能會變得很差。可以觀察以下現(xiàn)象：
- 觀察執(zhí)行后的執(zhí)行計劃，傳回大量記錄卻是用「索引查找」。
- 透過 Profiler 觀察存儲過程內(nèi)的語法，和單獨(dú)執(zhí)行的語法，所耗的 IO/CPU/Duration 的數(shù)值。若將某句的語法單獨(dú)拿到 Management Studio 執(zhí)行的性能，遠(yuǎn)好于該句語法在存儲過程內(nèi)執(zhí)行，就有可能是上述原因。
簡單的解法，是在執(zhí)行或創(chuàng)建存儲過程時，搭配 WITH RECOMPILE 選項。
...中間略...
存儲過程的執(zhí)行情境可以分 80-20 定律，若少數(shù)執(zhí)行狀況 AP 自己知道，則 AP 可以判讀是否要下 with recompile 或是撰寫存儲過程直接搭配 Option(Optimize for (參數(shù)定義))
但在一些狀況，例如使用者下 Range 查詢，或是「財務(wù)滾算」數(shù)據(jù)，會大量刪除、插入中繼表內(nèi)的數(shù)據(jù)，developer 無法預(yù)先評估可能的數(shù)據(jù)量大小，則在存儲過程創(chuàng)建時，直接搭配 with recompile，可得到較穩(wěn)定的執(zhí)行性能。
結(jié)語： 我自己早年寫 AP 時，一直查不到 WITH RECOMPILE 是干麻的，當(dāng)時我寫用來「分頁(換頁)」的存儲過程時 (雙 TOP 夾擊、或 ROW_NUMBER 函數(shù))，就一律加上 WITH RECOMPILE 選項。現(xiàn)在回想起來，其實是不必加的，因為重復(fù)用舊的「執(zhí)行計劃」即可 (可節(jié)省許多數(shù)據(jù)庫伺服器上的資源)，丟入的參數(shù)也都差不多 (用戶目前所在頁數(shù)、每頁要傳回幾條記錄)。
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
相關(guān)文章?:
談一談 SQL Server 中的執(zhí)行計劃緩存
http://www.cnblogs.com/CareySon/archive/2013/05/04/3058592.html
http://www.cnblogs.com/CareySon/archive/2013/05/04/PlanCacheInSQLServerPart2.html
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
