Question

A scheduled T-SQL stored procedure in a Fabric Warehouse joins a large fact table to several dimensions and fails intermittently with a query timeout during peak hours. The Capacity Metrics app shows the capacity is not throttled, and the same procedure succeeds when run manually off-peak. You need to identify the most likely root cause and a targeted resolution without over-provisioning. What should you do first?

Accepted Answer

Review the query execution plan and statistics; update stale statistics on the joined tables and rewrite the query to reduce data movement so it completes within the timeout window.

Answer

Increase the Fabric capacity SKU to a higher tier to guarantee more compute for the procedure during peak hours.

Answer

Enable result set caching on the Warehouse so the procedure returns cached results and avoids re-executing during peak load.

Answer

Convert the Warehouse tables to a Lakehouse and run the join in a Spark notebook to bypass the T-SQL engine timeout.