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

Image for post
Image for post

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.

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.

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.

Scenario Analysis

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

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
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post
Image for post

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:

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.

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store