This sproc
create proc dbo.Get_Accounts as
begin
declare @current_date datetime
set @current_date = dbo.fn_currdate()
select [fields]
into dbo.current_accounts
from linkedserver.database.dbo.accounts
where date = @current_date
end
continually fails after 10 minutes with the following error message:
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB'
reported an error. Execution terminated by the provider because a
resource limit was reached. [OLE/DB provider returned message: Timeout
expired] OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ICommandText::Execute returned 0x80040e31: Execution terminated by the
provider because a resource limit was reached.].
However, when I run the same query from the same database (not on the remote one) in an interactive query window with the date hard-coded:
select [fields]
into dbo.current_accounts
from linkedserver.database.dbo.accounts
where date = '1/20/2012'
It returns in 30 seconds.
The local server is SQLSERVER 2008, the remote is SQLSERVER 2000.
We have done the following to no avail:
- Recreated the stored proc.
- sp_recompile on the stored proc
- update statistics on dbo.accounts
- dropped and recreated the indexes on dbo.accounts
- dropped the index on dbo.accounts and try
- DBCC FREEPROCCACHE & DBCC DROPCLEANBUFFERS on both local and remote servers
- Rebooted the remote server (not an easy option on the local one)
Questions
- Can anyone explain this bizarre behavior?
- Any suggestions on other options for correcting it?
Best Answer
You can turn on trace flag 7300 which might give you a more detailed error message
How many rows does a representative query return? How fast/reliable is the network connection between the two servers?
It's possible that a large dataset is taking too long to transfer (on top of the actual query time). You could raise the timeout value.
You can try to reconfigure the timeout setting as follows:
Set the remote login timeout to 300 seconds:
Set the remote query timeout to 0 (infinite wait):
Update: SQL Server 2012 SP1 onwards: users with
SELECT
permission will be able to accessDBCC SHOW_STATISTICS
which will improve read only performance on linked servers. Ref: https://msdn.microsoft.com/en-us/library/ms174384(v=sql.110).aspxUpdate: You are correct in saying it's not the size of data or connection speed. It rang a bell in my foggy memory and I remembered where I had seen it: Slow in the Application, Fast in SSMS?(An Issue With Linked Servers). It's not parameter sniffing, it is the statistics themselves that are missing (due to permissions), causing a bad query plan to be used: