CODEWITHSUNDEEP
sqlsql-serversql-server-2005linked-server
Sergey Olontsev
Sergey Olontsev

Reputation: 1104

Query through linked server is very slow

I have 2 SQL 2005 servers SRV1 and SRV2. SRV2 is the linked server on SRV1. I run a stored proc with params on SRV2 and it is completed immediately. But when I run the same proc through the linked server on SRV1, for example EXEC [SRV1].DB_TEST.dbo.p_sample_proc it takes about 8-10 minutes to complete. After restarting SRV2 the problem is gone. But some time later it returns. Does anyone have any ideas what it could be?

Upvotes: 2

Views: 4014

Answers (2)

gbn
gbn

Reputation: 432411

Might need more rights on SRV2, says Linchi Shea in this article

The login used need to run DBCC SHOW_STATISTICS

Edit: After andomar's comment: what does this do?

SELECT * FROM OPENQUERY ('SRV1', 'EXEC DB_TEST.dbo.p_sample_proc')

Upvotes: 1

Andomar
Andomar

Reputation: 238166

In SQL Server Management Studio, check Management -> Activity Monitor on SRV2. That should show you the state of the process that's running p_sample_proc. Maybe it is blocking on a lock from some other process.

It's safe to say that resetting a server removes all locks, and maybe the blocking application takes a while to reconnect to SRV2.

Upvotes: 0

Related Questions