sqlmashup
#powershell deepest folder top 1 alphabetical
Get-ChildItem -path “C:\getdeepestfolder\top1alphabetical" -recurse | Sort-Object -property @{Expression={[regex]::matches($_.FullName, “\”).count}; Ascending=$false} | Select-Object -property “FullName” -first 1
PowerShell functions for Grading Operating System of SQL Server

##beta release of functions which are based on best practices used to grade operating system configurations for SQL Server 2008 R2 Enterprise Edition on Windows Server 2008 R2 Enterprise Edition

function Get-GradePowerPlan {
 $Reference = “http://support.microsoft.com/kb/2207548”;
 $Name = “GradePowerPlan”;
 $Worse = [int]0;
 $Better = [int]1;
 $Value = [int](([array](Get-WmiObject -Class “Win32_PowerPlan” -Namespace “root\cimv2\power” | Where-Object {$_.IsActive -eq $true -and $_.ElementName -eq “High performance”})).Count);
 $Grade = if ($Value -ge 1) {100.0} elseif ($Value -eq 0) {70.0} else {$null};
 $Fixit = if ($Grade -eq 100.0) {”} elseif ($Grade -eq $null) {‘throw (“GradePowerPlan experienced an unexpected error”)’} else {“POWERCFG.EXE -setactive $([string]((POWERCFG.EXE -list | Where-Object{$_ -like ‘*(High performance)’}).Replace(‘(High performance)’, ”).Replace(‘Power Scheme GUID:’, ”).Trim())); Get-GradePowerPlan;”};

 @{Reference=$Reference;Name=$Name;Worse=$Worse;Better=$Better;Value=$Value;Grade=$Grade;Fixit=$Fixit};
}

function Get-GradeLicenseStatus {
 $Reference = “http://blogs.technet.com/b/jamesone/archive/2009/07/22/how-to-activate-windows-from-a-script-even-remotely.aspx”;
 $Name = “GradeLicenseStatus”;
 $Worse = [int]6;
 $Better = [int]1;
 $Value = ((Get-WmiObject -Class “SoftwareLicensingProduct” | Where-Object {$_.LicenseIsAddon -eq $False -and $_.PartialProductKey -gt “”}).LicenseStatus);
 $Grade = if ($Value -eq 1) {100.0} elseif ($Value -eq 0 -or $Value -gt 1) {50.0} else {$null};
 $Fixit = if ($Grade -eq 100.0) {”} elseif ($Grade -eq $null) {‘throw (“GradeLicenseStatus experienced an unexpected error”)’} else {‘$ProductKey = “XXXXX-XXXXX-XXXXX-XXXXX-XXXXX”; $SoftwareLicensingService = Get-WmiObject -Class “SoftwareLicensingService”; $SoftwareLicensingService.InstallProductKey($ProductKey) | Out-Null; $SoftwareLicensingService.RefreshLicenseStatus() | Out-Null; Get-GradeLicenseStatus;’};

 @{Reference=$Reference;Name=$Name;Worse=$Worse;Better=$Better;Value=$Value;Grade=$Grade;Fixit=$Fixit};
}

function Get-GradeDeviceStatus {
 $Reference = “http://msdn.microsoft.com/en-us/library/aa387884.aspx”;
 $Name = “GradeDeviceStatus”;
 $Worse = [int](([array](Get-WmiObject -Class “CIM_LogicalDevice”)).Count);
 $Better = [int]0;
 $Value = [int](([array](Get-WmiObject -Class “CIM_LogicalDevice” | Where-Object {$_.Status -eq “Error”})).Count);
 $Grade = if ($Value -ge 1) {60.0} elseif ($Value -eq 0) {100.0} else {$null};
 $Fixit = if ($Grade -eq 100.0) {”} elseif ($Grade -eq $null) {‘throw (“GradeDeviceStatus experienced an unexpected error”)’} else {‘throw (“GradeDeviceStatus fix action cannot be scripted”)’};

 @{Reference=$Reference;Name=$Name;Worse=$Worse;Better=$Better;Value=$Value;Grade=$Grade;Fixit=$Fixit};
}

