We are on SQL Server 2016
Query Store is activated on the database in question.
We have had some timeout problems reported on a nightly batchjob, and we have located the query in question.
The query is from a .Net application, generated by some framework
I have also found the query in Query Store.
But if i use 'tracked queries', all executions are reported as good, no timeouts (and no errors)
And if i query the QueryStore for that particular queryID, all executions has result 'Regular'
If i setup an extended events session to catch timeouts, the timeouts of the query is indeed collected and i can see that the actual call of the query is done with
EXEC SP_EXECUTESQL N'querystring'
does this somehow cheat the querystore into thinking it is ok, no matter what the result ?
regards
Peter
Best Answer
Queries executed with
sys.sp_executesql
can definitely be logged in the Query Store as "Aborted" or "Exception." Here's a small repro. First I created a database with impractical Query Store settings:Then I ran some .NET code that exercised the following scenarios:
Here's how the came through in the Extended Events "rpc_completed" event:
I embedded a comment of
/* Track Me */
in each of the queries so I could find them in the query store:As expected, we see Regular, Exception, and then three Aborted queries.
All that is to say that I don't think the discrepancy can be explained by the application's use of
sys.sp_executesql
.If you could elaborate more on:
Then you might be able to get some more help tracking this down.