SQL Server – Why Query Execution Time Differs from Measured Time

sql servert-sql

I'm trying to optimise a stored procedure. I have inserted some manual profiling into my SP by just using a date variable and then displaying time elapsed by using datediff.

E.g.

DECLARE @MyDate datetime = GetDate()
PRINT '..profiling text...'
PRINT DateDiff(ms, @MyDate, GetDate())
SET @MyDate = GetDate()

<execute some actions here>

PRINT '..elapsed time...'
PRINT DateDiff(ms, @MyDate, GetDate())
SET @MyDate = GetDate()

When I run this in a query window of SQL Server Management Studio the total elapsed time of my profile equates to about 5 seconds. However, the duration of the whole query is more like 14 seconds as indicated in the query properties in the bottom right corner (and when I time it manually with a watch).

Is there any reason why this would be happening? There is nothing wrong with where I have placed or declared my date variables. There is definitly a mismatch in time output via datediff and time taken for the query.

It should be noted also that while optimising I am not returning any large tables of data. I'm simply inserting them into temp tables to ensure that I don't have the overhead of returning results.

Best Answer

The elapsed time measured by the T-SQL batch will include only execution time.

The elapsed time measured by the client application will include not only execution time but also compilation time and network latency.

Run your batch with SET STATISTICS TIME ON to see if the unaccounted time is due to compilation. With SSMS, you can also include more detailed client statistics (Query-->Include Client Statistics ).

Related Question