Combine PerfMon and SQL Server Profiler Data

Found this nice in-depth blog post: https://www.simple-talk.com/sql/database-administration/correlating-sql-server-profiler-with-performance-monitor/

You should use the following measures for perfmon.exe:

<Counter>\Memory\Pages/sec</Counter>
<Counter>\PhysicalDisk(0 C:)\Disk Transfers/sec</Counter>
<Counter>\PhysicalDisk(3 D:)\Disk Transfers/sec</Counter>
<Counter>\PhysicalDisk(5 E:)\Disk Transfers/sec</Counter>
<Counter>\PhysicalDisk(0 C:)\Split IO/Sec</Counter>
<Counter>\PhysicalDisk(3 D:)\Split IO/Sec</Counter>
<Counter>\PhysicalDisk(5 E:)\Split IO/Sec</Counter>
<Counter>\PhysicalDisk(0 C:)\Avg. Disk Queue Length</Counter>
<Counter>\PhysicalDisk(3 D:)\Avg. Disk Queue Length</Counter>
<Counter>\PhysicalDisk(5 E:)\Avg. Disk Queue Length</Counter>
<Counter>\Processor(*)\% Processor Time</Counter>
<Counter>\SQLServer:Buffer Manager\Buffer cache hit ratio</Counter>
<Counter>\SQLServer:Buffer Manager\Page life expectancy</Counter>
<Counter>\SQLServer:Memory Manager\Free Memory (KB)</Counter>
<Counter>\SQLServer:Memory Manager\Total Server Memory (KB)</Counter>

If you have problems with perfmon, that your collector is in a “compiling” status and you want to terminate that Data Collector set:

  1. Write down the name of the troublesome set
  2. Close all of your PerfMon windows
  3. Open RegEdit and goto “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Schedule\TaskCache\Tree\Microsoft\Windows\PLA”
  4. Delete the SubFolder containing your Data Collector set name
  5. Re-Open perfmon.exe – the Data Collector Set is gone

Tested on a Windows Server 2012 R2.

Extracted from http://kb.itimpulse.in/?p=147

Leave a Reply

Your email address will not be published. Required fields are marked *