WizardWu 編程網

一位台灣的工程師,接觸 .NET 逾十年,近年研究 SQL Server、Performance Tuning、手機應用

博客园 首页 新随笔 联系 订阅 管理

在 SQL Server 创建或修改「存储过程(stored procedure)」时,可加上 WITH RECOMPILE 选项,但多数文档或书籍都写得语焉不详,或只解释为「每次执行此存储过程时,都要重新编译」。事实上,是指执行此一存储过程时,要强制重新产生「执行计划(execution plan)」,而不要从「缓存(cache)」去取得旧的「执行计划」。

SQL Server 在评估与产生「执行计划」时,非常耗 CPU 资源,因此,如何让其正确地从 cache 中,重复使用旧的「执行计划」就很重要;但是,若误用旧的「执行计划」,导致 SELECT 查询的性能大幅下降,则更得不偿失。

一般的 SQL 查询,两次或多次执行的 SQL 语句中,内容必须完全符合,才能延用旧的「执行计划」,包含: 大小写、换行、空白。如下图 1,因为两次执行的 SQL 语句,差了一个「半形空格」,导致产生了两次「执行计划」,而无法重复使用旧的「执行计划」。


图 1 浪费资源产生了两次「执行计划」

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

若是改用「参数化查询」,如下 :
SELECT * FROM Customers WHERE CustomerID=@CustomerID
即可避免因参数值不同,一直产生新的「执行计划」,亦可避免 SQL Injection 攻击。

而存储过程,相对于一般 SQL 语句,其在性能上的优势,除了已事先编译外,存储过程也可提升「执行计划」的重用性(复用性),避免产生新的「执行计划」、消耗 CPU 资源。如下图 2,两次调用同一个存储过程时,但传入不同的参数,SQL Server 会重复使用同一个「执行计划」,如同上述的「参数化查询」一样,不会浪费资源产生新的「执行计划」。


图 2 「执行计划」被重复使用,避免浪费资源

 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

但若存储过程「数据内容分布不平均」,例如某个 Table,里面有个 Int 类型的字段,大量记录里所存储的值依序为 1~100,但只有某一条记录存的是 10000。亦即符合过滤条件的记录有时极多 (「执行计划」适合用「索引扫描」),但有时符合的只有一两条 (「执行计划」适合用「索引查找」)。而未来在调用此存储过程时,两种情境都有可能出现,因此我们希望此一存储过程,在执行时「不要 cache 执行计划」,亦即让此存储过程在每次执行时,都重新评估、产生最适当的「执行计划」,此时就可加上 WITH RECOMPILE 选项。或者如下图 3,丢给前端应用程序去决定,亦即 AP 在调用此存储过程时,再决定是否加上 WITH RECOMPILE 参数。


图 3

1 Exec select_Proc1 @Key1=5       --自動選用高效能的「執行計畫」
2 Exec select_Proc1 @Key1=10000   --從 cache 延用舊的「執行計畫」,因不適用,反而導致效能不佳
3 Exec select_Proc1 @Key1=10000 WITH RECOMPILE --強制重新產生新的、高效能「執行計畫」
View Code

还有其他进阶的选项应用,像是可以在创建存储过程时,使用 OPTIMIZE FOR 选项,只针对特定某一个参数值来做 cache,来产生固定一种、平均对性能影响最小的「执行计划」,又能避免一直重复产生新的「执行计划」而浪费 CPU 资源。


案例分析 - 同样的语法在存储过程内跑很慢,单独跑很快 (胡百敬, 繁体中文) :
http://byronhu.wordpress.com/2010/07/15/with-recompile/

引用该文部分内容 :

朋友问了一个有趣的问题:同样的语句,在存储过程内跑很慢,单独跑很快。

存储过程会缓存执行计划 (若未加上 WITH RECOMPILE),一般来说可以省掉 CPU 耗费。但若两次执行此存储过程的期间,所引用的记录数量差异很大,则第二次执行时沿用旧的执行计划,性能会变得很差。可以观察以下现象:

  1. 观察执行后的执行计划,传回大量记录却是用「索引查找」。
  2. 透过 Profiler 观察存储过程内的语法,和单独执行的语法,所耗的 IO/CPU/Duration 的数值。若将某句的语法单独拿到 Management Studio 执行的性能,远好于该句语法在存储过程内执行,就有可能是上述原因。

简单的解法,是在执行或创建存储过程时,搭配 WITH RECOMPILE 选项。

...中间略...

存储过程的执行情境可以分 80-20 定律,若少数执行状况 AP 自己知道,则 AP 可以判读是否要下 with recompile 或是撰写存储过程直接搭配 Option(Optimize for (参数定义))

但在一些状况,例如使用者下 Range 查询,或是「财务滚算」数据,会大量删除、插入中继表内的数据,developer 无法预先评估可能的数据量大小,则在存储过程创建时,直接搭配 with recompile,可得到较稳定的执行性能。


结语: 我自己早年写 AP 时,一直查不到 WITH RECOMPILE 是干麻的,当时我写用来「分页(换页)」的存储过程时 (双 TOP 夹击、或 ROW_NUMBER 函数),就一律加上 WITH RECOMPILE 选项。现在回想起来,其实是不必加的,因为重复用旧的「执行计划」即可 (可节省许多数据库伺服器上的资源),丢入的参数也都差不多 (用户目前所在页数、每页要传回几条记录)。

 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

相关文章 :

谈一谈 SQL Server 中的执行计划缓存
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-10-31 20:59  WizardWu  阅读(5129)  评论(4编辑  收藏  举报