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

Published Jul 07 2022 04:38 PM 1,430 Views

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?

 

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

 

Enjoy!

 

Version history
Last update:
‎Jul 07 2022 04:47 PM
Updated by: