背景引入

執行計劃快取是SQL Server記憶體管理中非常重要的特性，這篇系列文章我們探討執行計劃快取設計中遇到的single-used plans問題，以及如何發現、如何定性和定量分析single-used plans帶來的影響，最後我們使用兩種方法來解決這個問題。

什麼是Single-used Plans

要解釋清楚什麼是Single-used Plans，首先需要解釋SQL語句執行計劃快取是什麼？SQL Server執行每一條SQL語句之前，會從執行計劃快取記憶體中檢視是否存在本條語句的執行計劃，如果不存在，會將其編譯、選擇最優路徑、生成執行計劃，然後會將執行計劃儲存在一塊專門的記憶體區域中（這塊記憶體叫著執行計劃快取），以供下次該語句執行直接從執行計劃快取中獲取編譯完畢的執行計劃。以此來節約資料庫系統對於查詢語句編譯、生成執行計劃過程的效能開銷，提高SQL語句的執行效率。
而Single-used Plans是指那些第一次執行後被快取起來的執行計劃，而後再也沒有被重複利用過的執行計劃快取。其中ad hoc query（即席查詢）就是典型的single-used plans中的一種。

如何發現Single-used Plans

從上一節我們可以大致知道，single-used plans僅會第一次被使用（從名字也可以很好理解到這一點），所以，實際上single-used plans是對SQL Server記憶體空間和CPU資源的浪費，對資料庫效能有一定的損害。那麼，我們如何來發現single-used plans呢？我們可以使用下面的查詢語句：

USE master
GO

SELECT
    database_name = QUOTENAME(db.name),
    st.text,
    cp.objtype,
    cp.size_in_bytes,
    qp.query_plan,
    cp.cacheobjtype
FROM sys.dm_exec_cached_plans AS cp 
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
    INNER JOIN sys.databases AS db WITH(NOLOCK)
    ON st.dbid = db.database_id
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE cp.cacheobjtype = N`Compiled Plan`
AND cp.objtype IN (N`Adhoc`, N`Prepared`)
AND cp.usecounts = 1
ORDER BY cp.size_in_bytes DESC

我的測試例項展示的查詢結果如下：

執行計劃快取中有多少Single-used Plans

當我們可以有效的發現single-used plans以後，我們可能又會問：到底我的SQL Server資料庫例項中，有多少執行計劃快取是屬於single-used plans型別呢？可以從兩個維度來分析：
Single-used plans記錄總數
Single-used plans總的執行計劃佔用的記憶體空間大小
可以使用以下的查詢語句來回答這個問題。

USE master
GO
SELECT
    COUNT(1) AS [total_plans], 
    CAST(SUM(size_in_bytes)/1024. AS DECIMAL(18,2)) AS [total_size_in_kb], 
    CAST(SUM((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END))/1024. AS DECIMAL(12,2)) AS [total_size_single_used_in_kb], 
    SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [total_plans_count_single_used],
    AVG(usecounts) AS [avg_used_counts], 
    CAST(AVG(size_in_bytes)/1024. AS DECIMAL(12, 2)) AS [avg_used_size_kb]
FROM sys.dm_exec_cached_plans WITH(NOLOCK)

我的測試例項執行結果如下截圖：

從這個結果我們可以得出如下single-used plans的資料：總的single-used plans記錄數為73，記憶體空間佔用大小為13912 KB。

Single-used Plans對效能的影響

就單單一條Single-used Plan來看的話，對資料庫系統的效能影響很小，小到可以忽略的地步，所以，資料庫執行計劃快取中，存在少量的Single-used Plans是很正常的，可以不用太關注。但是，如果Single-used Plans大量存在的話，可能就會對系統帶來比較嚴重的效能影響。

定性分析

定性分析大量Single-used Plans對資料效能的影響，主要體現在以下幾個方面：
總的執行計劃快取利用率不高：因為存在大量不能被重複利用的執行計劃快取，從而拉低了執行計劃快取整體利用率
浪費執行計劃快取中記憶體的開銷：每一條執行計劃快取或多或少會佔用一定的執行計劃快取記憶體空間，大量的Single-used Plans導致了記憶體空間的浪費
導致CPU使用率的上升：每一條SQL語句執行計劃的編譯、最優路徑選擇和執行計劃快取，這些過程都需要消耗系統CPU資源，如果大量存在Single-used Plans，會導致系統CPU使用率的上升。
舉一個最為極端的例子，假設執行計劃快取中儲存的所有執行計劃都是Single-used Plans的話，那麼導致的嚴重後果是：
執行計劃快取利用率就是0%，因為沒有任何的執行計劃被重用
執行計劃快取這種設計就毫無意義，因為快取起來也沒有被重用
浪費執行計劃快取的記憶體開銷和CPU開銷

定量分析

