Getting performance metrics

We can also programmatically create Data Collector Sets in Performance Monitor and choose to start, run, and/or schedule them in PowerShell. We can list all the counter sets by running the following script, which uses the SMO server object:

#current server name
$servername = "ROGUE"    # or localhost

Get-Counter -ComputerName $servername -ListSet * | 
Sort-Object CounterSetName | 
Select-Object CounterSetName |
Format-Table

This will be a long list of counter sets. But some of the typical ones we usually look at are as follows:

• Memory

• Network Interface

• LogicalDisk

• PhysicalDisk

• Processor

When you go through the list, note that you will also find instance-specific counters. Here are some of the instance-specific counters I have in my system for my named instance SQL2014:

• MSSQL$SQL2014:Access Methods

• MSSQL$SQL2014:Availability Replica

• MSSQL$SQL2014:Backup Device

• MSSQL$SQL2014:Batch Resp Statistics

• MSSQL$SQL2014:Broker Activation

• MSSQL$SQL2014:Broker Statistics

• MSSQL$SQL2014...