Get script execution time in SQl Server 2008 R2

Question

I have a script to execute and it takes runtime, how can I calculate it?

I am thinking of using this method:

DECLARE @startTime DATETIME SET @startTime = GETDATE(); --Script DECLARE @endTime DATETIME SET @endTime = GETDATE(); PRINT 'Transfer Duration:'; GO PRINT CONVERT(varchar,@ endTime-@startTime ,14); GO 

But since I use GO (and I need it), so an error occurs. So, what is your suggestion to calculate and print the duration of the script (with GO )?

Answer 1

Use the table to store the start and end, also enter the process ID. Then use DATEDIFF () to calculate the elapsed time.

  CREATE TABLE Profiling( spid smallint NOT NULL, StartTime DATETIME2 NOT NULL, EndTime DATETIME2 ) INSERT INTO Profiling(spid, StartTime) SELECT @@SPID, CURRENT_TIMESTAMP GO -- your queries come here -- ..... UPDATE Profiling SET EndTime = CURRENT_TIMESTAMP WHERE spid = @@SPID GO SELECT DATEDIFF(MS, startTime, EndTime) as MilliSeconds FROM Profiling WHERE spid = @@SPID 

Trim the table from time to time to avoid spid collisions.

Answer 2

See SET STATISTICS TIME .

Answer 3

 Go DECLARE @startTime DATETIME SET @startTime = GETDATE(); -- your sql statement DECLARE @endTime DATETIME SET @endTime = GETDATE(); PRINT 'Transfer Duration:'; PRINT cast((@ endTime-@startTime ) AS TIME); Go 

or, to be more precise, you can use SET STATISTICS TIME , which displays the number of milliseconds required for parsing, compiling, and executing each statement.