RDSSQLServer-專題分享-巧用執行計劃快取之索引缺失

風移發表於2017-06-05
SQLServer快取索引

title: RDS SQL Server – 專題分享 – 巧用執行計劃快取之索引缺失

author: 風移

摘要

執行計劃快取是MSSQL Server記憶體管理十分重要的部分，同樣如何巧用執行計劃快取來解決我們平時遇到的一系列問題也是一個值得深入研究的專題。這篇文章是如何巧用執行計劃快取的開篇，分享如何使用執行計劃快取來分析索引缺失（Missing Indexes）。

問題引入

缺失索引是SQL Server CPU使用率居高不下的第一大殺手，也是SQL Server資料庫非常大的潛在風險點。在之前的高CPU使用率系列文章中，我們分享了使用系統動態檢視的方法來獲取索引缺失的方法，詳情請戳：RDS SQL Server – 最佳實踐 – 高CPU使用率系列之索引缺失。那麼有沒有其他的方法既可以獲取到缺失索引，還能夠展示相應查詢語句執行計劃中有價值的詳細資訊呢？這篇文章從執行計劃快取的角度和視野來獲取缺失索引，並且對相應執行計劃有價值的資訊進行了詳細展示，包括單不僅限於：
建立缺失索引對查詢效能的提升預估百分比
執行計劃針對的查詢語句、資料庫物件
執行計劃建立時間和最後使用時間
執行計劃快取大小、編譯時間、CPU和記憶體消耗
最小、最大、最後一次和總共消耗CPU的時間
最小、最大、最後一次和總共IO物理、邏輯讀寫
最小、最大、最後一次和總共影響的行數
……

場景分析

MSSQL Server引擎，在執行特定語句時，需要對語句進行語法檢查、語義分析、編譯、最佳執行路徑選擇、生成執行計劃和快取執行計劃，以便下次執行相同語句時，可以直接從執行計劃快取中獲取執行計劃，以節約效能開銷和提升查詢語句執行效能。執行計劃快取中有非常多有價值的資訊，那麼我們如何有效利用執行計劃快取來幫助我們分析系統存在的潛在風險和效能問題呢？本篇文章分享巧用執行計劃快取來獲取缺失索引。

測試環境

測試環境搭建和相應查詢語句參見之前的文章RDS SQL Server – 最佳實踐 – 高CPU使用率系列之索引缺失中的測試環境和執行查詢部分，在此不再累述。

解決方法

前面做了很多鋪墊關於背景的介紹，執行計劃快取基礎理論，終於到了激動人心的解決方法部分了。一言不合，直接上程式碼：

USE master
GO

DECLARE
    @EngineEdition INT = CAST(SERVERPROPERTY(N`EngineEdition`) AS INT)
;

