…brew your own Business Intelligence

Choosing the Right Instance in Perfmon

There’s been quite a bit posted on this blog regarding the collection of perfmon counters to monitor Analysis Services instances and troubleshoot performance problems. And some of the recommended perform counters (e.g. Process: % Processor Time) are “instance”-specific… meaning you need to select the name of the process for which you want to collect counter values.

I’m referring to the “Instances of selected object” box in the perfmon dialogue (shown below). After select the counter(s), you then need to select the “instance” for which you want to trace the selected counter.

image

So if I’m setting up a perfmon trace for an analysis services instance, I going to choose the [Process: % Processor Time] counter and then scroll down in the window below and choose the msmdsrv instance.

Note: msmdsrv.exe is the name of the Analysis Services executable. If we wanted to track process time related to the SQL Server database engine, I’d choose the sqlserver instance from the perfmon list because that’s the name of the SQL Server executable.

However, if there are multiple Analysis Services instances running on the same box, how do I know which one to choose when adding perfmon counters?

image

One option is to just select all of them… which is fine when you’re doing a 1-off trace. However, if you’re setting up a permanent/semi-permanent solution (e.g. rolling your own performance monitoring) or just bit OCD, you can use either of the following methods to determine which instance in the perfmon window corresponds to which Analysis Services instance running on the box.

Step 1. Get the Process ID

Open Perfmon and add the [Process\ID Process] counter for all msmdsrv instances…

image

The counter value for each instance is the corresponding process ID…

image

In the example above, msmdsrv is process ID 1120 and msmdsrv#1 is process ID 1136…

Step 2. Match the Process ID to the Instance Name

The easiest way to do this is via Task Manager…

image

In this example, we see that my tabular instance (SSAS_TAB) is running under process ID 1136 and my multidimensional instance (SSAS_MD) is running under process ID 1120.

So now, when I’m setting up a perfmon trace to collect performance counters, I will use the “msmdsrv” instance in perfmon for my tabular instance and “msmdsrv#1” for my multidimensional instance.

But what about automation?

In many cases you’ll want to automate this type of stuff… in which case you can use the following powershell commands to accomplish this…

# based on answer from Andrew A. @ stackoverflow
# https://stackoverflow.com/questions/11067565/powershell-get-a-specific-process-counter-with-id-process

# provide name of SSAS instance (assumes named instance)
$ssas_instance_name = "SSAS_TAB"

# get process id for specified named instance
$process_id = (get-wmiobject win32_service | where { $_.name -like 'MSOLAP$'+$ssas_instance_name}).ProcessID

# get instance name 
$perfmon_instance=((Get-Counter "\Process(*)\ID Process").CounterSamples | ? {$_.RawValue -eq $process_id}).Path

# get perfmon counter values
(Get-Counter ($perfmon_instance -replace "\\id process$","\% Processor Time")).CounterSamples 



Additional References

Leave a Reply