RDS SQL Server– Best Practices of Execution Plan Cache for Missing Indexes

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
简介: Execution plan cache is a significant part of SQL Server memory management. It can reveal to you how the execution of a query will occur, or how query execution took place.

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

1

Screenshot of the second result segment

2

Screenshot of the third result segment

3

Screenshot of the fourth result segment

4

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

5

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.

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
3月前
|
SQL 关系型数据库 MySQL
mysql 简单的sql语句,入门级增删改查
介绍MySQL中的基本SQL语句,包括数据的增删改查操作,使用示例和简单的数据表进行演示。
mysql 简单的sql语句,入门级增删改查
|
8天前
|
SQL Oracle 关系型数据库
SQL(MySQL)
SQL语言是指结构化查询语言,是一门ANSI的标准计算机语言,用来访问和操作数据库。 数据库包括SQL server,MySQL和Oracle。(语法大致相同) 创建数据库指令:CRATE DATABASE websecurity; 查看数据库:show datebase; 切换数据库:USE websecurity; 删除数据库:DROP DATABASE websecurity;
|
19天前
|
SQL 开发框架 .NET
突破T-SQL限制:利用CLR集成扩展RDS SQL Server的功能边界
CLR集成为SQL Server提供了强大的扩展能力,突破了T-SQL的限制,极大地拓展了SQL 的应用场景,如:复杂字符串处理、高性能计算、图像处理、机器学习集成、自定义加密解密等,使开发人员能够利用 .NET Framework的丰富功能来处理复杂的数据库任务。
|
2月前
|
SQL 关系型数据库 MySQL
|
2月前
|
SQL 运维 关系型数据库
MySQL 运维 SQL 备忘
MySQL 运维 SQL 备忘录
48 1
|
2月前
|
SQL 存储 关系型数据库
SQL文件导入MySQL数据库的详细指南
数据库中的数据转移是一项常规任务,无论是在数据迁移过程中,还是在数据备份、还原场景中,导入导出SQL文件显得尤为重要。特别是在使用MySQL数据库时,如何将SQL文件导入数据库是一项基本技能。本文将详细介绍如何将SQL文件导入MySQL数据库,并提供一个清晰、完整的步骤指南。这篇文章的内容字数大约在
272 1
|
1月前
|
SQL 关系型数据库 MySQL
MySql5.6版本开启慢SQL功能-本次采用永久生效方式
MySql5.6版本开启慢SQL功能-本次采用永久生效方式
41 0
|
1月前
|
SQL 关系型数据库 MySQL
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
31 0
|
2月前
|
存储 SQL 关系型数据库
MySQL查询数据库锁表的SQL语句
MySQL查询数据库锁表的SQL语句
91 1
|
3月前
|
SQL 关系型数据库 MySQL
创建包含MySQL和SQLServer数据库所有字段类型的表的方法
创建一个既包含MySQL又包含SQL Server所有字段类型的表是一个复杂的任务,需要仔细地比较和转换数据类型。通过上述方法,可以在两个数据库系统之间建立起相互兼容的数据结构,为数据迁移和同步提供便利。这一过程不仅要考虑数据类型的直接对应,还要注意特定数据类型在不同系统中的表现差异,确保数据的一致性和完整性。
36 4