OPENQUERY timing out...

Question

We re-engineered a Linked Server Query to an OPENQUERY after discovering that a Linked Server Query does not use SQL Server Optimizer to get its result set. Linked Servers are set-up to access different relational databases...Oracle...DB2..etc.. Our Linked Server Query in our SSIS Package was timing out sometimes because it typically aggregates its entire data set and then passes it back via dynamic temporary tables. After re-engineering the Linked Server Query to an OPENQUERY, our query went typically 5 to 6 minutes and timing out after 20 minutes to 10-seconds!

Wellllllll this weekend, the OPENQUERY being invoked and utilized in a SSIS Package seemed to have timed out.

At least I think it did with the following message.

Execution terminated by the provider because a resource limit was reached.

How is it possible for an OPENQUERY to time out?

I did Google it and saw that there may be "dangling" SPIDs that may have been open that were preventing the OPENQUERY from running to completion. The problem is that the SQL Server it is trying to Access is an Azure Server rather than VM. So I don't really know how to check SPIDs and dangling entities that may be preventing the OPENQUERY from executing on a Microsoft Azure SQL Server.

Can anyone help me out with this? May be seen something like this in their own environment.

I appreciate any insight and/or help you can provide.

Thanks!

Answer 1

How is it possible for an OPENQUERY to time out?

By default when you run a query against a linked server, SQL Server has a timeout of ten minutes. It does not matter if you are using four-part notation, OPENQUERY, EXEC AT or execute a remote stored procedure.

So if your query against the remote data source takes more than ten minutes, you will get the timeout error.

As for why the query runs for ten minutes, there can be many reasons:

• Lots of data to chug through.
• Bad query plan.
• Blocking.
• Network issues.

The latter may be applicable in your case, if the remote database is in the cloud, and the local server is on-prem. But it could also be any of the other issues.

Answer 2

Hi,

in our SSIS Package

If you are using SSIS then you should use the relevant tags :-)

the OPENQUERY from executing on a Microsoft Azure SQL Server.

Your description is not fully clear to me and in the end of the description is seems like you speak about using OPENQUERY from Azure SQL logical Server, which is not supported.

OPENQUERY only supported from on-premises or from Azure Managed Instance

https://learn.microsoft.com/en-us/sql/t-sql/functions/openquery-transact-sql?view=sql-server-ver15