SQL Server & PowerShell – Get Memory Utilization

Background

Earlier today I found myself trying to better understand a system.

As part of my quick read-up, I found a one line code that shows how to read memory utilization via Powershell.

Code

Overview

Here is that one line code in far too many lines.

Powershell


Set-StrictMode -Version Latest;
Import-Module SQLPS -DisableNameChecking

function displayPath($path)
{
	[object] $objGI = $null;
	[int] $physicalMemoryUsageInKB = 0;
	[int] $physicalMemoryUsageInMB = 0;

	$objGI = (gi $path) 
	
	#Get Data
	$displayName = $objGI.DisplayName;
	$physicalMemoryUsageInKB = $objGI.PhysicalMemoryUsageInKB;
	$physicalMemoryUsageInMB = $objGI.PhysicalMemoryUsageInKB / 1024;
	
	#Display Data       
	$strLog = "`tPath {0}" -f $path;        
	Write-Host $strLog
	
	$strLog = "`tInstance {0}" -f $displayName;     
	Write-Host $strLog
	
	$strLog = "`tPhysical Memory (KB) {0}" -f $physicalMemoryUsageInKB;     
	Write-Host $strLog
	
	$strLog = "`tPhysical Memory (MB) {0}" -f $physicalMemoryUsageInMB;     
	Write-Host $strLog      
	
	Write-Host
        
}

[string] $typeSQLEngine = "SqlServer";
[string] $defaultInstance = "MSSQLServer";
[string] $machineName = $null;
[int]    $iNamedInstanceStartingPos = 6;

# Get machine name
$machineName = $env:computername


#Get a managed computer instance  
$mc = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer  

#List out all sql server instances running on this machine  
foreach ($item in $mc.Services)
{

	if ($item.Type -eq $typeSQLEngine)
	{
		
		if ($item.Name -eq $defaultInstance)
		{
			$instanceName = "DEFAULT";
		}
		else
		{
		
		    $instanceNameLen = ($item.Name).length - $iNamedInstanceStartingPos;
			
			$instanceName = ($item.Name).Substring( `
													  $iNamedInstanceStartingPos `
													, $instanceNameLen `
												  );
			
		}
		
		$path = "SQLSERVER:\SQL\" + $machineName + "\" + $instanceName;
		
		displayPath $path
					
	} # if ($item.Type -eq $typeSQLEngine)
	
} 

Output

 

Source Code Control

GitHub

DanielAdeniji/SQLServerPS

Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s