1

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
bandarlogen
  • 11
  • 3
  • 1
    [SQL Server stored procedure runs fast in SSMS and slow in application](https://www.mssqltips.com/sqlservertip/4318/sql-server-stored-procedure-runs-fast-in-ssms-and-slow-in-application/), [Stored procedure slow when called from web, fast from Management Studio](https://stackoverflow.com/questions/6585417/stored-procedure-slow-when-called-from-web-fast-from-management-studio) and [Monitoring Performance of Compiled Stored Procedures](https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/monitoring-performance-of-natively-compiled-stored-procedures?view=sql-server-ver16) – Ecstasy Jul 18 '22 at 07:59
  • [Symptoms of Parameter Sniffing in SQL Server](https://www.sqlshack.com/symptoms-of-the-parameter-sniffing-in-sql-server/) – Ecstasy Jul 18 '22 at 08:02
  • @DeepDave-MT thanks a lot for the links! What confuses me is that the slow operation of a stored procedure is not reflected in the statistics in any way (sys.dm_exec_procedure_stats, sys.query_store_runtime_stats). Is it ok for Parameter Sniffing Problems? – bandarlogen Jul 18 '22 at 08:32

1 Answers1

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.

Greg
  • 83
  • 5
  • Thanks for the answer, but I think that Parameter-Sniffing it's not my case. Because I don't have any slow query in the Query Store. – bandarlogen Jul 28 '22 at 16:08
  • Also I propose to read the article https://www.sommarskog.se/query-plan-mysteries.html It expains why SET ARITHABORT ON is not a real solution (pt. 5.1 A Non-Solution). – bandarlogen Jul 28 '22 at 16:12