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
- 99% features available from OnPremises - Compare the database engine features of SQL Database and SQL Managed Instance - Azure SQL Database & SQL Managed Instance | Microsoft Docs
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
- Update Statistics with fullscan - How to maintain Azure SQL Indexes and Statistics - Microsoft Tech Community
- Lesson Learned #195: Performance Best Practices for Azure SQL Database and Managed Instance. - Microsoft Tech Community
- dimitri-furman/managed-instance: Code samples for use with Azure SQL Database Managed Instance (github.com)
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.0Jose_Manuel_Jurado
Microsoft
Joined November 29, 2018
Azure Database Support Blog
Follow this blog board to get notified when there's new activity