March 29, 2009 at 6:54 am
Morning all......I'm brand spanking new to performance tuning, and have a pile of questions. However, I figured I'd start off with the easiest and then post back when I can.
I read somewhere a while back that you can set the Enable File Roll over in the Query analyzer version of the trace. Apparently running profiler on your production server is a bad idea as it can add to the already existing performance issues (I'm assuming it's because there's an interface, which needs CPU time to run etc. Running through Query Analyzer is "behind the scenes", I'm guessing). However, having read this tidbit of info just as I was starting out, I thought "bah, if I need it I can find it again no problem"....well, I need it and I have been looking now for a couple of days with no luck. Does anyone know how to do this?? I'm hoping to set up a couple of trace files to run over a 24 hour period, and from the looks of it, a 5 Mb file fills up really quickly during peak usage so I am going to need a way to capture more data (say 2 hours on, an hour off).
Appreciate any ideas or suggestions.....Thanks in advance and happy SQLing!
Chris
March 29, 2009 at 7:09 am
Take a look at this article over at Simple Talk. It goes into creating the trace scripts and running them. http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
If you need an hour on/hour off kind of thing, I would suggest you set up the trace and have a job that pauses and restarts the trace as necessary. It's done by setting the status of the trace.
As for the files, I'd set the size a lot large than 5 MB. I usually use a 250 MB file, though I'm willing to go larger if necessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2009 at 7:22 am
Hi Gail - thanks for your reply (I had actually printed off your article, I just haven't read it yet so I didn't make the connection to your name!). So, a couple of new questions:
1) Will the roll overs happen automatically? The article I had read had a specific line to be added in query analyzer once the SQL had been generated, but I don't see that line in your code. I've tried a couple of times and once the filesize max had been reached, the trace stops, regardless of how long it's been running.
2) Does it make a difference if I'm running the Trace on my computer (client side) or on the server? I'd rather run it on my computer for the simple fact I can save the TRC files directly to my folder. Otherwise, I'll have to save the files to the Server and then copy them over (which I'm afraid will impact performance!).
3) Is there anything else that I need to be aware of (cautions, Don't-Dos, that sort of thing) before I start playing with these?
Appreciate your help!
Chris
March 29, 2009 at 7:33 am
darth.pathos (3/29/2009)
1) Will the roll overs happen automatically? The article I had read had a specific line to be added in query analyzer once the SQL had been generated, but I don't see that line in your code. I've tried a couple of times and once the filesize max had been reached, the trace stops, regardless of how long it's been running.
Have you checked Books Online for the syntax and parameters of the sp_trace_create procedure?
http://msdn.microsoft.com/en-us/library/aa260328(SQL.80).aspx
sp_trace_create [ @traceid = ] trace_id OUTPUT
, [ @options = ] option_value
, [ @tracefile = ] 'trace_file'
[ , [ @maxfilesize = ] max_file_size ]
[ , [ @stoptime = ] 'stop_time' ]
[ @options = ] option_value
Specifies the options set for the trace. option_value is int, with no default. Users may choose a combination of these options by specifying the sum value of options picked.
Option name: TRACE_FILE_ROLLOVER
Option value: 2
Description: Specifies that when the max_file_size is reached, the current trace file is closed and a new file is created. All new records will be written to the new file. The new file will have the same name as the previous file, but an integer will be appended to indicate its sequence. For example, if the original trace file is named filename.trc, the next trace file is named filename_1.trc, the following trace file is filename_2.trc, and so on.
As more rollover trace files are created, the integer value appended to the file name increases sequentially.
SQL Server uses the default value of max_file_size (5 MB) if this option is specified without specifying a value for max_file_size.
I'm not using the rollover feature in my article because I don't like file rollovers. I find it much, much easier to work with one 200MB file than to work with 40 5MB files.
2) Does it make a difference if I'm running the Trace on my computer (client side) or on the server? I'd rather run it on my computer for the simple fact I can save the TRC files directly to my folder. Otherwise, I'll have to save the files to the Server and then copy them over (which I'm afraid will impact performance!).
It's a server-side trace. It's running on the SQL server regardless of where you run the query from.
You do not want to write the trace files across the network, it could seriously impact the performance of the server while the trace is running. They should be written to a fast, local disk that does not have any database files on it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2009 at 7:42 am
GilaMonster (3/29/2009)
darth.pathos (3/29/2009)
Have you checked Books Online for the syntax and parameters of the sp_trace_create procedure?
Why is it the easiest answer frequently escapes me?:ermm:
GilaMonster (3/29/2009)
darth.pathos (3/29/2009)
I'm not using the rollover feature in my article because I don't like file rollovers. I find it much, much easier to work with one 200MB file than to work with 40 5MB files.
I will now set my file max to 200 Mb; my concern is that this maximum will be hit 20 minutes into my trace, leaving me with an hour and forty minutes of no data.
GilaMonster (3/29/2009)
darth.pathos (3/29/2009)
It's a server-side trace. It's running on the SQL server regardless of where you run the query from.You do not want to write the trace files across the network, it could seriously impact the performance of the server while the trace is running. They should be written to a fast, local disk that does not have any database files on it.
Great - so as long as I'm connected to the database through SQL Query Analyzer, I'll be able to run the trace anywhere, which is good because some groups have issues with different modules in the database, so this will be a good way to find them.....
OK, time for breakfast (for me at least!)!!
Chris
March 29, 2009 at 8:51 am
darth.pathos (3/29/2009)
I will now set my file max to 200 Mb; my concern is that this maximum will be hit 20 minutes into my trace, leaving me with an hour and forty minutes of no data.
Then set the file size to a reasonable size based on the amount of activity in your database. If you're still worried, then set the option to roll over. Just make sure you have enough hard drive space if that's the case.
btw, trace data (providing you trace just the columns you need, isn't that big. I've got a 100 MB trace file that I'm currently working with that contains just under 250 000 events in it.
Great - so as long as I'm connected to the database through SQL Query Analyzer, I'll be able to run the trace anywhere, which is good because some groups have issues with different modules in the database, so this will be a good way to find them.....
Yes, but the trace files will be written to the server's hard drive, so you will have to copy the files off to work with them. Schedule that for a quiet time and it won't cause problems.
OK, time for breakfast (for me at least!)!!
🙂 I'm just starting to cook supper.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2009 at 12:16 pm
Hi Gail - I think I've had a realisation - running the Profiler itself allows me to save the TRC file to my desktop; running the SQL Query (from either client- or server-side) saves the file to the server....am I close or totally off-base?
I can't believe how much stuff there is to know on this stuff (and I need to have a full-blown report written by end of April!!)
Thanks...
Chris
March 29, 2009 at 12:40 pm
darth.pathos (3/29/2009)
Hi Gail - I think I've had a realisation - running the Profiler itself allows me to save the TRC file to my desktop; running the SQL Query (from either client- or server-side) saves the file to the server....am I close or totally off-base?
If you run the profiler gui, then SQL streams the trace data to the gui (which is running on your PC). From there, the profiler gui can do several things with it.
1) Display it without saving (though it does use a temp file)
2) Save to a file anywhere that is accessible from that machine with your user account
3) Save to a table in any database that the user running profiler has access to.
If the trace is run as a server-side trace, the SQL takes care of saving the trace data and it can save it to a file anywhere that it can access. That is, any directory or network share that the SQL Server Service Account has access to.
That's the facts. There are a few other things to take into consideration.
1) Using the Profiler GUI to trace a server can cause a slow down. This is because Profiler takes exclusive latches to synchronise the trace. This isn't a problem on a server that's barely used. On a busy production server, using the Profiler gui can crash the server. I should know, I've done it twice (same server, same day)
2) Using the profiler option to save to a database table is the most intensive way to trace a server. Especially if the trace results are been written to the same server that the trace is running on
3) When using a server-side trace, the results should not be written across the network. Network latency can cause the trace to 'back up' and result in processes waiting to write events before they complete.
4) When using a server-side trace, results should not be written to any drive that contains a database data or log file. This is because of potential IO contention.
Feel free to ignore all of that on servers that aren't very busy. Trace the dev server using the profiler GUI and write the results to a table. No problem. Do that on a very busy production server and it will very likely have a severe impact on the server's performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2009 at 2:07 pm
Gail - I have to thank you as sincerely as I possibly can - You have made me realise I understood more than I thought, and what I didn't understand you clarified for me. It's people like yourself that make forums such an amazing place.
I look forward to seeing your replies to my other totally newbie questions 😉
Chris
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply