Monitoring Query Performance

All,
I have a question regarding query performance. I have a SQL 2000 server that
is acting as a backend for a business reporting application (IB WebFocus).
Most of the time, the system runs along at a fairly descent clip, but there
are times when our users (who might be new to WebFocus) submit queries that
hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
usage, and I end up hving to kill some sessions (in the reporting app) befor
e
CPU usage is back under control.
I would like to try and be more proactive than that... what are some ways I
can identify the queries that are running when this happens? Is setting up a
trace file my best bet, or...?
Thanks in advance...
Davidmonitor the sysprocess system table. You can also use sp_who or sp_who2 to
see the contents of this table.
look for processes that have gone awry...or are blocking other users. Use
net send to notify the user...then Use kill <spid> to kick them.
"David Levine" <DavidLevine@.discussions.microsoft.com> wrote in message
news:953DFEF8-18E6-4BA1-AD4B-436310DE5E09@.microsoft.com...
> All,
> I have a question regarding query performance. I have a SQL 2000 server
> that
> is acting as a backend for a business reporting application (IB WebFocus).
> Most of the time, the system runs along at a fairly descent clip, but
> there
> are times when our users (who might be new to WebFocus) submit queries
> that
> hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
> usage, and I end up hving to kill some sessions (in the reporting app)
> before
> CPU usage is back under control.
> I would like to try and be more proactive than that... what are some ways
> I
> can identify the queries that are running when this happens? Is setting up
> a
> trace file my best bet, or...?
> Thanks in advance...
> David|||Yes...pretty much. I had the same issue a couple of years
ago at a client site using WebFocus. It always turned out to
be the ad hoc queries that the users ran through the tool -
and some of the ugly ways the query was built by the tool
before sending it to SQL. You'll need to trace to find the
issues.
-Sue
On Tue, 24 Jan 2006 15:26:13 -0800, David Levine
<DavidLevine@.discussions.microsoft.com> wrote:

>All,
>I have a question regarding query performance. I have a SQL 2000 server tha
t
>is acting as a backend for a business reporting application (IB WebFocus).
>Most of the time, the system runs along at a fairly descent clip, but there
>are times when our users (who might be new to WebFocus) submit queries that
>hang up the system. I will notice SQLSERVR.EXE is jammed up at 100% CPU
>usage, and I end up hving to kill some sessions (in the reporting app) befo
re
>CPU usage is back under control.
>I would like to try and be more proactive than that... what are some ways
I
>can identify the queries that are running when this happens? Is setting up
a
>trace file my best bet, or...?
>Thanks in advance...
>David