We're reviewing the top running queries in the Azure Portal for SQL database. Wondering if there is a way to determine all of the application server requests that call that query, so we can focus on our code to optimize it (use caching, etc).
In app insights, you can go down, from the application server calls, then drill down to dependencies, but I want to go up the tree....i.e. search for the dependency call query, then work up to see which application server calls are invoking it.
Query Performance Insight PM here. Our feature take advantage of information stored in Query Store.
Unfortunately, currently Query Store doesn't track any information about query source or host where query came from. Query Store provides aggregated view on your queries, e.g. Query X was executed Y times and took Z resources in the interval t. It's a bit hard to store all ip addresses/hosts in such setup.
So I guess for now, easiest way to get information you're looking for:
Also, I encourage you to create an item on our feedback portal so this request collect votes and gets into our backlog eventually.
Hope it helps. Andrejs
If you cannot find the query in your code because you are using Entity Framwork, then you can log all the queries with just a few lines of code. I also added the stack trace so you know where each query is being invoked.
In the constructor of your DbContext class add the following code:
Database.Log = (sql =>
{
System.Diagnostics.Debug.WriteLine(System.Environment.StackTrace);
System.Diagnostics.Debug.WriteLine(sql);
});
you could use AI the search tools in the portal to find the dependencies you want, and then from the details, use "all requests for this operation" or other related items links there.
if you use the AI Analytics Portal, you could write whatever kql queries you want to query that information, joining between the two tables (requests and dependencies) presuming that the dependencies properly have operationId or some other field set to join on.
