REF: Page allocation changes in Sql Server 2016

\ by

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

Posted in: Configuration, Documentation

PowerShell: What jobs are currently executing, running now?

\ by 
# ------------------------------------------------------------------------------------------------------------------------
# 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
Posted in: PowerShell, Reports

SQL: Count rows for each Range of rows

\ by 
/*

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
Posted in: Coding, SQL

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

\ by 
$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
Posted in: DOS, PowerShell

PowerShell: Get list of registered SQL Servers

\ by

No reason to maintain more than one list of SQL Servers when you can query SSMS’s Registered Servers.


Import-Module SQLPS -DisableNameChecking 
$SQLInstanceProd = @()
            
$ServerGroup = 'PRODUCTION'
$SQLPath = "SQLSERVER:\sqlregistration\Database Engine Server Group\PROD*\" 
        
$SQLInstanceProd += @(get-childitem $SQLPath -Recurse).Name | ?{$_ -notmatch '\d\d\d\d'} | % {
    $MyPSObject = New-Object PSObject -Property @{ 
            SQLInstance  = $_ 
            ServerGroup  = $ServerGroup;
        }
    $MyPSObject
    }

$SQLInstanceProd

Putting that logic into Get-RegisteredServers I populate the following list in my Profile.

$AllServersList = (Get-RegisteredServers).SQLInstance
$AllComputersList = $AllServersList | %{  $_.split('\')[0] }
$SQLInstanceDev = $((Get-RegisteredServers) |Where-Object {$_.ServerGroup -match 'DEV'}).SQLInstance
$SQLInstanceTest = $((Get-RegisteredServers) |Where-Object {$_.ServerGroup -match 'TEST'}).SQLInstance
$SQLInstanceProd = $((Get-RegisteredServers) |Where-Object {$_.ServerGroup -match 'PROD'}).SQLInstance
Posted in: Configuration, PowerShell, SQL Server

PowerShell: Get Windows Update scheduled date

\ by

Look for WU install dates for all servers.

$DayOfWeek = @{n='DayOfWeek';e={ ($_.LastRunTime).DayOfWeek }}
$Computer = @{n='Computer';e={$ComputerName}}
$TimeFromNow = @{n='HoursFromNow';e={"{0}" -f (New-TimeSpan -Start (Get-date) -End $_.NextRunTime) } }


$SQLInstanceDev + $SQLInstanceTEST + $SQLInstancePROD  | %{
    $ComputerName = $_.Split('\')[0]
    Write-Host $ComputerName
    # Actually get STATE for Windows Updates for each computer

    .\adhoc\Get-ScheduledTask.ps1 -ComputerName $ComputerName  |
        Where-Object {$_.Name -in 'ProgramDataUpdater','AUScheduledInstall' } |
            Select-Object $Computer, $TimeFromNow, Name, State, Enabled, LastRunTime, NextRunTime, ComputerName, $DayOfWeek

} | Out-GridView
Posted in: Coding, PowerShell, Windows