Correlate SQL Profiler and Performance Monitor data

Lot of times while troubleshooting the performance issues for al application, we might collect two different sets of data. One tool we normally use for performance issues is SQL Server Profiler and the other tool is performance monitor. Both of these tools have different features and they collect different data sets. While SQL profiler is SQL server tool performance monitor is a windows tool.

Profiler gives you an insight in what is happening within SQL Server. Performance monitor gives you insight in what is happening on the windows level.

But lot of times I have wondered if I could get a single unified view of these 2 tools together.

It would be great to see what queries were running while at the same time knowing the CPU utilization of the SQL Server at the time when that query was running.

And that’s where we have a problem. Profiler gives us queries running in SQL Server and performance monitor gives us CPU utilization.

So how do we correlate these 2 different data sets?

Well there is a way to do it and in this post we will demonstrate how to do that.

There is a condition on how to do this though.

We need to collect both the data sets at around the same time. So basically both profiler data and performance monitor data have to be for the same time window.

So let’s start with creating the performance monitor data first.

For this demonstration I am only going to use CPU utilization although you can use any other counters you want.

Click on performance monitor and it will start the following window.

This is where we will configure the performance monitor log. Right click on the User Defined and click on new data collector set as shown in the screen shot below.

This will take you to the following screen.

Enter a name for the data collector set and then click on Create Manually as shown in the screen shot above and then click on Next button.

On the next screen click on Create Data logs and check Performance Counter check box and then click on Next button.

In the next screen, click the Add button as shown below.

On the next screen, we will need to choose the performance counter that we are interested in monitoring. For this demonstration we are going to choose CPU usage. This is represented by the counter processor time as shown in the screen below.

Select processor time and then click on Add button. When you add it, it will be added to the right side as shown in the screen above. Click on OK button and you will see the next screen.

This will allow you to change the location where the data will be stored. In my case it is the selected directory but you can store the log anywhere you want. You can choose the file location by using browse button. Click Next.

After choosing the location, click Next button. This will take you to the following screen.

On this screen click Finish. Once you do this, you will see the following screen. You should be able to see the CPU Usage performance log as shown below.

Double click on the Data Collector 01 that you see on the right side and change the log format from binary to comma separated and then click on Apply button.

This will make sure that our log file is in comma separated and readable format.

Right click on this and click on start to start the data collection for the performance monitor log.

You can see that log is running by checking the green arrow just next to CPU Usage. Also you can see in the right pane the complete path where the log file is stored. I have erased my log location.

You can visit the same location and actually see the file there as shown in the screen shot below.

So this basically has set up our performance monitor log.

Now in the second step, let’s start the SQL Server profiler that will collect queries that take more than 1 second on our server.

To set up the profiler, first connect to the SQL Server that you want to monitor using SQL Server Management Studio.

After connecting to SQL Server for which you want to collect data, click on Tools and then SQL Server Profiler as shown below.

Once again you will see the same screen that will ask you to connect to SQL Server. Connect to the same server again but this time you will see the SQL Server Profiler window and not SQL Server management Studio as shown below.

In this window click Event Selection tab. This will open new window where we can select the events that we want to monitor.

You can monitor procedure completed, statement completed, statement inside procedure completed etc. For this demonstration purpose, I am using only batch completed event.

Since we ant to monitor only those statements that take more than 1 second, we need to add filter to our trace. Click on Column Filters button.

This will take you to the following screen.

On the screen add filter of 1000 in the duration and click on OK button.

On the next screen click Run.

And that’s it. Our profiler trace is started. You should see the data filling in the profiler window. Let it run for as long as you need.

After you have enough data, stop the trace using the stop button as shown below.

Now save the trace file to any location you want using the File – Save button. This will ask you for a location where you want to save the trace file. Provide the location and save the trace file.

Now close the profiler and then open it again. Once it is open, use file – open button to open the same trace file again.

You would see the same data that you collected earlier in the trace. Now this is showing you the queries or batches that took more than 1 second. That’s good.

But how do we add performance monitor data in this window?

Do you remember that we had started a performance monitor log at the start of this post?

Well, let’s stop that too and get that file in here in the same window.

Right click on CPU Usage in the performance monitor and click on the Stop button.

You know the location where this particular log file is stored. If you don’t you would be able to see it in the right side pane.

Go to the same location and copy the file where you have access.

Now let’s open this file in the same profiler window where we have opened our trace file earlier.

To do this, click on File and then import performance data.

This will open a file browse dialog box. Now select our performance monitor log file in the file choose dialog box.

Now the profiler will show you the counters available in the performance monitor log file that you selected.

Here we can see that there is only one counter which is processor time. This is the only counter that we had selected when we set up our performance monitor log.

Choose it and click OK.

And et voila!

We can see both the performance monitor log as well as profiler trace in the same window.