Before SQL Server 2005 became available, I often ran into two situations when using either Profiler or System Monitor that were completely frustrating. First, I’d see a long duration event in Profiler and I wanted to know how it affected server performance. Or second, I would see a spike in server activity in System Monitor and wondered what query or other activity caused the spike to happen. In either case, I was unable to find out my answer because there was no way to correlate Profiler events and System Monitor counter activity. But in SQL Server 2005, there is.
In this article, we take a look at how to collect both System Monitor and Profiler data, then correlate the two so you can see exactly how SQL Server activity is directly affecting server performance, and vice versa. For this article, I am assuming you already know the basics of how to use System Monitor and Profiler.
Collecting Data in System Monitor
If you want to correlate System Monitor counter data with SQL Server 2005 Profiler Trace Events, you must capture both types of data during the same time period. While you can start either System Monitor or Profiler first, for the following example, I am going to start with System Monitor first, then Profiler second.
To begin collecting counter performance data with System Monitor, launch System Monitor, then expand “Performance Logs and Alerts.” See the figure below. This is where you create a new System Monitor log file, which is needed to correlate to the Profiler Trace data we will also be collecting.
Next, right-click on “Counter Logs” and select “New Log Settings.” The following dialog box appears.
Above, enter the name you want to assign to the log file you are going to be collecting. Then click “OK.” The following dialog box appears.
Now, you can add whatever Objects or Counters you want to collect, and specify the length of the sample data interval you want to use to collect the data. When you select “Add Objects” or “Add Counters” to specify the data you want to capture in the log file, keep in mind that the more counters you collect, the more resources it will take away from SQL Server, so don’t get carried away with trying to collect every Object or Counter there is. The same applies to selecting a sample data collection interval. While the more often you collect data, the easier it will be to correlate System Monitor Counters with Profiler Events, doing so also adds to SQL Server’s resource overhead. You may want to experiment with different time intervals to see what works best for you.
In this demonstration, I have selected to collect counter data on three Objects, and to collect them every five seconds. Once you are done specifying the above two options, and any other options you choose to modify, select “OK.” The following window appears.
You are done creating the Counter Log. Now, to manually start the counter, right-click on the Counter Log name and select “Start.” At this time, the counter log will begin collecting the counter data you selected.
Collecting Data in SQL Server 2005 Profiler
Now that System Monitor is collecting data, it is time to create a Profiler Trace and have it begin collecting activities and logging them.
To do this, start Profiler. The following dialog box appears.
In the above dialog box, you will first need to assign a “Trace Name.” Next, you need to select the “Save to file” option so that the trace you are collecting will be saved. Now click on “RUN,” and the following dialog box appears.
In this dialog box, you select what events and data columns you want to capture. In my case, I limited my data collection to TSQL events, both SQL:BatchCompleted and SQL:BatchStarting. When specifying events, it is important that you select both their start and completed times so that you can see the proper correlation between System Monitor and Profiler data.
Once you are happy with the events, data columns, and any filters you want to use, select “Run,” and the following window appears. This is your standard Profiler data collection screen. Profiler events are now being captured and stored in a trace log.
