In synapse pipeline which activity is better, stored procedure or sql pool stored procedure

Question

In synapse pipeline which activity is better, stored procedure or sql pool stored procedure

PRABHAKARAN MATHIVANAN 20

Mar 22, 2025, 2:20 a.m.

I would like to understand the main difference between stored procedure vs sql pool stored procedure activities in Synapse pipelines. Which is better to execute stored procedure against sql dedicated pool?. Need insights from both performance and cost perspective to decide which to use in the Synapse pipelines.

Ganesh Gurram 5,390 Reputation points Microsoft External Staff

Mar 24, 2025, 4:13 a.m.

@PRABHAKARAN MATHIVANAN - Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Accepted answer

1 additional answer

Your answer

Ganesh Gurram 5,390 Reputation points Microsoft External Staff

Mar 24, 2025, 4:13 a.m.

@PRABHAKARAN MATHIVANAN - Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

The Stored Procedure Activity is a generic activity that can execute stored procedures against various SQL-based services, including Azure SQL Database and Synapse SQL pools (both dedicated and serverless). In contrast, the SQL Pool Stored Procedure Activity is specifically optimized for Synapse Dedicated SQL Pools, leveraging its Massively Parallel Processing (MPP) architecture for better performance.

The SQL Pool Stored Procedure Activity is better suited for executing stored procedures in Synapse Dedicated SQL Pools because it directly integrates with the MPP engine, optimizing execution plans and reducing overhead. The Stored Procedure Activity works via a generic ADO.NET connection, which may introduce additional latency and inefficiencies in query execution for dedicated SQL pools.

SQL Pool Stored Procedure Activity is more cost-effective when working with Synapse Dedicated SQL Pools, as it efficiently utilizes parallel processing and reduces query execution time, minimizing compute usage costs. Stored Procedure Activity may lead to higher costs due to potential inefficiencies and increased execution times, especially when handling large datasets in a dedicated pool.

If you are executing a stored procedure against an Azure SQL Database or Synapse Serverless Pool, use Stored Procedure Activity. If you are executing a stored procedure against a Synapse Dedicated SQL Pool, use SQL Pool Stored Procedure Activity for better performance and cost efficiency.

For Synapse Dedicated SQL Pools, the SQL Pool Stored Procedure Activity is the recommended option as it ensures optimal execution, reduces query latency, and minimizes compute cost.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

PRABHAKARAN MATHIVANAN 20 Reputation points

Mar 24, 2025, 1:39 p.m.

Hello @Marcin Policht . Thank you so much for the details. It did make sense. Do you have any official Microsoft link on this? Because before asking this I did lot of research and couldn't find anything from official site. So if you have where this details are available please do share as that will be helpful.

Answer 2

Ganesh Gurram 5,390 Microsoft External Staff

Mar 25, 2025, 2:31 a.m.

@PRABHAKARAN MATHIVANAN

These are the MS documentations that might help you:

Hope this helps!

Share via

In synapse pipeline which activity is better, stored procedure or sql pool stored procedure

1 additional answer

Your answer