Forum Discussion
Lakehouse Cold Start
Hi Team,
There is a Production workspace with customer which is not Live yet, however, when the internal Business users are querying a specific report via intercal client API, the query gets timeout out due to Lakehouse Cold Start.
This issue is not user-specific, it is related to whether the query has been executed recently.
If the query has already run, cached data is available and subsequent users running the same query within the cache window (around 15 minutes) should not experience the timeout. If no recent execution has occurred and the system is in a cold state, the first execution can take around 30 seconds, which causes the downstream API layer to time out.
As system is expected to go live soon and client will start using these reports, the queries can still get timeout out, if this issue is not resolved until then.
Looking out for a solution to rectify this and have the lakehouse up and warm whenever there is a SQL endpoint hit.
Feel free to ask more questions if the requirement is not clear.
Thanks
Ati Puri
Hi ati_puri ,
Thanks for reaching fabric community. will always happy to assist.You've correctly diagnosed the issue. This is the Fabric SQL Analytics Endpoint cold start problem a well-known platform behaviour. Let me give you the honest picture along with practical workarounds.
Why This Happens
The first execution of a query can be unexpectedly slower than subsequent runs this is a cold start caused by system initialization. Data is loaded from OneLake into memory because it's being accessed for the first time and isn't yet cached. Fabric automatically pauses nodes after a period of inactivity to reduce cost, and resuming them adds to query duration. Microsoft Learn
The ~30 second delay you're seeing is exactly this resume + cache-load time.
Workaround 1 Scheduled "Keep Warm" Pipeline (Most Practical)
Create a lightweight Fabric Pipeline that runs a simple dummy query against the Lakehouse SQL Endpoint every 10–12 minutes (within the ~15 min cache window you mentioned).
SELECT TOP 1 * FROM your_schema.your_tableSchedule this as a recurring pipeline trigger. It keeps the endpoint warm so real user queries never hit a cold state. This is the most reliable self-service fix available today.
Workaround 2 Add Retry Logic in Your API Layer
Microsoft recommends adding retry logic with delay to handle transient cold start delays use exponential backoff to avoid retry storms. Microsoft Learn
In your intercal client API, implement:
- Retry up to 3 times with a 10–15 second wait between attempts
- This handles the cold start transparently without the user ever seeing a timeout
This is actually the architecturally cleaner fix compared to keep-warm, because it handles unexpected cold starts even if the warm-up pipeline misses a window.
Workaround 3 Run a Warm-Up Query at App Startup
If your client API has an initialisation/startup phase, fire a lightweight background query to the SQL endpoint when the app loads before any user triggers a real query. This warms the cache proactively at session start.
Workaround 4 Pre-compute with Direct Lake Semantic Model
If your reports are Power BI based, consider switching to a Direct Lake Semantic Model on top of the Lakehouse. Direct Lake has its own warm-up mechanism and is significantly more optimised for report query performance compared to hitting the SQL endpoint directly via API.
There is no native "keep warm" setting in Fabric as of now Microsoft doesn't expose a configuration to prevent node pausing on the Lakehouse SQL Endpoint. The keep-warm pipeline is a community workaround, not an official feature. Worth submitting this as a feature request on the Fabric Ideas Forum it's a real gap.
Recommended Approach for Go-Live
Priority
Action
Must have
Retry logic with backoff in your API layer
Recommended
Scheduled keep-warm pipeline every 10 min
Nice to have
Warm-up query on app/session startup
Combine Workarounds 1 + 2 for the most robust solution before go-live. Hope this helps!
Please consider marking this as the Accepted Solution to help other community members find this fix more easily. If this helped you, Kudos are always appreciated!
5 Replies
Hi ati_puri ,
Thanks for reaching fabric community. will always happy to assist.You've correctly diagnosed the issue. This is the Fabric SQL Analytics Endpoint cold start problem a well-known platform behaviour. Let me give you the honest picture along with practical workarounds.
Why This Happens
The first execution of a query can be unexpectedly slower than subsequent runs this is a cold start caused by system initialization. Data is loaded from OneLake into memory because it's being accessed for the first time and isn't yet cached. Fabric automatically pauses nodes after a period of inactivity to reduce cost, and resuming them adds to query duration. Microsoft Learn
The ~30 second delay you're seeing is exactly this resume + cache-load time.
Workaround 1 Scheduled "Keep Warm" Pipeline (Most Practical)
Create a lightweight Fabric Pipeline that runs a simple dummy query against the Lakehouse SQL Endpoint every 10–12 minutes (within the ~15 min cache window you mentioned).
SELECT TOP 1 * FROM your_schema.your_tableSchedule this as a recurring pipeline trigger. It keeps the endpoint warm so real user queries never hit a cold state. This is the most reliable self-service fix available today.
Workaround 2 Add Retry Logic in Your API Layer
Microsoft recommends adding retry logic with delay to handle transient cold start delays use exponential backoff to avoid retry storms. Microsoft Learn
In your intercal client API, implement:
- Retry up to 3 times with a 10–15 second wait between attempts
- This handles the cold start transparently without the user ever seeing a timeout
This is actually the architecturally cleaner fix compared to keep-warm, because it handles unexpected cold starts even if the warm-up pipeline misses a window.
Workaround 3 Run a Warm-Up Query at App Startup
If your client API has an initialisation/startup phase, fire a lightweight background query to the SQL endpoint when the app loads before any user triggers a real query. This warms the cache proactively at session start.
Workaround 4 Pre-compute with Direct Lake Semantic Model
If your reports are Power BI based, consider switching to a Direct Lake Semantic Model on top of the Lakehouse. Direct Lake has its own warm-up mechanism and is significantly more optimised for report query performance compared to hitting the SQL endpoint directly via API.
There is no native "keep warm" setting in Fabric as of now Microsoft doesn't expose a configuration to prevent node pausing on the Lakehouse SQL Endpoint. The keep-warm pipeline is a community workaround, not an official feature. Worth submitting this as a feature request on the Fabric Ideas Forum it's a real gap.
Recommended Approach for Go-Live
Priority
Action
Must have
Retry logic with backoff in your API layer
Recommended
Scheduled keep-warm pipeline every 10 min
Nice to have
Warm-up query on app/session startup
Combine Workarounds 1 + 2 for the most robust solution before go-live. Hope this helps!
Please consider marking this as the Accepted Solution to help other community members find this fix more easily. If this helped you, Kudos are always appreciated!
Hi Lodha_Jaydeep ,
Thanks for suggesting to post this an idea.
I have submitted my idea here for the same -Need Inbuilt capability for Lakehouse SQL Endpoint... - Microsoft Fabric Community
Please vote for it.
Thanks
Ati Puri
Hello ati_puri
Your observation does align with Microsoft docs around the SQL endpoint becoming idle around 15 mins of inactivity. A cold start could take 30 secs, and if your client API expects a 10 - 20 secs latency it will time out.
SQL Analytics Endpoint Performance Considerations - Microsoft Fabric | Microsoft Learn
In order to prevent the endpoint becoming inactive, you could schedule lightweight "keep-alive" pipelines that touch frequently used tables/views. Setting this up would be simple, doesn't involve architectural designs, and shouldn't consume a significant amount CUs.
You can also look at increasing the reqeust timeout, and if possible look at adopting a retry mechanism with a set time delay. This doesn't remove the cold start, but would reduce the chances of a time out.
Microsoft has designed Fabric to be an elastic shared capacity, keeping a compute instance hot would simply waste capacity and increase cost. In your case you only need to prevent the SQL Analytics Endpoint from going fully idle (≈15 minutes), which triggers cold start on the next query.
ati_puri as already explained by other expert,
The most robust native way to solve this is to schedule a lightweight query to run just before the cache window expires.
Additionally please consider below performance optimizations too:
A 30-second cold start suggests the engine is struggling to scan metadata or initial files. You can minimize this "startup tax" by:
1. V-Order: Ensure the underlying Delta tables in the Lakehouse are V-Ordered. This optimizes the Parquet file structure for the Fabric engines, making the initial read significantly faster.
2. Compaction: Run the OPTIMIZE command to merge small files. If the "cold" engine has to open 1,000 small files to answer the first query, it will time out. Fewer, larger files load faster.
Microsoft Standard documentation:- https://learn.microsoft.com/en-us/fabric/data-warehouse/v-orderHi ati_puri ,
Thank you anuragccsu, Lodha_Jaydeep , deborshi_nag for your inputs.Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.
Best regards,
Chaithra E.
