How to get timing when querying with Microsoft SQL Server

Question

I'm testing Microsoft SQL Server, I want to check that how much time it takes when I perform a query.

score 2 · Accepted Answer · answered Oct 31 '17 at 06:12

2

In the query window place SQL like below:

SET STATISTICS TIME ON;  

--Your Query Goes Here 
SELECT * FROM Table

SET STATISTICS TIME OFF;

answered Oct 31 '17 at 06:12

Khorshed Alam

314
2
11

score 1 · Answer 2 · answered Oct 31 '17 at 06:39

1

Declare @startDate datetime
Declare @endDate datetime
begin

set @startDate=GETDATE()
select * from Table1
set @endDate=GETDATE()
 select DATEDIFF(ms,@startDate,@endDate) as 'Execution Time '


     /*
 ms is for millisecond you can also use following argument
 minute mi, n
second  ss, s
millisecond ms
microsecond mcs
nanosecond  ns
 */
 end

answered Oct 31 '17 at 06:39

Hitesh Thakor

471
2
12

better approach @ hitesh – Sunny Jangid Oct 31 '17 at 06:55
@SunnyJangid - No it isn't. It doesn't give you the breakdown of CPU and elapsed time and compile time and would also involve littering your code with timing statements that you then need to remove. – Martin Smith Oct 31 '17 at 07:57

score 0 · Answer 3 · answered Oct 31 '17 at 06:29

DECLARE @EndT datetime
DECLARE @StartT datetime 
SELECT @StartT=GETDATE() 

-- Write Your Query


SELECT @EndT=GETDATE()

--This will return execution time of your query
SELECT DATEDIFF(ms,@StartT,EndT) AS [Execution Time in millisecs]

for more information About DATEDIFF See https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql

How to get timing when querying with Microsoft SQL Server

3 Answers3