当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - 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 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?

网友答案:

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')
网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: