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

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.

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:

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:

4. Will a statistics update affect the query?

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

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.

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.

--

--

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