Skip to main content

Notifications

Power Automate - Using Connectors
Answered

SQL Execute Stored Procedure v2 suddenly very slow

(0) ShareShare
ReportReport
Posted on by 195

Hi All,

I have a simple flow which sets up some variables,  calls a SQL Stored procedure, and then sends the response back to PowerApps.

LaelH_0-1618837636760.png

LaelH_2-1618838170229.png

 

It has been running absolutely fine for months, up until 12 pm today, when it has suddenly gone from taking between 1 and 4 seconds to run, to taking up to and over 40 seconds to run.  Nothing in the data has changed. In fact, the stored procedure itself in Azure SQL is still executing in under 2 seconds - so nothing has changed there.  Something has gone pear in flow and I don't know what?  I had another flow that corrupted totally at about the same time. It just refused to execute at all - just hung up and said "running". To get that one to succeed again I had to save a copy, delete the old, rename back to original, and then delete from Power Apps and re-import to get it right. It cam right. I tried the same with this one, but sadly it hasn't worked. At least this one is still running, but it is just dog slow. 

Any ideas?

Regards

Lael

  • Verified answer
    LaelH Profile Picture
    LaelH 195 on at
    Re: SQL Execute Stored Procedure v2 suddenly very slow

    Hi Kierian.

     

    It turned out to be a Parameter Sniffing issue in SQL - I didn't pick it up because whenever I tested the stored proc in SQL I replaced the parameters with local variables. Anyway, I found stacks in google on how to fix it and I am back to normal.  For anyone else who has the issue, google parameter sniffing and there are lots of strategies offered to fix. What worked for me was parameter masking. Not sure if it is the most elegant solution but is solves the problem.

  • kierian Profile Picture
    kierian on at
    Re: SQL Execute Stored Procedure v2 suddenly very slow

    Hello

    regarding environment duplication that can cause issue because you will have duplicate flows that make same calls to same sql that not expected behavior  please open request with support so environment team can dig more see what's happened.

  • LaelH Profile Picture
    LaelH 195 on at
    Re: SQL Execute Stored Procedure v2 suddenly very slow

    Hi Kierian

     

    No - it is Azure SQL

  • LaelH Profile Picture
    LaelH 195 on at
    Re: SQL Execute Stored Procedure v2 suddenly very slow

    More on the above - it is  now taking over a minute and a half - it is getting worse and worse! I am so frustrated.  I noticed something weird - it seems that my environment has duplicated itself for some reason - could this be the issue? Well, not exactly duplicated, but I now seem to have two environments, both exactly the same name - one has all my flows in it, and the other is empty.

    Confusion reigns supreme.

    Any ideas? Help please?

  • kierian Profile Picture
    kierian on at
    Re: SQL Execute Stored Procedure v2 suddenly very slow

     

    Hello 

    Is this SQL server on prem ?

    If so please check gateway logs network errors or latency

    is the gateway up to date? 

    Have  good day

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,691

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 65,019

Leaderboard

Featured topics