long durations for query intermittently

  • Hi Folks,

    I was running profiler on a sql2008 r2 standard instance 10.50.4290. Occasionally I get long duration on batchcompleted for the following query

    The explain plan for the query is doing an index seek and not much else.

    the indexes are defragmented and stats are updated

    the query returns about 10000 rows small amount of reads and there is no blocking from a blocked process report

    When the query takes long time the application server is at 100% cpu. I do notice network_io_async as a significant wait

    It seems to me that the long duration of the query (from batch completed) may be caused by the client doing some intensive process with the dataset the query returns.. Is that possible? My original thinking was the time it take a query to run (i.e duration in batch completed) is not influenced by client processing.

  • djordan 4543 (2/3/2014)


    Hi Folks,

    I was running profiler on a sql2008 r2 standard instance 10.50.4290. Occasionally I get long duration on batchcompleted for the following query

    The explain plan for the query is doing an index seek and not much else.

    the indexes are defragmented and stats are updated

    the query returns about 10000 rows small amount of reads and there is no blocking from a blocked process report

    When the query takes long time the application server is at 100% cpu. I do notice network_io_async as a significant wait

    It seems to me that the long duration of the query (from batch completed) may be caused by the client doing some intensive process with the dataset the query returns.. Is that possible?

    Hi

    Yes it's possible, the client application doing intensive operations and the session is waiting.

    Another reason could be the network hardware.

    You should inspect these two reasons.

    Regards,

    Igor Micev

    Igor Micev,My blog: www.igormicev.com

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply