SQL Server Performance Forum – Threads Archive

[Solved]Trace using a script

Hello Im trying to monitor information of a server using a trace and i have some question : – Is running a trace trough the help of a script has less impact on a server performance than using the SQL Profiler tool? By much? – Is there a way to run a scripted trace on a remote machine like we can do for the SQL profiler? Dale

Hi Dale and welcome to the site!
Answers: Q1: Yes it has much less impact. You would have to run tests to be sure how much. Below is a link for a description on how to do it and it discusses impact.
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm Q2: You can kick off the procs (from above) from any machine. The trace files, however would be local unless you shoved them into a table (not my recommended way) I like to have jobs set up that start and stop the trace at given times during the day. Then I have the stop trace job load the text files into a table and keep it for a certain duration. Up to you how you want to do it. It has never caused any performance problems for me when running.
Michael
MCDBA "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends — if they’re okay, then it’s you!"

Thanks for the fast answer. I just had a 4 day formation on sql server 2005 and our teacher told us never to do trace on the server using profiler but could not answer us about server sided trace using script because he never did that before. I’ill do some testing. Dale

He is a newbie!!!<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> LOL anyway, yah. its the best way. You can also create some special procs that work for diff things (like the link has) for like blocking etc and have them as a job that you can just fire off without having saved any profiler templates. Its fun to get into it (my oppinion) and very low load. And stay and help out this forum. It is a good group of DBAs here and we all try to help quickly – depending on time zone<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Some from India rock too<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Michael MCDBA "The statistics on sanity are that one out of every four persons is suffering from some sort of mental illness. Think of your three best friends — if they’re okay, then it’s you!"

Server side trace is not well documented in the Books online or references, mostly you will find from the non-microsoft websites and forums etc. So I wouldn’t blame your teacher too or may be not ready to accept anything other than what is written in a book [:]. As referred by Mike you will get more information on server side trace: – Is running a trace trough the help of a script has less impact on a server performance than using the SQL Profiler tool? By much?
Yes, by far you will notice this will have a better return of results if the server is already stressed with resources. If you run PROFILER from the client then it need to pingback to client each and every time it is traced, image 10 round-trips in 2 seconds will cost more in 15 minutes for a server. Refer tohttp://mssqltips.com/tip.asp?tip=1111 in this case. – Is there a way to run a scripted trace on a remote machine like we can do for the SQL profiler?
Refer to the links give for such scripts, also BOL states, that "Default trace provides troubleshooting assistance to database administrators by ensuring that they have the log data necessary to diagnose problems the first time they occur." Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

]]>