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.
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.
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
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)
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
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.
(517 rows affected) Completion time: 2024-05-13T09:28:25.4453045+10:00You 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.
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]
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.
