Why does SQL Server return some rows during query execution but Lakehouse in Fabric doesn't?

[marcoG]

In general there are some operators that require all rows to come to them before they can start processing them and pass them downstream, there are other operators that are able to start streaming or pass the found rows immediately.

This can happen due to row destinations introduced by the user, by the optimizer or rather because, for some reason, a wrong plan is chosen for example due to insufficient statistics.

When queries begin returning data immediately, but do not finish immediately, it is usually a sign that the optimizer has chosen a plan to quickly locate and return some rows using operators that have a lower startup cost.

Why in Fabric, even using a connection with sql server management studio, does it never happen that the results of a long query are shown before the query is completed?

[AndyDDC]

Great question @marcoG (i'd like to see what @Anonymous comes back with).  I'm going to suggest it may be something to do with the way the SQL Endpoint (based on the synapse serverless sql pools) engine has been architected.  It distributes the workload out to a series of nodes (automatic scale out) and must receive the results from all the nodes used in the query before combining and returning back to the client.  I'd say a long running query won't display any rows as it doesn't know what rows to display before all the nodes work is done and results returned.  There's a description about the process here:

Workload management - Microsoft Fabric | Microsoft Learn

[marcoG]

Hi @AndyDDC ,

HI
I have the impression that the Fabric lakehouse and data warehouse SQL endpoints perform less well than the Azure Synapse serveless SQL pools.
How to test I used an F64 capacity but while in Fabric I have to wait several minutes to get a Power BI paginated report rendered (without the possibility of seeing the progressive loading of the rows) in Azure Synapse Serveless Pool this happens within the minute with a management of loading incremental rows

[AndyDDC]

Hi @marcoG I'm suprised you're seeing worse performance in Fabric vs Serverless SQL Pools.  What is your underlying data in Serverless? parquet/delta?  If so are the file sizes the same (eg compaction and vacuuming happening).  What's the performance like if you run the SQL query that the paginated report generates on the sql endpoint vs serverless?

[marcoG]

For the moment it's just a feeling, we would need some comparative tests of the 2 products.

I haven't found anything to this effect online.
In any case, on serveless pools I use external tables of parquet files on adls gen2 while on fabric I obviously use delta tables but the underlying data is the same.
In my opinion, the access latency to Onelake Vs Adls Gen2 and above all the execution plans Fabric SQL Vs Azure Serveless Pool should be better investigated

[Anonymous]

Hi @marcoG ,

We are reaching out to the internal team to get more information related to your query and will get back to you as soon as we have an update.

[marcoG]

is there any news?

Thank you

[Anonymous]

Hi @marcoG ,

We didn't get any update from internal team.  I will get back to you as soon as we have an update.
Appreciate your patience.

[Anonymous]

Hi @marcoG ,

Apologies for the issue that you are facing.

This might require a deeper investigation from our engineering team about your workspace and the logic behind it to properly understand what might be happening. 

Please go ahead and raise a support ticket to reach our support team:

https://support.fabric.microsoft.com/support
Please provide the ticket number here as we can keep an eye on it.

[marcoG · Accepted solution]

Hi @Anonymous ,

I don't think it's a problem with my workspace, I wish it were.
It is clearly a general problem as the performance of the Azure Synapse Serveless Pools is superior to that of the Fabric SQL endpoints and furthermore the Azure Synapse serveless pools guarantee, like the other databases, the incremental fetch of the response while in the Fabric SQL endpoint this does not happen by having to wait for the query to complete before getting the response.
Thanks anyway for the attempt to support
Greetings