在和SQLPass討論adhoc和Prepare時，有各自不同的觀點(diǎn)，我來發(fā)表下我的理解，不對之處，敬請指出！

Adhoc(即席查詢):沒有參數(shù)化的查詢計劃會被標(biāo)記為adhoc,adhoc不能理解為該執(zhí)行計劃不會被重用。

Prepared(預(yù)定義):查詢中使用到參數(shù)的執(zhí)行計劃會被標(biāo)記為Prepared.

在后續(xù)測試中，每次測試之前需要清除執(zhí)行計劃：

--清理執(zhí)行計劃DBCC FREEPROCCACHE

測試語句執(zhí)行結(jié)束后需要使用以下語句來查看執(zhí)行計劃：

--查看執(zhí)行計劃select cp.usecounts as '使用次數(shù)',cp.cacheobjtype as '緩存類型',cp.objtype as [對象類型],st.text as 'TSQL',--cp.plan_handle AS '執(zhí)行計劃',qp.query_plan as '執(zhí)行計劃',cp.size_in_bytes as '執(zhí)行計劃占用空間(Byte)'           from sys.dm_exec_cached_plans cpcross apply sys.dm_exec_sql_text(plan_handle) stcross apply sys.dm_exec_query_plan(plan_handle) qpORDER BY[對象類型]

測試1:簡單查詢

--執(zhí)行兩遍SELECT *FROM [TestDB].[dbo].[TB1] WHERE ID=3

執(zhí)行結(jié)果：

可以看到,生成了一個Adhoc執(zhí)行計劃和一個Prepared執(zhí)行計劃,其中Adhoc執(zhí)行計劃被執(zhí)行兩次，證明Adhoc執(zhí)行計劃也是可以被重用的，而Prepared執(zhí)行計劃是由于“簡單參數(shù)化”的原因生成的。

（PS:在該場景中，Adhoc執(zhí)行計劃最終使用的是Prepared執(zhí)行計劃來執(zhí)行的，因此可以發(fā)現(xiàn)Prepared的執(zhí)行計劃占用的空間更多一些）

測試2：使用sp_executesql來實(shí)現(xiàn)參數(shù)化查詢

--執(zhí)行兩遍EXEC sp_executesql N'SELECT *FROM [TestDB].[dbo].[TB1] WHERE ID=@ID',N'@ID INT',@ID=2

執(zhí)行結(jié)果：可以看到在TSQL列里有明顯的參數(shù)，因此該執(zhí)行計劃被標(biāo)記為Prepared，同時該計劃被執(zhí)行兩遍

測試3：使用sp_executesql來實(shí)現(xiàn)非參數(shù)化查詢

--執(zhí)行兩遍EXEC sp_executesql N'SELECT *FROM [TestDB].[dbo].[TB1] WHERE ID=3'

執(zhí)行結(jié)果：

可以看到，即使使用sp_executesql，但由于TSQL里沒有使用參數(shù)，因此執(zhí)行計劃仍然被標(biāo)記為Adhoc。

測試4：使用sp_executesql來實(shí)現(xiàn)混合查詢

--執(zhí)行兩遍EXEC sp_executesql N'SELECT *FROM [TestDB].[dbo].[TB1] WHERE ID=3 AND C1=@C1',N'@C1 INT',@C1=3

執(zhí)行結(jié)果：

可以發(fā)現(xiàn)，只有含有一部分的參數(shù)，執(zhí)行計劃就會被標(biāo)記為Prepared

測試5：使用sp_executesql來實(shí)現(xiàn)混合查詢2

--執(zhí)行兩遍EXEC sp_executesql N'SELECT *FROM [TestDB].[dbo].[TB1] WHERE ID=3',N'@C1 INT',@C1=3

執(zhí)行結(jié)果在上面的測試中，查詢根本沒有使用到參數(shù)C1，但是由于整個查詢里有參數(shù)，所以仍被標(biāo)記為Prepared。

綜上所述，只有查詢計劃里有參數(shù)，執(zhí)行計劃就標(biāo)記為Prepared，如果沒有參數(shù)，就會標(biāo)記為Adhoc.

SQL SERVER 會在兩個環(huán)節(jié)考慮是否有可重用執(zhí)行計劃

1>在解析SQL語句之前，對SQL語句進(jìn)行hash的到一個key，使用這個key去查找是否存在現(xiàn)成的執(zhí)行計劃；

2>將SQL解析成語法樹后，再使用語法樹的hash key去尋找是否存在現(xiàn)成的執(zhí)行計劃。

為證明上述觀點(diǎn)，我們做以下測試：

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

測試結(jié)果：

兩條語句中有一個空格的差別，因此會生成兩個adhoc執(zhí)行計劃，但是只會生成一個Prepared執(zhí)行計劃，表明這兩個Adhoc執(zhí)行計劃最終都使用該P(yáng)repeared的執(zhí)行計劃。Adhoc執(zhí)行計劃會調(diào)用Prepared執(zhí)行計劃，但Prepared執(zhí)行計劃不會調(diào)用Adhoc執(zhí)行計劃，這是兩者的另一區(qū)別。

誤區(qū)：Adhoc會導(dǎo)致重編譯，Adhoc就是影響性能，就是需要把Adhoc查詢改成Prepared查詢

這個是初學(xué)者很容易犯的誤區(qū)，容易把問題一刀切，由于我們需要在查詢里使用到不同的變量，如"WHERE ID=1"和"WHERE ID=2"這樣的語句，會生成不同的adhoc的執(zhí)行計劃，每個執(zhí)行計劃生成會消耗CPU資源，并需要占用buffer pool里的內(nèi)存，當(dāng)頻繁執(zhí)行這些類似但又不相同的SQL語句時，就會浪費(fèi)大量的資源，因此需要將之參數(shù)化，共用一個執(zhí)行計劃，尤其在執(zhí)行復(fù)雜SQL(如四五個表做連接查詢)時，查詢優(yōu)化器需要分析生成很多執(zhí)行計劃并選擇一種比較合理的執(zhí)行計劃來執(zhí)行，消耗很多CPU資源并延長總的SQL執(zhí)行時間，共用一個執(zhí)行計劃會大大提升系統(tǒng)性能。

當(dāng)然，參數(shù)化也有其切點(diǎn)，在數(shù)據(jù)分布不均或參數(shù)變動對查詢影響巨大的情況下，參數(shù)化反而會導(dǎo)致系統(tǒng)異常，如果“WHERE ID>@ID”語句，當(dāng)ID=10000000時返回一條數(shù)據(jù)，而當(dāng)ID=1是返回10000000條數(shù)據(jù)，前者適合索引查找，后者適合全表掃描，如果兩者使用同一個執(zhí)行計劃，并會導(dǎo)致系統(tǒng)性能嚴(yán)重下降，此時Adhoc反而更適合。

此外，還有一種情況，當(dāng)查詢語句特別簡單，簡單到編譯幾乎不消耗資源時，SQL SERVER會選擇不保存這些語句的執(zhí)行計劃。

在分析執(zhí)行計劃問題時，需要考慮以下問題：

1>系統(tǒng)是否有過多的adhoc執(zhí)行計劃占用大量內(nèi)存

2>這些adhoc的執(zhí)行頻率和相似度

3>是否可以改寫這些adhoc執(zhí)行計劃的SQL

4>是否可以使用'optimize for ad hoc workloads'來優(yōu)化

5>是否可以使用'強(qiáng)制參數(shù)化'

推薦閱讀：http://www.cnblogs.com/TeyGao/p/3526804.html

照例要上妹子一張，養(yǎng)眼和招狼：