开发者社区> 芷沁> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介: 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.

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
k8s小工具之kubectl-mytop插件
Kubectl-mytop是一个基于metrics-server组件做指标展示的命令行工具,综合kubectl top以及kubectl describe的展示能力,可以提供Kubernetes集群中node以及pod资源请求、资源限制、资源利用率、以及pod数量做排序展示。Kubectl-mytop插件的目的在于弥补当前常用k8s监控工具的不足,便于如资源不均,调度不均,使用率不均等问题的分析定位。如指定node后,可以查看这个node上所有的pod的request,limit,util资源使用等信息并排序输出,支持指定不同namespace的相同的pod label的资源展示,以及过滤指定
26 0
Java访问Elasticsearch报错Request cannot be executed; I/O reactor status: STOPPED
Java访问Elasticsearch报错Request cannot be executed; I/O reactor status: STOPPED
2814 0
where is Fiori count server execution interval configured
where is Fiori count server execution interval configured
30 0
Adaptive and Big Data Scale Parallel Execution in Oracle
在上篇文章中,主要讨论了SQL Server的MPP数仓系统PDW的分布式优化过程,PolarDB的并行优化从中有所借鉴,本篇文章主要看下这篇介绍Oracle并行执行策略的paper,因为在PolarDB的分布式执行策略中,有很多与其有所重叠。
95 0
【故障处理】ORA-12545: Connect failed because target host
【故障处理】ORA-12545: Connect failed because target host or object does not exist 1  BLOG文档结构图       2  前言部分 2.
1732 0
Missing Indexes in SQL Server 2005
There are several new features in SQL Server 2005. There are a few features to help find missing indexes, which are some of the very good ones.
472 0
在as3中只有事件(或该事件的子级)的发送者才能侦听事件
在as3中只有事件(或该事件的子级)的发送者才能侦听事件, 与该发送者无关系的 对象 是侦听不到的, 因为事件流模型,冒泡,事件目标对象 解决办法可以用stage统一发送和侦听   补:用stage统一是否会因冒泡层数过多而影响效率?
515 0
关于ORACLE通过file_id与block_id定位数据库对象遇到的问题的一点思考
在ORACLE中,我们可以通过file_id(file#)与block_id(block#)去定位一个数据库对象(object)。例如,我们在10046生成的trace文件中file#=4 block#=266 blocks=8,那么我可以通过下面两个SQL去定位对象   SQL 1:此SQL效率较差,执行时间较长。
882 0
+关注
芷沁
https://www.alibabacloud.com/blog/
文章
问答
文章排行榜
最热
最新
相关电子书
更多
SQL Server 2017
立即下载
Bringing MySQL Compatibility to HBase using Database Virtualization
立即下载
Major advancements in Apache Hive towards full support of SQL compliance
立即下载