Monitoring I/O of the SQL Managed Instance for specific database/table

Question

We have some performance issue for the application. It seems some database/table may have lots of transaction. Cloud you show us how to gather the data by different level i.e. database/table/view....

Answer 1

Hi @James Lee Kwok Ming

You can quickly monitor a variety of resource metrics in the Azure portal in the Metrics view. These metrics enable you to see if a database is approaching the limits of CPU, memory, IO, or storage resources. High DTU, CPU or IO utilization may indicate that your workload needs more resources. It might also indicate that queries need to be optimized.

Also, Query Performance Insight shows the performance in the Azure portal of top consuming and longest running queries for single and pooled databases.

Intelligent Insights for Azure SQL Database and Azure SQL Managed Instance uses built-in intelligence to continuously monitor database usage through artificial intelligence and detect disruptive events that cause poor performance. Intelligent Insights automatically detects performance issues with databases based on query execution wait times, errors, or time-outs. Once detected, a detailed analysis is performed by Intelligent Insights that generates a resource log called SQLInsights

Additionally, you can use Extended Events for advanced monitoring and troubleshooting in SQL Server, Azure SQL Database, and Azure SQL Managed Instance. Extended Events is a "tracing" tool and event architecture, superior to SQL Trace, that enables users to collect as much or as little data as is necessary to troubleshoot or identify a performance problem, while mitigating impact to ongoing application performance.

Query store and automatic tuning should be enabled on your SQL Managed Instance as they help you measure workload performance and automatically mitigate potential performance issues.

Thank you!

Answer 2

I would suggest that you enable Query Store for your database. The next day or so, you can look in the Object Explorer for the database, and you will find the Query Store node, and you can easily find slow queries.