Calacualtion of Response Time of Query

Hi
How do you calaculate the response time of a query ?
Is it possible to capture from the SQL Server trace?
Regards
ImtiazImtiaz,
The duration of a query can be captured in Profiler using the duration data
column. From within Query Analyzer, use the Execution Time in the status
bar or SET STATISTICS TIME or SELECT GETDATE() before and after the query.
You can also turn on Show Sever Trace and Show Client Statistics.
HTH
Jerry
"Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
news:D22B06A3-8D1A-4018-8FE3-05D9E7E2CC5A@.microsoft.com...
> Hi
> How do you calaculate the response time of a query ?
> Is it possible to capture from the SQL Server trace?
> Regards
> Imtiaz
>|||type this in Query Analyzer
SET STATISTICS TIME ON
run your query
http://sqlservercode.blogspot.com/
"Imtiaz" wrote:
> Hi
> How do you calaculate the response time of a query ?
> Is it possible to capture from the SQL Server trace?
> Regards
> Imtiaz
>|||And look in the message tab for elapsed time
"SQL" wrote:
> type this in Query Analyzer
> SET STATISTICS TIME ON
> run your query
> http://sqlservercode.blogspot.com/
>
> "Imtiaz" wrote:
> > Hi
> >
> > How do you calaculate the response time of a query ?
> > Is it possible to capture from the SQL Server trace?
> >
> > Regards
> > Imtiaz
> >
> >|||I want the response time. Not the througput time.
Does CPU time translate to Response Time ? Definitely Elapsed Time is the
total time of execution of query '
Regards
Imtiaz
"SQL" wrote:
> type this in Query Analyzer
> SET STATISTICS TIME ON
> run your query
> http://sqlservercode.blogspot.com/
>
> "Imtiaz" wrote:
> > Hi
> >
> > How do you calaculate the response time of a query ?
> > Is it possible to capture from the SQL Server trace?
> >
> > Regards
> > Imtiaz
> >
> >|||Sorry to stress again...
I need Reponse Time Not the Duration Time of the Query.
Response Time : is the time it takes for the first record to appear to the
client
"Jerry Spivey" wrote:
> Imtiaz,
> The duration of a query can be captured in Profiler using the duration data
> column. From within Query Analyzer, use the Execution Time in the status
> bar or SET STATISTICS TIME or SELECT GETDATE() before and after the query.
> You can also turn on Show Sever Trace and Show Client Statistics.
> HTH
> Jerry
> "Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
> news:D22B06A3-8D1A-4018-8FE3-05D9E7E2CC5A@.microsoft.com...
> > Hi
> >
> > How do you calaculate the response time of a query ?
> > Is it possible to capture from the SQL Server trace?
> >
> > Regards
> > Imtiaz
> >
> >
>
>|||Well how would you capture that at the SQL Server level only?
If there is dial up connection at the client then the result will take
longer than for example a T1 connection
You can give the client the illusion that the results are there faster
(particulary for a scrollable grid) by using with (fast n) in your select
statement as a hint
http://sqlservercode.blogspot.com/
"Imtiaz" wrote:
> Sorry to stress again...
> I need Reponse Time Not the Duration Time of the Query.
> Response Time : is the time it takes for the first record to appear to the
> client
> "Jerry Spivey" wrote:
> > Imtiaz,
> >
> > The duration of a query can be captured in Profiler using the duration data
> > column. From within Query Analyzer, use the Execution Time in the status
> > bar or SET STATISTICS TIME or SELECT GETDATE() before and after the query.
> > You can also turn on Show Sever Trace and Show Client Statistics.
> >
> > HTH
> >
> > Jerry
> > "Imtiaz" <Imtiaz@.discussions.microsoft.com> wrote in message
> > news:D22B06A3-8D1A-4018-8FE3-05D9E7E2CC5A@.microsoft.com...
> > > Hi
> > >
> > > How do you calaculate the response time of a query ?
> > > Is it possible to capture from the SQL Server trace?
> > >
> > > Regards
> > > Imtiaz
> > >
> > >
> >
> >
> >|||Imtiaz wrote:
> Sorry to stress again...
> I need Reponse Time Not the Duration Time of the Query.
> Response Time : is the time it takes for the first record to appear
> to the client
>
Duration is more or less the response time, just not exactly as you
describe it. Use Profiler for more complete statistics. The CPU is the
time SQL Server took to execute the query. The Duration is the total
time from query execution to rowset fetch completion. The number your
looking for is probably somewhere in between (assuming CPU value is from
a single CPU). You might find what you're looking for in the Query
Analyzer - Show Client Statistics menu option. OTOH, the response time
you are looking for could easily be calculated from the client
application. Assuming you are sending all your SQL through common
routines, you could easily add either a conditional compiler argument or
a parameter to the common routine that captures the execution time.
Assuming you're not running the queries asynchronously, it should be a
trivial matter to determine when the query returns after the first row
is ready to be fetched.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Imtiaz,
By default, the optimizer tries to minimize the response time of the
last row of the resultset. If you want to optimize the return of the
first row of the resultset, then you should add the FAST hint, in this
case OPTION(FAST 1).
As others have mentioned, the way to measure 'default' response times,
you can use SET STATISTICS TIME and/or SELECT CURRENT_TIMESTAMP.
You might be able to simulate the response time of the first row by
running the command SET ROWCOUNT 1 before the batch (and resetting it
afterwards with SET ROWCOUNT 0).
Hope this helps,
Gert-Jan
Imtiaz wrote:
> Hi
> How do you calaculate the response time of a query ?
> Is it possible to capture from the SQL Server trace?
> Regards
> Imtiaz