How to get the correct query execution time in SQL Server?

Question

I need to get the execution time of my request. I use

declare @starttime datetime declare @endtime datetime set @starttime =getdate() -- execute my query here set @endtime = GETDATE() select @ endtime-@starttime 

But the way out goes like 1900-01-01 00:02:10.707

I need only part of the time.

Answer 1

Use this:

 set statistics time on --query set statistics time off 

Go to the Message tab to see the following message:

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

Answer 2

 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 

Answer 3

Try with this other conversion 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, see http://msdn.microsoft.com/it-it/library/ms187928.aspx

Answer 4

Use the time data type that is available in SQL Server 2008 and later.

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

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

Answer 5

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

- Your request will be launched here -

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

Answer 6

Update for those who use later versions of SQL Server than OP: in SQL Server 2017 (version 14.0), the time spent on the query and the number of rows affected are displayed by default on the Messages tab.

Answer 7

You had to use DATEDIFF(MICROSECOND, @starttime, @endtime) to get the elapsed time in milliseconds, so your request 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 the built-in function called Include Client Statistics , which was explained here .