A really cool feature in SQL Server Profiler is the ability to import performance monitor (perfmon.exe) logs. Prior to this, DBAs used to have to jump through hoops working w/ tables, excel, and whatever else we had on hand to determine exactly what database activities are causing the resource spikes.
1) Create a Counter Log. Add only relevant counters, and remember that there is a resource overhead to any type of monitoring activity. Also remember to set the interval to a number that will generate a manageable, but useful amount of data.
2) Start a Profiler trace with a time span that overlaps that of the Counter Log. Again, trace only those events that are relevant, to keep overhead as light as possible (Management Studio –> Tools Menu –> SQL Server Profiler, then File –> New Trace).
3) After your Counter Log and Profiler trace have accumulated enough data for your needs (a single query’s worth, or a month’s worth, depending on your goal), stop both and make sure they are saved. Then go to Profiler, File–> Import Performance Data…
You can filter down both your Perfmon counters and your SQL Server columns, to pare down what you are analyzing. Also, we can highlight a certain range on the graph to localize the trace to that time span.
