PowerShell: Get .NET PS and OS versions on all computers

Leave a reply

Run this from an account which has access to all servers in the list.


# Get .NET Version and PowerShell Versions for all computers in AllComputersList.
#
$remoteCommand = @"
Get-ChildItem 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP' -recurse |
	Get-ItemProperty -name Version,Release -EA 0 |
		Where { `$_.PSChildName -match '^(?!S)\p{L}'} | sort Version -Descending |
			Select-Object -first 1 -ExpandProperty Version
"@

$VersionReport = @()
$AllComputersList |%{
	$CurrentServer = $_
    $dotNetVersion = $(Invoke-Command  -Computername $CurrentServer -Scriptblock $ScriptBlock -ErrorAction SilentlyContinue)
    $PsVersion = $(Invoke-Command  -Computername $CurrentServer -Scriptblock {$PSVersionTable.psversion} -ErrorAction SilentlyContinue)
    $os = Get-WmiObject -class Win32_OperatingSystem -computername $CurrentServer
    $Versions = [pscustomobject]@{
        Computer = $CurrentServer
        NETVersion = $dotNetVersion
        PSVersion = $PsVersion
        OSVersion = $os.Version + "`t" + $os.Caption 
    }
    $VersionReport += $Versions

	Write-Host   "$(Get-Date -Format g)	[$CurrentServer] `t.NET Version $dotNetVersion `tPSVersion $PsVersion `t$($os.Version)"
}

$VersionReport | sort PSVersion,Computer |Out-GridView

Powershell: Get all installed applications

Leave a reply

Produce a nice output of our best guess at all the installed applications on a server or PC.

$ComputerName = 'SQLPROD'

 

# Get Software list for a 64-bit computer SOFTWARE
$remoteCommand = @"
Get-ItemProperty HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\* 
"@

$scriptBlock = [Scriptblock]::Create($remoteCommand)
# Test 1
$Software1 = Invoke-Command  -Computername $ComputerName -Scriptblock $ScriptBlock

# Get Software list for a computer Wow6432Node
$remoteCommand = @"
Get-ItemProperty HKLM:\Software\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall\* 
"@

$scriptBlock = [Scriptblock]::Create($remoteCommand)
# Test 2
$Software2 = Invoke-Command  -Computername $ComputerName -Scriptblock $ScriptBlock
 

$ComputerSoftwareList = [Pscustomobject]@()
if ($Software1) {
    $ComputerSoftwareList += $Software1 | Sort-Object DisplayName
    $ComputerSoftwareList.Count
}
if ($Software2) {
    $ComputerSoftwareList += $Software2 | Sort-Object DisplayName
    $ComputerSoftwareList.Count 
}
$ComputerSoftwareList.Count 

$OutGrid = $ComputerSoftwareList | 
    Sort-Object DisplayName |
        Select-Object DisplayName, DisplayVersion, Publisher, InstallDate -Unique  
        
$Global:seq = 1
$OutGrid | 
    Select-Object @{n=“Seq”; e={$Global:seq; $Global:seq++;}}, 
        @{n="Computer";e={"$ComputerName"}}, DisplayName, DisplayVersion, Publisher, InstallDate |
            Out-GridView

REF: Compression and its Effects on Performance

Leave a reply

I want to remember this study by Erin Stellato.

The summary:

If you’re in an OLTP system, you don’t want to return that many rows, so the tests here should give you an idea of how compression may affect performance. If you’re in a data warehouse, then you will probably see higher duration along with the higher CPU when returning large data sets. While the COMPRESS function provides significant space savings compared to page and row compression, the performance hit in terms of CPU, and the inability to index the compressed columns due to their data type, make it viable only for large volumes of data that will not be searched.

REF: Page allocation changes in Sql Server 2016

Leave a reply

While searching for information about MIXED_PAGE_ALLOCATION in SQL 2016+ and TF1118 I came across great examples about Page allocation changes in Sql Server 2016 here

Summary: For user databases in the versions of Sql Server older than 2016, the first 8 data pages were allocated from the mixed extent and next pages from uniform extent. In Sql Server 2016 even the first eight data pages were allocated from the uniform extent. Below image summaries this page allocation mechanism comparison between Sql Server 2016 and older versions of Sql Server

PowerShell: What jobs are currently executing, running now?

Leave a reply 
# ------------------------------------------------------------------------------------------------------------------------
# What jobs are currently executing, running now? RUNNING JOBS


$ExclusionList = @('SQLEXT1')#   @() # # @() #  @() # 
#
$RunningJobs = Get-SQLExecutingJobs -AllServersList $AllServersList -ExclusionList $ExclusionList -ErrorAction Continue # -Verbose -WarningAction SilentlyContinue

# Summarize
$RunningJobs |Sort-Object ServerName |
    Select-Object RunDateTime, @{n="Duration   ";e={ ("{0:dd\:hh\:mm\:ss}" -f (NEW-TIMESPAN –Start $_.RunDateTime –End (GET-DATE))) }},
        ServerName, JobName,current_execution_step,last_run_date,last_run_time |
            Format-Table -autosize

SQL: Count rows for each Range of rows

Leave a reply 
/*

For a table with INT column count rows for each Range of rows. 

Set to 10M rows...
*/
SELECT
    MIN([KeyTable].[Key]) AS [MinKey],
    MAX([KeyTable].[Key]) AS [MaxKey],
    [KeyTable].[Range] * 10000000 AS [Range10M],
    COUNT(*) AS [RangeRows]
FROM (
    SELECT
        [Key],
        (([Key] - 1) / 10000000) + 1 AS [Range]
    FROM [dbo].[Detail]
) AS [KeyTable]
GROUP BY [KeyTable].[Range]
ORDER BY [KeyTable].[Range]

-- Test one.
SELECT COUNT(*)
FROM [dbo].[Detail]
WHERE [Key]
BETWEEN 150000001 AND 160000000
GO

PowerShell: Get the first files in each folder for each day

Leave a reply 
$SetFileNames = @()
$TESTFiles | 
    Sort-Object Directory, LastWriteTime, Name | 
        Group-Object -Property {Get-Date $_.LastWriteTime -Format d } |
            ForEach-Object {
                $_.Group |
                    ForEach-Object -Begin {
                        $counter = 0
                        $FirstFile = $_ 
                        # Grab the files for each folder which are the first file of each day.
                        $SetFileNames += $FirstFile.Group[0]
                    } -Process {
                        $counter++;
                        # Do work here
                    }
            }
$SetFileNames