SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Set Up And Schedule a Server Side Trace


Set Up And Schedule a Server Side Trace

Author
Message
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93403 Visits: 33007
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
SalvageDog
SalvageDog
SSC-Enthusiastic
SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)SSC-Enthusiastic (135 reputation)

Group: General Forum Members
Points: 135 Visits: 336
Very good Grant. Thank you for the reply.
rob mcnicol
rob mcnicol
SSC Veteran
SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)SSC Veteran (286 reputation)

Group: General Forum Members
Points: 286 Visits: 686
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
Grant Fritchey
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93403 Visits: 33007
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)SSC Guru (202K reputation)

Group: General Forum Members
Points: 202970 Visits: 41947
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93403 Visits: 33007
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
ejavier.r.dk
ejavier.r.dk
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 18
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
Grant Fritchey
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93403 Visits: 33007
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
dyerjohn
dyerjohn
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 5
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search