Detecting or monitoring for long-running queries.

I can't seem to find a step-by-step guide to how to detect or
continuously monitor for long-running queries that is suitable
for a comparative SQL Server novice. I know that it is possible
to monitor for such with other database products - can anyone
enlighten me as to how this is done with SQL Server? Ideally,
I'd like to snapshot running queries (preferably with query
text) at a particular instant via a script, although any help
to show what queries are running at an instant will be much
appreciated. Any ideas?

__________________________________________________ ___________
Are you Catholic ?
http://www.CatholicEmail.com

100s of FREE email addresses -->
http://www.UltimateEmail.com

Send an Online Greeting Card http://www.UltimateEcards.comsqlserver yeahbaby (Use-Author-Address-Header@.[127.1]) writes:
> I can't seem to find a step-by-step guide to how to detect or
> continuously monitor for long-running queries that is suitable
> for a comparative SQL Server novice. I know that it is possible
> to monitor for such with other database products - can anyone
> enlighten me as to how this is done with SQL Server? Ideally,
> I'd like to snapshot running queries (preferably with query
> text) at a particular instant via a script, although any help
> to show what queries are running at an instant will be much
> appreciated. Any ideas?

On my web site there is a stored procedure, aba_lockinfo, that is not
a perfect match for your request, but which does give the information
you are asking for - and maybe information which you do not care
about. You could look at
http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html, and see if
it anything for you.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp