I originally started writing ClearTrace because Microsoft hadn’t released Read80Trace for SQL Server 2005. They finally did release it but by then I had a pretty handy little utility. Because I was writing it for myself I made some “interesting” design decisions along the way. One of these is now called a “Trace Group”.
I do a lot of SQL Server performance tuning for for my clients. I was struggling with a way to organize the traces I collect. At various times I want to categorize by client, application, server and/or whatever I happen to be tracing at that time. And if I run the traces inside their networks then I really don’t need the client part. And if they only have one server then I really don’t need the server part but I’d be willing to keep it just to make things consistent. After many failed fancy hierarchal database designs I just put in a free form text box and called it a Trace Group and used it as a way to group traces together. Trace Group was the most generic phrase I could think of.
Ninety percent of the time a trace group is just the name of a server. This lets me quickly review performance for any individual server. You can create a new trace group by typing a name into the trace group down down list box. You can add new trace files to an existing trace group by choosing the trace group from the drop down list box. The command-line version has similar features. If a trace group you specify isn’t found in the database it’s added.
I pull the date and time out of the trace file itself so I can go back and look at previous performance.
All the traces I read into ClearTrace are generated by a script to generate SQL Server traces that I wrote. ClearTrace will handle any trace file as long as the required fields are present but this script gives me the minimum I need to capture performance data.
I’ve also used a “client name – server name” format when I’m processing traces on my machine into my ClearTrace database. I also use names like “Quick test of the prod server to see how badly this new code broke our server” but I try to avoid names like that.
I also store the name of the trace file within the trace group. If you reprocess a file within a trace group I replace what was previously in the database for that file. It’s important that a trace file name be unique within a trace group. In my original planning for ClearTrace I wanted to it to run as a service and handle reprocessing files gracefully. My little script above automatically adds a date and time stamp to each trace file and I’d encourage you to do the same.
There’s a special trace group called “(One Time Trace Analysis)”. Any time you start to process a file for this trace group all the detail for this trace group is deleted. This was my quick and dirty way to just read a trace file and see what’s going on. The data will be saved but only until you process another set of files for this trace group.
Hopefully this gives you some ideas on what you can use the Trace Group. I’d be curious to hear any thoughts you have on this and how you use it.