CODEWITHSUNDEEP
azureazure-synapse
Alex
Alex

Reputation: 741

Adding compute instance to Azure Synapse (dedicated pool)

I have a DWH running on Azure Synapse dedicated pool.

In addition to existing nightly/daily ETL processes, I need to add another in parallel that will kill performance of the current instance. That process is required to be run only 1 week per month during day time.

Similar to a Snowflake approach, is it possible to set up independent Azure Synapse compute to process the same data as the first instance? Not a copy of data, but the same data in the same files.

Or should I simply change instance size 2 times a day for 1 weak per month? (Requires to pause all activity)

Any advise will be appreciated!

Thanks!

Upvotes: 0

Views: 96

Answers (1)

Miles Cole
Miles Cole

Reputation: 56

I agree that scaling up or using a serverless SQL pool is a good option.

Before implementing I would also evaluate if the additional (and/or existing) process you are adding is properly optimized for MPP. Validate first that you are effectively co-locating data as much as possible via leveraging common HASH distributions. Often times ETL written first for SQL server (SMP) needs some amount of refactoring to truly leverage the power of MPP.

  1. Look at query plans for long running jobs - is there excessive data broadcasting or shuffling? Fix via updating table distributions
  2. Are statistics available and up to date?

Upvotes: 2

Related Questions