Microsoft Fabric Warehouse – Cross-Warehouse vs. Same-Warehouse Query Performance

Hi mh2587​,

Adding the "why" behind the observation above that there is no meaningful difference, plus a way to prove it on your own workload.

Why it is usually the same. Warehouses in the same workspace are not separate servers. A cross-warehouse query runs on the same distributed SQL engine, which reads both tables' Delta/Parquet files directly from OneLake. There is no linked-server-style hop that moves FactSales or DimProduct between databases first:

same-warehouse join  -> engine reads A.FactSales + A.DimProduct from OneLake
cross-warehouse join -> engine reads A.FactSales + B.DimProduct from OneLake
                        (same engine, same read path, no copy step)

So for questions 1 and 3: no inherent extra data movement, and similar performance. For question 2: CU is driven by data scanned and CPU used, which is the same in both layouts if the data is the same.

What can make them differ in practice has to do with the copies rather than the location:

statistics   -> copied tables have fresh stats, originals may not
file layout  -> a fresh copy can be better compacted / V-Ordered
data volume  -> a filtered copy scans less than the original
caching      -> the second run of any test benefits from warm cache

That is the usual explanation when someone sees the copied version run faster: it is the newer data layout, not the single warehouse.

Measuring it on your data. Label both variants, run each a few times, and compare them in query insights:

-- variant 1: cross-warehouse
SELECT COUNT(*)
FROM WarehouseA.dbo.FactSales AS f
JOIN WarehouseB.dbo.DimProduct AS d ON f.ProductID = d.ProductID
OPTION (LABEL = 'xwh_join');

-- variant 2: both tables in WarehouseA
SELECT COUNT(*)
FROM WarehouseA.dbo.FactSales AS f
JOIN WarehouseA.dbo.DimProduct_copy AS d ON f.ProductID = d.ProductID
OPTION (LABEL = 'samewh_join');

SELECT label,
       COUNT(*)                            AS runs,
       AVG(total_elapsed_time_ms)          AS avg_ms,
       AVG(allocated_cpu_time_ms)          AS avg_cpu_ms,
       AVG(data_scanned_remote_storage_mb) AS avg_remote_mb
FROM queryinsights.exec_requests_history
WHERE label IN ('xwh_join', 'samewh_join')
GROUP BY label;

Ignore the first (cold) run of each, and use COUNT(*) or an aggregate rather than SELECT * so client transfer time doesn't dominate. CPU time and data scanned are the closest proxies to CU.

The one real limit: cross-database queries work across warehouses and lakehouse SQL endpoints in the same workspace. Across workspaces you need shortcuts, which is a design constraint more than a performance one.

So my overall approach would be:

keep shared dims in one warehouse, join across (no copies)
-> keep stats and table maintenance current on both sides
-> compare variants with labels in queryinsights if in doubt
-> plan workspace boundaries around the same-workspace rule

That gives you the governance benefit of shared dimensions without paying a performance penalty.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.