How to get transaction execute time in SQL Server?

Question

I want to get transaction execute time in SQL Server 2008 R2 i want to get this time programmatically and save it in a table . How can do it?

Answer 1

Try executing "SET STATISTICS TIME ON;" on your connection before executing your transaction.

Answer 2

Save the time before and after the query and to calculate the difference, something like this:

DECLARE @start_time DATETIME, @end_time DATETIME
SET @start_time = CURRENT_TIMESTAMP

 -- query goes here

SET @end_time = CURRENT_TIMESTAMP
SELECT DATEDIFF(ms, @start_time, @end_time)

Answer 3

JohnC is right and that is probably what you want.

But depending on your exact needs, there are other options. For one, SSMS tells you the amount of time, at least to the second, passed in the lower right cornor when you execute a query.

And of course you use getdate() to get the time from SQL Server immediately before and immediately after the execution and find the difference.

For repeated testing of large numbers of queries, you could also build a test harness in some other language like python with a library like timeit.