“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, [CounterGroup] [varchar](100) NULL, )
Step 02: Open PowerShell console as administration and past below to create “event log source”.
[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.
Configure Agent Job:
1. In SSMS open “SQL Server Agent” and create a new job.
2. Assign a “Domain User” which should have “admin rights” in server and “read-write” permission on the above created table.
3. Create job step as Type as “PowerShell” and paste below code in command area.
4. Replace <SQLInstanceName> with your SQL Instance name.
5. Replace <DatabaseName> with the database, where you have created the above table.
5. Schedule it for every 5 min.
$SQLInstanceName = "<SQLInstanceName>"
$DatabaseName = "<DatabaseName>"
$Couters = 'CPU utilization,\Processor(_total)\% Processor Time','CPU utilization,\Process(*sql*)\% Processor Time','CPU utilization,\System\Processor Queue Length','Memory,\Memory\Page Reads/sec','Memory,\Memory\Page Writes/sec','Memory,\Memory\Available MBytes','Memory,\Memory\Pages/sec','Memory,\Memory\Page Faults/sec','Memory,\Paging File(_total)\% Usage','Disk IO,\LogicalDisk(*)\Disk Reads/sec','Disk IO,\LogicalDisk(*)\Disk Writes/sec','Disk IO,\LogicalDisk(*)\Avg. Disk Queue Length','Disk IO,\LogicalDisk(*)\Avg. Disk Read Queue Length','Disk IO,\LogicalDisk(*)\Avg. Disk Write Queue Length','Disk IO,\LogicalDisk(*)\% Free Space','Disk IO,\LogicalDisk(*)\Free Megabytes','Disk IO,\LogicalDisk(*)\Disk Transfers/sec','Physical IO,\PhysicalDisk(*)\Avg. Disk Queue Length','Physical IO,\PhysicalDisk(*)\Disk Reads/sec','Physical IO,\PhysicalDisk(*)\Disk Writes/sec','Physical IO,\PhysicalDisk(*)\Disk Transfers/sec','Network,\SQLServer:Wait Statistics(*)\Network IO waits','Network,\Network Interface(*)\Bytes Received/sec','Network,\Network Interface(*)\Bytes Sent/sec','SQL Server,\SQLServer:General Statistics\User Connections','SQL Server,\SQLServer:SQL Statistics\Batch Requests/sec','SQL Server,\SQLServer:Access Methods\Page Splits/sec','SQL Server,\SQLServer:Buffer Manager\Lazy Writes/sec','SQL Server,\SQLServer:Databases(*)\Transactions/sec','SQL Server,\SQLServer:Databases(*)\Active Transactions','SQL Server,\SQLServer:SQL Statistics\SQL Compilations/sec','SQL Server,\SQLServer:SQL Statistics\SQL Re-Compilations/sec','SQL Server,\SQLServer:Latches\Average Latch Wait Time (ms)','SQL Server,\SQLServer:Databases(*)\Data File(s) Size (KB)','SQL Server,\SQLServer:Databases(*)\Log File(s) Used Size (KB)','Locks,\SQLServer:General Statistics\Processes Blocked','Locks,\SQLServer:Locks(*)\Lock Requests/sec','Locks,\SQLServer:Locks(*)\Number of Deadlocks/sec','Locks,\SQLServer:Locks(*)\Lock Waits/sec','Locks,\SQLServer:Locks(*)\Average Wait Time (ms)','Locks,\SQLServer:Locks(*)\Lock Timeouts (timeout > 0)/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)
{
$Couter = $ctval.Substring($ctval.IndexOf(',') + 1,$ctval.Length - $ctval.IndexOf(',') - 1)
$Prc = Get-Counter -Counter $Couter
$Val = $Prc.CounterSamples | SELECT Path,@{Name = "Timestamp"; Expression= {$_.timestamp}} , @{Name = "CookedValue"; Expression= {IF(($Couter -like '\Process(*') -OR ($Couter -like '\System*')) {$_.CookedValue/$env:number_of_processors} else {$_.CookedValue}}}
foreach ($Info in $Val)
{
$cmd.commandtext = "INSERT INTO SystemInfo (DateTime,Path,Value,CounterGroup)
VALUES('{0}','{1}','{2}','{3}')" -f $Info.Timestamp.ToString("yyyy-MM-dd HH:mm:ss"),$Info.Path,$Info.CookedValue,$ctval.Substring(0,$ctval.IndexOf(','))
$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”
