Performance impact of tracing?

I have set up a trace using sp_trace_create and it works fine for most of my
SQL 2k SP 3 servers. However on one server (which is no different to the
others) it has a huge impact on performance to the point where i can not run
tracing. The only difference between servers is that this particular server
has a database which uses a huge amount of UDF's?
Anyone had a similar problem?
Since UDF's run for every row processed they can cause a lot of trace events
if you are tracing at the statement level. Try tracing only the Batch
Completed, SP Completed and RPC Completed events instead. You can always
filter down once you find offending statements and open up the trace. Also
make sure you are sending the results to a LOCAL disk drive that is not used
by the data or especially the log file.
Andrew J. Kelly SQL MVP
"Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:1C0D4DF0-D62D-4258-A658-F44EB85B798F@.microsoft.com...
>I have set up a trace using sp_trace_create and it works fine for most of
>my
> SQL 2k SP 3 servers. However on one server (which is no different to the
> others) it has a huge impact on performance to the point where i can not
> run
> tracing. The only difference between servers is that this particular
> server
> has a database which uses a huge amount of UDF's?
> Anyone had a similar problem?

Performance impact of tracing?

I have set up a trace using sp_trace_create and it works fine for most of my
SQL 2k SP 3 servers. However on one server (which is no different to the
others) it has a huge impact on performance to the point where i can not run
tracing. The only difference between servers is that this particular server
has a database which uses a huge amount of UDF's?
Anyone had a similar problem?Since UDF's run for every row processed they can cause a lot of trace events
if you are tracing at the statement level. Try tracing only the Batch
Completed, SP Completed and RPC Completed events instead. You can always
filter down once you find offending statements and open up the trace. Also
make sure you are sending the results to a LOCAL disk drive that is not used
by the data or especially the log file.
Andrew J. Kelly SQL MVP
"Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:1C0D4DF0-D62D-4258-A658-F44EB85B798F@.microsoft.com...
>I have set up a trace using sp_trace_create and it works fine for most of
>my
> SQL 2k SP 3 servers. However on one server (which is no different to the
> others) it has a huge impact on performance to the point where i can not
> run
> tracing. The only difference between servers is that this particular
> server
> has a database which uses a huge amount of UDF's?
> Anyone had a similar problem?

Performance impact of tracing?

I have set up a trace using sp_trace_create and it works fine for most of my
SQL 2k SP 3 servers. However on one server (which is no different to the
others) it has a huge impact on performance to the point where i can not run
tracing. The only difference between servers is that this particular server
has a database which uses a huge amount of UDF's?
Anyone had a similar problem?Since UDF's run for every row processed they can cause a lot of trace events
if you are tracing at the statement level. Try tracing only the Batch
Completed, SP Completed and RPC Completed events instead. You can always
filter down once you find offending statements and open up the trace. Also
make sure you are sending the results to a LOCAL disk drive that is not used
by the data or especially the log file.
--
Andrew J. Kelly SQL MVP
"Malarb" <Malarb@.discussions.microsoft.com> wrote in message
news:1C0D4DF0-D62D-4258-A658-F44EB85B798F@.microsoft.com...
>I have set up a trace using sp_trace_create and it works fine for most of
>my
> SQL 2k SP 3 servers. However on one server (which is no different to the
> others) it has a huge impact on performance to the point where i can not
> run
> tracing. The only difference between servers is that this particular
> server
> has a database which uses a huge amount of UDF's?
> Anyone had a similar problem?

Performace Monitor and SQL Profiler

I see, I will have to check that out on if it has any affect on user database
or not. But today I did a trace that logged about 250,000 records on
another database, and that server didn't notice any deadlocks or lock
timeouts. But I did take your advice and this time ran the SQL Monitor on a
different computer. So maybe that had some affect.
So I do suspect me running it on the serer caused locks but might have
caused some slow down. So I think the application is either timing out too
quickly or something else is a miss. Thank-you for your reply.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Ekrem ?nsoy" wrote:

