April 28, 2014 at 10:23 am
Hello everybody, Is it posible to know the elapsed time after I execute a transaction statement in SQL?
I'm killing the inactive connections with the statement:
DECLARE @DatabaseName nvarchar(50)
DECLARE @sql varchar(max)
SET @DatabaseName = N'DBTest'
SELECT @sql = COALESCE(@SQL,'') + 'Kill ' + CAST(SPId as varchar(4)) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId AND (((Status = 'SLEEPING' AND open_tran > 0) OR Status IN ( 'RUNNABLE', 'SUSPENDED' )) AND cmd NOT LIKE 'BACKUP%') AND DATEDIFF("MI", last_batch, GETDATE()) >= 10
EXEC(@SQL)
In the last part of the where condition I'm using DATEDIFF("MI", last_batch, GETDATE()) >= 10, but it doesn't solve the problem because the column "last_batch" in the SysProcesses table updates its values constantly. Somebody could help me please?
April 28, 2014 at 10:50 am
There's no real way to dynamically capture statement times within the execution unless you output to a variable before and after a statement starts & stops. Otherwise, to capture statement level execution time, you have to use extended events or trace.
But maybe I don't understand what you're going for.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 28, 2014 at 11:18 am
Thanks for your reply. According with your answer, I think It will be necessary create an additional table to trace the start and end time of the queries in order to know the elapsed time.
April 28, 2014 at 6:42 pm
If a connection is hung, inactive or stuck on a single long running statement, then last_batch should not be updating.
However that does not equate with the beginning of a transaction, since it can consist of several statements.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply