[SQL Server 2005/2008]重用执行计划(存储过程性能优化)

比眉伴天荒 2022-07-12 11:49 185阅读 0赞

源自《Microsoft SQL Server 2005技术内幕:T-SQL程序设计》7.4 编译、重新编译和重用执行计划

**      语句或存储过程第1次执行时，会生成一个经时“查询优化器”优化后的执行计划，默认情况下，还将会缓存这次的执行计划； 下次执行时将直接使用缓存的执行计划，而且不检查这样做是否合适。**

\-- 存在2个测试表: t\_bak\_20100520（inttime int, desc varchar(50)）,inttime字段上建聚集索引，1000行数据；另外一个表t\_bak\_20100520\_bak，和上面那个表结构和表数据，及索引完全一致。

\-- 测试存储过程1(整个存储过程不缓存执行计划,每次执行时生成新的查询计划)

create proc p\_test\_detail1(@i\_time int) withrecompile

select \* from dbo.t\_bak\_20100520 where inttime >= @i\_time;

select \* from dbo.t\_bak\_20100520\_bak where inttime >= @i\_time;

\-- 测试存储过程2(缓存存储过程中，第二个查询语句的计划，第一个语句每次都重新生成计划)

create proc p\_test\_detail2 (@i\_time int)

select \* from dbo.t\_bak\_20100520 where inttime >= @i\_time option(recompile);

select \* from dbo.t\_bak\_20100520\_bak where inttime >= @i\_time;

\------ 测试内容和结果------------------------------------------------------------------------------------

SET STATISTICS IO   ON;   -- 查询结束后，显示io统计信息

SET STATISTICS TIME ON;   -- 查询结束后，显示cpu统计信息

exec p\_test\_detail1 145500

exec p\_test\_detail2 145500

exec p\_test\_detail1 700

exec p\_test\_detail2 700

(13 行受影响) 表't\_bak\_20100520'。        扫描计数 1，逻辑读取 15 次，...省略..       --生成查询计划是"索引扫描",不缓存查询计划  
(13 行受影响) 表't\_bak\_20100520\_bak'。扫描计数 1，逻辑读取 15 次，...省略同上.. --生成查询计划是"索引扫描",不缓存查询计划

(13 行受影响) 表't\_bak\_20100520'。        扫描计数 1，逻辑读取 15 次，...省略同上.. --生成查询计划是"索引扫描",不缓存查询计划  
(13 行受影响) 表't\_bak\_20100520\_bak'。扫描计数 1，逻辑读取 15 次，...省略同上.. --生成查询计划是"索引扫描",缓存查询计划

(595行受影响) 表't\_bak\_20100520'。        扫描计数 1，逻辑读取 740 次，...省略..    --生成查询计划是"全表扫描"

(595行受影响) 表't\_bak\_20100520\_bak'。扫描计数 1，逻辑读取 740 次，...省略..    --生成查询计划是"全表扫描"

(595行受影响) 表't\_bak\_20100520'。        扫描计数 1，逻辑读取 740次，...省略..    --生成查询计划是"全表扫描"

(595行受影响) 表't\_bak\_20100520\_bak'。扫描计数 1，逻辑读取 1121 次,...省略..    --引用缓存的查询计划"索引扫描"

**       存储过程执行时，默认会缓存第一次执行时，最优的执行计划。以后再次执行该存储过程，则直接使用缓存中的执行计划。除非发现存储过程执行环境发生变化，要重新编译，并重新生成查询计划。(如引用的表结构变化了,索引变化了,SET选项（如ANSI\_NULLS、CONCAT\_NULL\_ YIELDS\_NULL等）变化了，或创建存储过程时，指定了要重新生成查询计划。“第一次执行”也包括因一定时间内没有重用或其他原因从缓存中移除计划之后的第一次执行。**

\-- 查询系统中缓存的执行计划

selectcacheobjtype, objtype, usecounts, sql

fromsys.syscacheobjects

wheresql not like '%cache%'

andsql like '%p\_test\_detail%';