Measure the time it takes to execute a t-sql query

I have two t-sql queries using SqlServer 2005. How can I measure how long it takes for each one to run?

Using my stopwatch doesn't cut it.

6 Answers

If you want a more accurate measurement than the answer above:

set statistics time on -- Query 1 goes here -- Query 2 goes here set statistics time off 

The results will be in the Messages window.

Update (2015-07-29):

By popular request, I have written a code snippet that you can use to time an entire stored procedure run, rather than its components. Although this only returns the time taken by the last run, there are additional stats returned by sys.dm_exec_procedure_stats that may also be of value:

-- Use the last_elapsed_time from sys.dm_exec_procedure_stats -- to time an entire stored procedure. -- Set the following variables to the name of the stored proc -- for which which you would like run duration info DECLARE @DbName NVARCHAR(128); DECLARE @SchemaName SYSNAME; DECLARE @ProcName SYSNAME=N'TestProc'; SELECT CONVERT(TIME(3),DATEADD(ms,ROUND(last_elapsed_time/1000.0,0),0)) AS LastExecutionTime FROM sys.dm_exec_procedure_stats WHERE OBJECT_NAME(object_id,database_id)=@ProcName AND (OBJECT_SCHEMA_NAME(object_id,database_id)=@SchemaName OR @SchemaName IS NULL) AND (DB_NAME(database_id)=@DbName OR @DbName IS NULL) 

One simplistic approach to measuring the "elapsed time" between events is to just grab the current date and time.

In SQL Server Management Studio

SELECT GETDATE(); SELECT /* query one */ 1 ; SELECT GETDATE(); SELECT /* query two */ 2 ; SELECT GETDATE(); 

To calculate elapsed times, you could grab those date values into variables, and use the DATEDIFF function:

DECLARE @t1 DATETIME; DECLARE @t2 DATETIME; SET @t1 = GETDATE(); SELECT /* query one */ 1 ; SET @t2 = GETDATE(); SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms; SET @t1 = GETDATE(); SELECT /* query two */ 2 ; SET @t2 = GETDATE(); SELECT DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms; 

That's just one approach. You can also get elapsed times for queries using SQL Profiler.

Another way is using a SQL Server built-in feature named Client Statistics which is accessible through Menu > Query > Include Client Statistics.

You can run each query in separated query window and compare the results which is given in Client Statistics tab just beside the Messages tab.

For example in image below it shows that the average time elapsed to get the server reply for one of my queries is 39 milliseconds.

You can read all 3 ways for acquiring execution time in here. You may even need to display Estimated Execution Plan ctrlL for further investigation about your query.

DECLARE @StartTime datetime DECLARE @EndTime datetime SELECT @StartTime=GETDATE() -- Write Your Query SELECT @EndTime=GETDATE() --This will return execution time of your query SELECT DATEDIFF(MS,@StartTime,@EndTime) AS [Duration in millisecs] 

You can also See this solution

even better, this will measure the average of n iterations of your query! Great for a more accurate reading.

declare @tTOTAL int = 0 declare @i integer = 0 declare @itrs integer = 100 while @i < @itrs begin declare @t0 datetime = GETDATE() --your query here declare @t1 datetime = GETDATE() set @tTotal = @tTotal + DATEDIFF(MICROSECOND,@t0,@t1) set @i = @i + 1 end select @tTotal/@itrs 

Click on Statistics icon to display and then run the query to get the timings and to know how efficient your query is