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

James Lee Kwok Ming 100 Reputation points

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....

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
11,640 questions
0 comments No comments
{count} votes

Accepted answer
  1. Rahul Randive 5,656 Reputation points Microsoft Employee

    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.

    User's image

    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!

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 93,141 Reputation points

    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.

    1 person found this answer helpful.
    0 comments No comments