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.
This question is related to
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.
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)[email protected] AND (OBJECT_SCHEMA_NAME(object_id,database_id)[email protected] OR @SchemaName IS NULL) AND (DB_NAME(database_id)[email protected] OR @DbName IS NULL)