The goal here is to get CPU usage per SQL instance. As far as I can tell there is no perfmon counter that will give you this data-- please correct me if I'm wrong.
So the only way I can figure to get this is combining WinHostMon data with Perfmon data.
WinHostMon data looks like this:
Type=Service
Name="MSSQL$SYSTEMS2"
DisplayName="SQL Server (SYSTEMS2)"
Description="Provides storage, processing and controlled access of data, and rapid transaction processing."
Path=""C:\Program Files\Microsoft SQL Server\MSSQL11.SYSTEMS2\MSSQL\Binn\sqlservr.exe" -sSYSTEMS2"
ServiceType="Own Process"
StartMode="Manual"
Started=true
State="Running"
Status="OK"
ProcessId=7880
and the PerfMon data looks like this (in 2 separate, unrelated events)
Event1:
12/20/2016 16:50:43.866 -0500
collection=sqlserverhost:process
object=Process
counter="ID Process"
instance=sqlservr
Value=7880
Event2:
12/20/2016 16:50:43.866 -0500
collection=sqlserverhost:process
object=Process
counter="% Processor Time"
instance=sqlservr
Value=3.1103384864426066869
Any idea how to combine all three of these events into 1 event and get a result that looks like:
DisplayName,ProcessID,%CPU
eg:
"SQL Server (SYSTEMS2)", 7880, 3.11
