34,590
社区成员
发帖
与我相关
我的任务
分享
dbcc freeproccache
select * from table1 where id=1
select * from table1 where id=2
select cacheobjtype,objtype,usecounts,sql from sys.syscacheobjects where dbid=DB_ID('dbtest1')
cacheobjtype objtype usecounts sql
----------------- -------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Compiled Plan Adhoc 1 select cacheobjtype,objtype,usecounts,sql from sys.syscacheobjects where dbid=DB_ID('dbtest1')
Compiled Plan Adhoc 1 select * from table1 where id=1
Compiled Plan Adhoc 1 select * from table1 where id=2
(3 行受影响)
--直接拼sql的方式,生成的是Adhoc计划,没问题
--第二次测试,开启强制化参数
--下面三个sql分别执行一次,生成了一个Prepared类型的计划,并且重用了三次,可以理解,
--是强制化参数的结果
--但是这三句sql为什么还有三个Adhoc类型的计划生成,
--所以不太理解sql执行过程中,执行计划的产生过程
select * from table1 where id=1
select * from table1 where id=2
select * from table1 where id=3
select cacheobjtype,objtype,usecounts,sql from sys.syscacheobjects where dbid=DB_ID('dbtest1')
cacheobjtype objtype usecounts sql
----------------- -------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Compiled Plan Adhoc 4 select cacheobjtype,objtype,usecounts,sql from sys.syscacheobjects where dbid=DB_ID('dbtest1')
Compiled Plan Prepared 1 (@0 varchar(8000))select cacheobjtype , objtype , usecounts , sql from sys . syscacheobjects where dbid = DB_ID ( @0 )
Compiled Plan Adhoc 1 select * from sys.syscacheobjects where dbid=DB_ID('dbtest1')
Compiled Plan Adhoc 1 select * from table1 where id=1
Compiled Plan Prepared 1 (@0 varchar(8000))select * from sys . syscacheobjects where dbid = DB_ID ( @0 )
Compiled Plan Adhoc 1 select * from table1 where id=2
Compiled Plan Adhoc 1 select * from table1 where id=3
Compiled Plan Prepared 3 (@0 int)select * from table1 where id = @0
(8 行受影响)