We had a performance issue with one of our queries in our application that was taking 20 seconds to run. Using azure data studio we figured out the SQL that was long running and then eventually traced that back to the entity framework query that was executed.

I had an idea of adding a logging function to our code where it is called before any data access is done (insert, select, delete, update etc) in the entity framework code.

What the function would do is simple execute a "Select user_functionname_now" sql statement.

Then in azure data studio profiler we would see :

The image tells me that the user ran the load invoice function and it took 2717 milliseconds.

Granted if you have 100 users doing things in the app the logs might get mixed up a bit but it would go a long way in being able to figure out where in the code the long running query is executing from.

I was also thinking that we could add a fixed column to each query run so that you could see something like this:

But the issue with adding a column is you are returning extra data each time a query is run which requires more data back and forth between the SQL server and the application and that for sure is not a good thing.

So my question is: Is adding a "Select XYZ" before every CRUD call a bad idea? If we add this logging call to some or all of our code where it executes our queries will it cause a performance issue/slowdown that I haven't thought about?

CodePudding user response：

I don't think using any "select ..." is reasonable in your case.

Perhaps, SET CONTEXT_INFO or sp_set_session_context would be better.

CodePudding user response：

This is the scenario that EF Query Tags are for.