How to get correct query execution time in SQL Server?

Question

I need to get the execution time of my query. I am using

declare @starttime datetime
declare @endtime datetime
set @starttime =getdate()   

 -- execute my query here

set @endtime = GETDATE()
select @endtime-@starttime

But the output is coming as 1900-01-01 00:02:10.707

I need only the time part.

Check out http://stackoverflow.com/questions/595762/calculate-execution-time-of-a-sql-query — Rohan, May 09 '13 at 10:14

n.y · Answer 1 · 2017-09-19T08:35:48.980

62

Use this:

set statistics time on
--query
set statistics time off

then go to the 'Message' tab to see a message like this:

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 165 ms.

edited Sep 19 '17 at 08:35

answered Jan 29 '14 at 17:46

n.y

3,343
3
35
54

score 13 · Answer 2 · edited May 09 '20 at 02:02

13

Set the STATISTICS TIME option:

SET STATISTICS TIME { ON | OFF }

Example:

USE AdventureWorks2012;
GO 

SET STATISTICS TIME ON;    
GO

SELECT ProductID, StartDate, EndDate, StandardCost 
FROM Production.ProductCostHistory
WHERE StandardCost < 500.00;    
GO

SET STATISTICS TIME OFF;    
GO

edited May 09 '20 at 02:02

user2864740

60,010
15
145
220

answered Apr 15 '14 at 07:32

Rohitkumar

151
1
6

score 3 · Answer 3 · answered May 09 '13 at 10:09

3

Use the time datatype which is available in SQL Server 2008 and higher

Now the tags are correct and this is SQL Server 2005...

select CONVERT(varchar(12), @endtime-@starttime, 114)

answered May 09 '13 at 10:09

gbn

422,506
82
585
676

1

@user2360877: so why tag this SQL Server 2008? – gbn May 09 '13 at 10:12

score 3 · Answer 4 · answered May 09 '13 at 10:14

Try with this different convertion after your diff:

SELECT CONVERT(VARCHAR(12),@endtime-@starttime, 108)  -- 00:02:10
SELECT CONVERT(VARCHAR(12),@endtime-@starttime, 114)  -- 00:02:10.707

108 and 114 represent the format type for date conversion, please refer to http://msdn.microsoft.com/it-it/library/ms187928.aspx

score 3 · Answer 5 · edited Jun 10 '20 at 07:31

3

DECLARE @StartTime datetime,@EndTime datetime   
SELECT @StartTime=GETDATE()

--Your Query to be run goes here--

SELECT @EndTime=GETDATE()   
SELECT DATEDIFF(ms,@StartTime,@EndTime) AS [Duration in milliseconds]

edited Jun 10 '20 at 07:31

Michael Bray

14,998
7
42
68

answered Sep 25 '13 at 10:23

Mohammad Atiour Islam

5,380
3
43
48

2

the duration is in milliseconds, not microseconds. Change ms to mcs for microseconds – Conner Feb 05 '20 at 16:52

score 3 · Answer 6 · answered Jul 02 '19 at 07:14

3

An update for those who are using later versions of SQL Server than the OP: In SQL server 2017 (version 14.0), the time taken by the query and the number of rows affected is shown by default under Messages tab.

answered Jul 02 '19 at 07:14

Rohan Talesara

186
2
6

score 3 · Answer 7 · answered Sep 05 '19 at 08:48

You should have used DATEDIFF(MICROSECOND, @starttime, @endtime) to get the elapsed time in milliseconds, so your query should be changed to something like this:

DECLARE @starttime datetime
DECLARE @endtime datetime

SET @starttime =getdate()   

-- execute my query here

SET @endtime = GETDATE()

SELECT DATEDIFF(MICROSECOND, @starttime, @endtime)

Although you can use a built-in feature named Include Client Statistics which has been explained here.

score 0 · Answer 8 · answered Sep 14 '21 at 13:52

In SSMS, In the Menu bar: Go to

1.Query>Query options>Advanced>SET STATISTICS TIME

2.Query>Query options>Advanced>SET STATISTICS IO

That will give the output looking something like this in your Messages window:

SQL Server parse and compile time: CPU time = x ms, elapsed time = y ms.

SQL Server Execution Times: CPU time = x ms, elapsed time = y ms.

How to get correct query execution time in SQL Server?

8 Answers8