Merge outputs of perfmon and profiler to get a great view

There is a way by which we can merge the .trc and .blg files to get a great view as below :

MergeProfilerPerfmon

We have SQL Server Profiler event data in the top pane and PerfMon data in the middle. Data from both PerfMon and Profiler is correlated. If you click on a Profiler record on the top pane, a red marker in the PerfMon window jumps to the point in time for that Profiler entry. And vice versa, if you click on the perfmon chart, say, just before disk I/O activity begins to rise, the Profiler event in the top pane is highlighted.
The SQL or T-SQL statement issued by that Profiler event is shown in the bottom third pane.

These two performance tools are present in all versions of SQL Server.
PerfMon shows the overall resource consumption of Windows. On the other hand, Profiler shows specific events as they occur inside the SQL Server.
PerfMon helps identify the bottleneck on resources, not the exact problem.
Using the overlay of perfmon chart and profiler events, when resource consumption, say CPU or Disk I/O, gets high, we can tell exactly what statement, batch, or job caused it.

Below are the steps to get this surreal overlay :

1 Start PerfMon counter log and record to a file.
2 Start Profiler trace and record to a file.
3 Stop recording to both files after sometime.
4 Open SQL Profiler -> select File -> Open > Trace File > abc.trc
5 Then again -> select File -> Import Performance Data -> perfmonOutput.blg
6 Select the PerfMon counters.(You may get a waring but proceed)

At this point, you should have an overlay as shown in the screenshot before.
Click on a particular event in profiler pane and see how the red marker navigates in perfmon chart.

By correlating, you can exactly pinpoint which SQL statements have affected performance.
Quite simple !!

Advertisements

8 thoughts on “Merge outputs of perfmon and profiler to get a great view

  1. When we ran Profiler against one of our databases it set our server to a crawl. This database doesn’t get all that much use <100 users per hour. What settings do I need upon Profiler and PerfMon and how do I set up Profiler so the server doesn't come to a screeching halt?

    • I generally use one of the profiler templates and filter based on DatabaseName/Application etc.
      You can try server side tracing as this will reduce some overhead and might be useful in your case. Refer below link for setting up server side trace :

      http://technet.microsoft.com/en-us/library/cc293613.aspx

      For perfmon, I usually go with the below counters :

      Memory – Pages/sec
      Memory – Available MBytes
      Paging File – % Usage
      Processor – % Processor Time ( We may track the % Processor Time for each individual processor, or for all of them combined (_Total). Its better to select for each individual processor rather than selecting the Total)
      Physical Disk – % Disk Time (again we get multiple instances, one per physical disk.Better to add for all individual disks)
      Physical Disk – Avg. Disk sec/Read
      Physical Disk – Avg. Disk sec/Write
      MSSQL$:Memory Manager\Total Server Memory (KB)
      MSSQL$:Memory Manager\Target Server Memory (KB):
      SQLServer: Buffer Manager – Page life expectancy
      SQLServer: General Statistics – User Connections
      SQLServer: Memory Manager – Memory Grants Pending
      SQLServer: SQL Statistics – Batch Requests/sec
      SQLServer: SQL Statistics – Compilations/sec
      SQLServer: SQL Statistics – Recompilations/sec
      System – Processor Queue Length

  2. Hi, Great article! This is probably a daft question, but how do you save the trace in perfmon to a blg file? I can’t see anywhere on the perfmon interface that gives you the option to save the trace to file (though I can see the option to open a blg file).

    I am running Windows 7. Thanks

    • You need to create a new data collector set. It will ask for a directory where the output will be saved. Once you start this data collector set, blg file will be auto generated and save in above location.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s