利用計畫指南來重用執行計畫降低CPU壓力

2019-09-06

2863
0
[SQL]效能
2019-09-05

很久以前就聽聞過SQL的計畫指南功能，但我一直沒有用過。可以從字面上初步了解就是針對某些查詢我們可以干涉最佳化引擎，讓SQL根據我們的需求來跑執行計畫。一些官方文件的舉例就是在不異動查詢語句的狀況下讓管理者可以針對語句下hint(如MAXDOP設定及OPTIMIZE FOR或Recompile等)。

下面是官方簡易的範例，強制讓該查詢語法只能用單核心執行 :

sp_create_plan_guide   
@name = N'Guide2',   
@stmt = N'SELECT TOP 1 * FROM Sales.SalesOrderHeader ORDER BY OrderDate DESC',  
@type = N'SQL',  
@module_or_batch = NULL,   
@params = NULL,   
@hints = N'OPTION (MAXDOP 1)';

這一次Rock遇到的問題是一套外購的系統，由於資料量漸漸上升加上業者開發習慣都採巢狀view的結構(view包view這種狀態)，這種view中還有view的方式固然開發快速，但是往往會加重查詢時的複雜度。因此該系統在運作時有越來越慢的狀況且CPU也越飆越高。

因此我先抓出耗用大量CPU資源的語法，然後分析該語法為何會耗這麼多CPU資源。後來發現這些語法居然是在Compile時耗費最多CPU資源，反而抓取資料時非常快速。所以應該就是語法過於複雜導致找尋執行計畫時需消耗較多CPU資源，而系統又都是丟Adhoc這類的查詢語法過來，因此無法重用執行計畫，重複的語法僅因參數不同就得重新Compile，因而加重CPU的負擔。

一般遇見這種問題我會請PG改寫查詢方法，可能改用Stored Procedure或是用sp_executesql來做，目的就是降低Compile讓執行計畫可以重用。然而該系統是外購，我無法請他們改寫，因此只好想辦法從資料庫下手。此時我想到的方式就是建立計畫指南。

本LAB我是拿該系統測試資料庫來Demo因此需遮蓋掉查詢語法，下圖中我們可以看見該語法執行時，編譯時間花了4408 ms而實際抓取資料的耗時卻只有77 ms而已。

下圖中我們去資料庫記憶體撈出該語法的執行計畫，你會發現有一堆的Adhoc語法的執行計畫，他們都是同一句查詢，差別只是查詢參數不同而已。一但這種Adhoc執行計畫過多，不僅是耗費CPU資源，也耗費記憶體的資源。

下圖中我就針對該語法建立一個計畫指南叫TemplateGuid1，我用該語法建立一個Template並將語法中的查詢給參數化。這裡請注意我在該計畫指南中加了OPTION(PARAMETERIZATION FORCED)來讓該語法使用強制參數化而不是簡單參數化。參數化可參考 [SQL SERVER][Performance]淺談簡單和強制參數化這篇大作。

我們可以從sys.plan_guides系統表中查到我們剛剛建立完成的計畫指南。

也可以在SSMS的UI中看到，如下圖的路徑。

接下來我們先清空SQL記憶體中的所有執行計畫，如下圖所示。

清除後我們在查詢一下記憶體看看所有執行計畫是不是都已經清掉了，下圖中我們查不到相關執行計畫了。

接下來我們重跑一次剛剛那句語法，你會發現他編譯時間還是要3900 ms，這是因為該語法在清空記憶體後第一次執行，所以得重新編譯找執行計畫。

我們在上一步執行了一次語法後，我們再來看看記憶體中產出了幾個相關的執行計畫呢? 如下圖所示產出了2個執行計畫，1個物件類型還是Adhoc然而多了1個類型為Prepared的執行計畫。Prepared的執行計劃就是我們建立的計畫指南。你會好奇既然有了Prepared的執行計畫為何還會有另一個Adhoc的執行計畫呢?一開始我也覺得奇怪，我當下以為是SQL不吃計畫指南所以自己又產出Adhoc的執行計劃，但我後來發現Adhoc這個執行計劃的位元組只占2KB左右，而Prepared的執行計畫居然高達2MB左右。後來一查才發現Adhoc的執行計劃內紀錄直接指向Prepared執行計畫，所以Adhoc的位元組才會比較低。

接下來我在執行一次該語法，看看SQL是不是會從用執行計畫呢? 這一次的執行我們可以發現沒有編譯得耗時了，直接86 ms跑完。

上一步執行後我們再來看看記憶體中產出了幾個相關的執行計畫呢? 如下圖所示產出了3個執行計畫，2個物件類型還是Adhoc(都佔2KB位元組)及一個類型為Prepared的執行計畫。2個Adhoc的位元組都一樣，也都是紀錄指向Prepared的執行計畫，兩個Adhoc的執行次數都是1次，而Prepared的執行計畫執行次數為2次。因此可以確定計畫指南的執行計畫是有被重用的。