Monday, January 26, 2015

Need a Query to Get Execution time of each query in Proc if its running

Is this the logic you're after?



DECLARE @timeStamp DATETIME2 = CURRENT_TIMESTAMP, @lapTimeStamp DATETIME2, @x INT = 0

SET @lapTimeStamp = CURRENT_TIMESTAMP

WHILE @x < 10000
BEGIN
SET @x = @x + 1
END

PRINT CAST(DATEDIFF(MS,@TIMESTAMP,CURRENT_TIMESTAMP) AS VARCHAR) + ' milliseconds have elapsed since the begining of the proc. ' + CAST(DATEDIFF(MS,@lapTimeStamp,CURRENT_TIMESTAMP) AS VARCHAR) + ' have elapsed since the last batch'

SET @lapTimeStamp = CURRENT_TIMESTAMP
SET @x = 0
WHILE @x < 10000
BEGIN
SET @x = @x + 1
END

PRINT CAST(DATEDIFF(MS,@TIMESTAMP,CURRENT_TIMESTAMP) AS VARCHAR) + ' milliseconds have elapsed since the begining of the proc. ' + CAST(DATEDIFF(MS,@lapTimeStamp,CURRENT_TIMESTAMP) AS VARCHAR) + ' have elapsed since the last batch'

SET @lapTimeStamp = CURRENT_TIMESTAMP
SET @x = 0
WHILE @x < 10000
BEGIN
SET @x = @x + 1
END

PRINT CAST(DATEDIFF(MS,@TIMESTAMP,CURRENT_TIMESTAMP) AS VARCHAR) + ' milliseconds have elapsed since the begining of the proc. ' + CAST(DATEDIFF(MS,@lapTimeStamp,CURRENT_TIMESTAMP) AS VARCHAR) + ' have elapsed since the last batch'

No comments:

Post a Comment