存储过程与缓存

提升SQL Server最具性能的一个方面就是存储过程，SQL Server具备执行计划的缓存功能，以便计划重用。SQL Server2000增强了ad-hoc执行计划的缓存功能，就处理存储过程上性能最佳，其原因由于存储过程是作为数据库对象来使用;不过，存储过程的使用不当也必然导致缓存执行计划在初始查询时丢失，当然也会导致存储过程的重编译，因而带来不必要的性能损失。本文主要介绍以下几点：

过程缓存
用于测试的DBCC命令
syscacheobjects系统表
使用Profiler捕获重用的执行计划
存储过程编译与执行计划重用
使用sp_的问题
不指定owner的问题
编码不当产生的重编译

过程缓存

过程缓存占据着SQL Server内存池的主要部分，早在SqL Server 7.0时，内存池中的对象由SqL Server动态分配，DBA无法为过程缓存指定内存配置，SQL Server为根据系统使用情况自我调节来确保缓存的命中率。很明显，SQL Server可用的内存越多，其内存池相应也越大，其缓存组件也就越大，下图列出了SQL Server内存池的各部分占用情况：

图 1: SQL Server 内存池组件

如果SQL Server使用动态内存配置，则它会同操作系统进行交互以请求所需要的内存，相应也会释放一些内存供其它进程使用。这必将会影响不同内存组件的缓存使用。例如，在一台SQL Server上运行一些存储过程，然后检查缓存的执行计划和是否重用。启动占用内存比较大的程序，回过后来再来检查缓存时会发现，之前缓存的计划已经从内存中删除。再次执行存储过程时，在Profiler中可以看到Cache Miss事件，其原因主要由于先前缓存的执行计划已经不再可用。

DBCC DROPCLEANBUFFERS 与 DBCC FREEPROCCACHE

在生产环境中，通常不建议对buffers和缓存进行修改，原因会导致性能的极大损失。然而，微软提供了两个DBCC命令，方便我们无需停止和重启SQL Server服务就可以删除缓存的数据和计划缓存，当然这两个命令也存在性能的差异，下面我们来逐一介绍：

DBCC DROPCLEANBUFFERS

此命令用于清除数据缓存，该命令需要有sysadmin组权限。

DBCC FREEPROCCACHE

此命令用于清空过程缓存。通过SQL Profiler可以看到Cache Remove事件的存在。当使用此命令回到未缓存状态。在存储过程第一次执行和之后执行时性能上存在着极大差异，由于在执行之前会执行编译过程。

syscacheobjects

系统表syscacheobjtects（SQL Server 2005中为sys.dm_exec_cached_plans）存储了缓存执行计划的信息，这里我主要介绍以下几列：

列名	描述
cacheobjtype	这个是缓存对象的类型，这里主要介绍以下两个： Compiled Plan （编译计划） Executable Plan （执行计划）
objtype	这个是对象类型，由于本文介绍存储过程，此类型为Proc
objid	这个与*sysobjects*表中的id字段相对应（ad-hoc或prepared查询除外）即存储过程的名称
dbid	由于objid参照*sysobjects*的id，dbid即对应数据库标识。
uid	Uid对应用户标识
sql	Sql执行语句或存储过程名称（无参数）

通过查询该系统表可以校验计划是否被缓存。缓存中存储的执行计划只能通过该表来查询，它一方面使我们找出缓存了哪些计划，另一方面使我们对缓存的工作模式有了更多了解，对于更多的信息，需要使用Profiler来获取执行计划的信息。

Profiler模板配置

在SQL Server 2000中，Profiler初始为我们提供了关于存储过程的缓存和编译的事件，启动默认的模板，我们只需添加SP:Cache事件、SP:ExecContextHit事件和SP:Recopile事件。由于是在本地上运行SQL Server，这里移除了RPC:Completed事件，添加SP:Completed事件，若要查看当缓存丢失和重编译事件，也需要添加SP:Starting和SP:Stmt事件。

编译与执行计划

当存储过程首次被调用时，则会生成其执行计划，这也就是编译执行计划的含义，不过这与VB语言或即时编译（just-in-time）及其他语言如Java不同，SQL Server将存储过程构建一个中间阶段－执行计划。从执行计划中可以知道哪些索引可以使用，以并行执行时需要分哪些步骤做等等。

存储过程第一次调用时，SQL Server需要在过程缓存中（更高层次上）查看是否已存在该执行计划。由于存储过程是首次调用，并没有找到相应的执行计划。SQL Server的编译进程则处于准备阶段，然后对该存储过程发出一个[COMPILE]锁，当然也会在过程缓存中进行搜索，以找到与该对象对应的执行计划。正因为是首次执行，SQL Server也会由于找不到匹配的执行计划，而编译生成新的执行计划，并将其放入过程缓存中执行。

