CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ali Bigdeli
Ali Bigdeli

Reputation: 1385

How to get transaction execute time in SQL Server?

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?

Upvotes: 0

Views: 6991

Answers (3)

TimothyAWiseman
TimothyAWiseman

Reputation: 14873

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.

Upvotes: 2

Waqar Janjua
Waqar Janjua

Reputation: 6123

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)

Upvotes: 7

JohnC
JohnC

Reputation: 842

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

Upvotes: 4

Related Questions