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

Question

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.

Pattison

Are you using Sql Server Management Studio? It generally displays the time elapsed for each query, albeit only with second resolution. Also see this related question: #8248087 – Louden

Answer 1

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.

Gasconade

I looked for Sql Profiler doc on how to do this but couldn't find doc that didn't require hours of reading. Can you recommend a "Profiler for Dummies" link? – Pattison

@Pattison I know I'm late but the MSDN links are perfect for your "[Subject] for Dummies" needs :). Try to take a look at this How To: Use SQL Profiler – Indebted

Has anyone else had issues with using this in sql management studio? I added it to a set of about 15 queries in a stored procedure for testing and it takes way too long to run. I canceled at 7 minutes and all the timers added up was only about 2 minutes. So I think there is some return text cache issue or maybe it takes too long to calculate all the datediffs for so many. – Casi

@Hanoncs: There is a small amount of time used to evaluate GETDATE() and assign the result to a variable, and a small amount of time to evaluate the DATEDIFF(), and return the result. The simplistic approach I proposed was to get a rough measurement for singleton queries. I wouldn't recommend using this approach within a tight loop in a stored procedure. If I had a series of queries in a stored procedure, I might use this approach to add some debugging output at some judicious points, adding a discriminator column so I could know which line in the procedure emitted which result. – Gasconade

I usually stick SET @t1 = GETDATE(); at the top of my query and then paste SET @t2 = GETDATE();SELECT 'NOTE 1',DATEDIFF(millisecond,@t1,@t2) AS elapsed_ms;SET @t1 = GETDATE(); at judicious points within the query (editting "NOTE 1" appropriately). Treating the selects as breakpoints rather than measurements is semantically identical to your approach (though the final set to @t1 is spurious, and this assumes all queries should be measured). This is purely a mental/typing optimization (one paste per breakpoint, rather than two pastes per query). – Scott

Answer 2

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)

Rambow

Just a note that this function isn't available if your access to the database is read-only. To use SET STATISTICS TIME, users must have the appropriate permissions to execute the Transact-SQL statement. The SHOWPLAN permission is not required. from: technet.microsoft.com/en-us/library/ms190287.aspx – Coincide

Is there a way where I can see the whole time that a stored procedure needs to execute? Right now I see a lot of single measurements. – Sibilant

@Rookian, I added some code to the answer to help you with that. – Rambow

What Messages window? – Yoder

Answer 3

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.

Breann

Can this be used to find the execution time of two queries run in the same batch? – Carrel

Answer 4

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

Stroud

That gives the time in nanoseconds. Milleseconds would be DATEDIFF(MS,@StartTime,@EndTime) – Leisurely

Answer 5

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

Soutache

I changed the MICROSECOND to MILLISECOND and to clear the cache everytime I inserted following lines between begin and declare @t0 ...: CHECKPOINT; DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE;. Works like charm and exactly what I was looking for. +1 – Gablet

I have been using yr snippet to gauge incremental performance tweaks to a stored procedure, very slick! – Birr

Thank you both. I've been doing sql for a long time now, its a quirky language. But once you know what the kinks are and how to spin them to your advantage, well it helps a lot XD – Soutache

This works well. However, if the result set is large, there is a way to inhibit SSMS from showing the results set while still running the entire query .... See also my own question which I self-answered : dba.stackexchange.com/questions/307922/… – Purim

Answer 6

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

Kentish

Answer 7

`declare @time1 as datetime =getdate()

SELECT * FROM [dbo].[Sheet1] where name like '%d%'

declare @time2 as datetime =getdate() select @time2-@time1`

This simply works champ

Ceramics

Answer 8

A little bit change answer of @spencer7593

DECLARE @tStart DATETIME;
SET @tStart = GETDATE();
-- hard query
PRINT ('Elapsed:' + CAST(  DATEDIFF(millisecond,@tStart,GETDATE()) AS varchar) + ' ms');

Result:

Elapsed:250 ms

Inference