Query through linked server is very slow

Question

I have 2 SQL 2005 servers SRV1 and SRV2. SRV2 is the linked server on SRV1. I run a storep 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?

Answer 1

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.

Answer 2

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')