This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .
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. - Microsoft Tech Community
Enjoy!