none
Remote query via linkedserver run long time and cannot return until it reach time out sometimes RRS feed

  • 问题

  • Dear,

        I am working on 2 sql2005withsp2 sql servers standard edition. Recently, my remote query via linkedserver run long time and cannot return until it reach time out sometimes, and sometimes it execute successfully in 20 seconds. The time out error message is: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

        I traced the finnal 2 sql commands  on remote server(spid=111) as below:

    declare @p1 int
    set @p1=1
    declare @p2 bigint
    set @p2=177051458093370
    exec [sys].sp_getschemalock @p1 output,@p2 output,N'"One_DB"."dbo"."cv_AView @p1, @p2

    declare @p1 int set @p1=NULL exec sp_prepexec @p1 output,NULL,N'SELECT "Tbl1002"."SNAPSHOT_DATE" "Col1004","T

    bl1002"."PN" "Col1005","Tbl1002"."QTY" "Col1009" FROM "One_DB"."dbo"."cv_AView" "Tbl1002"' select @p1

        There is no following sp_unprepare and sys.sp_releaseschemalock executions on remote server, the sql process(spid=111) is reused by other processes now, but the local server is still waitfor Runable, OLEDB(resource is remote server(SPID=111)), Could someone please give me a suggestion to locate the problem? Thank you.


    Active DBA

    2012年11月15日 6:31

答案

  • Finally, since the remote view is complex and its result dataset is samll, so we save the data of the view into a table locally, and then query that table remotely, it works fine for months.

    Thank everybody, sorry for late to close this ticket.


    Active DBA

    • 已标记为答案 Anson Dai 2013年10月10日 6:15
    2013年10月10日 6:08

全部回复

  • 执行过程中等待信息是什么?Linked Server使用的账户有没有远程数据库DB_OWNER权限?如果没有的话可能对性能会有影响,无法使用正确的统计信息。

    参考这篇文章:http://blogs.msdn.com/b/sqlsakthi/archive/2011/05/09/best-performer-distributed-query-four-part-or-openquery-when-executing-linked-server-queries-in-sql-server.aspx

    2012年11月15日 9:12
  • 关注一下

    给我写信: QQ我:点击这里给我发消息

    2012年11月15日 13:34
  • Did you try run those queries on linked server locally?
    2012年11月15日 16:54
  • Thanks for your suggestion, the local process is running and still waitfor (OLEDB, remote server process(111)), but the remote process(spid=111) is reused by other softwares.

    I don't care about performance right now, because sometimes it can return from the remote view without any parameter in 20 seconds. somettime, it is running for 1 hour and caused time out.


    Active DBA

    2012年11月16日 1:54
  • No, I only run the 2 queries via this linkedserver one by one.

    Thanks for your reply.


    Active DBA

    2012年11月16日 1:56
  • You can run those queries on linked server directly and check execution plan.
    2012年11月16日 4:47
  •   Thanks rmiao, our software need to access the 2 remote queries via linked server, they are two simply views, we don't care about the performance or/and the execution plan right now since sometimes it can finished successfully in 20 seconds, but sometimes it keep running for an hour, the network have a very small number of lost packet, but we cannot explain the the process of local server keep running for an hour and the process of remote server have been reused by other software after 10~30 seconds.

    Active DBA

    2012年11月19日 2:13
  • That's why should test queries on linked server directly to find out what caused slowness. Another option is setting profiler trace on linked server to get details.
    2012年11月19日 3:06
  • Yes, I made the tests on the linked server directly, all test finished successfully. I also had some finding by the profiler trace as my first post, but I cannot find the root cause.

    Active DBA

    2012年11月19日 4:54
  • Which events did you trace? Included connection settings and wait status?
    2012年11月19日 6:28
  • Thank you for following up again. I traced by the default events: ExistingConnection, RPC:Completed, SQL:BatchCompleted, SQL:BatchStarting, there is no wait status event or column, ? I noticed that the process status of local server is running and waiting for OLEDB(linked server process) for long time and the process status of linked server is running in that 10~30 seconds and reused by other software suddenly.

    Active DBA

    2012年11月19日 7:17
  • That sounds link related issue to me, did you trace the process on both servers?
    2012年11月19日 7:46
  • 请问LZ你跟踪的时候有选择这些事件吗?


    给我写信: QQ我:点击这里给我发消息

    2012年11月19日 11:43
  • Thanks 桦仔, we restarted the linked server and it looks OK now in 3 days, I tried the remote queries again, the remote server cannot trace any OLEDB events, but the local server can trace some OLEDB events, I will monitor them later while we meet the problems again.

    Thanks all.


    Active DBA

    2012年11月21日 1:16
  • Finally, since the remote view is complex and its result dataset is samll, so we save the data of the view into a table locally, and then query that table remotely, it works fine for months.

    Thank everybody, sorry for late to close this ticket.


    Active DBA

    • 已标记为答案 Anson Dai 2013年10月10日 6:15
    2013年10月10日 6:08
  • Or create index on the view.
    2013年10月10日 13:12