Milestone – 10k views

Today this blog passed 10k views, most of them the last year. This is above my expectations when I started to blog at least a few post per month. My goal for this blog was to write notes to myself to learn new stuff and to summerise things I do at work that might be of common interest.

a_brief_history_of_smileys_2

Perfmon – put data automatically into database

People have seen I have been working with perfmon lately, collecting data on performance counters on my local machine and servers to get a grip of it all. Until now I have been collecting data into *.BLG files and manually run the a relog command to put these perfmon counter values into my database. I have been working on some routine to make this go automatically using tasks and powershell. The first thing to do, is to tell perfmon what task that shall be run when the data collector set stops, either automatically when the schedule stops it, or when a user is manually stopping it. The screenshot below shows that my “SQLServer” data collector set shall run the task “UpdateBaseline” taking the predefined value “{logs}” as input argument. This is the current file that is generated  since the data collector set is started.

Runtask1

The standard task “UpdateBaseline” will contain an action that looks like the following screenshoot. The program should be a command file that fires with an argument (prameter {logs} from the data collector set. And you can also set the “Start in” directory or “Working folder” that people are calling it.

Runtask3

The Collect.cmd will in my case look like the following command line that will fire  powershell with the script file Collect.ps1 and send in the values from the {logs} parameter from perfmon.

      start powershell 
              -noprofile 
              -NonInteractive 
              -ExecutionPolicy ByPass 
              -Command "& {.\collect.ps1 %1}"

The Collect.ps1 is looking like this:

param([String]$path="")

$startDate = Get-Date;
$basename = gi $path | select BaseName 

<# ----------- Defining logfiles ---------------------- #>
$resLogFile = "C:\temp\relogFiltered\relogresult.log";
$CollectLogFile = "C:\temp\relogFiltered\collect.log";
$targetDir = "C:\temp\relogFiltered\Completed\";

<# ----------- Running relog utility ------------------ #>
$params = " " + $path + " -f SQL -o SQL:<ODBC-Name>!<Server-Name>";

$ps = new-object System.Diagnostics.Process
$ps.StartInfo.Arguments = $params
$ps.StartInfo.Filename = "C:\Windows\System32\relog.exe"
$ps.StartInfo.RedirectStandardOutput = $True
$ps.StartInfo.UseShellExecute = $false
$ps.start()
$ps.WaitForExit()
[string] $Out = $ps.StandardOutput.ReadToEnd();

<# ------------ logging output from relog ------------- #>
Add-Content $resLogFile $Out ;

<# logging summary for relog #>
$endDate = Get-Date;
$elapsedTime = New-TimeSpan $startDate $endDate;
$res = $endDate.ToString() + ": Collect.ps1 - File: " + 
       $basename[0].BaseName + ", Time = " + 
       $elapsedTime.TotalSeconds + " second(s)";

Add-Content $CollectLogFile $res ;

<# moving file to completed directory #>

Copy-Item $path $targetDir;

Start & stop perfmon collector set from powershell

I have been working quite much with perfmon lately, and have just started to explore Powershell the last few weeks. I just loving this language since this is pretty much similar to old school unix programming as we used at the University back in the early 90’s. Powershell offers a rich set of commands and libraries for accessing Windows server components.

In my case, I needed some commands to start and stop data collector sets in “Performance Monitor”. This code will use COM object Pla.DataCollectorSet to start and stop the collector set.

The first code piece will start the perfmon data collector set “SQLServer”.

$datacollectorset = New-Object -COM Pla.DataCollectorSet;
$datacollectorset.Query("SQLServer", $null);
$datacollectorset.Start($false);

…while the second code piece will stop the same data collector set.

$datacollectorset = New-Object -COM Pla.DataCollectorSet;
$datacollectorset.Query("SQLServer", $null);
$datacollectorset.Stop($false);

 

Perfmon – autostart of collector set at reboot

I found the solution for how to configure automatic startup perfmon collector set on server reboot. This is not possible by using only “Performance Monitor” (perfmon.exe). The counter collector sets are available in the “Task Scheduler” (taskschd.msc) on tree node path “Task Scheduler Library\Microsoft\Windows\PLA“. The screenshot below shows the main screen for where my “AlwaysOn” data collector set is defined in Task Scheduler. The screenshot shows that there exist multiple triggers. This option is not available in Perfmon. In addition, you might need to enable the Show Hidden Tasks under View on the menu.

perfmon_in_taskscheduler

The “Trigger” tab contains all the actions that should start the performance counter collector set. By default, the only trigger available is the schedule created in the perfmon. Right-click the task in the screen above, select the Properties, go to the “triggers” tab and click “New” button to create a new trigger. Select the “At Startup” in the Begin task conbobox, and press OK.

taskscheduler_triggers

Another useful property is the “Run task as soon as possible after scheduled start is missed” on the Settings tab. Now your performance counter collector set is started automatically when the server reboots. You might also want to select the “Run whether user is logged on or not”.

Happy perfmon-ing….