Azure SQL DB Cross Reference query to app insights caller

Question

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.

Francisco Goldenstein · Accepted Answer · 2017-05-09 16:06:57Z

2

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

answered May 9, 2017 at 16:06

Francisco Goldenstein

13.9k7 gold badges63 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andrejs Antjufejevs anant-MSFT · Accepted Answer · 2016-12-02 11:00:09Z

1

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:

Find query text
Find this query in your code
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

answered Dec 2, 2016 at 11:00

Andrejs Antjufejevs anant-MSFT

111 bronze badge

2 Comments

crichavin Over a year ago

Thanks but that suggestion won't work for ASP.Net apps using Entity Framework as in code we write LINQ queries, which Entity Framework then converts to SQL, so we can't search our code for SQL. You wouldn't need to track each IP address that requested it, just the server operation name. This info is already tracked in Application Insights (AI), it is just missing a good way to search it and link it to the query Id....in Query Performance (QP), you have a query ID, it would be great to be able to search on that in AI. But even tighter interoperability between AI and QP would be great.

crichavin Over a year ago

Added new item in Feedback Portal: feedback.azure.com/forums/217321-sql-database/suggestions/…

John Gardner · Accepted Answer · 2016-12-01 20:50:31Z

0

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.

answered Dec 1, 2016 at 20:50

John Gardner

25.3k5 gold badges65 silver badges78 bronze badges

2 Comments

crichavin Over a year ago

thanks for replying. It sort of works. When you search the slow/frequent queries in Query Performance Insights, the only unique identifiers you have are the Query Id and the query command (T-SQL) . Then you to to the AI, click on Server Response Time graph, then on the new blade, click on the Dependency Duration. From here, you can't search by that Query Id (which would be ideal), but you can put in some text to search for the Query command. But you cannot simply copy the entire command and search on it, so you have to selectively choose a portion of the command text to search on.

crichavin Over a year ago

Is that the process you were envisioning, or did I interpret that incorrectly?

Pietro · Accepted Answer · 2017-03-21 20:02:21Z

0

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.

edited Mar 21, 2017 at 20:02

answered Mar 21, 2017 at 19:24

Pietro

7817 silver badges30 bronze badges

Collectives™ on Stack Overflow

Azure SQL DB Cross Reference query to app insights caller

4 Answers 4

Comments

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related