Set Up And Schedule a Server Side Trace

Question

Set Up And Schedule a Server Side Trace

Viewing 9 posts - 31 through 38 (of 38 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

SalvageDog (2/1/2011)
Hello,
Thanks for a great article.
I have a very intermittent problem, occurs only two or three times a month. Would there be any issues with running a server side trace all day, other than creating a lot of .trc files? I'm especially concerned about an all-day trace using too many resources and affecting server performance.

As long as the trace is set up with a minimum of events and columns, and it's output to a file, you should not affect performance at all. In testing I've done, a standard set of RPC:Complete and SQL Batch:Complete events put less than 1% load on the server, regardless of load. You'll just have to deal with all the data.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SalvageDog SSC Veteran Points: 205 More actions · Answer 2

SalvageDog

SSC Veteran

Points: 205

February 1, 2011 at 8:21 am

#1280961

Very good Grant. Thank you for the reply.

rob mcnicol SSC Eights! Points: 834 More actions · Answer 3

rob mcnicol (1/31/2011)
great article, thanks.
what tweaks to this process are required to run a server-side trace on analysis services? when i export my Script Trace Definition, the 'For SQL...' options are greyed out. i am only able to export 'For Analysis Services...' which produces an xmla file.
what do i do with that xmla file to schedule the trace?

i subsequently found this:

http://www.sqlservercentral.com/articles/Profiler/63097/

the summary answer to my question is: paste the xmla code into a job step

thanks again

rob

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 4

rob mcnicol (2/1/2011)
rob mcnicol (1/31/2011)
great article, thanks.
what tweaks to this process are required to run a server-side trace on analysis services? when i export my Script Trace Definition, the 'For SQL...' options are greyed out. i am only able to export 'For Analysis Services...' which produces an xmla file.
what do i do with that xmla file to schedule the trace?
i subsequently found this:
http://www.sqlservercentral.com/articles/Profiler/63097/
the summary answer to my question is: paste the xmla code into a job step
thanks again
rob

That's good. And thanks for posting it back here. Other people who come along will find it useful.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 5

I know that I've already posted on this thread but I've recently had the need to revisit the article and wanted to say "Thanks" again, Grant.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 6

Thank you Jeff!

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ejavier.r.dk Newbie Points: 7 More actions · Answer 7

great article. for a very very basic sql user like me, this is great!

Now, I want to use the info from the trace in SCOM, and I know SCOM can read logs. but would it be possible to somehow add a logic that would generate a NT log event when queries take longer than x ms?

Then the scom side would just have to catch that event in the nt log.

Thank you in advamce

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 8

ejavier.r.dk (5/9/2011)
great article. for a very very basic sql user like me, this is great!
Now, I want to use the info from the trace in SCOM, and I know SCOM can read logs. but would it be possible to somehow add a logic that would generate a NT log event when queries take longer than x ms?
Then the scom side would just have to catch that event in the nt log.
Thank you in advamce

I used to set up & maintain our SCOM system at my previous company and I'm honestly not sure how you could do this in SCOM. As a matter of fact, I don't think you can. It's just not built that way. I could be wrong.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

dyerjohn Newbie Points: 9 More actions · Answer 9

Excellent stuff and it will be quite useful.

One point about the trace file location, in your example you point it to a network share. What issues will there be in the server isn't available? i.e. in your example you point it to your machine, what if your machine was turned off?

Also if writing the trace to a local file and your DB is busy you want to think carefully about which drive the trace file will go to. Systems I have used were tuned with file groups so the parts could go on different drives. There were also temp DB issues from time to time. So placing the trace file is something that your should think carefully about.

John