Not recording query plans when query plan recording is turned on

We are using .net framework (not core) 4.6.1 and MS SQL server 2012

What would cause query plans to not get recorded even through we have the explain slow queries setting turned on in the settings?

Hi, @patrick52: What is the value of the explainThreshold configuration setting? Are you sure you have queries whose execution time exceed the threshold?

1 Like

That value is 0.5 seconds. We have queries showing up in the slow queries section of the database traces, but none of them have query plans. Perhaps I’m looking in the wrong place?

Does the user making the queries have permission to run explain plans in MSSQL? Also, you’ll need to select a specific Slow Query Trace to see the explain plans. They are not captured for every slow query.

Which user is it that would be running this? The one in the connection string, or the one on the app pool?

That it likely the problem, we lock things down pretty heavily here.

We reuse the connection that you used to run your query so it will be the user from the connection string. We try to rerun the query with EXPLAIN but if that user can’t generate an explain plan due to permissions it won’t work.

Another thing to note is that we only run EXPLAIN for SELECT queries as running an INSERT a second time would add an additional row.

1 Like