SQL Server 利用Profiler观察执行计划是否重用时SP:Cachemiss，SP:CacheInsert以及SP:CacheHit的含义

 

本文出处：http://www.cnblogs.com/wy123/p/6913055.html 

 

 

执行计划的缓存与重用

在通过SQL Profile观察一个SQL语句或者存储过程是否有可用的缓存执行计划的时候，
通过SP:CacheMiss和SP:CacheHit事件可以说明是否发生了编译/重编译和是否重用了缓存的执行计划，
但是对于SP:CacheMiss这一细节，还是存在不少理解错误的情况的，本文通过一个简单的例子来解释说明SP:CacheMiss所表达的真实含义。

简单建个测试表，来测试使用

CREATE TABLE Test
(
    Id INT,
    NAME VARCHAR(100)
)
GO

DECLARE @i INT = 0
WHILE @i<100000
BEGIN
    INSERT INTO Test VALUES(@i,NEWID())
    SET @i = @i+1
END
GO

CREATE INDEX IX_Id ON Test(Id)
GO

 

如何利用Profile观察SQL语句的执行计划的编译/重编译和重用？

　　众所周知的，参数化SQL的执行计划可以缓存并重用，那么就以如下一个简单的参数化SQL为例，观察在传入不同参数时的执行计划重用现象
　　下面是两个参数化的SQL语句，语句的主体都是一样的，只是带入的参数不同，正常情况下，第二句代码是可以重用第一句代码执行之后缓存的执行计划的。

　　至于怎么开Profile就不说了，基础问题。

 

SP:CacheInsert和SP:CacheHit

　　执行上述代码，如下

DBCC FREEPROCCACHE
GO

EXEC SP_EXECUTESQL N'SELECT * FROM [dbo].[Test] WHERE Id = @id', N'@id INT',@id =9999
GO

EXEC SP_EXECUTESQL N'SELECT * FROM [dbo].[Test] WHERE Id = @id', N'@id BIGINT',@id =201
GO

　　得到的Profile中跟踪的信息如下

　　简单说明一下，执行第一句代码之前，是清空了执行计划缓存的，所以第一次执行的sql语句是没有执行计划缓存可用的。

　　参考截图。

　　首先看第一次执行的情况：
　　　　上面说了，执行第一句代码之前，是清空了执行计划缓存的，所以第一次执行的sql语句是没有执行计划缓存可用的。
　　　　因此第一句SQL代码是需要编译的（不是重编译），
　　　　参数化SQL编译之后要缓存起来（不同参数可以重用执行计划），表现就是有一个SP:CacheInsert。
　　　　SP:CacheInsert的意思是当前SQL编译，生成了一个执行计划，并且向缓存中插入一个执行计划缓存。
　　然后看第二次执行的情况：
　　　　在第二句SQL执行的时候，因为第一句SQL已经编译并且缓存了一个执行计划，
　　　　因此当前SQL是不需要编译，并且可以重用已有的执行计划的。
　　　　这个就表现在有一个SP：CacheHit，
　　　　SP:CacheHit的意思就是，当前SQL的执行计划在缓存的执行计划中命中。

 

为什么第一次和第二次都会出现一个“SP:CacheMiss”事件

　　为什么第一次和第二次都会出现一个“SP:CacheMiss”事件？
　　上面不是说第二个SQL的执行已经重用了第一次的执行计划了吗，为什么还会出现“SP:CacheMiss”事件？
　　“SP:CacheMiss”事件到底代表什么含义？
　　原因就是：
　　SQL Server在缓存执行计划的时候，只缓存SQL语句本身，而不缓存执行的语句。
　　听起来这么别扭，还是以实例来说明吧，参考下图，缓存就是换成的语句本身，而不是整个执行的字符串信息
　　因此“SP:CacheMiss”代表的是：“EXEC SP_EXECUTESQL N'SELECT * FROM [dbo].[Test] WHERE Id = @id', N'@id INT',@id =8888”这个字符串。

　　

 　 关于上述结论再举一例：

　  如果上述实例不足以说明问题的话，再举一个例子，这里创建一个没有任何参数和动态SQL的存储过程（不会导致每次执行都重新编译，执行计划会重用），
　  正常情况下，该存储过程第一次执行之后，执行计划会被缓存起来，第二次执行的时候就可以重用第一次的执行计划。

　　

　  这里情况缓存的执行计划，连续执行两次EXEC TestPlanCache，观察Profile中的现象，是不是还是每次执行仍有有一个SP:CacheMiss事件？
　  为什么，难道说还是因为没有缓存可用吗？
　  肯定不是，第一次执行仍旧是有一个SP:CacheInsert，第二次仍旧是SP:CacheHit，跟上述发生执行计划重用的现象一致。
　  结论仍旧一样，与上面所述类似，SQL Server不会缓存EXEC TestPlanCache这个文本本身，缓存的执行计划是内部的SQL语句的执行计划，
　  SP:CacheMiss是针对EXEC TestPlanCache这个命令来说的，SQL Server是不缓存调用存储过程或者参数化SQL的命令本身的。

 　 

总结：

有人在观察执行计划缓存与重用的时候，尤其是存储过程或者sp_executesql执行的参数化SQL语句，
会发现不停地出现SP:CacheMiss事件，就武断地断定为发生了编译/重编译，
编译与重编译是针对SQL语句或者存储过程中的SQL来说的，而不是针对调用存储过程或者参数化SQL的命令本身来说的。
SQL Server自身不会缓存调用存储过程或者参数化SQL的命令本身，因此会经常发现SP:CacheMiss事件。
对于SQL语句的计划缓存，如果是第一次编译，会缓存起来，缓存的时候就会出现SP:CacheInsert，第二次或者以后重用这个计划，就是出现SP:CacheHit事件。