Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

long durations for query intermittently Expand / Collapse
Author
Message
Posted Monday, February 3, 2014 3:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:04 PM
Points: 58, Visits: 225
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.









Post #1537236
Posted Monday, February 3, 2014 12:55 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:36 AM
Points: 3,076, Visits: 3,193
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,
SQL Server developer at Seavus
www.seavus.com
Post #1537476
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse