locked
Running a stored proc manually from SSMS takes 16 minutes, running it with logic apps takes > 12 hours RRS feed

  • Question

  • I have a stored proc on an azure SQL database that updates SQL statistics and rebuilds indexes based on fragmentation level (once an index gets above a certain level of fragmentation it'll rebuild it). When I run this manually in SSMS it can take anywhere from 15-30 minutes to run. However, when I run it from logic apps, sometimes it will run just fine, and other times it will run until the timeout I have set (which is 12 hours) then fail. Why would running the proc on logic apps get stuck whereas running it manually always works?

    I'm assuming that the logic app will only fail if there's an index that needs rebuilt because after I run it manually, it seems like the logic app will complete just fine until there's an index that needs rebuilt.

    Also, I never had this issue with whatever I was using to run the stored proc before I had to move it to logic apps since azure was depreciating whatever I was using before (I can't remember what ran the job last time).

    Appreciate any help anyone can provide here or troubleshooting steps. Thank you!

    Friday, November 30, 2018 5:05 PM

All replies

  • Hi,

    Have you tried to find any existing lock on the sql server side when you execute the stored procedure ? If you are running the sql statement in parallel there might be case when your sql resources get locked . 

    I would prefer to look into sql statement execution to troubleshot the issue 

     


    If this answers your question please mark it accordingly. If this post is helpful, please vote as helpful by clicking the upward arrow mark next to my reply

    Sunday, December 2, 2018 7:48 PM
  • Hi,

    Have you compared the query plans? I think that has to be the first step.

    You can do this with SQL Profiler.

    Also, are you running it just once from SSMS, but multiple times from Logic Apps? It is possible for the query to slow down after multiple executions if the data distribution/volume changes dramatically between first and last executions.

    Also Note that from SSMS query execution will always be faster since SSMS is designed in such way it supports threading.

    HTH


    Hope this Helps!!!! Regards, Note: Please Mark As Answered if you satisfy with Reply.

    Monday, December 3, 2018 7:34 AM
  • I've never had a lock when running it manually, no. Do you know how I can find the execution plan for whatever Azure is using when it runs the stored proc (assuming it's different than the one that runs when I run it from SSMS)?
    Monday, December 3, 2018 2:41 PM
  • Hi Vikas, how can I use SQL Profiler to look at the query plan for the stored proc that's run from Azure? 

    I am running it once from SSMS, but I have to run it "multiple times" on Logic Apps (but it's really only run once) due to how Logic Apps works with async calls right? Here's how I currently have it set up:

    https://i.imgur.com/yHL2oDg.png

    Monday, December 3, 2018 3:02 PM