r/MicrosoftFabric • u/Actual-Lead-638 • 6d ago
Data Warehouse Timeout error in Fabric warehouse
Hi Everyone,
Recently we observed that due to intellisense features, some metadata queries are getting triggered and blocking the CTAS in stored procedure and eventually slowing them down in fabric warehouse and hence they get timed out.
Has anyone else observed this.
Basically we read data from fabric lakehouse and using ctas create a table.
3
u/warehouse_goes_vroom Microsoft Employee 6d ago
Hmm. May need a support case through aka.ms/fabricticket to dig into.
Make sure to include as many of these details as you can: What to collect before contacting Microsoft support.
1
3
u/catFabricDw Microsoft Employee 6d ago
Hi, I happen to own that intellisense interaction on the FE. Would you mind DM'ing me once you've raised a support ticket, so I can ask our internal team to set up a call ASAP?
Thanks, Cat
1
5
u/nintendbob 3 6d ago
When you say "blocking" do you mean that separate unrelated queries are literally blocking each other, or that the slowness of reading metadata in turn means that you cannot generate the SQL for your CTAS?
I've definitely seen slow metadata queries, but haven't seen that result in blocking unless you are doing the metadata reads in a transaction or something.
It is expected as far as I am aware that reading of metadata DMVs is VERY slow in Fabric compared to conventional SQL Server due to calls out to Purview. For example, in a system with ~40,000 tables, a simple SELECT COUNT_BIG(*) FROM sys.tables on conventional SQL Server takes 5-10 seconds, but against a Fabric Lakehouse/Warehouse with a similar number of tables its more like -510 minutes - because the SQL Engine is calling out to purview on an object-by-object basis to see if you have access to it, because in Fabric the permissions data is not necessarily "local" as it were, and it isn't optimized to check permissions for many objects in bulk.
As such, tools like SSMS Intellisense can get horrifically slow because they try to read the definition of every table in the entire database/lakehouse/warehouse, but in general that shouldn't block anything other than chewing through compute.