How to dynamically determine the correct Process\Working Set performance counter instance

  • Article

The Process performance counter object uses the following #1, #2 naming scheme whenever multiple instances of a process with the same executable name are running.

  • \Process(sqlservr)\Working Set: this may be a default or named instance of SQL
  • \Process(sqlservr#1)\Working Set: this may be a default or named instance of SQL
  • \Process(sqlservr#2)\Working Set: this may be a default or named instance of SQL

 

This behavior is not specific to SQL Server. For example you would see the following counters if you have two instances of notepad.exe running:

  • \Process(notepad)\Working Set
  • \Process(notepad#1)\Working Set

 

We want to be able to determine which “Process\Working Set” counter corresponds to a specific instance of SQL Server in an environment with  multiple instances of SQL Server running on the same machine.

The PowerShell script below demonstrates how to find the “Process\Working Set” perfmon counter path (#1, #2,...) which corresponds to a particular instance of SQL. For a specific instance of SQL Server, it  will query its service, find its process id, and then find the right “Process\Working Set” counter path, such as "\\server1\\process(sqlservr)\Working Set" or "\\server1\\process(sqlservr#1)\Working Set". #-------------------------------------------------------------------

$computername="server1"

$processExeName="sqlservr"

$sqlInstanceName="sqlinstance2"

$namedIntanceFilter="Name='mssql`$$sqlInstanceName'" #Escape the first dollar sign because the service name has dollar sign in its name

#1. Find the process id for an instance of SQL Server by querying its service. We can also get the process  id via sp_readerrorlog.

#1.a For default instance of SQL:

$ProcessId=Get-WmiObject  -ComputerName $computername  WIN32_service -Filter "Name='mssqlserver'" | Select-Object  -ExpandProperty ProcessId

#1.b For named instance of SQL:

#$ProcessId=Get-WmiObject  -ComputerName $computername  WIN32_service -Filter $namedIntanceFilter | Select-Object  -ExpandProperty ProcessId

if (-not $ProcessId) {

Write-Output "Process ID is null, exiting."

return -1

}

#2. Enumerate all the ‘process\ID Process’ counters for any sqlservr executables and their counter Path (sqlservr, sqlservr#1, sqlservr#2, etc)

#RawValue contains the ProcessId

$counterObj=get-counter -ComputerName $computername -Counter "\Process(*)\ID Process" |select-object CounterSamples -ExpandProperty CounterSamples| where-object{$_.InstanceName -like "$processExeName"} |  Select Path, RawValue

 

#3. Store into $ProcessPerfmonPath the correct path for the process with id = id of SQL service we want

$ProcessPerfmonPath = $counterObj | Where-Object {$_.RawValue -eq $ProcessId }

 

#4.Build the string with the correct name and path of the performance counter we are interested in (Process\Working Set)

$workingSetCounterPath = $ProcessPerfmonPath.Path -replace "id process", "Working Set"

$workingSetCounterPath #this will have correct path for the counter

#Sample output:   \\server1\\process(sqlservr#2)\Working Set

get-counter -ComputerName $computername  -Counter $workingSetCounterPath

 

Fany Carolina Vargas | SQL Dedicated Premier Field Engineer | Microsoft Services