locked
SQL Query takes too much of time to Display RRS feed

  • Question

  • Hi All,

    I have one SP which is taking inconsistent time for execution, Sometimes it will take 2 seconds and sometimes it will take more than 15 seconds with the same parameter(I have recompiled the SP & updated all outdated statistics an Removed all index fragmentation).No keylookup and index issues are there, But its taking too much of time probably at evening and the time is not consistent at all.

    So what I have done is, I took the script from the SP and substituted the values for the parameters and put the below code at the start and end of the script. But even though it is showing less than 1 second(700- 900 milli seconds) the SQL server is taking more than 15 seconds to display the results.

    What might be the issue here? Can anyone help on this..

    DECLARE @StartTime DATETIME = GETDATE ()

    ---SQL Script

    SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [Duration in milliseconds]

    Tuesday, October 11, 2016 4:52 AM

Answers

  • Does any running process block the session?
    • Marked as answer by Sandeep777 Thursday, December 8, 2016 4:06 PM
    Wednesday, October 19, 2016 3:26 AM

All replies

  • How many rows returned? Do you have the same problem if you change output query result from Grid to file or text?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, October 11, 2016 4:58 AM
  • But even though it is showing less than 1 second(700- 900 milli seconds) the SQL server is taking more than 15 seconds to display the results.


    By this I would guess the frontend causes the issues and is not fast enough to render the result. Have you checked local CPU usage and I/O when you run the query?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, October 11, 2016 5:50 AM
  • hi Naomi,

    Thanks for your reply!

    The scripts returns multiple dataset(Multiple select statements are there) ,Also 4 small SP's are calling inside the script.That is not causing an issue , I have commented them and checked but the same issue is there.(89 rows are returned by the script).

    Also the CPU is normal at the time of execution.And I have changed the results to text but the issue exists.

    The entire script I have put in between the below code which returns the total time taken by it.This is taking less than one second but the query is showing results after 15 seconds only( This contains multiple select statements).

    I am not able to identify what is causing the issue, because of this the page is taking too much time to load from the application. Why there is a delay in displaying the results even though the entire script execution took less than only 1 second?

    DECLARE @StartTime DATETIME = GETDATE ()

    ---SQL Script

    SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [Duration in milliseconds]


    • Edited by Sandeep777 Tuesday, October 11, 2016 4:30 PM
    Tuesday, October 11, 2016 4:11 PM
  • Hi Olaf,

    Thanks for your reply!

    The CPU and IO is normal at the time of execution. Also the time delay is from the SSMS itself not from the application. Because of this delay the page load in application is slow.I have used the below code to find out the total time taken by the script(in milli seconds).It is showing less than 1000 milli seconds only but the results are displaying in SSMS only after 15 seconds.

    Any idea on why this delay in displaying the results in SSMS ?

    DECLARE @StartTime DATETIME = GETDATE ()

    ---SQL Script which contains multiple select statements and 4 SP's are calling inside this

    SELECT DATEDIFF(ms, @StartTime, GETDATE()) AS [Duration in milliseconds]


    • Edited by Sandeep777 Tuesday, October 11, 2016 4:36 PM
    Tuesday, October 11, 2016 4:28 PM
  • Instead of that better turn on client statistics for the query in SSMS, see How to: Control the Gathering and Display of Client Statistics
    Then you get details about execution time, etc. See also Analyze Script Performance


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, October 11, 2016 4:47 PM
  • HI Olaf,

    I have checked the client statistics and below are the results for the first consecutive executions.

    I can see the "Wait time on server replies" is high. Is there any way to reduce this?

    Time   Statistics      Trail 6       Trail 5       Trail 4       Trail 3       Trail 2     Trail 1  Average
      Client processing time 21 15 21 12 47 35 25.1667
      Total execution time 13293 202 256 217 15339 437 4957.333
      Wait time on server replies 13272 187 235 205 15292 402 4932.167

    Wednesday, October 12, 2016 5:03 AM
  • Client processing time + Wait time on server replies = Total execution time (the order of the values is a bit "inperfect")


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, October 12, 2016 6:46 AM
  • Hi Olaf,

    Is there a way to overcome this issue? I mean to reduce the "Wait time on server replies"?


    From the above result set you can see out of 6 Trails only 2 is having issues rest all are working fine as expected(With same parameters).Is there anyway to reduce this huge "Wait time on server replies"?
    • Edited by Sandeep777 Wednesday, October 12, 2016 6:52 AM
    Wednesday, October 12, 2016 6:48 AM
  • Hi Olaf,

    I have used OPTION(FAST 1000) query hint in the script now the results are displaying pretty fast (in 4 seconds) even though the actual time it took is 1.8 seconds.

    So can you please advise whether the usage of OPTION(FAST 1000) query hint can be used or not?Ids there any drawbacks for this?

    Wednesday, October 12, 2016 7:46 AM
  • At trials when Server respond took time, did you check CPU, memory, blocking, jobs running and queries running on server to check the difference.


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Wednesday, October 12, 2016 9:08 AM
  • HI All,

    The CPU, memory and everything is normal at that time.

    But could not able to understand what exactly the issue is?Also if we execute the query by substituting the parameters it is taking less time, The issue is occurring while it is executed as a SP(Taking more than 15 seconds to execute).Earlier I thought It was due to the parameter sniffing so tried with "Declared local variables and substituting the parameter values to those variables and using them inside the SP".

    and recompiled the SP ,Tried with RECOMPILE option ...what ever I tried it is not giving proper results

    Any solutions for this?

    Monday, October 17, 2016 8:36 AM
  • Does any running process block the session?
    • Marked as answer by Sandeep777 Thursday, December 8, 2016 4:06 PM
    Wednesday, October 19, 2016 3:26 AM
  • Hi Sandeep777,

    Query hint FAST specifies that the query is optimized for fast retrieval of the first number_rows. However, it may not always result in a good execution plan for the whole query. You may need to monitor the performance from time to time.


    Sam Zha
    TechNet Community Support

    Wednesday, October 19, 2016 4:55 AM
  • HI All,

    Got several issues similar to these.In all those the "Wait time on Server Replies" is high and the actual processing time is lower as expected.

    Any suggestions on how to reduce this Wait time and improve the performance.

    Note: At the time of execution the CPU and memory was normal and there were no BLOCKING Sessions also.

    Thursday, October 20, 2016 9:59 AM
  • It would be better that there is an easy way to give an example to test in our machines.
    Thursday, October 20, 2016 10:15 AM