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
performance
sql-server-2005
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)
Click on Statistics icon to display and then run the query to get the timings and to know how efficient your query is
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]
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.
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
Source: Stackoverflow.com