Performance Counters Gathering using Powershell



Sorry for the English 🙂

I was reading the excellent article by Master Allen White (Twitter|Blog) on the Performance Data Gathering with Powershell – Performance Data Gathering and that’s when a an Powershell apple fell on my head ( far from this)


Why not make a function that facilitates this?

As a DBA, one of my almost daily tasks is to capture and analyze Perfmom counters.
You may think with me, but there is the cmdlet get-counter in Powershell 2.0.
Yes and it is very helpful.
But I need more counters and the result is in line with all values separate by comma (usually used as to facilitate insertion into sql server) and  get-counter gets a little trickier to use.
I tried to think in a easy way todo this process to choose witch counter you want, to save this configuration to use later and inserting the output data into a SQL Server table for further analysis.

I belive I got some cool thing. Not forgetting some people who helped me solve some issues, as always MVP Shay Levy (Twitter|Blog), MVP JVierra and Trevor Sullivan (Twitter|Blog)
I also want to say it’s an first version and can and probably will have some issues and you can contact me anytime
I hope the big boss Chad Miller (Twitter|Blog) like it and can be embedded in SQLPSX in next update.

This module consists of five functions:

Get-PerfCounterCategory Returns information about the categories
$ComputerName = String Computer name. Default is local computer
$CategoryName = String Category Name. Default is all categories


Get-PerfCounterInstance Returns information about the instances. You can have Multiple Instances and Single Instance in a Counter
$CategoryName =  Object with Categories choosed
$InstanceName = String Instance name. Default is all


Get-PerfCounterCounters Returns information about the counters.
$ObjectPerf = Object with Categories and Instances Choosed
$CounterName = String Counter Name. Default is all


Save-ConfigPerfCounter This function Saves all configurations did (categories, Instances and Counters) in a Proprietary XML. This will be used to Start the gatheing .
$ObjectPerf = Object with all configurations ((categories, Instances and Counters)
$PathConfigFile = Full Path to ConfigFile. Example C:\temp\Config.XML
$NewFile = Switch Parameter to create a New File (XML)  or add new counters to the config file (XML)


Set-CollectPerfCounter Starts The Gathering
$PathConfigFile = Full Path where is the ConfigFile. Example C:\temp\Config.XML
$PathOutputFile = Full Path to OUTPUT the data. Examples c:\temp\output.TXT
$DateTimeStart = Date time to Start the gathering
$DateTimeEnd = Date time to end the gathering
$Interval = Interval of gathering in seconds default is 10 s


And now how to use :

Gathering Information

1 – To get all categories


   1: #All Categories and description
   2: Get-PerfCounterCategory 

2 – All categories sort by Machine_name,  Category_name and Category_Description

   1: Get-PerfCounterCategory | Sort-Object Machine_Name,Category_Name,Category_Description,Category_Type | Select-Object Machine_Name,Category_Name,Category_Description,Category_Type


3 – All Categories and Instances sorted

   1: Get-PerfCounterCategory | Get-PerfCounterInstance | Sort-Object Machine_Name,Category_name,Instance_name | select Machine_Name,Category_name,Instance_name


4 – All Categories, All Instances and All counters

   1: Get-PerfCounterCategory | Get-PerfCounterInstance | Get-PerfCounterCounters | Select-Object  Machine_Name,Category_Name,Instance_Name,Counter_Name | select Machine_Name,Category_Name,Instance_Name,Counter_Name


5 – All categories from SQLServer, all instances and All counters

   1: Get-PerfCounterCategory -CategoryName "SQLServer*" | Get-PerfCounterInstance | Get-PerfCounterCounters | Select-Object  Machine_Name,Category_Name,Instance_Name,Counter_Name | select Machine_Name,Category_Name,Instance_Name,Counter_Name


6 – All CAtegories from SQL Server and with countername like  Buffer cache hit ratio*

   1: Get-PerfCounterCategory -CategoryName "SQLServer*"  | Get-PerfCounterInstance |  Get-PerfCounterCounters -CounterName "Buffer cache hit ratio*" 


Setting the Configure File

Well, at this point yiou already know what counters wiil be used so, let´s do the Configure File.

Why whe use this. First, you can choose many categories/counters and add to this file. Second, you can use later.

Lets say you could have a file with Processors counters, Other with SQL Server counters..etc.

Lets create a config file with all counters from SQL server Buffer Mananger ?

   1: Get-PerfCounterCategory -CategoryName "SQLServer:Buffer Manager" | Get-PerfCounterInstance | Get-PerfCounterCounters | Save-ConfigPerfCounter -PathConfigFile "c:\temp\BufferManager.xml" -NewFile

and the file will looks like :


repair I used the parameter-newfile. This indicates that a new file is created, because I can add more counters in the same file if I do not use this parameter.

Lets  say I want to Add “Processor” category and counter" “% Processor Time” from Instance _Total from (just use Get-PerfCounterCategory -CategoryName "Processor" | Get-PerfCounterInstance  and you will see all instances from processor category)

   1: Get-PerfCounterCategory -CategoryName "Processor" | Get-PerfCounterInstance -InstanceName "_Total" | Get-PerfCounterCounters -CounterName "% Processor Time" | Save-ConfigPerfCounter -PathConfigFile "c:\temp\BufferManager.xml" 


And at the end of the file wiil be add this counter 🙂


well, with all OK, lets get the data

Gathering Data

lets get the data starts with "05/23/2010 08:00:00" and end in "05/23/2010 22:00:00" with 10 seconds of interval,  and ouptut the data in c:\temp\BufferManager.txt

   1: Set-CollectPerfCounter -DateTimeStart  "05/23/2010 08:00:00" -DateTimeEnd "05/23/2010 22:00:00" -Interval 10 -PathConfigFile c:\temp\ConfigFile.xml -PathOutputFile c:\temp\OutputCollect.txt

and the output txt will be :


As you can see all the counter are in columns, separete by comma. Incredibly ready for a simple bulk insert 🙂

I am studing how to run the Set-CollectPerfCounter as a Powershell Job by parameter. For now you can use start-job and run asynchronous

Well folks this is it. I’m not an expert in Powershell as I know the SQL Server so the code can be optimized and will probably have issues. Suggestions on improvements and bug report are welcome



“Some say this is a dangerous place
Dangerous women lipstick mace
Men disappear without a trace
Stay anonymous hide your face
In this town you`d need a
Shatterproof heart

When I came here I was innocent
Soon found out what trouble ment
Now I regret the times I`ve spent
In your towerblocks and tenements

In this town you`d need a
Bulletproff heart
In this town you`d need a
Shatterproof heart

Watch your step
Don`t open your door
Watch your step
They want to settle a score
Watch your step
Don`t open your door
Open your door”

Bulletproof Heart

The Silencers

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Algo que Esqueci de Categorizar. Bookmark the permalink.

2 Responses to Performance Counters Gathering using Powershell

  1. Juliano says:

    Opa, muito bom em, vamos utiliza-lo.

  2. Laerte says:

    Grande Juliano, que bom que gostou. Por favor cara, e se possível me passe seu feedback pra eu melhorar ou tirar possíveis bugs 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s