Set Up And Schedule a Server Side Trace

  • 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • Very good Grant. Thank you for the reply.

  • 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

  • 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • 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)
    Intro to Tally Tables and Functions

  • 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • 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

  • 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • 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

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

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