专注,勤学,慎思。戒骄戒躁,谦虚谨慎

just do it

导航

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事件。

 

posted on 2017-05-27 15:52  MSSQL123  阅读(875)  评论(0编辑  收藏  举报