TempDB usage behavior in Azure Synapse SQL Pool wi...

ahwanmishra

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 using DirectQuery against Synapse SQL. We are observing frequent tempdb full issues, and the issue gets resolved only when users close their Power BI reports in the browser.

My key questions are:
1. When a Power BI report is loaded using DirectQuery, is the tempdb space allocated during query execution held/reserved for the entire duration that the report remains open?
2. Or is tempdb released immediately after the query execution / visual load completes, regardless of whether the report tab remains open?
3. Does keeping a Power BI report open and idle (no active refresh or interaction) still maintain active sessions or tempdb allocations on the Synapse SQL side?

Additional context:
* The tempdb issue consistently disappears once users close their Power BI reports from the browser.
* This suggests tempdb or sessions might be tied to active DirectQuery connections.
* We want to confirm whether this is expected behavior or a misconfiguration.

Any insights, documentation references, or architectural recommendations would be greatly appreciated.
Thanks in advance!

Zanqueta

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