[SQL Server] Automatic tuning

2018-02-16

SQL Server 2017提供自動調整功能，可以自動偵測執行計畫變更所造成的效能問題，

並自動(或手動)套用最佳執行計畫來修正效能問題，而Azure SQL Database則還多了自動索引管理，

可幫我們識別那些是重複索引、那些索引又可以刪除，

這項功能，我個人覺得，未來也會出現在SQL Server 2017。

我曾經介紹過SQL2016-查詢調校新利器 Query Store，

也曾說過造成執行計畫改變因素很多，索引變更、統計資料過時、CE估計演算法不同…等，

都會影響QO評估執行計畫，但我們知道不可能會有最完美執行計畫，

QO只會選擇一個夠好的執行計畫(最少的CPU、IO和執行時間)，因為評估最完美執行計畫所需時間和資源，

有可能還會超過本身查詢所需時間和資源。

Query Store幫助我們手動快速修正執行計畫改變所導致效能問題，

但SQL2017更進一步將自動幫我們修正(只要有發現且效能更好)，

便會自動套用最後一個更好執行計畫，同時SQL Server也會持續監控，

如果變更後並未改善任何效能，也會自動還原，說穿了，其實就是在自動判斷force 和unforce plan。

SQL2017我們可以透過sys.dm_db_tuning_recommendations來確認問題和建議，

這個DMV資料會自動更新(如果database engine有偵測到query performance regression)，

建議資訊將保留直到sql server is restarted(當然你也可以備份後再重新reboot)，

下面我簡單測試一下該功能。

--enable automatic tuning
alter database current set automatic_tuning(force_last_good_plan=on)

--enable query store
alter database current
set QUERY_STORE (  
    OPERATION_MODE = READ_WRITE, --讀寫模式 
    CLEANUP_POLICY =  (STALE_QUERY_THRESHOLD_DAYS = 31),--保留 31 天
    DATA_FLUSH_INTERVAL_SECONDS = 1800, --資料非同步寫入Disk間隔  
    MAX_STORAGE_SIZE_MB = 500,--空間上限
    INTERVAL_LENGTH_MINUTES = 1, --資料彙總間隔 1, 5, 10, 15, 30 & 60, 1440
    SIZE_BASED_CLEANUP_MODE = auto,  --快達空間上限自動清理舊資料
    QUERY_CAPTURE_MODE = auto ,--all擷取所有查詢 ,auto略過不頻繁及無意義編譯和執行期間的查詢
    MAX_PLANS_PER_QUERY = 1000 --每個查詢最大計畫上限
);  

--Clear procedure cache
 alter database scoped configuration clear procedure_cache;

--Clear the query store
alter database current set query_store clear all;

--verify it
select name, desired_state_desc, actual_state_desc, reason_desc
from sys.database_automatic_tuning_options

我用之前的小程式修改後進行測試

先讓SQL Server收集一段時間後，透過下面script，我們應該可以查到建議資訊

set transaction isolation level read uncommitted;
WITH DbTuneRecommend
AS (SELECT ddtr.reason,
           ddtr.score,
           pfd.query_id,
           pfd.regressedPlanId,
           pfd.recommendedPlanId,
           JSON_VALUE(ddtr.state,
                      '$.currentValue') AS CurrentState,
           JSON_VALUE(ddtr.state,
                      '$.reason') AS CurrentStateReason,
           JSON_VALUE(ddtr.details,
                      '$.implementationDetails.script') AS ImplementationScript
    FROM sys.dm_db_tuning_recommendations AS ddtr
        CROSS APPLY
        OPENJSON(ddtr.details,
                 '$.planForceDetails')
        WITH (query_id INT '$.queryId',
              regressedPlanId INT '$.regressedPlanId',
              recommendedPlanId INT '$.recommendedPlanId') AS pfd)
SELECT qsq.query_id,
       dtr.reason,
       dtr.score,
       dtr.CurrentState,
       dtr.CurrentStateReason,
       qsqt.query_sql_text,
       CAST(rp.query_plan AS XML) AS RegressedPlan,
       CAST(sp.query_plan AS XML) AS SuggestedPlan,
       dtr.ImplementationScript
FROM DbTuneRecommend AS dtr
    JOIN sys.query_store_plan AS rp
        ON rp.query_id = dtr.query_id
           AND rp.plan_id = dtr.regressedPlanId
    JOIN sys.query_store_plan AS sp
        ON sp.query_id = dtr.query_id
           AND sp.plan_id = dtr.recommendedPlanId
    JOIN sys.query_store_query AS qsq
        ON qsq.query_id = rp.query_id
    JOIN sys.query_store_query_text AS qsqt
        ON qsqt.query_text_id = qsq.query_text_id;