CODEWITHSUNDEEP
azureazure-application-insightsazure-sql-database
crichavin
crichavin

Reputation: 4572

Azure SQL DB Cross Reference query to app insights caller

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.

Upvotes: 0

Views: 532

Answers (4)

Francisco Goldenstein
Francisco Goldenstein

Reputation: 13767

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);
            });

Upvotes: 2

Pietro
Pietro

Reputation: 781

I have exactly the same need (voted the feature request). A possible workaround (haven't tried yet) is to use IDbCommandInterceptor to modify the sql before it is sent yo the db server. The idea is to add a comment at the beginning that tags/identifies yhe method/code.

Upvotes: 0

Andrejs Antjufejevs anant-MSFT
Andrejs Antjufejevs anant-MSFT

Reputation: 11

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:

  1. Find query text
  2. Find this query in your code
  3. Add custom logging to see when it's been executed.

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

Upvotes: 1

John Gardner
John Gardner

Reputation: 25116

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.

Upvotes: 0

Related Questions