Execution Time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Execution Time

Author

Topic

saahil_k
Starting Member

18 Posts

Posted - 2008-02-10 : 02:31:38

Hello,
What is the built-in-function to check the Query execution time in milli seconds.
thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-10 : 04:11:45

there are different ways of doing this

1.SET STATISTICS TIME ON

this will provide time taken along with results

SET STATISTICS TIME ON
GO
SELECT * 
FROM Table
WHERE field = condition
GO

and you will get time like:-

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

2.You can also use SQL profiler to do this. Start the profiler and add the following events to trace

SQL:StmtStarting :Transact-SQL statement has started.

SQL:StmtCompleted :Transact-SQL statement has completed.

SQL:BatchStarting :Transact-SQL batch has started.

SQL:BatchCompleted :Transact-SQL batch has completed.

and go to query analyser and execute the code.

3. Another crude method is as follows

DECLARE @StartTime datetime,@EndTime datetime

SELECT @StartTime= GETDATE()

SELECT * 
FROM Table
WHERE field = condition

SELECT @EndTime=GETDATE()

SELECT DATEDIFF(ms,@StartTime,@EndTime) AS 'ExecutionTime'

saahil_k
Starting Member

18 Posts

Posted - 2008-02-10 : 22:22:19

thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources