Profiler

We usually monitor our production server via profiler from
our desktop. We had an issue over the weekend and would
usually have the profiler results to review and
troubleshoot. However, over the weekend our desktop's were
rebooted and all info lost.
So we were thinking of running the profiler directly on
the production server but have read/heard of issue's
related to this and a potential performance hit on the
server. When running it on my own desktop I sometimes run
into memory issues.
What are the pro's and con's of running profiler directly
on the server?
What about running the stored proc's in query analyzer
from our desktop compared to running the profiler directly?
Would it be better to use the stored procedures instead of
profiler?
Any input would be greatly appreciated.
ThanksI don't run Profiler on the server directly because of the way I store the
Profiler data. In order to really use the data I find that I need to store
the profile stuff in a table. I don't like the idea of writing to the
production server so I have a seperate server where I run Profiler and log
the data in an SQL table.
Christian Smith
<anonymous@.discussions.microsoft.com> wrote in message
news:e36a01c3f0f4$082d9f80$a101280a@.phx.gbl...
> We usually monitor our production server via profiler from
> our desktop. We had an issue over the weekend and would
> usually have the profiler results to review and
> troubleshoot. However, over the weekend our desktop's were
> rebooted and all info lost.
> So we were thinking of running the profiler directly on
> the production server but have read/heard of issue's
> related to this and a potential performance hit on the
> server. When running it on my own desktop I sometimes run
> into memory issues.
> What are the pro's and con's of running profiler directly
> on the server?
> What about running the stored proc's in query analyzer
> from our desktop compared to running the profiler directly?
> Would it be better to use the stored procedures instead of
> profiler?
> Any input would be greatly appreciated.
> Thanks
>|||If your server's not under heavy load, this is probably ok, but if the
server is under heavy load, using the Profiler on the server might hurt
performance because it consumes CPU, memory to collect data, display on the
screen etc.
If you are dealing with a server under heavy load, there is a better
alternative:
SQL Profiler is a client side tool that collects data from SQL Trace (server
side component) and displays it on the GUI. SQL Trace can be run on it's own
on the server without the overhead of Profiler by using it's stored procs -
sp_trace_create (& cousins). SQL 7 has a different set of procs for this,
but I'll assume you're on SQL 2K for now unless you say otherwise..
When you use SQL Trace, you can set the output to go to a .trc file on the
server and open the .trc file later with Profiler, save to a table, run
queries whatever..
To make life super-easy when doing this, you can even define your SQL Trace
in SQL Profiler, then use SQL Profiler's scripting tool to script the
sp_trace_create commands. You can even schedule starting & stopping SQL
Trace via these stored procs from the SQL Agent.
For more info, read up on sp_trace_create in SQL Books Online..
Then again, if you're just doing ad-hoc tracing on a server that's not under
heavy load, you're probably ok running small-ish SQL Profilers directly on
the server, but do be sensitive that it will add load & might affect users..
HTH
Regards,
Greg Linwood
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:e36a01c3f0f4$082d9f80$a101280a@.phx.gbl...
> We usually monitor our production server via profiler from
> our desktop. We had an issue over the weekend and would
> usually have the profiler results to review and
> troubleshoot. However, over the weekend our desktop's were
> rebooted and all info lost.
> So we were thinking of running the profiler directly on
> the production server but have read/heard of issue's
> related to this and a potential performance hit on the
> server. When running it on my own desktop I sometimes run
> into memory issues.
> What are the pro's and con's of running profiler directly
> on the server?
> What about running the stored proc's in query analyzer
> from our desktop compared to running the profiler directly?
> Would it be better to use the stored procedures instead of
> profiler?
> Any input would be greatly appreciated.
> Thanks
>|||you can script the sp_trace calls directly per greg's
description or just run profiler on the server,
under almost no circumstances should profiler be saving
directly to a table on the production server
set it to save to a file, and place the file on lightly
loaded disk
also, an accumalation rate by profiler of <1000 line
items /sec should not be a significant load on the server,
but to be safe, try to keep it under 200/sec
>--Original Message--
>We usually monitor our production server via profiler
from
>our desktop. We had an issue over the weekend and would
>usually have the profiler results to review and
>troubleshoot. However, over the weekend our desktop's
were
>rebooted and all info lost.
>So we were thinking of running the profiler directly on
>the production server but have read/heard of issue's
>related to this and a potential performance hit on the
>server. When running it on my own desktop I sometimes run
>into memory issues.
>What are the pro's and con's of running profiler directly
>on the server?
>What about running the stored proc's in query analyzer
>from our desktop compared to running the profiler
directly?
>Would it be better to use the stored procedures instead
of
>profiler?
>Any input would be greatly appreciated.
>Thanks
>.
>