none
hanging remote SQL query

    Question

  • Hi ,

    I have a remote query connecting via linked server and suddenly after alter the SP has this update syntax the SP start hanging with no respond for hours...

    the process hang on the remote server while updating and after checked the execution plan on the master server I noticed 98% of the query is consumed in the "remote scan" and the session on the remote server switched to sleeping

    any suggestions would be greatly appreciated

    Tuesday, July 23, 2013 11:04 PM

Answers

All replies

  • Can you show the query?

    http://sqlblog.com/blogs/linchi_shea/archive/2010/12/01/linked-server-and-performance-impact-direction-matters.aspx


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, July 24, 2013 5:36 AM
  • UPDATE  B SET 
    B.Name                    =    A.name                      ,
    B.BasicS                     =    A.BAsicS                      ,
    B.allown                  =    A.allown              ,
    B.NOT       =    A.NOT         ,
    B.MAXNUMBEROFCHI              =    A.MAXNUMBEROFCHI              ,
    B.Hirining             =    A.Hirirning               ,
    B.[status]            =    CS.[Status_Value]                        ,
    B.[number]                 =    A.[Number]                         ,
    B.isvip                          =    A.vip                                    ,
    B.card_currency_code             =    A.Currency                         ,
    B.allow_basic                     =    A.Allow_basic                  ,
    B.TOTAL = A.Total,
    B.CustomerStatusID = CASE ISNULL(CU.FTI_Status, 1) WHEN 1 THEN 1 WHEN 3 THEN 2 WHEN 2 THEN 1 END,
    B.ProfileID = A.tProfileID
      
     from 
    Users A 
    INNER JOIN Users_Status CS
    ON A.Status = CS.ID
    inner join Customers cu
    on A.Customer_Id = cu.Id
    inner join 
    Linkedserver01.db1.DBO.Employess B
    ON A.Id = B.user_Id AND 
    (
    A.Salasrylimit                      <> A.salarylimit                       OR
    A.salarylimit                     <> B.salarylimit                        OR
    A.No_Limit                   <> B.No_Limit                    OR
    A.NOT        <> B.NOT        OR
    A.MaxNum               <> B.MaxNUm                OR
    A.Period            <> B.period              OR
    A.limitX               <> B.LimitY                
    
    
    OR A.Salasrylimit  <> B.TOTAL  
    OR ( A.TOTALDAilY <> B.DAILYDAILY  and A.allow <> 0) 
    OR (B.CustomerStatusID <>CASE ISNULL(CU.FTI_Status, 1) WHEN 1 THEN 1 WHEN 3 THEN 2 WHEN 2 THEN 1 END)
    OR (B.ProfileID <> A.tProfileID)
    
    
    )
    WHERE 
    
    left(a.Serial,6) in 
    (
    select b.Serial from 
    dbo.ProfilesTbl1 as a
    inner join
    dbo.ProfilesTbl2 as b
    on
    a.profile_id = b.id
    where
    a.client_Id=2
    )
    

    Wednesday, July 24, 2013 8:01 AM
  • I would suggest  you to get the data from linked server's employee table locall and then perform a join...BTW, stop using LEFT function in such way as it prevents from sql server to use useful index (if one exists) on Serial column.

    a.Serial in 
    (
    select b.Serial from 
    dbo.ProfilesTbl1 as a
    inner join
    dbo.ProfilesTbl2 as b
    on
    a.profile_id = b.id
    where
    a.client_Id=2
    )



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, July 24, 2013 9:41 AM