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

Question

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)

Leave a Comment Cancel reply