Q&A – High CPU Usage on Alibaba Cloud SQL Server

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介: A primary issue with SQL Server is its sensitivity to latency, often resulting in performance issues.

In our previous blog post, we talked about troubleshooting high CPU usage on Alibaba Cloud ApsaraDB for RDS SQL Server.

SQL Server is an open-source relational database management system (RDBMS). Its primary function is to store and retrieve data when required by other applications. Because SQL Server is sensitive to latency, higher CPU latency might result in performance issues. Therefore, it is essential to bring down the CPU usage to the extent possible by looking into the component that is causing this challenge. This article highlights seven possible causes of high CPU usage and their probable remedies.

In this article, we will look at some frequently asked questions about SQL Server delays and their answers.

1. How can we use XML check in DMV to check for conversion?

Open the actual execution plan > execute the query > open the execution plan XML, and you will find implicit data type conversion in the two places shown in the figure below.

First,

1

Second,

2

The steps to view the execution plan are as follows:

Step 1: Open the actual execution plan.

3

Step 2: Execute the query statement.

Step 3: Open the execution plan XML: Right click the execution plan to select: Show Execution Plan XML.

4

Step 4: Search for the keyword "Convert" in the opened XML.

The above illustrates the manual viewing method. You can write an XML parsing method to get XML in the execution plan cache and search for the keyword

2. The execution of stored procedures takes as long as 100 minutes, but CPU usage is not high. How can we solve this problem?

This is not a high CPU usage problem, but an overlong execution issue. It also deserves extreme care and attention. I encountered a similar case before in which the developer wrote a dead loop inside the stored procedure, resulting in a very long execution time and a failure to exit. The solutions include:

Method 1: Use Profiler Trace. These events should do it.

5

Here remember to set Filter:

6

Method 2: Execute the following queries constantly to check which statement slows down the stored procedure execution and tune the specific statement.

select
        @@servername AS Server
        ,B.Text
        ,sql_statement = (SELECT TOP 1 SUBSTRING(B.Text,stmt_start / 2+1 ,
                         (
                            (
                            CASE WHEN stmt_end = -1 THEN (LEN(CONVERT(nvarchar(max),B.text)) * 2)
                                   ELSE stmt_end END
                            )  - stmt_start) / 2+1
                         )
                      )
        ,A.hostName
        ,A.program_name
        ,A.loginame
        ,db_name(a.dbid) AS DbName
        ,A.status
        ,A.lastwaittype
        ,A.waitresource
        ,A.spid
        ,A.blocked
        ,A.login_time
        ,currnet_time = GETDATE()
        ,duration = DATEDIFF(MINUTE,login_time,GETDATE())
        --,A.*,B.*
from sys.sysprocesses AS A
cross apply sys.dm_exec_sql_text(A.sql_handle) aS B
where B.text like '%YourStoreProcedureName%'
AND A.spid <>  @@SPID

3. Currently, our largest table with GUID as the primary key contains 40 million data records. Will there be any performance problems after the number of data records exceeds 100 million? Is there any solution?

We would not recommend using GUID as the primary key very often for the following reasons:

First, the GUID field is too wide, specifically char (36), resulting in a high primary key band. It will indirectly lead to increased space for other non-primary key indexes (since non-primary key indexes need to record the value of the primary key). The larger the data space, the higher I/O used for queries, and the greater the CPU usage and the lower the efficiency.

Second, the size of each GUID value is unpredictable. When you insert new data into the table, if the GUID primary key is clustered, the data may be inserted between two rows of data. For example, the primary keys of the previous two rows of data are a and c, when b data comes in, it will insert data between a and c. This may lead to data movement.

Third, using GUID value as the primary key leads to primary key index fragmentation. You can follow the methods described above to understand index fragmentation.

Suggested Practice:

We suggest you use the "identity" attribute column to replace the GUID column as the primary key. The values in the identity attribute column are predictable. Also, the next primary key value must be greater than the previous one. Hence, SQL Server can write data rows one after another, without worrying about the insertion of data in the middle. The benefits are, first, the int data type substantially reduces the width, thereby reducing the I/O overhead and CPU overhead. Secondly, there is no data movement overhead. Lastly, it can also significantly reduce the probability of index fragmentation.

4. Will a statistics update affect the query?

Yes, it will, in two aspects - one positive and one negative.

First, it may result in the blockage of queries for a short time during the process of updating. However, you can solve this by choosing to perform the update during off-peak hours.

Second, after the update, the execution plan evaluation may be more accurate, resulting in the query being more efficient.

5. The CPU usage is too high, but all other indicators are low. What is the problem?

It may be because of a CPU bottleneck. Here are some troubleshooting ideas: if you have optimized the database, but CPU usage remains too high, and other indicators remain low, you can infer that the CPU has met its bottleneck.

