CODEWITHSUNDEEP
sql-serverazurestored-proceduresazure-data-factory
MiKeZZa
MiKeZZa

Reputation: 75

Stored Procedure runs slow when executed by DataFactory

I've made a Stored Procedure which runs in 14 minuten in SQL server (on Azure DB, the data stays in the same database).

When this Stored Procedure is executed as a pipeline in Data Factory then it takes around 1 hour or more. And when I double the selected period SQL execution take around 30 minutes, but DF even gets a time-out after 4.5 hours.

I'm pretty sure that DF is only a tool, that would use the normal SQL engine so that the performance would be identical, but it's not. Somebody any idea about what's going on?

Upvotes: 2

Views: 2108

Answers (2)

Lalatendu Mohanty
Lalatendu Mohanty

Reputation: 29

I have been facing the similar issue in ADF, where my TSQL procedure is overrunning for more than 4 hours than 2 hours normal completion time during run in SSMS.

As a workaround I split the Procedure into sequence of sub procedure and calling the master procedure to mitigate the slowness. I tried to do the troubleshooting from Query Store in SSMS, but did not get much help from it to find the root cause.

Upvotes: 0

Steve Lovell
Steve Lovell

Reputation: 2564

This "answer" comes a few years late, but I stumbled across this question when running into similar problems of my own. A stored procedure which took seconds to run via SSMS took around 20 minutes to complete when triggered (with the same parameters and work to do) via Azure Data Factory.

However, this slowness was only present when running my ADF pipeline via the Debug options. As soon as I ran it via a normal trigger, it completed in the normal amount of time. If you're seeing similar symptoms or it may be that like mine yours are a result of using the Debug options ... or perhaps you've enabled some diagnostics within Azure that have a similar effect.

No promises that this will fix your issue, but I thought I'd put this out there in case it's useful to anyone.

Upvotes: 1

Related Questions