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!