以上是定性分析Single-used Plans對系統效能的影響，那麼到底Single-used Plans達到哪個數量級，佔比多少的時候，我們需要密切關注呢？雖然微軟沒有官方的推薦數字，但是個人比較推薦的兩個數字是2GB和50%，即：所有的Single-used Plans使用的記憶體空間超過2GB或者記憶體空間使用佔比超過50%。當然最終也可能取決於SQL Server可以使用的最大記憶體數量。

USE master
GO

IF OBJECT_ID(`tempdb..#temp`) IS NOT NULL
    DROP TABLE #temp

SELECT 
    objtype AS [cachetype], 
    COUNT(1) AS [total_plans], 
    CAST(SUM(size_in_bytes)/1024. AS DECIMAL(18,2)) AS [total_size_in_kb], 
    CAST(SUM((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END))/1024. AS DECIMAL(12,2)) AS [total_size_single_used_in_kb], 
    SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [total_plans_count_single_used],
    AVG(usecounts) AS [avg_used_counts], 
    CAST(AVG(size_in_bytes)/1024. AS DECIMAL(12, 2)) AS [avg_used_size_kb]
INTO #temp
FROM sys.dm_exec_cached_plans WITH(NOLOCK)
GROUP BY objtype


SELECT 
    [cachetype],
    [total_plans],
    [total_size_in_kb],
    [total_plans_count_single_used],
    [total_size_single_used_in_kb],
    CAST(([total_plans_count_single_used]*1.0 / [total_plans]*1.0) * 100. AS DECIMAL(12, 2)) AS [single_used_plan_count%],
    CAST([total_size_single_used_in_kb]/[total_size_in_kb] * 100. AS DECIMAL(12, 2)) AS [single_used_size%],
    [avg_used_counts],
    [avg_used_size_kb]
FROM #temp
ORDER BY [total_size_single_used_in_kb] DESC


SELECT 
    SUM([total_plans]) AS total_plan_counts,
    SUM([total_size_in_kb]) AS total_plan_size_in_kb,
    SUM([total_plans_count_single_used]) AS [total_plans_counts_single_used],
    SUM([total_size_single_used_in_kb]) AS [total_plan_size_single_used_in_kb],
    CAST(SUM([total_plans_count_single_used]) * 100. / SUM([total_plans]) AS DECIMAL(12, 2)) AS [plan_counts_single_used%],
    CAST(SUM([total_size_single_used_in_kb]) * 100. / SUM([total_size_in_kb]) AS DECIMAL(12, 2)) AS [plan_size_single_used%]
FROM #temp

執行結果如下所示：

從這個執行結果來看，在我的SQL Server測試例項上，single-used plans佔用的執行計劃記錄數為72條，記憶體空間佔用14016 KB；single-used plans執行計劃記錄數佔總的百分比為39.78%，記憶體空間佔用比例為50.59%。

解決Single-used Plans問題

從定量分析小結，發現我的測試例項，single-used plans佔用執行計劃快取記憶體比例已經超過50%了，是解決single-used plans問題的時候到了。

清空Single-used Plans

第一種方法是手動清理single-used plans，當然，你也可以很暴力的將所有執行計劃快取清空，從而也就清理了single-used plans，但是這樣會誤殺很多有用的無辜的執行計劃快取，從而導致這些執行計劃重編譯，影響效能。我們推薦使用下面的方法，僅清空哪些single-used plans，方法如下：

USE master
GO

DECLARE
    @plan_handle varbinary(64)
;

DECLARE
     cur_single_used_plan_handle CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
SELECT cp.plan_handle
FROM sys.dm_exec_cached_plans AS cp WITH(NOLOCK)
WHERE usecounts = 1

OPEN cur_single_used_plan_handle
FETCH NEXT FROM cur_single_used_plan_handle INTO @plan_handle
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @plan_handle

    DBCC FREEPROCCACHE (@plan_handle); 

    FETCH NEXT FROM cur_single_used_plan_handle INTO @plan_handle
END

CLOSE cur_single_used_plan_handle
DEALLOCATE cur_single_used_plan_handle
GO

optimize for ad hoc workloads配置選項

除了手動清理single-used plans執行計劃快取外，另外一個更強大的功能是修改SQL Server 例項級別的配置選項optimize for ad hoc workloads。為了解決single-used plans帶來的問題，微軟從SQL Server 2008開始引入了這個全新選項，當開啟這個選項以後，一個ad hoc的查詢語句在第一次執行的時候，系統會建立一個“compiled plan stub”，並不會將執行計劃快取起來，只有當第二次該語句再被執行的時候，執行計劃才會被快取在記憶體中，從而避免了single-used plans帶來的問題。配置這個選項的方法很簡單，參見以下語句：

EXEC sys.sp_configure `show advanced options`, 1
RECONFIGURE
GO

EXEC sys.sp_configure `optimize for ad hoc workloads`, 1
RECONFIGURE
GO

最後總結

這篇文章討論了執行計劃快取中的single-used plans帶來的問題，如何發現，如何去定量和定性分析，以及最後怎麼解決single-used plans的問題。