执行计划--Adhoc和Prepare

在和SQLPass讨论adhoc和Prepare时，有各自不同的观点，我来发表下我的理解，不对之处，敬请指出！

Adhoc(即席查询):没有参数化的查询计划会被标记为adhoc,adhoc不能理解为该执行计划不会被重用。

Prepared(预定义):查询中使用到参数的执行计划会被标记为Prepared.

在后续测试中，每次测试之前需要清除执行计划：

--清理执行计划
DBCC FREEPROCCACHE

测试语句执行结束后需要使用以下语句来查看执行计划：

--查看执行计划
select cp.usecounts as '使用次数',cp.cacheobjtype as '缓存类型',
cp.objtype as [对象类型],
st.text as 'TSQL',
--cp.plan_handle AS '执行计划',
qp.query_plan as '执行计划',
cp.size_in_bytes as '执行计划占用空间(Byte)'           
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(plan_handle) st
cross apply sys.dm_exec_query_plan(plan_handle) qp
ORDER BY[对象类型]

 

测试1:简单查询

--执行两遍
SELECT *FROM [TestDB].[dbo].[TB1] WHERE ID=3

执行结果：

可以看到,生成了一个Adhoc执行计划和一个Prepared执行计划,其中Adhoc执行计划被执行两次，证明Adhoc执行计划也是可以被重用的，而Prepared执行计划是由于“简单参数化”的原因生成的。

（PS:在该场景中，Adhoc执行计划最终使用的是Prepared执行计划来执行的，因此可以发现Prepared的执行计划占用的空间更多一些）

测试2：使用sp_executesql来实现参数化查询

--执行两遍
EXEC sp_executesql N'SELECT *FROM [TestDB].[dbo].[TB1] WHERE ID=@ID',N'@ID INT',@ID=2

执行结果：

可以看到在TSQL列里有明显的参数，因此该执行计划被标记为Prepared，同时该计划被执行两遍

测试3：使用sp_executesql来实现非参数化查询

--执行两遍
EXEC sp_executesql N'SELECT *FROM [TestDB].[dbo].[TB1] WHERE ID=3'

执行结果：

可以看到，即使使用sp_executesql，但由于TSQL里没有使用参数，因此执行计划仍然被标记为Adhoc。

测试4：使用sp_executesql来实现混合查询

--执行两遍
EXEC sp_executesql N'SELECT *FROM [TestDB].[dbo].[TB1] WHERE ID=3 AND C1=@C1',N'@C1 INT',@C1=3

执行结果：

可以发现，只有含有一部分的参数，执行计划就会被标记为Prepared

测试5：使用sp_executesql来实现混合查询2

--执行两遍
EXEC sp_executesql N'SELECT *FROM [TestDB].[dbo].[TB1] WHERE ID=3',N'@C1 INT',@C1=3

执行结果

在上面的测试中，查询根本没有使用到参数C1，但是由于整个查询里有参数，所以仍被标记为Prepared。

综上所述，只有查询计划里有参数，执行计划就标记为Prepared，如果没有参数，就会标记为Adhoc.

SQL SERVER 会在两个环节考虑是否有可重用执行计划

1>在解析SQL语句之前，对SQL语句进行hash的到一个key，使用这个key去查找是否存在现成的执行计划；

2>将SQL解析成语法树后，再使用语法树的hash key去寻找是否存在现成的执行计划。

为证明上述观点，我们做以下测试：

SELECT *FROM [TestDB].[dbo].[TB1]  WHERE ID=3
SELECT * FROM [TestDB].[dbo].[TB1]  WHERE ID=3

测试结果：

两条语句中有一个空格的差别，因此会生成两个adhoc执行计划，但是只会生成一个Prepared执行计划，表明这两个Adhoc执行计划最终都使用该Prepeared的执行计划。
Adhoc执行计划会调用Prepared执行计划，但Prepared执行计划不会调用Adhoc执行计划，这是两者的另一区别。

  

误区：Adhoc会导致重编译，Adhoc就是影响性能，就是需要把Adhoc查询改成Prepared查询

这个是初学者很容易犯的误区，容易把问题一刀切，由于我们需要在查询里使用到不同的变量，如"WHERE ID=1"和"WHERE ID=2"这样的语句，会生成不同的adhoc的执行计划，每个执行计划生成会消耗CPU资源，并需要占用buffer pool里的内存，当频繁执行这些类似但又不相同的SQL语句时，就会浪费大量的资源，因此需要将之参数化，共用一个执行计划，尤其在执行复杂SQL(如四五个表做连接查询)时，查询优化器需要分析生成很多执行计划并选择一种比较合理的执行计划来执行，消耗很多CPU资源并延长总的SQL执行时间，共用一个执行计划会大大提升系统性能。

当然，参数化也有其缺点，在数据分布不均或参数变动对查询影响巨大的情况下，参数化反而会导致系统异常，如果“WHERE ID>@ID”语句，当ID=10000000时返回一条数据，而当ID=1是返回10000000条数据，前者适合索引查找，后者适合全表扫描，如果两者使用同一个执行计划，并会导致系统性能严重下降，此时Adhoc反而更适合。

此外，还有一种情况，当查询语句特别简单，简单到编译几乎不消耗资源时，SQL SERVER会选择不保存这些语句的执行计划。

  

在分析执行计划问题时，需要考虑以下问题：

1>系统是否有过多的adhoc执行计划占用大量内存

2>这些adhoc的执行频率和相似度

3>是否可以改写这些adhoc执行计划的SQL

4>是否可以使用'optimize for ad hoc workloads'来优化

5>是否可以使用'强制参数化'

推荐阅读：http://www.cnblogs.com/TeyGao/p/3526804.html

***************转摘：https://www.cnblogs.com/TeyGao/p/3531911.html