SQL Server Performance

Query trace

Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by danm, Jul 20, 2009.

  1. danm New Member

    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

  2. Luis Martin Moderator

    Welcome to the forum!.May be I don't understand fully your question, but, how to run profiler to find out what you need?
  3. danm New Member

    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 ?
  4. ndinakar Member

    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).
  5. Luis Martin Moderator

    As far I know for network issues you have to use 3rd party tool.
  6. RickNZ New Member

    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?

Share This Page