Blog Post

Azure Database Support Blog
2 MIN READ

Lesson Learned #221:Hands-On-Labs: Activity Monitor in my Azure SQL Managed Instance

Jose_Manuel_Jurado's avatar
Jul 09, 2022

We used to have cases where our customers reported a performance issue that is causing delay in their business, In this video below we going to provide us some insights about how to monitor your Azure SQL Managed Instance and we are going to share a query to obtain all the current processes that your Azure SQL Managed Instance.

 

Main Characteristics

 

 

How to monitor?

 

  • Azure Portal, Azure Monitor, Log Analytics and SQL Profiler, xEvents Profiler
  • Select * from sys.dm_exec_requests in combinations with other
  • Query Data Store
  • Use the queries provided in the demo

 

Best Practices

 

 

How to monitor queries that are running in my SQL Managed Instance

 

SELECT
 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
,dbs.name
,req.database_id
,program_name
,req.session_id
, req.cpu_time 'cpu_time_ms'
, req.status
, wait_time
, wait_resource
, wait_type
, last_wait_type
, req.total_elapsed_time
, total_scheduled_time
, req.row_count as [Row Count]
, command
, scheduler_id
, memory_usage
, req.writes
, req.reads
, req.logical_reads, blocking_session_id
FROM sys.dm_exec_requests AS req
inner join sys.dm_exec_sessions as sess on sess.session_id = req.session_id
left join sys.databases as dbs on dbs.database_id = sess.database_id
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as ST
where req.session_id <> @@SPID and program_name like 'Testing%'
order by dbs.name

 

This video about the demo how to monitor your Azure SQL Managed Instance

 

Enjoy!

Published Jul 09, 2022
Version 1.0
No CommentsBe the first to comment