Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Set Up And Schedule a Server Side Trace Expand / Collapse
Author
Message
Posted Tuesday, February 01, 2011 4:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 14,802, Visits: 27,276
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1056712
Posted Tuesday, February 01, 2011 8:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:47 PM
Points: 109, Visits: 335
Very good Grant. Thank you for the reply.
Post #1056850
Posted Tuesday, February 01, 2011 6:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 25, 2013 5:33 PM
Points: 149, Visits: 555
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
Post #1057155
Posted Wednesday, February 02, 2011 4:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 14,802, Visits: 27,276
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1057335
Posted Monday, February 07, 2011 11:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:06 PM
Points: 35,963, Visits: 30,256
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1060039
Posted Tuesday, February 08, 2011 5:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 14,802, Visits: 27,276
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1060136
Posted Monday, May 09, 2011 9:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 19, 2011 2:14 PM
Points: 1, 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
Post #1105509
Posted Monday, May 09, 2011 10:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 14,802, Visits: 27,276
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1105566
Posted Thursday, January 02, 2014 7:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 02, 2014 7:18 AM
Points: 1, Visits: 3
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
Post #1527114
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse