How to measure query response time

Question

How to measure query response time

barryFS

SSC Eights!

Points: 972
More actions
March 1, 2012 at 10:57 am

#269336

Hi,
I am using perfmon to gather stats about my servers / instances.
I do this by scheduling a SQL Agent job to run every minute, which executes the TYPEPERF command.
This information is written to a SQL Server table.
One of our developers has asked if I can also start monitoring average query response time.
I have looked at the available Perfmon counters, but I can't see anything that would give me what I want.
If it is at all possible I would like to do this using Perfmon, so that everything is kept together in one place.
However, I am will to adopt another method if that is the only way to go.
Can anyone advise the best way to achieve this?
Many Thanks
Barry

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sengeet86 Ten Centuries Points: 1252 More actions · Answer 1

use sql server profiler to get the query execution time and all other details.

http://www.mssqltips.com/sqlservertip/2040/use-sql-server-profiler-to-trace-database-calls-from-third-party-applications/

java programming

barryFS SSC Eights! Points: 972 More actions · Answer 2

Hi,

Thanks for the reply - I will read through the article.

One thing that I failed to mention in my original post is that the queries I need to monitor will be SOAP requests.

Does that make any difference?

Best Regards

Barry

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 3

Since ur using SOAP request , are you looking for total request response time or just the query execution time ?

If you want to monitor the actual procedures being called you could simply setup a trace in sql server and have it log the required details.

Jayanth Kurup[/url]

Steve Thompson-454462 SSCrazy Points: 2624 More actions · Answer 4

sengeet86 (3/1/2012)
use sql server profiler to get the query execution time and all other details.
http://www.mssqltips.com/sqlservertip/2040/use-sql-server-profiler-to-trace-database-calls-from-third-party-applications/

Profiler is certainly a valuable tool for measuring query performance, but it's worth noting that running it on production servers can sometimes carry too much overhead.

If you have a development box to run it on, then firing up Profiler and watching your queries in real time can give you valuable insight. If you have to do this on production, I'd recommend setting up server-side traces. They do the same thing that Profiler does, but have a smaller footprint.

http://www.sqlservercentral.com/stairway/72363/