As we observe from application insight logs from app services it show database is taking long time to respond to request

Question

We are using SQL managed instance in azure and in single manage instance we have many different database to it. As we observe from application insight logs from app services it show database is taking long time to respond to request as shown in the screenshot . But at the same time we observed that in SSMS at the given time mentioned in the screenshot the time taken is low as compared to the application insights log of app service . Why we are getting this two different output and how can we resolve this issues?

Answer 1

@Mayank Jain Based on the information you provided, it seems like you are experiencing performance issues with your SQL managed instance in Azure.

a) To identify performance issues in your managed instance, you can use Azure SQL Analytics. Azure SQL Analytics provides a centralized location to view and analyze performance metrics for all your SQL databases and managed instances. You can use it to identify performance bottlenecks, diagnose issues, and optimize your databases. You can also use Query Store to identify performance issues with specific queries.
In the Azure portal, go to your managed instance.

1. Click on the "SQL Analytics" tab.
2. Click on "Enable SQL Analytics" if it is not already enabled.
3. Once SQL Analytics is enabled, you can view performance metrics for your managed instance, such as CPU usage, memory usage, and storage usage.
4. You can also use Query Store to identify performance issues with specific queries. To enable Query Store, go to your managed instance and click on the "Query Store" tab. From there, you can configure Query Store settings and view query performance metrics.

b) To check the memory consumption of your managed instance, you can use the Azure portal or Azure PowerShell. In the Azure portal, go to your managed instance and click on the Metrics tab. From there, you can view metrics such as CPU usage, memory usage, and storage usage. You can also use Azure PowerShell to retrieve metrics programmatically.

1. In the Azure portal, go to your managed instance.
2. Click on the "Metrics" tab.
3. From the "Metrics" tab, select "Memory usage" from the "Metric Namespace" dropdown.
4. You can view memory usage metrics for your managed instance over a specified time range.

c) To set an alert for performance issues, you can use Azure Monitor. Azure Monitor provides a unified platform for monitoring and alerting on all your Azure resources, including your managed instance. You can create alerts based on metrics such as CPU usage, memory usage, and storage usage. When an alert is triggered, you can receive notifications via email, SMS, or other channels.

1. In the Azure portal, go to your managed instance.
2. Click on the "Alerts" tab.
3. Click on "New alert rule".
4. Configure the alert rule settings, such as the metric to monitor (e.g. CPU usage, memory usage), the threshold for the metric, and the notification settings.
5. Click on "Create alert rule" to create the alert.