“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 = "<SQL Instance Name>"
$DatabaseName = "<Datbase Name>"

$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 'Something went wrong' -EventId 1234
 $conn.close()
}

Step 04: Enjoy 🙂