在和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 cpcross apply sys.dm_exec_sql_text(plan_handle) stcross apply sys.dm_exec_query_plan(plan_handle) qpORDER 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=3SELECT * 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

照例要上妹子一張，養眼和招狼：