info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Replication, Cloud & Migrations



Powershell - Log CPU - per Process - to a Table & Output File
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2019-04-16








Powershell:


CLS

$Obj = (Get-Counter -ErrorAction 'SilentlyContinue' "\Process(*)\% Processor Time").CounterSamples | Select InstanceName, @{Name="CPU %";Expression={[Decimal]::Round(($_.CookedValue / $CpuCores), 2)}} | sort *CPU* -Descending | select -First 10

$Params = @{'server'='sql01';'Database'='DB01' ;'UserName'='user01';'Password'=(setsecurepwd)

ForEach ($O in $Obj)
{

$ProcessName = $O.InstanceName
$ProcessValue = $O.'CPU %'
$Date = Get-Date

$O.InstanceName + ' | ' + $O.'CPU %' + ' | ' + $Date | Out-File C:\Log\CPUPerProcess.txt -Append

$InsertResults = @"
INSERT INTO [DB01].[dbo].[LOG_CPU](ProcessName,CPU,InsertedDate)
VALUES ('$ProcessName','$ProcessValue', '$Date')
"@

Invoke-sqlcmd @Params -Query $InsertResults

}

Invoke-sqlcmd @Params -Query "Delete from dbo.LOG_CPU where inserteddate < getdate() - 30"