function Get-GradeHyperThreading {
 $Reference = “http://msdn.microsoft.com/en-us/library/cc615012.aspx”;
 $Name = “GradeHyperThreading”;
 $Worse = [int]1;
 $Better = [int]0;
 $Value = ((Get-WmiObject -Class “Win32_Processor” | Select-Object @{Name=”HyperThreadingProcessors”;Expression={($_.NumberOfLogicalProcessors - $_.NumberOfCores)}}).HyperThreadingProcessors);
 $Grade = if ($Value -ge 1) {70.0} elseif ($Value -eq 0) {100.0} else {$null};
 $Fixit = if ($Grade -eq 100.0) {”} elseif ($Grade -eq $null) {‘throw (“GradeHyperThreading experienced an unexpected error”)’} else {‘throw (“GradeHyperThreading fix action cannot be scripted”)’};

 @{Reference=$Reference;Name=$Name;Worse=$Worse;Better=$Better;Value=$Value;Grade=$Grade;Fixit=$Fixit};
}

function Get-GradeScreenSaver {
 $Reference = “http://support.microsoft.com/kb/121282”;
 $Name = “GradeScreenSaver”;
 $Worse = [int]1;
 $Better = [int]0;
 $Value = [int](([array](Get-WmiObject -Class “Win32_Desktop” | Where-Object {$_.ScreenSaverActive -eq $true})).Count);
 $Grade = if ($Value -ge 1) {60.0} elseif ($Value -eq 0) {100.0} else {$null};
 $Fixit = if ($Grade -eq 100.0) {”} elseif ($Grade -eq $null) {‘throw (“GradeScreenSaver experienced an unexpected error”)’} else {‘Get-ChildItem -Path “Registry::HKEY_USERS” | ForEach-Object {Set-ItemProperty -path “$($_.PSPath)\Control Panel\Desktop” -Name “SCRNSAVE.EXE” -Value “” -ErrorAction “SilentlyContinue”}; Get-GradeScreenSaver;’};

 @{Reference=$Reference;Name=$Name;Worse=$Worse;Better=$Better;Value=$Value;Grade=$Grade;Fixit=$Fixit};
}

function Get-GradeApplicationBoost {
 $Reference = “http://msdn.microsoft.com/en-us/library/ms176012.aspx”;
 $Name = “GradeApplicationBoost”;
 $Worse = [int]2;
 $Better = [int]0;
 $Value = ((Get-WmiObject -Class “Win32_OperatingSystem”).ForegroundApplicationBoost);
 $Grade = if ($Value -ge 2) {[int]50.0} elseif ($Value -eq 0) {[int]100.0} else {$null};
 $Fixit = if ($Grade -eq 100.0) {[string]”} elseif ($Grade -eq $null) {[string]’throw (“GradeApplicationBoost experienced an unexpected error”)’} else {[string]”Set-ItemProperty -Path ‘HKLM:\SYSTEM\CurrentControlSet\Control\PriorityControl’ -Name ‘Win32PrioritySeparation’ -Value $Better; Get-GradeApplicationBoost;”};

 @{Reference=$Reference;Name=$Name;Worse=$Worse;Better=$Better;Value=$Value;Grade=$Grade;Fixit=$Fixit};
}

