Today, I worked on a very interesting case where our customer is running the following query "Select top 20000 *,ROW_NUMBER() over (partition by [field1] order by field2) from dbo.[RemoteTable] that is taking too much time, where RemoteTable could be a table from Linked Server (available in OnPremise and Azure SQL Managed Instance) or Elastic Query (available in Azure SQL Database).
Using Elastic Query, SQL Server will send remotely the following query: select * from dbo.[RemoteTable] and after receiving the rows, the function ROW_NUMBER algorithm will be apply in local.
Using Linked Server, SQL Server will send remote the following query: select * from dbo.[RemoteTable] Order by Field1, Field2 and after receiving the rows, the function ROW_NUMBER algorithm will be apply in local
So, in this situation, using sp_execute_remote for Elastic Database Query and OpenQuery for Linked Server will be the best and faster solution, because the function will force query execution on the remote server.