Long Running Queries

A query that finds long-running queries that are waiting, blocked or holding open transactions.

Published Jan 31, 2008

A quick filter for the queries that deserve attention: this pulls from sysprocesses every non-sa session running longer than ten hours (adjust the multiplier) that is actually doing something - waiting on a resource, blocked, or holding an open transaction.

SELECT
	*
FROM
	master..sysprocesses AS SP
WHERE
	DateDiff(MINUTE, SP.last_batch, GetDate()) > 10 * 60 --Number of hours
	AND SP.loginame <> 'sa' AND lastwaittype <> 'WAITFOR'
	AND
	(
		SP.waittype <> 0
		OR SP.blocked <> 0
		OR SP.open_tran <> 0
	)