那么第二次执行会怎样？当存储过程再次调用时，首先会检查过程缓存中是否有对应的执行计划，这与先前第一次调用时所做的一样，若是对于指定了引用存储过程的数据库和拥有者则会在缓存中很快找到相匹配的执行计划，而无需重编译或重新生成新的执行计划。若是找不到匹配的执行计划，SQL Server会再次对调用的存储过程执行[COMPILE]锁，接着是一系列的缓存搜索等操作。Lazywriter会负责执行计划在内存中停留的时间量，确保了经常使用的执行计划在过程缓存中的时间会更长。

使用 sp_带来的问题

SQL Server中以sp_打头的存储过程默认为系统存储过程，这些存储过程默认应存储在master数据库中，不过也有一些开发人员选择sp_作为存储过程的命名前缀，而这些存储过程位于用户创建的数据库中。使用存储在非master数据库以sp_命名的存储过程所引发的问题是会产生一个缓存丢失事件，即每次调用存储过程时会执行[COMPILE]锁，随后则是一系列的缓存搜索，其原因是由于SQL Server处理以sp_打头的存储过程方式不同，并不关心其在过程缓存。

以下是SQL Server处理sp_存储过程的步骤：

In the master database.
Based on any qualifiers (database and/or owner).
Owned by dbo in thr current database in the absence of any qualifiers.

既使sp_存储过程的owner符合要求，SQL Server首先仍在master中查找，当在检查过程缓存时，在master数据库上会扫描执行计划，因而产生一个SP:CacheMiss事件，以下是调用第二次sp_存储过程时的SQL Profiler（这里添加了SP:StmtStarting和SP:StmtCompleted用来说明当存储过程首次执行时SP:CacheMiss事件的产生）

注意最开始的SP:Cache Miss事件，此事件的产生是由于存储过程sp_CacheMiss不在master数据库，从而,SQL Server执行[COMPILE]锁以及一系列的缓存搜索，在二次的缓存搜索时，SQL Server可以找到其对应的执行计划（即SP:ExecContextHit事件的出现），此时不需要额外的时间和资源来查找执行计划，不过应当注意的是[COMPILE]锁是排它锁。在重编译时，存储过程则是以有序进行，必然造成系统性能的降低。

正是由于SQL Server查询sp_命名的存储过程的方式，所以建议选择其他命名方法，例如usp_、proc_等。

指定Owner

SQL Server为我们提供了许多灵活性，但是需要我们合理地使用来确保产生不必要的性能问题，其中一个方面就是命名数据库对象。当调用一个存储过程时，SQL Server会查看是否指定了该对象的owner，若未指定，则会执行过程缓存初始化搜索，以查找满足与该调用者匹配的存储过程。因此，假如我们使用非dbo用户，如SQLUser，而存储过程属于dbo，我们会发现仍得到一个SP:CacheMiss事件，情况和在用户数据库中调用sp_存储过程一样。下面是未指定owner时产生SP:Cache Miss事件的一个Profiler例子：

从图中注意到该存储过程的执行方式并未指定owner，若以普通用户身份登录到SQL Server，则会扫描过程缓存并查找属于该owner的存储过程usp_CacheHit，SP:CacheMiss事件由此而来。若我们使用两部分命名方式显式指定owner，则直接得到SP:ExecContextHit事件，这表明未执行[COMPILE]锁和过程缓存的二次扫描操作。以下是指定owner的跟踪：

在调用存储过程之前的一点不同就是添加”dbo.”，不过我们常常匆略了这一点，建议养成添加“dbo.”习惯，一般地，我们以dbo的身份创建存储过程，但是若以非dbo身份创建，则需要指定owner，否则会产生SP:Cache Miss事件，由此产生来的性能前面已经讨论。

重编译问题

通常看到存储过程发生重编译有几种原因，少数的重编译未必是坏事。例如，某表中数据的更改，由于数据的实时性，先前的执行计划效率将会降低，必然需要重编译;另外一种情况是手动执行sp_recompile存储过程来强制存储过程的重编译，或者以WITH RECOMPILE选项来执行存储过程。

DML和DDL混合

存储过程内部将DDL（数据定义语言）和DML（数据操作语言）混合交错执行，也将导致重编译，甚至在执行过程中也会发生重编译，例如：让我们通过以下存储过程示例来说明：

CREATE PROC usp_Build_Interleaved 
AS 

-- DDL 定义
CREATE TABLE A ( 
CustomerID nchar(5) NOT NULL CONSTRAINT PK_A PRIMARY KEY CLUSTERED, 
CompanyName nvarchar(40) NOT NULL, 
City nvarchar(15) NULL, 
Country nvarchar(15) NULL) 

-- DML 定义
INSERT A 
SELECT CustomerID, CompanyName, City, Country 
FROM Customers 

