Forum Discussion
Immense SQL endpoint lag with external data share
Hello everyone,
our setup is:
Tenant A: Mirroring a Business Central database into Fabric, external data share into Tenant B
Tenant B: external data share shortcut landing in lakehouse (arrival in lakehouse works fine within a minute), DBT jobs access data via SLQ (that's why we're quite insistent on using SQL, DBT jobs are great)
Our problem is:
We're no strangers to SQL endpoint lag, but it is reaching unsustainable levels.
- Refreshing via the "Sync Metadata" button in the SQL endpoint GUI takes multiple hours.
- Without refresh the data becomes stale, up to several days old.
- Tables appear in the SQL endpoint GUI table list but are unreachable ("not existing") via SQL queries.
- Pipeline refresh activity and refresh via notebook take less but still too much time.
Our capacity load is well within the limits, that's no probable cause.
Our questions would be:
- A simple fix or workaround is always welcome, but also
- Is this a specific/known problem with external data shares (in combination with SQL endpoint)? Shortcuts should be a simple, latency free thing, right?
6 Replies
Hi NotebookEnjoyer,
What is the estimated size of this lakehouse?
I've never seen the metadata refresh take more than a minute or two, multiple hours is very much not expected.
Are you working with terabytes of data to sync?
This likely needs to be a ticket to Microsoft so their engineers can look into it.
It's currently just a testing system, ~200 tables with at most some MB each, so overall well below 1 GB. We're submitting a ticket.
Hi NotebookEnjoyer,
I would separate the freshness of the external share from the freshness of the SQL analytics endpoint here.
Microsoft's external data sharing documentation describes the consumer shortcut as live, in-place access, so changes in Tenant A should be available in Tenant B without an ETL/copy step.
The Lakehouse SQL analytics endpoint has a separate metadata/data synchronization layer though. Microsoft's SQL endpoint performance guidance says that under normal conditions the lag between a Lakehouse and its SQL endpoint should be less than one minute. So multiple hours, and especially days, is definitely not something I would consider normal shortcut latency.
Since you already see the new data in the Lakehouse within a minute, that seems to isolate the delay quite well to the SQL endpoint side.
Besides total data size, I would check the number of Lakehouses in that workspace and the Delta file profile. Microsoft notes that many Lakehouses in one workspace, large numbers of small Parquet files, and heavy Delta change activity can increase metadata-sync latency.
For another diagnostic, the SQL endpoint metadata refresh REST API can refresh specific tables rather than the entire endpoint. If only a few tables feed the DBT job, I would test a targeted refresh and compare the timing with the full Sync Metadata operation.
There is also a new metadata sync architecture currently in preview which Microsoft says can make Lakehouse changes available through SQL within seconds. One important limitation is that it only applies to new SQL endpoints created after enabling the workspace setting, so I would use a new test Lakehouse/endpoint to compare behaviour rather than changing the production design immediately.
If a fresh test endpoint against the same shared data still takes hours to synchronize, I agree with tayloramy that this deserves a Microsoft support case. I would include the Lakehouse/SQL endpoint IDs, affected table names, timestamp when the data became visible through the shortcut, and timestamp when it finally became queryable through SQL.
Our argument against this having nothing to do with the external data share is as follows: If we use a notebook to copy the shortcut tables into a different lakehouse (and thereby materialize them "locally"), the SQL endpoint of this other lakehouse has no significant lag.
Regarding the targeted refresh see my response to GilbertQ .
We're working on testing the new metadata sync, I'll come back to that.
Have you had a look at the latest blog post from Mark with regards to only being able to sync certain tables via a notebook? I think this might help your situation due to the fact that you do not have to wait for the sink for the entire lacasse, but you can do it for specific tables. Another thought is also to make sure that you are doing the maintenance on your lake house tables as that potentially there could also be another issue as to our the sink is taking so long.
https://medium.com/@sqltidy/md-sync-just-got-better-refresh-only-the-tables-you-want-dd17f8410ec1
Hello and thanks, good tip. Refreshing specific tables works and is faster, albeit only proportionally. One table ~40s, the whole 16 necessary in 7min, all in all we have maybe 200 tables mirrored/shortcut-linked; these are small tables!
We can't do lakehouse maintenance, because the source is a mirrored lakehouse where (as far as we know) this isn't available.
While the refresh runs, there are a lot of warnings(?) like this:
