“DBA with PowerShell” series is to focus on gathering information about SQL Server’s infrastructure. It’s little complex to fetch such detailed information using SQL Server; whereas Windows PowerShell gives us various options and saving the PowerShell output into a database becomes more useful information for analysis perspective. I am here using PowerShell 5.0 but given script will run in lower versions as well. Windows PowerShell is a very powerful, lightweight and free scripting tool based on .Net framework. You can download it from here.

Performance monitoring is a very crucial part of every DBA. In Performance monitoring “CPU utilization”, “memory utilization” and logical/physical disk usage are very important aspects to monitor. Built-in tools like “activity monitor”, “perfmon” and sometimes third party tool are very useful but with cost; either in term of money or machine resources.

We all know that perfmon and other default tools are resources intensive; that’s why these tools are not often used in prod environment, except very specific troubleshooting requirements.

I was keep thing, if we can fetch these info form system directly and save it to database for further analysis; and then I got a chance to attend Mr. Mukesh Shende‘s PowerShell training sessions in my org.. He shared some fundamentals & advance features of PowerShell. That inspires me to write PS scripts to fetch such infrastructure’s information and save the PowerShell output into a database table. I will share all of them in my coming posts.

Perfmon data with PS Script:

The script is to fetch performance counter’s information including “CPU utilization”, “memory usage”, Physical & logical disk read/write info etc. and saves into a SQL Server database table. You need to schedule this script to collect data in required frequency.

Step 01: Creating a table to hold the information

CREATE TABLE [dbo].[SystemInfo] (
[ID] [int] IDENTITY(1,1) PRIMARY KEY,
[DateTime] DATETIME NULL,
[Path] [varchar](100) NULL,
[Value] DECIMAL(18,2) NULL
)

Step 02: Creating event log source in PS.

[System.Diagnostics.EventLog]::CreateEventSource("PSPerformanceCounter", "Application")

Step 03: The PS Script

You need to schedule it in SQL agent job under PowerShell type OR can execute manually for run-time information from PS-ISE.

$SQLInstanceName = ""
$DatabaseName = ""

$Couters = '\Processor(*)\% Processor Time','\Memory\Page Reads/sec','\Memory\Available MBytes','\LogicalDisk(*)\Disk Reads/sec','\LogicalDisk(*)\Disk Writes/sec','\PhysicalDisk(*)\Disk Reads/sec','\PhysicalDisk(*)\Disk Writes/sec'

try
{

$conn = New-Object System.Data.SqlClient.SqlConnection

$conn.ConnectionString = "Data Source=$SQLInstanceName;
Initial Catalog=$DatabaseName;
Integrated Security=SSPI;"

$conn.open()

$cmd = New-Object System.Data.SqlClient.SqlCommand

$cmd.connection = $conn

foreach ($ctval in $Couters)
{
$Prc = Get-Counter -Counter $ctval

$Val = $Prc.CounterSamples | SELECT Path,@{Name = "Timestamp"; Expression= {$_.timestamp}}, CookedValue

foreach ($Info in $Val)
{
$cmd.commandtext = "INSERT INTO SystemInfo (DateTime,Path,Value)
VALUES('{0}','{1}','{2}')" -f $Info.Timestamp.ToString("yyyy-MM-dd HH:mm:ss"),$Info.Path,$Info.CookedValue

$cmd.ExecuteNonQuery()
}
}

$conn.close()
}

catch
{

Write-EventLog -LogName Application -EntryType Error -source 'PSPerformanceCounter' -Message $_.Exception.Message -EventId 1234
$conn.close()
}

Step 04: Enjoy 🙂

Update 17-Aug-2018: Error message has changed from “Something went wrong” to “$_.Exception.Message”. Correct message will be logged into event log under source name “PSPerformanceCounter”