Book Image

PowerShell for SQL Server Essentials

By : Donabel Santos
Book Image

PowerShell for SQL Server Essentials

By: Donabel Santos

Overview of this book

Table of Contents (15 chapters)
PowerShell for SQL Server Essentials
Credits
About the Author
Acknowledgments
About the Reviewers
www.PacktPub.com
Preface
Implementing Reusability with Functions and Modules
Index

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