Detecting or monitoring for long-running queries.

[sqlserver yeahbaby]

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.com

[Erland Sommarskog]

sqlserver 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, so****@algonet.se

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