6. The execution efficiency of the same statement varies greatly in different time periods. Is it because of database performance decline?

If the query statement is the same and the data result is the same, first, it may be because there is database blocking or other high-I/O or high-CPU-consuming query statements are impacting your queries; second, it may be the case discussed in the parameter sniffing section.

If the query is the same, but the data result sets are different, we may first advise you to consider that the database may be askew. It is normal for queries with different condition values and data sizes to deliver different performance levels. If the read data size is large, the query efficiency is low; otherwise, the efficiency is high.

7. Why does the database still generate a wrong execution plan after statistics are updated, and clearing the plan guide doesn't help, either?

You need to clear the execution plan cache. Updating statistics needs to cover all tables involved in the corresponding query (or view in some cases, where you need to find corresponding tables in the view). Another idea is that you may need to tune the index design. Also, look into it if there are any missing indexes, duplicate indexes or fragmented indexes.

8. Is profiler's automatic optimization recommendation reliable?

No, it is unreliable, but can serve as a reference. You need to assess it.

9. When is it a good time to re-build indexes for a large table?

The approach can be to split large tables into smaller ones, such as partitioning them. Then re-build indexes for each partition one by one. The re-building time must correspond to off-peak hours. Make sure you set the ONLINE = ON option for enterprise edition to further reduce the impact on your business. Timing suggestions: <10%: do nothing; 10% - 30%: reorganize; > 30%: rebuild.

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
SQL 网络安全 Python
[网络安全]DVWA之SQL注入—High level解题简析
DVWA请读者自行安装,本文不再赘述。
370 0
|
SQL 数据可视化 数据库
SQL SERVER数据库服务器CPU不能全部利用原因分析
SQL SERVER数据库服务器CPU不能全部利用原因分析
243 0
|
关系型数据库
InnoDB redo log thread cpu usage
InnoDB 在8.0 里面把写redo log 角色的各个线程都独立出来, 每一个thread 都处于wait 状态, 同样用户thread 调用log_write_up_to 以后, 也会进入wait 状态.这里的wait 等待最后都是通过调用 os_event_wait_for 来实现, 而 os_event_wait_for 是先spin + wait 的方式实现.所以这里有两个参数会影响os_event_wait_for 函数:spins_limit,timeout.
155 0
|
SQL 存储 缓存
【巡检问题分析与最佳实践】RDS SQL Server CPU高问题
CPU使用率过高问题是RDS SQL Server用户遇到的性能问题中较常见的一类。当RDS SQL Server实例的CPU使用率持续较高时,很容易导致数据库访问卡慢的情况,例如一些很简单的查询请求的响应时间也会很久甚至超时失败。
【巡检问题分析与最佳实践】RDS SQL Server CPU高问题
|
SQL 关系型数据库 数据库
Cloud Toolkit 数据库 SQL 执行器
Cloud Toolkit是一个IDE 插件,帮助开发者更高效地开发、测试、诊断并部署应用。 使用本插件,开发者能够方便地将本地应用一键部署到任意机器,或 ECS、EDAS、Kubernetes; 并支持高效执行终端命令和 SQL 等。
4596 7
|
SQL 索引
SQL Server性能优化之CPU
SQL Server CPU性能优化
1327 0
|
SQL 运维 Go
sql server 运维时CPU,内存,操作系统等信息查询(用sql语句)
原文:sql server 运维时CPU,内存,操作系统等信息查询(用sql语句) 我们只要用到数据库,一般会遇到数据库运维方面的事情,需要我们寻找原因,有很多是关乎处理器(CPU)、内存(Memory)、磁盘(Disk)以及操作系统的,这时我们就需要查询他们的一些设置和内容,下面讲的就是如何查询它们的相关信息。
1141 0
|
SQL Go 数据库
SQLSERVER排查CPU占用高的情况
SQLSERVER排查CPU占用高的情况 原文地址为:SQLSERVER排查CPU占用高的情况 今天中午,有朋友叫我帮他看一下数据库,操作系统是Windows2008R2 ,数据库是SQL2008R2 64位 64G内存,16核CPU 硬件配置还是比较高的,他说服务器运行的是金蝶K3软件,数据.
1451 0
|
SQL Go 调度
sql server 任务调度与CPU
原文:sql server 任务调度与CPU   一. 概述     我们知道在操作系统看来, sql server产品与其它应用程序一样,没有特别对待。但内存,硬盘,cpu又是数据库系统最重要的核心资源,所以在sql server 2005及以后出现了SQLOS,这个组件是sqlserver和windows的中间层,用于CPU的任务调度,解决I/O的资源争用,协调内存管理等其它的资源协调工作。
3832 0
下一篇
无影云桌面