Cold Start Query Performance

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-long

To address the cold start performance issue and mitigate risks, consider the following strategies:

1. Use tools like Spark’s coalesce or repartition to reduce the number of Parquet files. Fewer files mean less metadata to read.
2. Identify frequently queried Parquet files or partitions and cache them explicitly (e.g., Spark’s cache() or persist()).
3. Configure the cluster to maintain a minimum number of executors or containers to avoid initialization delays
4. Ensure the new DL’s cluster has sufficient CPU/memory to handle metadata and query planning without contention.
5. Ensure predicate pushdown, column pruning, and statistics-based skipping are enabled in the query engine
6. 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.

@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 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.

Thank you. We are waiting for feedback from the team and will get back to you ASAP.

@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.