Monitor SQL Statements

I would like to monitor any non stored procedure activity on a sql
server (2000 and 2005). Mainly I need to watch for ad hoc sql from
applications and for people running sql from query analyzer.
I think that Profiler will do this, but I have concerns about how much
overhead that will cause. At times, the server is very busy and I have
SLA in place that would cause me to pay a penalty for slow response
time.
Any suggestions?
Jim
St LouisFirst off you should never use Profiler for traces on busy systems. You can
simply use TRACE and send the results to a binary file on a disk attached
locally to the server. Then you can copy them to another machine for
processing or viewing. The easiest way (if you are not familiar with that)
to do that is to briefly use profiler. Set up the events and columns and
filters exactly how you want it. Then run the trace and immediately hit
stop. Then go to File - Script Trace - For 2000. This will generate a tsql
script that you can use to run a trace without the overhead of profiler.
Follow the instructions in the script to add the filename and adjust the
maxfilesize etc. Look up sp_tracecreate in booksonline for more details. If
the sp's are being run as RPC's and not batches you can just look for Batch
Completed events and ignore all the sps.
Andrew J. Kelly SQL MVP
"Jim Youmans" <jdyoumans@.gmail.com> wrote in message
news:1150981788.688853.289590@.u72g2000cwu.googlegroups.com...
>I would like to monitor any non stored procedure activity on a sql
> server (2000 and 2005). Mainly I need to watch for ad hoc sql from
> applications and for people running sql from query analyzer.
> I think that Profiler will do this, but I have concerns about how much
> overhead that will cause. At times, the server is very busy and I have
> SLA in place that would cause me to pay a penalty for slow response
> time.
> Any suggestions?
> Jim
> St Louis
>|||First off you should never use Profiler for traces on busy systems. You can
simply use TRACE and send the results to a binary file on a disk attached
locally to the server. Then you can copy them to another machine for
processing or viewing. The easiest way (if you are not familiar with that)
to do that is to briefly use profiler. Set up the events and columns and
filters exactly how you want it. Then run the trace and immediately hit
stop. Then go to File - Script Trace - For 2000. This will generate a tsql
script that you can use to run a trace without the overhead of profiler.
Follow the instructions in the script to add the filename and adjust the
maxfilesize etc. Look up sp_tracecreate in booksonline for more details. If
the sp's are being run as RPC's and not batches you can just look for Batch
Completed events and ignore all the sps.
Andrew J. Kelly SQL MVP
"Jim Youmans" <jdyoumans@.gmail.com> wrote in message
news:1150981788.688853.289590@.u72g2000cwu.googlegroups.com...
>I would like to monitor any non stored procedure activity on a sql
> server (2000 and 2005). Mainly I need to watch for ad hoc sql from
> applications and for people running sql from query analyzer.
> I think that Profiler will do this, but I have concerns about how much
> overhead that will cause. At times, the server is very busy and I have
> SLA in place that would cause me to pay a penalty for slow response
> time.
> Any suggestions?
> Jim
> St Louis
>