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 analyzedPlanCacheAfter 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.
