Lesson Learned #219:Hands-On-Labs: What do I need to do in case of high CPU wait time

[Jose_Manuel_Jurado]

We used to have cases where our customers reported a performance issue that is causing delay in their business. Working on this issue we saw that the CPU was the most wait time. In this video below we going to provide us some insights about it,.

Why this issue happens?

• Missing Indexes
• Conversion Implicit
• Out-of-Date statistics
• More Information: Diagnose and troubleshoot high CPU - Azure SQL Database | Microsoft Docs

How to identify the issue?

• Azure Portal, Azure Monitor, Log Analytics, SQL Auditing
• Select * from sys.dm_db_resource_stats

select top 5 * from sys.dm_db_resource_stats

• Select * from sys.dm_exec_requests in combinations with other

SELECT
req.session_id
, req.start_time
, cpu_time 'cpu_time_ms'
, status
, wait_time
, last_wait_type
, total_elapsed_time
, transaction_id
, wait_resource
, task_address
, object_name(st.objectid,st.dbid) 'ObjectName'
, substring
(REPLACE
(REPLACE
(SUBSTRING
(ST.text
, (req.statement_start_offset/2) + 1
, (
(CASE statement_end_offset
WHEN -1
THEN DATALENGTH(ST.text)
ELSE req.statement_end_offset
END
- req.statement_start_offset)/2) + 1)
, CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text,*
FROM sys.dm_exec_requests AS req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as ST
where session_id <> @@SPID

• Query Data Store

Best Practices

• Increase dynamically the command timeout
• Log the operations, time spent, create alerts.
• Reconnect if the connection is lost due to timeout
• Use the worst scenarios in our tests.
• Update Statistics with fullscan - How to maintain Azure SQL Indexes and Statistics - Microsoft Tech Community
• Rebuild the indexes
• Review missing indexes, check recommendations
• Lesson Learned #195: Performance Best Practices for Azure SQL Database and Managed Instance. - Micro...

Enjoy!