In SQL Server 2005 a great new feature was introduced into SQL Server Profiler – the ability to import Perfmon data.
If you’re not familiar with one or both of those tools, SQL Server Profiler is a package included with SQL Server that can “watch” your system’s activities. You add these activities into something called a “trace”, which is the recording of the activities you want to track. Perfmon is a tool in Windows and called the Windows System Monitor since Windows 2003 and the “Reliability and Performance Monitor” (whew) in Windows 2008 and later, does much the same thing but from a Windows perspective. You can read more about each of those with a quick Bing! search.
In the past, you had to look at these two data sets independently. It is interesting, however, to see what the two counters look like together. You can see when the processor or memory spikes just as a user runs a certain query – very helpful.
To do that, you need to do two things: First, start the Perfmon counter collection to a binary log file (.blg), which is an option within that tool. Next, add the pertinent trace events to a SQL Server Profiler session, and make sure you open the columns and get the StartTime and EndTime columns or the correlation won’t work. Record that trace. No stop the trace, and then stop the Perfmon logging.
Open the Trace in Profiler, and then click File and then Import Performance Data. Select the Perfmon Objects and Counters you want, and you’ll see the graphs just below the trace data. Tres’ cool!
(Oh – one more thing: this is great for just general testing in your development phase as well…)