;WITH XMLNAMESPACES (DEFAULT `http://schemas.microsoft.com/sqlserver/2004/07/showplan`)
,planCache
AS(
    SELECT 
        *
    FROM sys.dm_exec_query_stats as qs WITH(NOLOCK)
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
    WHERE qp.query_plan.exist(`/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex`)=1
), analyedPlanCache
AS(
    SELECT 
        sql_text = T.C.value(`(@StatementText)[1]`, `nvarchar(max)`)
        ,[impact%] = T.C.value(`(./QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]`, `float`)
        ,cachedPlanSize = T.C.value(`(./QueryPlan/@CachedPlanSize)[1]`, `int`)
        ,compileTime = T.C.value(`(./QueryPlan/@CompileTime)[1]`, `int`)
        ,compileCPU = T.C.value(`(./QueryPlan/@CompileCPU)[1]`, `int`)
        ,compileMemory = T.C.value(`(./QueryPlan/@CompileMemory)[1]`, `int`)
        ,database_name = T.C.value(`(./QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Database)[1]`,`sysname`)
        ,schema_name = T.C.value(`(./QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Schema)[1]`,`sysname`)
        ,object_name = T.C.value(`(./QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Table)[1]`,`sysname`)
        ,equality_columns = (
            SELECT 
                DISTINCT REPLACE(REPLACE(tb.col.value(`(@Name)[1]`, `sysname`), N`]`, N``), N`[`, N``) + `,`
            FROM T.c.nodes(`./QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/ColumnGroup`) AS T(cg)
                CROSS APPLY T.cg.nodes(`./Column`) AS tb(col)
            WHERE T.cg.value(`(@Usage)[1]`, `sysname`) = `EQUALITY`
            FOR  XML PATH(``)
        )
        ,inequality_columns = (
            SELECT 
                DISTINCT REPLACE(REPLACE(tb.col.value(`(@Name)[1]`, `sysname`), N`]`, N``), N`[`, N``) + `,`
            FROM T.c.nodes(`./QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/ColumnGroup`) AS T(cg)
                CROSS APPLY T.cg.nodes(`./Column`) AS tb(col)
            WHERE T.cg.value(`(@Usage)[1]`, `sysname`) = `INEQUALITY`
            FOR  XML PATH(``)
        )
        ,include_columns = (
            SELECT 
                DISTINCT REPLACE(REPLACE(tb.col.value(`(@Name)[1]`, `sysname`), N`]`, N``), N`[`, N`@`) + `,`
            FROM T.c.nodes(`./QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/ColumnGroup`) AS T(cg)
                CROSS APPLY T.cg.nodes(`./Column`) AS tb(col)
            WHERE T.cg.value(`(@Usage)[1]`, `sysname`) = `INCLUDE`
            FOR  XML PATH(``)
        )
        ,pc.*
    FROM planCache AS pc
        CROSS APPLY query_plan.nodes(`/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple`) AS T(C)
    WHERE C.exist(`./QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex`) = 1
)
SELECT 
    plan_handle
    ,query_plan
    ,query_hash
    ,query_plan_hash
    ,sql_text
    ,[impact%]
    ,cachedplansize
    ,compileTime
    ,compileCPU
    ,compileMemory
    ,object = database_name + `.` + schema_name + `.` + object_name
    ,miss_index_creation = 
            N`CREATE NONCLUSTERED INDEX ` + QUOTENAME(N`IX_` + REPLACE(LEFT(equality_columns, len(equality_columns) - 1), N`,`, N`_`) + `_`
            + REPLACE(LEFT(inequality_columns, len(inequality_columns) - 1), N`,`, N`_`) + `_`
            + REPLACE(LEFT(include_columns, len(include_columns) - 1), N`,`, N`_`), `[]`)
            + N` ON ` + database_name + `.` + schema_name + `.` + object_name 
            + QUOTENAME(
                CASE 
                    WHEN equality_columns is not null and inequality_columns is not null 
                        THEN equality_columns + LEFT(inequality_columns, len(inequality_columns) - 1)
                    WHEN equality_columns is not null and inequality_columns is null 
                        THEN LEFT(equality_columns, len(equality_columns) - 1)
                    WHEN inequality_columns is not null 
                        THEN LEFT(inequality_columns, len(inequality_columns) - 1)
                END
                , `()`)
            + CASE 
                    WHEN include_columns is not null 
                    THEN ` INCLUDE ` + QUOTENAME(REPLACE(LEFT(include_columns, len(include_columns) - 1), N`@`, N``), N`()`)
                    ELSE ``
                END
            + N` WITH (FILLFACTOR = 90`
            + CASE @EngineEdition 
                WHEN 3 THEN N`,ONLINE = ON` 
                ELSE ``
                END + `);`
    ,creation_time
    ,last_execution_time
    ,execution_count
    ,total_worker_time
    ,last_worker_time
    ,min_worker_time
    ,max_worker_time
    ,total_physical_reads
    ,last_physical_reads
    ,min_physical_reads
    ,max_physical_reads
    ,total_logical_writes
    ,last_logical_writes
    ,min_logical_writes
    ,max_logical_writes
    ,total_logical_reads
    ,last_logical_reads
    ,min_logical_reads
    ,max_logical_reads
    ,total_clr_time
    ,last_clr_time
    ,min_clr_time
    ,max_clr_time
    ,total_elapsed_time
    ,last_elapsed_time
    ,min_elapsed_time
    ,max_elapsed_time
    ,total_rows
    ,last_rows
    ,min_rows
    ,max_rows
FROM analyedPlanCache

執行完畢以後的結果展示如下，由於結果集太長，人為分為四段結果集：
第一段結果集截圖
01.png

第二段結果集截圖
02.png

第三段結果集截圖
03.png

第四段結果集截圖
04.png

點開第一個張截圖中的其中一行query_plan xml，我們檢視到的Missing Indexes資訊節點：
05.png
從截圖中，我們同樣可以找到非常有用的資訊，包括：
建立索引後的效能提升為99.8369%（第11行）
缺失索引的資料庫物件，包括資料庫名，架構名和表名稱（第12行）
相等謂詞使用的缺失索引列（第13行）
不相等謂詞使用的缺失索引列（第16行）
覆蓋欄位的缺失索引列（第19行）

注意事項

由於執行計劃緩是儲存在SQL OS的記憶體中，所以會隨著以下動作被自動或被動清空：
SQL Server Service重啟
作業系統重啟
人為清空快取
系統感覺到記憶體壓力自動回收等
當這些動作發生以後，再通過執行計劃快取來獲取有效資訊，可能會導致資訊獲取不完整。所以，使用本篇文章方法獲取缺失索引資訊之前，請確保你的SQL Server系統已經充分Warm Up。

最後總結

這篇文章是巧用執行計劃快取系列文章的開篇，詳細講解了如何使用執行計劃快取來獲取缺失索引資訊以及執行計劃的一些有價值的詳細資訊，以此來破解RDS SQL Server高CPU使用率的問題。

本文的視訊演示,我已經上傳到Youku，詳情請戳 MSSQL Server巧用執行計劃快取獲取缺失索引資訊


相關文章