RDS SQL Server: Best Execution Plan Cache for Missing Indexes

86e43d940883c108c3166060805b39ee921d58eb_jpeg

Introduction

Alibaba Cloud ApsaraDB for RDS (Relational Database System) is an on-demand database service that frees you from the administrative task of managing a database, thus leaving you with more time to focus on your core business. It is a ready-to-use service offered on MySQL, SQL Server, and PostgreSQL. Additionally, ApsaraDB for RDS handles routine database tasks such as provisioning, patch up, backup, recovery, failure detection, and repair.

Execution plan cache is a significant part of SQL Server memory management. It can reveal to you how the execution of a query occurs, or how the query execution takes place. It is the Data Base Administrator's primary means of troubleshooting a poorly performing query.

Role of Execution Plan Cache in SQL Server Memory Management

SQL Server has a pool of memory used to store both execution plans and data buffers. The percentage of the pool allocated to either execution plans or data buffers fluctuates dynamically, depending on the state of the system. The part of the memory pool used to store execution plans is referred to as the procedure cache.

SQL Server execution plans have the following main components:

● Query Plan: The bulk of the execution plan is a re-entrant, read-only data structure used by any number of users. This is referred to as the query plan. The query plan stores no user context.
● Execution Context: Each user that is currently executing the query has a data structure that holds the data specific to their execution, such as parameter values. We refer to this data structure as the execution context.

Missing Indexes

Missing indexes are the primary culprits for the high CPU usage of SQL Server. They also pose a major threat for SQL Server databases. When it comes to missing indexes, users often have questions such as, "Is there any other method to obtain missing indexes?", "Can we display valuable details of the execution plan of related query statements?" among others.

Consequently, this article seeks to answer these queries by shedding light on the execution plan cache to obtain missing indexes. Additionally, it discusses the information of the corresponding execution plans, listed as follows:

● The estimated percentage by which creating a missing index will improve the query performance;
● The query statement and database object that the execution plan targets;
● The time of creation of the execution plan and last usage;
● The execution plan cache size, compile time, CPU and memory consumption;
● The shortest, longest, last and total CPU consumption durations;
● The smallest, largest, last and total IO physical and logic reads/writes;
● The smallest, largest, last and total numbers of lines impacted.

Scenario Analysis

Let us discuss SQL Server's engine requirements and the various activities that it needs to perform.

SQL Server engine needs to perform statement syntax checks, semantic analysis, and compilation. It also needs to perform the selection of the best execution route, execution plan generation, and cache in the execution of specific statements. This process is carried out so that the system can obtain the execution plan from the execution plan cache the next time it executes the same statement. This helps save performance overheads and improves query statement performance.

Since the execution plan cache contains valuable information, you may wonder, how can you effectively use the execution plan cache to discover potential risks and performance issues in the system?

Solution

In the above section, we discussed the background and basic theories of the execution plan cache. You can use the following code, to solve the concerns that many of you must have faced regarding SQL Server engine:

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 analyzedPlanCache

After the execution, the result that we derive is depicted through the four screenshots below.

Screenshot of the first result segment

Screenshot of the second result segment

Screenshot of the third result segment

Screenshot of the fourth result segment

When we click open the query_plan xml line in the first screenshot, the information nodes of the missing indexes appear as follows:

From the aforementioned screenshot, we can infer the following results:

• The performance increase was 99.8369% (Line 11) after the creation of indexes;
• Database objects of the missing indexes, including database name, schema name, and table name (Line 12);
• Missing index columns used by equality predicates (Line 13);
• Missing index columns used by inequality predicates (Line 16);
• Missing index columns of overwrite field (Line 19).

Precautions

The memory of the SQL Operating System is responsible for storing the execution plan. Users should keep in mind the following precautions, which will result in it being automatically or passively clear during the below-mentioned actions:

● Reboot of the SQL server service
● Reboot of the operating system
● Manual cache wipe
● Automatic recycle by the system when the system feels high pressure on the memory

When these actions occur, you may retrieve incomplete information while trying to extract valid information through the execution plan cache. Therefore, before practicing methods in this article for getting missing indexes, make sure to warm up the SQL Server system completely.

Summary

This article describes the best practices of the execution plan cache series. It elaborates on how to use the execution plan cache to obtain missing index information. It also provides some valuable details about the execution plan in a bid to crack the high CPU usage of RDS SQL Server.

Community

RDS SQL Server: Best Execution Plan Cache for Missing Indexes

Introduction

Role of Execution Plan Cache in SQL Server Memory Management

Missing Indexes

Scenario Analysis

Solution

Precautions

Summary

Read previous post:

Read next post:

Alibaba Clouder

You may also like

Comments

Alibaba Clouder

Related Products

Database Overview

ApsaraDB RDS for SQL Server

ApsaraDB RDS for PostgreSQL

ApsaraDB RDS for MySQL