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

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

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

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?

4. Will a statistics update affect the query?

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

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

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

8. Is profiler’s automatic optimization recommendation reliable?

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

--

--

--

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Export JMX metrics from Confluent KSQL to Datadog

Metastarter Gaming Guild (MSGG) — Thetan Arena

What happens when you type `gcc main.c`

Things to consider when choosing a Dedicated Server in Cyprus

Cachly 6.1 Features and Release

Deploying a Deep Learning Keras Computer Vision Model to AWS Using an AWS EC2 Instance (as a Web…

CS 373 Spring 2020: Abhi Velaga

What exactly is SOLID principles commonly used in Software Engineering project?

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
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

More from Medium

Cassandra Monitoring Setup Using Open Source Tools

Building Event Driven Applications with Apache Flink, Apache Kafka and Amazon EMR — Part 2

Eventbus written in Python based on Kafka

AWS S3 Same Region Replication (SRR)

AWS S3 — Same Region Replication diagram