Automation is the separation point for the professional DBA from the amateur. That makes this a very important topic. Thanks to Pat Wright (blog|twitter) for coming up with something great to write about and hosting the event.
I recently wrote an article for the SQL Spackle series over on SQL Server Central on how to “Set Up and Schedule a Server Side Trace.†That covers well what to do to set up a trace on your system so that you can automate it. But I think I left out a few details that I think are worth pointing out here.
The basics on creating the Server Side Trace using Profiler and scheduling it using SQL Agent are well covered in the article. The extra areas I want to address are in regards to scheduling and file management.
For scheduling, as the article points out, you schedule the start time using SQL Agent, and set the end time using a variable within the trace script. But, I don’t suggest how long you should be running these things. Some people will disagree with this, but I think that on production systems, especially ones that need lots of tuning and tender loving care, you should run the trace 24/7. Or rather, almost 24/7. You could start a trace, not give it a stop time, and then just manage it forever, no worries, just lots of work. A few problems come up from this. What happens when you need to restart the server? If you’re applying service patches, or upgrading machines or something, you either have to kill the process, or let it get killed when the server reboots. And then, you’ll have to manually restart it to get the trace going again. Or, you can schedule the trace to start and stop every 23 hours and 59 minutes. Then, it runs, all day, every day (almost) and disabling it for a reboot experience is simply a matter of turning it off at the Agent. Or, if you don’t turn it off, you let it die with the reboot. Restarting just requires turning it back on at the Agent or waiting for the schedule to restart the event.
When you collect trace data, the best way to collect it is to a file. It’s fast and won’t affect your server in most cases. But how big should it be? Now that’s a balancing act. If it’s too big, you can actually affect the performance. It does take longer to write to a larger file. If it’s too small you run into the problem of having hundreds and thousands of little files to manage. My best offer here, figure out what works best for you. I like a number around 25mb. This is big enough that on most systems I would only have to deal with 8-10 files a day, but small enough that I get the files freed up by the trace as it goes into rollover every three or four hours. But this is really a case where your mileage may vary.
Just a couple of notes that hopefully help you make decisions on how best to automate your own performance metric data collection.
Yes, we’ve been running it on the 23:59 schedule too, and letting it die at reboot, then auto restart for a few months now. It’s worked very well so far!
I set mine up to run 23:59:57. I have it in an SQL Agent job that is set to run every 30 min. It checks if the the trace is running if not it starts it for me. So even on a reboot within 30 min I should have the trace restarted. I set my file size to 10 Meg and set file role over. I also have the SQL agent job (which actually calls an SP to do the work) Create a new file name each day.
There you go. Even more sophisticated. Nice. I like it.
Another way is sp_procoption: (http://msdn.microsoft.com/en-us/library/ms181720.aspx). Using that, as soon as the server starts, it will execute a specific procedure, which in this case kicks off the trace(s). No need for SQL Agent.
[…] Grant Fritchey gives us suggestions on how to keep a server side trace going all the time. Great suggestions from grant on what you should do to maintain the trace. I typically keep my server side trace running every 15 minutes to store the file and load it to the table. I have this fully automated so it’s just a sql job that runs. […]
[…] from code. An interesting way to enforce the rules that you might want to have in your environment. Grant Fritchey talks about keeping server side traces running all the time with a little automation to manage the […]
Or… change the Job’s “Schedule Type” to “Start automatically when SQL Server Agent Start”. I usually set up this job with two schedules: one as set above, the second to fire on Sundays at midnight. The job searches for and stops a current trace, then restarts it. Trace set for automatic file rollover, and the file name is timestamped with the day/time.
Both good ideas. Thanks for posting them.