function Get-GradePageFile {
 $Reference = “http://msdn.microsoft.com/en-us/library/ms187877.aspx”;
 $Name = “GradePageFile”;
 $Worse = [int]0;
 $Better = $null;
 $Value = $null;
 $Grade = $null;
 $Fixit = [string]’throw (“GradePageFile experienced an unexpected error”)’;

 If (([int](([array](Get-WmiObject -Class “Win32_PageFileSetting” | Where-Object {$_.InitialSize -gt 0})).Count)) -eq 1)
 {
  $PoolNonpagedBytesMB = [int]((Get-WmiObject -Class “Win32_PerfFormattedData_PerfOS_Memory”).PoolNonpagedBytes / 1MB);
  $PoolPagedBytesMB = [int]((Get-WmiObject -Class “Win32_PerfFormattedData_PerfOS_Memory”).PoolPagedBytes / 1MB);
  $KernelMemoryPoolSizeMB = [int]($PoolNonpagedBytesMB + $PoolPagedBytesMB);

  $TotalVisibleMemorySizeMB = [int]((Get-WmiObject -Class “Win32_OperatingSystem”).TotalVisibleMemorySize * 1KB / 1MB);
  $Better=[int](($TotalVisibleMemorySizeMB * 1.5) + 0.5);

  $Value = [int]((Get-WmiObject “Win32_PageFile”).MaximumSize);

  $Grade = if ($Value -ge $Better) {[double]100.0} elseif ($Value -ge $TotalVisibleMemorySizeMB) {[double]80.0} elseif ($Value -ge $KernelMemoryPoolSizeMB) {[double]60.0} elseif ($Value -ge 0) {[double]50.0} else {$null};
  $Fixit = if ($Grade -eq 100.0) {[string]”} elseif ($Grade -eq $null) {‘throw (“GradePageFile experienced an unexpected error”)’} else {[string]”`$PageFile = Get-WmiObject ‘Win32_PageFile’;`$PageFile.MaximumSize=’$Better’;`$PageFile.Put() | Out-Null; Get-GradePageFile;”};
 }

 @{Reference=$Reference;Name=$Name;Worse=$Worse;Better=$Better;Value=$Value;Grade=$Grade;Fixit=$Fixit};
}


Get-GradePowerPlan; #If ((Get-GradePowerPlan).Grade -lt 100.0) {Invoke-Expression -Command (Get-GradePowerPlan).FixIt};
Get-GradeLicenseStatus; #If ((Get-GradeLicenseStatus).Grade -lt 100.0) {Invoke-Expression -Command ((Get-GradeLicenseStatus).FixIt.Replace(“XXXXX-XXXXX-XXXXX-XXXXX-XXXXX”, “”))};
Get-GradeDeviceStatus; #If ((Get-GradeDeviceStatus).Grade -lt 100.0) {Invoke-Expression -Command (Get-GradeDeviceStatus).FixIt};
Get-GradeHyperThreading; #If ((Get-GradeHyperThreading).Grade -lt 100.0) {Invoke-Expression -Command (Get-GradeHyperThreading).FixIt};
Get-GradeScreenSaver; #If ((Get-GradeScreenSaver).Grade -lt 100.0) {Invoke-Expression -Command (Get-GradeScreenSaver).FixIt};
Get-GradeApplicationBoost; #If ((Get-GradeApplicationBoost).Grade -lt 100.0) {Invoke-Expression -Command (Get-GradeApplicationBoost).FixIt};
Get-GradePageFile; #If ((Get-GradePageFile).Grade -lt 100.0) {Invoke-Expression -Command (Get-GradePageFile).FixIt};

[humor] Get-SqlServerPerformanceMagicEightBallSolution (CC @anonythemouse)

function Get-SqlServerPerformanceMagicEightBallSolution

{

$UniversalSolutions = @()

$UniversalSolutions += "Reboot the server"

$UniversalSolutions += "Scale-up your hardware"

$UniversalSolutions += "Denormalize for performance"

$UniversalSolutions += "Must be a memory leak"

$UniversalSolutions += "Maybe it needs a second reboot"

$UniversalSolutions += "Hire SQLSkills"

$UniversalSolutions += "Just keep adding indexes"

$UniversalSolutions += "Tweet how do i speed up sql? #sqlhelp"

$UniversalSolutions += "Open a Microsoft Support incident"

$UniversalSolutions += "Maybe it needs a third reboot"

$UniversalSolutions | Get-Random

}

 

Get-SqlServerPerformanceMagicEightBallSolution