> Well... I'm not sure if SQL Profiler may cause locks or not however
> Microsoft recommends using these kind of tools (SQL Profiler, Performance
> Monitor) from another machine. Because they effect performance on the
> machine they work. However, in this case they also recommend using a
> dedicated connection between those two machines to avoid network traffic
> because of using these tools if possible.
> So, in your case, yes, they have affect on the SQL Server somehow.
> --
> Ekrem ?nsoy
>
> "Mohit K. Gupta" <mohitkgupta@.msn.com> wrote in message
> news:E838BE3D-DDAF-46C3-A9B0-7F102C59D806@.microsoft.com...
>
Hi
SQL Profiler will not create database locks unless you are logging to a
database table, which is not usually recommended. In whioch case it is
unlikely that other applications are using that table (or database!)
If you can't log from a separate machine on a dedicated lan, you can use a
server side trace. Make sure that any trace files are not using the same
discs as SQL Server preferrably have fast spindles for it's exclusive use
(although this is rarely possible!)
If you use the SQL Profiler GUI it will use space on the TEMP directory
(even if logging to a file) make sure that this is not the system disc as
running out of space on that, will cause the system to hang.
John
"Mohit K. Gupta" wrote:
[vbcol=seagreen]
> I see, I will have to check that out on if it has any affect on user database
> or not. But today I did a trace that logged about 250,000 records on
> another database, and that server didn't notice any deadlocks or lock
> timeouts. But I did take your advice and this time ran the SQL Monitor on a
> different computer. So maybe that had some affect.
> So I do suspect me running it on the serer caused locks but might have
> caused some slow down. So I think the application is either timing out too
> quickly or something else is a miss. Thank-you for your reply.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "Ekrem ?nsoy" wrote:
|||Thanks Guys ;-).
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> SQL Profiler will not create database locks unless you are logging to a
> database table, which is not usually recommended. In whioch case it is
> unlikely that other applications are using that table (or database!)
> If you can't log from a separate machine on a dedicated lan, you can use a
> server side trace. Make sure that any trace files are not using the same
> discs as SQL Server preferrably have fast spindles for it's exclusive use
> (although this is rarely possible!)
> If you use the SQL Profiler GUI it will use space on the TEMP directory
> (even if logging to a file) make sure that this is not the system disc as
> running out of space on that, will cause the system to hang.
> John
> "Mohit K. Gupta" wrote:

Perf Mon creating large temp files

I am using a remote connection to connect to a specific server (13B) and I a
m
running a trace from there against another server (138). I have a template t
o
track performance points and am saving the results to a table. My problem is
that when I run this trace, I also am creating a very large set of tmp files
on the local C drive of 13B. The files are named nst1.tmp,prf3.tmp,prf4.tmp.
The C drive space is not very large so I max out the space quickly. I am
looking for a way to point the tmp files to another drive or elimate them
altogether.
ThanksNever go directly to a table. Place the files on a local hard drive first
and then import them later. Otherwise you will incur more overhead than you
need to. If you want to import Perfmon files take a look at the Logman.exe
utility.
Andrew J. Kelly SQL MVP
"MNDiver" <MNDiver@.discussions.microsoft.com> wrote in message
news:1F3F9BF1-5D66-4882-83A2-19C28F1522B1@.microsoft.com...
>I am using a remote connection to connect to a specific server (13B) and I
>am
> running a trace from there against another server (138). I have a template
> to
> track performance points and am saving the results to a table. My problem
> is
> that when I run this trace, I also am creating a very large set of tmp
> files
> on the local C drive of 13B. The files are named
> nst1.tmp,prf3.tmp,prf4.tmp.
> The C drive space is not very large so I max out the space quickly. I am
> looking for a way to point the tmp files to another drive or elimate them
> altogether.
> Thanks