none
Long running select query RRS feed

  • Question

  • Good morning,

    Here is the select query that is running for long time

    SELECT TE.JobID,TotalDistanceTravelled as Distance,--convert(varchar(30),TotalFare) as TotalFare,
    
    TotalTripFare AS TotalFare
    
    FROM [sqlcdscluster\sqlcds].cdsbusiness.dbo.Tbljobbooking MW WITH(NOLOCK)
    
    INNER JOIN dbo.tbltripenddetail TE WITH(NOLOCK) ON MW.JobID=TE.jobID
    
    AND DATEDIFF(HOUR,TripEndTime,Getdate())<=1 AND TripendStatus =0 AND TE.JOBID!=0
    
    INNER JOIN [sqlcdscluster\sqlcds].cdsbusiness.dbo.tblCitrusSendingDetails CS WITH(NOLOCK) ON TE.JobID=CS.JobID
    
    AND ( ( (CS.Response IS NOT NULL AND CS.Response!='') AND DATEDIFF(MINUTE,TripEndTime,Getdate())<=20 )
    
    OR DATEDIFF(MINUTE,TripEndTime,Getdate())>21 )
    
    ORDER BY TE.JobID DESC
    Actually, we cannot use OPENQUERY instead of linked server because we will be migrating to amazon cloud and our architect instructed us not to replace linked server with OPENQUERY.

    Here is the actual execution plan:

    Please help if there are other ways to tune this query.



    B.Chaitanya Kiran

    Tuesday, August 18, 2015 12:33 PM

Answers

  • The performance for when running a query on a remote (linked) Server depends on the performance capabilities of the remote Server (for the database, SQL Server and the Server) and also the speed of the link between the two Servers.

    In your screenshot above the number of Executions is 1; this would suggest that the returned data is not being staged in the Page file prior to the results being returned.

    Also the actual cost appears to be pretty low as well.

    I would suggest you run the Query again both remotely and local and display the actual execution plans and compare.  (https://msdn.microsoft.com/en-us/library/ms189562.aspx?f=255&MSPPError=-2147217396);

    If running it locally seems to be a lot quicker then this could be an issue with the Network speed and/or the NIC configuration.

    Also look at the Database Maintenance and see if this is up to date.


    Please click "Mark As Answer" if my post helped. Tony C.

    Tuesday, August 18, 2015 1:20 PM

All replies

  • Do you have the execution plan from the remote query? 
    Also, can you rerun the query with set statistics IO enabled and post the results here?
    Tuesday, August 18, 2015 12:51 PM
  • As far as I can see the Remote Query---(Querying the linked server table) is having high cost as expected which is causing the latency. To my understanding, there are limitations in tuning this part. Although if you are querying the data frequently from Linked server table, then you can use temporary tables for session.

    Hope this helps ! ------------------------------------------------------Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

    Tuesday, August 18, 2015 1:02 PM
  • The performance for when running a query on a remote (linked) Server depends on the performance capabilities of the remote Server (for the database, SQL Server and the Server) and also the speed of the link between the two Servers.

    In your screenshot above the number of Executions is 1; this would suggest that the returned data is not being staged in the Page file prior to the results being returned.

    Also the actual cost appears to be pretty low as well.

    I would suggest you run the Query again both remotely and local and display the actual execution plans and compare.  (https://msdn.microsoft.com/en-us/library/ms189562.aspx?f=255&MSPPError=-2147217396);

    If running it locally seems to be a lot quicker then this could be an issue with the Network speed and/or the NIC configuration.

    Also look at the Database Maintenance and see if this is up to date.


    Please click "Mark As Answer" if my post helped. Tony C.

    Tuesday, August 18, 2015 1:20 PM
  • What's the purpose of:

    AND DATEDIFF(MINUTE,TripEndTime,Getdate())<=20 )
    OR DATEDIFF(MINUTE,TripEndTime,Getdate())>21 )

    and

    DATEDIFF(HOUR,TripEndTime,Getdate())<=1 

    Each join is an inner join, so why not just use the minute filter at the top instead of a hour filter?

    I'd also check the waits on the remote server with the following query:

    SELECT *
    FROM sys.dm_os_waiting_tasks


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Tuesday, August 18, 2015 2:15 PM
  • Sir/Madam,

    Actually, our architect instructed not change linked servers because we will be migrating to cloud soon and it will be helpful. Any other areas or places where it can be tuned?


    B.Chaitanya Kiran

    Friday, August 21, 2015 10:57 AM
  • Can i set statistics IO and run the query in production during business hours?

    B.Chaitanya Kiran

    Friday, August 21, 2015 10:59 AM
  • Can i set statistics IO and run the query in production during business hours?

    B.Chaitanya Kiran

    That's not something that we could answer here. If the query simply runs for a long period of time and doesn't affect system resources (disk, memory, cpu, network) and also doesn't cause blocking or affect users then it might be OK.

    I'd suggest that you seek manager approval before you knowingly run anything in production that could be potentially harmful to the workload.


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Friday, August 21, 2015 2:36 PM