Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
5,373 questions
Cold Start Query Performance
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(76.8, 45%, 17%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESB%3C/text%3E%3C/svg%3E)
Steve Busi
0
Reputation points
When running queries against the parquet format in the new DL, the initial "cold start" query takes significantly longer (e.g., 50 seconds) compared to subsequent queries (e.g., 10 seconds). This is double the time compared to the existing DL (25 seconds initially), posing a risk of timeout issues and potential data loss in integrations that run 24/7, as retries may be needed.
Azure Synapse Analytics
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(252.8, 99%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJN%3C/text%3E%3C/svg%3E)
J N S S Kasyap 3,625 Reputation points Microsoft External Staff Moderator2025-04-11T17:55:41.1266667+00:00 Hi @Steve Busi
If you're executing the same query and observing variations in the query durations, several reasons might cause this behavior:
- Check if this is the first execution of a query. The first execution of a query collects the statistics required to create a plan. The statistics are collected by scanning the underlying files and might increase the query duration. In Synapse Studio, you'll see the "global statistics creation" queries in the SQL request list that are executed before your query.
- Statistics might expire after some time. Periodically, you might observe an impact on performance because the serverless pool must scan and rebuild the statistics. You might notice another "global statistics creation" queries in the SQL request list that are executed before your query.
- Check if there's some workload that's running on the same endpoint when you executed the query with the longer duration. The serverless SQL endpoint equally allocates the resources to all queries that are executed in parallel, and the query might be delayed.
Serverless SQL pool self-help - Azure Synapse Analytics | Microsoft Learn
Why is the first query execution in Azure Synapse Serverless SQL pool slower
Cold start concept explained in the below thread please refer this thread
https://learn.microsoft.com/en-us/answers/questions/1655993/synapse-serverless-sql-pool-first-query-takes-longTo address the cold start performance issue and mitigate risks, consider the following strategies:
- Use tools like Spark’s coalesce or repartition to reduce the number of Parquet files. Fewer files mean less metadata to read.
- Identify frequently queried Parquet files or partitions and cache them explicitly (e.g., Spark’s cache() or persist()).
- Configure the cluster to maintain a minimum number of executors or containers to avoid initialization delays
- Ensure the new DL’s cluster has sufficient CPU/memory to handle metadata and query planning without contention.
- Ensure predicate pushdown, column pruning, and statistics-based skipping are enabled in the query engine
- Repartition data to create larger, more manageable partitions (e.g., 128MB–1GB per file) to reduce overhead.
I hope this information helps. Please do let us know if you have any further queries.
Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
Thank you.
-
J N S S Kasyap 3,625 Reputation points Microsoft External Staff Moderator
2025-04-14T16:46:29.48+00:00 @Steve Busi
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help. -
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Anonymous2025-04-15T11:41:42.8466667+00:00 @Steve Busi We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
-
Steve Busi 0 Reputation points
2025-04-15T15:26:59.6066667+00:00 Thank you. We are waiting for feedback from the team and will get back to you ASAP.
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(64, 68%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESJ%3C/text%3E%3C/svg%3E)
Singireddi JagadishKumar 0 Reputation points Microsoft External Staff Moderator2025-04-17T13:13:34.6733333+00:00 @Steve Busi
I just wanted to follow up and see if you had a chance to review above response from Rakesh to your question. Please let us know if it was helpful, and feel free to reach out if you have any further queries. -
Singireddi JagadishKumar 0 Reputation points Microsoft External Staff Moderator
2025-04-18T05:47:17.62+00:00 @Steve Busi
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help. -
Steve Busi 0 Reputation points
2025-04-22T20:40:20.91+00:00 Thanks. This appears to have resolved our issue. Thanks!
Sign in to comment
Sign in to answer