Profiler / sql trace

There are several ways to capture/view sql activities. Can
you tell me what the best way is ?
Run Profiler from a client machine or
Use sql trace procs from a client machine using QA or
Run sql trace procs at the server (which I've seen MS PSS
has always used)
thanksFor a quick look at a server I'll use Profiler from my PC and if I want to
keep it I'll save to a file (which gives me the ability to save to a table
later if I want to do more analysis) For longer traces I'll run a server
side trace on the server and generally import the resulting files into a
table for analysis. I tend to use a rollover file size of 50 MB to keep them
manageable and generally define a stop time or set up a job to stop it at a
future date.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dba" <Dba@.Dba1.Com> wrote in message
news:021001c3d61e$e5e79bf0$a401280a@.phx.gbl...
> There are several ways to capture/view sql activities. Can
> you tell me what the best way is ?
> Run Profiler from a client machine or
> Use sql trace procs from a client machine using QA or
> Run sql trace procs at the server (which I've seen MS PSS
> has always used)
> thanks|||Hello,
My name is Michael and I would like to thank you for using Microsoft
newsgroup.
Based on my experience, this kind of problem does not have an easy and
clear answer. It is hard for me to tell you which the best way is to
capture/view sql activities; what a good method is for you may not be a
good method for other people. The way in which we choose to monitor SQL
Server depends on specific and particular concerns. I can help you find
the most appropriate way for you to monitor SQL server based on your needs,
but, please understand that there is no "best way" to do this.
When people use stored procedures to monitor SQL Server, the information we
are able to get is limited. For example:
Sp_who stored procedure provides information about current Microsoft SQL
Server users and processes.
sp_lock stored procedure reports information about locks.
SQL Server Profiler (SQL Trace) is a graphical tool that allows system
administrators and database developers to monitor engine events on
computers running Microsoft SQL Server. Using SQL Server Profiler on the
client machine to monitor SQL Server will increase network traffic.
Monitoring on the server will increase the workload of the server machine.
For more information regarding this issue, please refer to the following
article.
325263 Support WebCast: SQL Server 2000 Profiler: What's New and How to
http://support.microsoft.com/?id=325263
I hope the above explanation is clear. Please let us know if you need
further assistance on this issue.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Michael,
I'm very familiar about sp_lock/sp_who. My main confusion
is that MS PSS strongly is against running Profiler at the
client side everytime I've talked/worked with them. If the
Proiler is so bad, why MS ship it at all ! As I've said
they have always run sql trace procs at our sql server
box. Any comments ?
thanks
long
>--Original Message--
>Hello,
>My name is Michael and I would like to thank you for
using Microsoft
>newsgroup.
>Based on my experience, this kind of problem does not
have an easy and
>clear answer. It is hard for me to tell you which the
best way is to
>capture/view sql activities; what a good method is for
you may not be a
>good method for other people. The way in which we choose
to monitor SQL
>Server depends on specific and particular concerns. I
can help you find
>the most appropriate way for you to monitor SQL server
based on your needs,
>but, please understand that there is no "best way" to do
this.
>When people use stored procedures to monitor SQL Server,
the information we
>are able to get is limited. For example:
>Sp_who stored procedure provides information about
current Microsoft SQL
>Server users and processes.
>sp_lock stored procedure reports information about locks.
>SQL Server Profiler (SQL Trace) is a graphical tool that
allows system
>administrators and database developers to monitor engine
events on
>computers running Microsoft SQL Server. Using SQL Server
Profiler on the
>client machine to monitor SQL Server will increase
network traffic.
>Monitoring on the server will increase the workload of
the server machine.
>For more information regarding this issue, please refer
to the following
>article.
>325263 Support WebCast: SQL Server 2000 Profiler: What's
New and How to
>http://support.microsoft.com/?id=325263
>I hope the above explanation is clear. Please let us know
if you need
>further assistance on this issue.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>|||Hi Long,
Thanks for your feedback. Generally, the way we choose to monitor SQL
Server depends on the specific objective. Based on the particular
situation, our engineers will choose the most appropriate method. As for
this issue, can you please give me a sample to help describe the specific
objective? That way, we can help you analyze and determine which the most
appropriate way is to monitor SQL Server.
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Long,
Thank you for choosing Microsoft! Michael is on holiday and I'm his backup. My name is
Billy and it's my pleasure to assist you with this issue.
To start with, as we known, Microsoft sometimes recommends our customers to perform the
best practices for their benefits. However, we never prohibit customers from applying their
easier way to do anything. It all depends on customer's specific issues and detailed
scenarios.
Based on my experience, the main reason why you are strongly recommended to run the
Profiler at the server side is that it guarantees you can trace all the information.
In the GUI trace setup, you can click the checkbox for: "server processes sql server trace
data"
The stored procedures to create/exec profiler traces will guarantee events will not be
dropped unless you use: sp_create_trace @.options = 1 which instead of a file creates a
rowset.
Server side trace files guarantee events not to be dropped, however, result set (or rowset
cannot guarantee this. On the client side, you cannot ensure the trace information is
complete as SQL Profiler gets the trace as a result set to the client.
Another consideration is the trace performance. On the client side, the trace information is
received as a result set, in which case if there is a large volume of trace information, the
performance will definitely be poorer than tracing on the server side. That's why we
recommend you run the Profiler on the server side.
However, if trace information is complete and the performance is also ok on the client side. I
fully understand that running the Profiler is convenient and also efficient for you in some
situations (such as the server is not on hand). Do you agree with me Long?
If there is anything more we can still do to assist you, please don't hesitate to let us know.
We are here to be of assistance!
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.