Forum Discussion
Solved
TempDB usage behavior in Azure Synapse SQL Pool with Power BI DirectQuery
I’m trying to understand the impact of Power BI DirectQuery on Azure Synapse SQL (dedicated/serverless SQL pool), specifically around tempdb usage and lifecycle. We have multiple Power BI reports us...
Hi ahwanmishra,
This behaviour is often misunderstood because DirectQuery introduces a persistent connection model that differs from Import mode.1. Does tempdb remain allocated while the report is open?
Yes, in most cases. When Power BI uses DirectQuery, it establishes a session with the Synapse SQL pool. This session can create temporary objects (spools, hash tables, intermediate results) in tempdb during query execution.
If the connection remains open (which it does while the report is active), the session and its associated tempdb allocations may persist until the session is closed.2. Is tempdb released immediately after query execution?
Not always. While some temporary structures are released after the query completes, others tied to the session context (e.g., cached query plans, session-level temp tables) remain until the session ends.
This explains why closing the report frees tempdb space — because the DirectQuery session is terminated.3. Does an idle report still maintain active sessions?
Yes. Even if the report is idle, the DirectQuery connection typically stays alive to support quick interactions. This means:- The session remains active.
- Any session-level tempdb allocations are retained.
- If multiple users keep reports open, tempdb pressure can accumulate.
Why does this happen in Synapse?
Synapse Dedicated SQL Pools use tempdb heavily for query execution (joins, sorts, aggregations). With DirectQuery, each user session can consume tempdb resources, and these are not released until the session ends.
Serverless pools behave similarly but scale differently.Recommendations
- Reduce tempdb pressure:
- Optimise queries (avoid large intermediate results).
- Use aggregations or pre-aggregated tables to minimise heavy joins.
- Consider Import mode for highly interactive reports.
- Connection management:
- Configure Power BI idle timeout or use Gateway settings to close inactive sessions sooner.
- Review Synapse resource class assignments to avoid excessive tempdb usage per session.
- Monitoring:
- Use sys.dm_pdw_exec_requests and sys.dm_pdw_request_steps to track tempdb usage.
- Check active sessions via sys.dm_pdw_exec_sessions.
Official References:
DirectQuery in Power BI: When to Use, Limitations, Alternatives - Power BI | Microsoft Learn
Azure Architecture Center - Azure Architecture Center | Microsoft Learn
If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.
Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.
Hi ahwanmishra,
We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.
Thank you.