Why Your Azure Synapse Pipeline Is Slow: 5 Real Fixes from Production

I lost 6 hours of pipeline runtime to five quiet mistakes. Here’s exactly what broke, what I found in the logs, and how I fixed each one.

Non-member, click here to read

Photo by Claudio Schwarz on Unsplash

I spent almost two full days staring at logs before I figured out what was wrong.

Our nightly pipeline had gone from 45 minutes to 6 hours.

No deployments.

No schema changes.

Nothing had changed.

It just got slow and stayed slow.

My first instinct was to blame Azure.

Outage?

Capacity issue?

I checked the service health dashboard. Everything green. Great.

So I started digging.

What I eventually found wasn’t one problem. It was five separate issues that had been quietly building up, and they all decided to show up at the same time. Classic.

I’m writing this down because every single one of these has come up in conversations with other data engineers. You’ve probably hit at least one of them. Let me save you the two days.

1. The Integration Runtime switch nobody noticed

When we enabled Managed Virtual Network on our Synapse workspace, we did it for security reasons. Compliance requirement. Made sense at the time.

What we didn’t realize is that enabling Managed VNet automatically moved all our Copy Activities from the standard Azure IR to the Managed VNet IR. We never explicitly changed anything. It just happened.

Here’s why that matters: Managed VNet IR doesn’t keep a warm compute node ready for you. Every time a copy activity kicks off, it has to spin up a fresh copy.

That warm-up isn’t huge for one activity, maybe a few minutes. But we had 40+ copy activities chained together.

Suddenly, you’re paying that cost 40 times over.

We switched back to standard Azure IR for the pipelines that didn’t actually need the VNet isolation. If you’re stuck with Managed VNet for compliance (like we eventually were on some pipelines), the workaround is to consolidate. Fewer, bigger copy activities instead of many small ones.

"connectVia": {
    "referenceName": "AutoResolveIntegrationRuntime",
    "type": "IntegrationRuntimeReference"
}

That one config change recovered about 90 minutes of runtime.

2. Trusting Azure’s “Auto” DIU setting

DIU stands for Data Integration Unit. Think of it as the compute power behind a copy activity: CPU, memory, and network all bundled together. More DIUs means more throughput.

The default setting is Auto. Azure will figure it out.

Except when it doesn’t.

Auto works fine when you’re copying one big file. Azure looks at the file, estimates the work, and allocates accordingly.

But when you’re copying hundreds of small JSON files from ADLS Gen2, it looks at each one, sees it’s small, and assigns maybe 2 to 4 DIUs total. Meanwhile, you need those files processed in parallel across a decent number of threads.

I checked the Copy Activity run details in Synapse Studio. DIU utilization was barely hitting 15%. We were underutilizing compute the entire time.

The fix was to set it manually:

"parallelCopies": 16,
"dataIntegrationUnits": 32

One step that was taking 48 minutes came down to 11. Same data, same pipeline, just telling Azure to actually use the resources available.

Worth checking your own runs: if DIU utilization is low, you’re leaving speed on the table. If it’s pegged at 100%, go higher.

3. The CCI problem that nobody warned me about

This one genuinely surprised me, even after 5 years of working with Synapse.

Our dedicated SQL pool tables all use Clustered Columnstore Indexes, which is the right call for analytical workloads. CCIs compress data into row groups and enable fast scans across large datasets.

The catch is how they handle frequent small loads.

Every time you load data, if the batch is under a certain threshold (roughly 102,400 rows), it goes into a delta store instead of a compressed row group. Delta stores are basically an uncompressed overflow area.

Over time, especially if you’re running your pipeline multiple times a day, you accumulate a pile of these small, uncompressed row groups sitting alongside your compressed data.

Your queries start scanning both. Performance tanks. Azure Advisor eventually shows up with a warning about low segment quality and you wonder why it took so long to flag it.

Query to check the damage:

