SEO

Erland Sommarskog wrote:
> sqlserver yeahbaby (Use-Author-Address-Header@[127.1]) writes:
> > [ ... ] 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 [ ... ]
> http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html

Thank you very much for that Mr. Sommarskog. I set up a job to run
every minute, calling aba_lockinfo and writing the output to a file.
This was intended to help debug an issue we have - every so often
the SQL Server process goes berserk, using all the CPU and making
the system effectively grind to a halt - even logging on takes 10
minutes!

Anyway, am I correct in thinking that your stored procedure should
show any and all actvity on the system?

The results of this monitoring are slightly confusing. The "Job X
began executing" line shows uniformly incrementing times until the
issue arises, when it is scheduled erratically, e.g.:

Job 'aba_lockinfo' : Began Executing 2003-10-06 08:44:00
Job 'aba_lockinfo' : Began Executing 2003-10-06 08:45:00
Job 'aba_lockinfo' : Began Executing 2003-10-06 08:46:00
Job 'aba_lockinfo' : Began Executing 2003-10-06 08:47:00
Job 'aba_lockinfo' : Began Executing 2003-10-06 08:48:14
Job 'aba_lockinfo' : Began Executing 2003-10-06 08:53:24

The above seems to indicate that the issue arose some time between
08:47 and 08:48, however there is NO output from your SP until the
08:48 run, when (presumably) things were queueing for resource
instead of being instantly processed.

Could it be that some bug is being tickled in SQL Server by a
process that immediately exits without waiting to be detected?
We're running 8.00.679 (SP2). I can't see anything in the error
log - the last few entries read:

2003-10-06 08:03:13.19 backup Log backed up: Database: XYZ [ ... ]
2003-10-06 08:03:27.34 backup Log backed up: Database: app_db [ ... ]
2003-10-06 09:10:35.40 backup Log backed up: Database: app_db [ ... ]
2003-10-06 09:12:58.08 backup Log backed up: Database: XYZ [ ... ]
2003-10-06 09:53:18.98 spid2 SQL Server is terminating due to 'stop' request from Service Control Manager.

I wonder whether installing the SP3a would be helpful....

__________________________________________________ ___________
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

sqlserver yeahbaby (Use-Author-Address-Header@[127.1]) writes:
> Anyway, am I correct in thinking that your stored procedure should
> show any and all actvity on the system?

Depends a little on how you call it. Default is to list "interesting"
proceses, which is defined as processes that are running or are holding
locks. (Other than a basic database lock.)

> Job 'aba_lockinfo' : Began Executing 2003-10-06 08:44:00
> Job 'aba_lockinfo' : Began Executing 2003-10-06 08:45:00
> Job 'aba_lockinfo' : Began Executing 2003-10-06 08:46:00
> Job 'aba_lockinfo' : Began Executing 2003-10-06 08:47:00
> Job 'aba_lockinfo' : Began Executing 2003-10-06 08:48:14
> Job 'aba_lockinfo' : Began Executing 2003-10-06 08:53:24
>
> The above seems to indicate that the issue arose some time between
> 08:47 and 08:48, however there is NO output from your SP until the
> 08:48 run, when (presumably) things were queueing for resource
> instead of being instantly processed.

I have to admit that there is an issue with aba_lockinfo that I have
not been aware of until recently: if there is a huge number of locks,
aba_lockinfo may need several minutes to return. I had a case where
a process held 450000 locks and aba_lockinfo ran for over five minutes.

It's not really the number of locks which is the killer, as aba_lockinfo
aggregates lock on the same object. But this process was calling the
same stored procedure over and over again in the same transaction, and
on each call it created some 10-15 temp tables, and even if the tables
goes away when the procedures exists, the locks stayed around, and
aba_lockinfo tried to find the name of all these guys.

But if it eventually returned, the data it outputted should be
interesting.

> I wonder whether installing the SP3a would be helpful....

At least then, you can use a version of aba_lockinfo which shows more
information.

Some people have run into problems with some queries running slower
with SP3, so it maybe wise to test before you take SP3 in production.
But in general, this is a recommended pack to install.

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

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