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 (hahahaah..so far from this)

laerte

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

image

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

image

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

image

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

image

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*" 

image

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 :

image

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 🙂

image

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 :

image

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

NEW UPDATE VERSION IN HERE

POWERSHELL ROCKS!!!

“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
Bulletproof
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
Bulletproff
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:

WordPress.com Logo

You are commenting using your WordPress.com 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