Azure SQL Stored Procedure suddenly run super slow and timeout

Like (0) Share

Report

Posted on 28 May 2024 11:08:59 by MengtingXie

112

I have a Azure SQL Stored Procedure, I use the "Execute stored procedure (V2)" to run it in the cloud flow.

Last week it return a 3000 rows table for only 30 seconds, but this week suddenly the workflow timeout always. The Stored procedure works fine by running directly in Azure Data Studio and maximum runs 59 seconds. It only run slow in Power Automate and timeout after 110 seconds. I checked that it might be the parameter sniffing in SQL, but I don't think fits my case.

Is there any changes for "Execute stored procedure (V2)" action recently? or any others from the community facing the same issue and find the solution?

All responses (1)

Answers (1)

Verified answer

MengtingXie 112 on 11 Jun 2024 at 07:36:58

Like (0)

Report
Re: Azure SQL Stored Procedure suddenly run super slow and timeout

I found the solution for this issue myself. It is about "Parameter Sniffing". I tried two solutions from this article https://www.linkedin.com/advice/0/what-best-practices-avoiding-parameter-sniffing

The first solution for avoiding parameter sniffing is to use local variables, this doesn't help. On the contrary it makes my stored procedure slower. Not sure why.

The fourth solution for avoiding parameter sniffing is to use RECOMPILE hints. This is the great savior for my stored procedure running by Power automate. I just added OPTION (RECOMPILE); at the end of my stored procedure, the the stored procedure running by Power Automate will always finish within 30 seconds. No timeout anymore.

OPTION (RECOMPILE);