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)

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:

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

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