-- DDL 定义
CREATE TABLE B ( 
OrderID int NOT NULL CONSTRAINT PK_B PRIMARY KEY NONCLUSTERED, 
CustomerID nchar(5) NOT NULL, 
Total money NOT NULL) 

-- DML 定义
INSERT B 
SELECT O.OrderID, O.CustomerID, SUM((OD.UnitPrice * OD.Quantity) * (1 - OD.Discount)) 
FROM Orders O JOIN [Order Details] OD ON O.OrderID = OD.OrderID 
GROUP BY O.OrderID, O.CustomerID 

CREATE CLUSTERED INDEX IDX_B_CustomerID ON B (CustomerID)

如上所示的，DML和DDL语句交错执行，要看其实际的效果，下面是使用Profiler捕获的结果：

如上图所示的SP:ExecContextHit事件，已经产生了一个缓存执行计划。不过，由于DDL与DML的交错执行，在存储过程执行过程中产生了两个SP:Recompile事件。由于示例中的存储过程同时含有DML和DDL语句，必然不能够一起移除重编译，但是，可以通过将所有的DDL语句放在存储过程的顶部的方法可以将2次重编译减少到1次重编译。

CREATE PROC usp_Build_NoInterleave 
AS 

-- DDL 
CREATE TABLE A ( 
CustomerID nchar(5) NOT NULL CONSTRAINT PK_A PRIMARY KEY CLUSTERED, 
CompanyName nvarchar(40) NOT NULL, 
City nvarchar(15) NULL, 
Country nvarchar(15) NULL) 

CREATE TABLE B ( 
OrderID int NOT NULL CONSTRAINT PK_B PRIMARY KEY NONCLUSTERED, 
CustomerID nchar(5) NOT NULL, 
Total money NOT NULL) 

-- DML 
INSERT A 
SELECT CustomerID, CompanyName, City, Country 
FROM Customers 

INSERT B 
SELECT O.OrderID, O.CustomerID, SUM((OD.UnitPrice * OD.Quantity) * (1 - OD.Discount)) 
FROM Orders O JOIN [Order Details] OD ON O.OrderID = OD.OrderID 
GROUP BY O.OrderID, O.CustomerID 

CREATE CLUSTERED INDEX IDX_B_CustomerID ON B (CustomerID)

通过重写存储过程，我们看到两个CREATE TABLE语句都位于前面，仅在这两个表创建后执行DML语句来填充数据和创建聚集索引，运行跟踪，我们只看到一个SP:Recompile事件：

我们并不能完全移除SP:Recompile事件，但是可以重写存储过程的方式来减少重编译的次数。

使用 sp_executesql

通常在存储过程内部不使用sp_executesql系统存储过程，不过，此方法也可以解决一些重编译问题。使用sp_executesql和EXECUTE语句的主要问题是基于安全上的考虑，若使用存储过程来限制数据库的访问，使用sp_executesql会带来一些麻烦。

但是，若使用sp_executesql或EXECUTE语句传递SQL串来执行，SQL Server则自动检查其安全性，如果使用sp_executesql来从表中获取数据，需要授权调用者在该表的SELECT权限。那么为什么要采用sp_executesql呢？下面通过一个例子：

CREATE PROC usp_Display_Recompile
AS
SELECT A.CompanyName, A.City, A.Country, B.OrderID, B.Total
FROM A JOIN B ON A.CustomerID = B.CustomerID

DROP TABLE A

DROP TABLE B

以上提到的一点：如果给定的表数据发生了更改，存储过程显然很容易产生重编译。由于usp_Display_Recompile的数据依赖于先前创建的存储过程，usp_Display_Recompile每次执行时，数据因表删除、重建和重填充而改变，查看Profiler确认重编译事件：

注意到执行SELECT语句，SQL Server执行了一次重编译。若是以sp_executesql执行，可以避免重编译，下面来重写存储过程：

CREATE PROC usp_Display_NoRecompile
AS
EXEC sp_executesql N'SELECT A.CompanyName, A.City, A.Country, B.OrderID, B.Total
FROM A JOIN B ON A.CustomerID = B.CustomerID'

DROP TABLE A

DROP TABLE B

此时的SELECT语句已经在sp_executesql的上下文内，若在两个表上具有SELECT权限，则可以避免存储过程的重编译，通过Profile的跟踪结果可以发现避免了重编译：

与SELECT语句对应是的，产生了一个SP:CacheInsert事件，但是并无SP:Recompile事件的产生，因此使用sp_executesql，可以完全避免重编译。

备注

上面我们简要地介绍了存储过程和缓存，过程缓存是内存缓冲池的主要部件，由SQL Serve动态分配，截至到SQL Server 7.0，仍没有可用的方法来控制缓存大小，不过，SQL Server的内部缓存结构仍保留最经常使用和开销比较低的执行计划驻留内存。可以通过syscacheobjects系统表查看内存中缓存计划，对于更详细的可以通过SQL Profiler来获得。