djordan 4543 (2/3/2014)
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?
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.
SQL Server developer at Seavus