SQL Server Performance Forum – Threads Archive

Measuring Query Time

When I run a query in Query Analyzer i see the time that the query took in the status bar area. When a query takes less than 1 second, I get 0:00:00 for the total amount of time it took to run the query. Is there a way to find out exactly how long the query actually took to run? thanks.

I donÂ´t remember if there is any option in SQL Analizer, but using Profiler you will find in miliseconds how much it take.
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell

I cant remember where it is in sql2000, but in sql7 options, you can find a check box that says ‘show query time’. chris

Can you use a GETDATE() function before the start of the query and store in a variable and then at the end calculate the time taken for the query to execute. Also I think time statistics can be shown using SET options. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Yes, you can use SET STATISTICS TIME ON/OFF to measure your time.
To get accurate results, you might want to consider using DBCC DROPCLEANBUFFERS and/or DBCC FREEEPROCCACHE. See BOL for details and please don’t do this on a production server!!! Frank
http://www.insidesql.de
http://www.familienzirkus.de

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
declare @d datetime
select @d = getdate()
select count(*) from pubs.dbo.authors
select datediff(ms, @d, getdate())

1) In Query Analyzer, Click the ‘Query’ menu
2) Select ‘Show Server Trace’
3) Run the query
4) Click the new tab called ‘Trace’ at the bottom

]]>