SELECT  
    object_name(object_id) AS table_name,
    row_group_id,
    state_description,
    total_rows,
    size_in_bytes
FROM sys.dm_pdw_nodes_db_column_store_row_groups
WHERE state_description != 'COMPRESSED'
ORDER BY size_in_bytes DESC;

If you’re seeing a lot of OPEN or CLOSED rows, run the rebuild:

ALTER INDEX [your_cci_index] ON [dbo].[your_table] REBUILD;
UPDATE STATISTICS [dbo].[your_table];

We made both of these a post-load Script Activity in the pipeline. Downstream report queries dropped by about 60% in runtime. I’m honestly a bit embarrassed this wasn’t already there from day one.

4. Hammering the Azure SQL sink without checking

This one was obvious in hindsight, but only because I finally looked at the right metric.

Our pipeline writes to an Azure SQL Database as an intermediate layer, a staging area before data moves to the dedicated SQL pool. The database was on General Purpose 8 vCores. Seemed reasonable.

What I hadn’t checked was DTU utilization during the load window. When I finally pulled it up in Azure Monitor, it was sitting between 97 and 100% the entire time the pipeline was running.

Throttling, retries, more throttling. Every downstream activity that depended on that write was waiting for retries to clear.

Two things fixed this.

First, we started disabling non-clustered indexes before the bulk load and rebuilding them after. This is one of those best practices you know about but skip because it feels like extra steps:

-- before the load
ALTER INDEX [idx_your_index] ON [dbo].[your_table] DISABLE;

-- after the load
ALTER INDEX [idx_your_index] ON [dbo].[your_table] REBUILD;

Every row insert into a table with active indexes means the database maintains those indexes in real time. Turn them off during bulk loads, it makes a big difference.

Second, we added scaling around the load window. Scale up before, scale back down after:

# bump up before pipeline starts
az sql db update \
  --name your-db \
  --resource-group your-rg \
  --server your-server \
  --service-objective BC_Gen5_16

# back to normal after it finishes
az sql db update \
  --name your-db \
  --resource-group your-rg \
  --server your-server \
  --service-objective GP_Gen5_8

Costs a bit more during the load window. But it costs a lot less than the retries were costing us in time and sanity.

5. We built the pipeline like it was 2018

This is the one I’m most embarrassed about.

We had 12 Copy Activities running one after another in a straight line. Some of them were correctly dependent: dimension tables before fact tables, obviously.

But a lot of them had no relationship to each other at all. The orders table doesn’t care about inventory.

Neither cares about customer_profiles.

We just loaded them sequentially because that’s how we built it the first time and nobody ever went back to revisit it.

The total pipeline time was basically the sum of all 12 activity runtimes. There was no reason for that.

In Synapse, independent activities can run in parallel. Use ForEach with isSequential set to false:

{
    "name": "ForEach_LoadIndependentTables",
    "type": "ForEach",
    "typeProperties": {
        "isSequential": false,
        "batchCount": 5,
        "items": {
            "value": "@pipeline().parameters.tableList",
            "type": "Expression"
        },
        "activities": [
            {
                "name": "CopyTableData",
                "type": "Copy"
            }
        ]
    }
}

Draw out your dependency graph before you restructure. Anything without an arrow between it can run in parallel. We went from 12 sequential activities down to 3 parallel batches.

3.5 hours became 55 minutes. From this one change alone.

I should have done this years ago.

Where we landed

Pipeline went from 6 hours back down to 45 minutes.

If your pipeline is slow right now

Don’t just throw more compute at it. Profile it first.

Check what IR your copy activities are actually using. Look at DIU utilization numbers in the run details, not just the settings you configured. Query your CCI row group health.

Pull up Azure Monitor during your load window and watch DTU in real time. Draw your dependency graph and count how many sequential activities actually need to be sequential.

Most slow pipelines have multiple things going wrong at once. Fix them one layer at a time.

I write about the messy, unglamorous side of Azure data engineering. **Follow **along if that sounds useful.