PowerShell for SQL Server Essentials

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 ...