Long-time SqlCommand.ExecuteReader() stored procedure execution when sys.dm_exec_procedure_stats.max_elapsed_time is short

Question

I have an application that calls an Azure SQL stored procedure "report.GetReportCell". To call this procedure my app uses SqlCommand.ExecuteReader() method.

Sometimes calls are too slow. In Dependency telemetry I see a lot of records with:

• my proc name ("report.GetReportCell") in the Data field
• long (> 25,000) Duration

I suspect the stored procedure is too slow but I can't confirm it:

• sys.dm_exec_procedure_stats.max_elapsed_time is short (less than 8 sec)
• sys.query_store_runtime_stats doesn't contain any related long-term query
• when I call this proc in SSMS it works fast

What could be causing the slowdown when calling a stored procedure with a SqlCommand.ExecuteReader() method?

UPD 2022-07-21

I have read an excellent article about "Parameter Sniffing Problems" and other other possible reasons for slow executions - https://www.sommarskog.se/query-plan-mysteries.html

But I think the reason for the slowdown is something else. Because:

• Query Store doesn't contain any related slow query
• sys.dm_exec_procedure_stats.max_elapsed_time for report.GetReportCell is small

Answer 1

I had the exact same issue and @DeepDave-MT had the answer. The procedure was taking 30 seconds in the application but 0 seconds via SSMS. When I ran it in SSMS with SET ARITHABORT OFF, I found that it ran for 22 seconds. Adding SET ARITHABORT ON directly to the stored procedure made my application run in <1 second.