2

I'm running an Azure SQL instance behind an Azure Web App, both of which run perfectly fine the majority of the time. At seemingly random intervals (ever few days or so), the database DTUs peg out completely. How would I trouble shoot the source of these problems? I think it's related to a sudden influx of requests from the server, but I can't figure out exactly where/how.

According to the "Performance Metrics" in Azure, the offending metrics seem to be

  • Data I/O
  • Log I/O
  • Increased connection count
  • Tempdb file size

Again, I think it might be caused by the server all of a sudden sending a ton of requests, but I have no idea how to validate that or how to trace the request sources that are causing this.

Thoughts?

enter image description here

enter image description here

2 Answers 2

0

Start by checking the reports in Querystore. Set a timespan that includes a time when it ran both well and badly and then examine the various metrics. Were there more executions during the bad period, Did some queries get a bad plan and cause resource problems - if so it is easy to force the better plan.

0

I have no idea how to validate that or how to trace the request sources that are causing this.

You can do it using any of these 2 methods:

  1. Use an extended event trace and collect hostname for each session. You already know that you have an increased workload. This will tell what hosts are the source of the increased workload. If you need a tutorial on the extended events I find this by Phil is a good one.
  2. Turn on Azure SQL Audit which gives details for each completed batch

If you want more details set setup diagnostic. Once your data is collected for a while you use the 'AzureSQLAnalytics' solution to see built-in graphs and datasets. You can also use Kusto Query Language to query raw data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.