Query trace

hi all,
we are running performance tests on our application and as load increases the duration of the query increase but the time in cpu (on sql trace) remains constant. During the test, executing any query on the database server itself does not show any degradation with load. This leads me to believe sql server is spending most of the time in sending back the data.
we are using jdbc for connectivity and apache commons pool for connection pooling.
How can we trace the journey of a query so as to pin point the issue ?is it possible to do so in sql server or we have to take the hard way of tcp dump and monitor the web server etc...
thank you
dan

hi luis,
thanks for the reply.
lets say most of time spent by sql server is in sending back the result to the client.
my understanding is profiler can has events such as amount of time spent in cpu / recompile / locks etc...but not network events.
how could i track network issues on the profiler or through DMV's ?
dan

If you run the query between SET STATISTICS IO ON and OFF, you will get the time the query took to actually get the data. The difference between this time and the timeyou see in the bottom right corner of your query analyzer window where you see elapsed time is the time it took for the data to reach the client (in this case query analyzer window).

Are you seeing total CPU use on the SQL machine increase with load? If not, then it probably isn't a network problem.
From your description, it sounds more like a queuing issue -- either the disk queue or perhaps at the web tier. How are you measuring query duration?
Have you looked at the SQL performance counters, disk queue depth, etc?