DeoCmdlet3.ps1

gci | unblock-file
cls

$cn = "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=master;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
#$cn = "Data Source=.\SQLExpress;Initial Catalog=master;Integrated Security=True"

. .\Config.ps1

#Import-Module .\DeoCmdlet3.dll
@(gci *\DeoCmdlet3.dll -Recurse)[0] | Import-Module
#Get-Help Get-PoorPerformingQuery -full
#pause

function Show-Menu
{
    param (
        [string]$Title = 'Deo Cmdlet MENU'
    )
    Clear-Host
    Write-Host "================ $Title ================"
    
    Write-Host "- 'wait-stats' for Wait Statistics."
    Write-Host "- 'wait-tasks' for Wait Wait Tasks."
    Write-Host "- 'wss' for Wait Session Statistics."
    Write-Host "- 'lus' for Last Updated Statistics."
    Write-Host "- 'vfs' for Virtual File Statistics."
    Write-Host "- 'pc' for Performance Counters."
    Write-Host "- 'cachep' for Plan Cache Usage."
    Write-Host "- 'cpucost' for CPU COST Query."
    Write-Host "- 'ppq' for PoorPerformingQuery."
    Write-Host "- 'cns' for Connections."
    Write-Host "- 'mi' for Missing Index."
    Write-Host "- 'ui' for Unused Index."
    Write-Host "- 'sessions' for Current Sessions."
    Write-Host "- 'requests' Running requests."
    Write-Host " "
    Write-Host "Q: Press 'Q' to quit."
}

function Show-List
{
    param (
        $Data
    )

    if ($Data -ne $null -and $Data.Count -gt 0)
    {
        Write-Output $Data | Format-List
    }
    else
    {
        Write-Host "No Data. "  -ForegroundColor yellow
    }
}

function Show-Grid
{
    param (
        $Data,$title
    )

    if ($Data -ne $null -and $Data.Count -gt 0)
    {
        Show-DataGrid -Data:$data -Title:$title
    }
    else
    {
        Write-Host "No Data. "  -ForegroundColor yellow
    }
}

function Show-Table
{
    param (
        $Data
    )

    if ($Data -ne $null -and $Data.Count -gt 0)
    {
        Write-Output $Data | Format-Table
    }
    else
    {
        Write-Host "No Data. "  -ForegroundColor yellow
    }
}

#Get-Module
#Get-Command -Module EVcmdlet
#Remove-Module EVcmdlet


do
 {
     Show-Menu
     $selection = Read-Host "Please make a selection"
     cls
     $data = $null
     $title = $null
     switch ($selection)
     {
         'wait-tasks'{
            $title = "Wait Tasks"
            write-host $title -ForegroundColor green 
            $data = Get-WaitingTasks -ConnectionString:$cn #| Format-Table
            Show-Table $data
            Show-Grid -Data:$data -Title:$title
         }
         'wait-stats' {
            $title = "Wait Statistics"
            write-host $title -ForegroundColor green 
            $data = Get-WaitStatistics -TOP:10 -ConnectionString:$cn #| Format-Table
            Show-Table $data
            #Show-Grid -Data:$data -Title:$title
         } 
         'wss' {
            $title = "Wait Session Statistics"
            write-host $title -ForegroundColor green 
            $data = Get-WaitSessionStatistics -ConnectionString:$cn
            Show-Table $data
            Show-Grid -Data:$data -Title:$title
         }
         'lus'{
            $title = "Last Updated Statistics"
            write-host $title -ForegroundColor green 
            $data = Get-LastUpdatedStatistics -ConnectionString:$cn -Database:$DB1
            Show-Table $data
            Show-Grid -Data:$data -Title:$title
         } 
         'vfs' {
            $title = "Virtual File Statistics"
            write-host $title -ForegroundColor green 
            $data = Get-VirtualFileStatistics -ConnectionString:$cn
            Show-List $data
            Show-Grid -Data:$data -Title:$title
         } 
         'pc' {
            $title = "Performance Counters"
            write-host $title -ForegroundColor green 
            $mycolumn1 = @{ Name = 'OBJECT_NAME'; Expression = {  $_.OBJECT_NAME.Trim() }}
            $mycolumn2 = @{ Name = 'counter_name'; Expression = {  $_.counter_name.Trim() }}
            $mycolumn3 = @{ Name = 'instance_name'; Expression = {  $_.instance_name.Trim() }}
            $data = Get-PerformanceCounters -ConnectionString:$cn | Select-Object -Property $mycolumn1, $mycolumn2, $mycolumn3,cntr_value #| Format-Table
            Show-Table $data
            Show-Grid -Data:$data -Title:$title
         }
         'cachep' {
            $title = "Plan Cache Usage"
            write-host $title -ForegroundColor green 
            $data = Get-Cache -ConnectionString:$cn
            Show-List $data
            Show-Grid -Data:$data -Title:$title
         }
         'cpucost' {
            $title = "CPU COST Query"
            write-host $title -ForegroundColor green 
            $data = Get-CPUCostQuery -ConnectionString:$cn
            Show-List $data
            Show-Grid -Data:$data -Title:$title
         }
         'cns' {
            $title = "Connections"
            write-host $title -ForegroundColor green 
            $data = Get-Connections -ConnectionString:$cn
            Show-List $data
            Show-Grid -Data:$data -Title:$title
         }
         'ppq' {
            $title = "Poor Performing Query"
            write-host $title -ForegroundColor green
            $orders = @(
                       @{Direction='Ascending';Field='Execution_Count'}
                       @{Direction='Descending';Field='Avg_CPU_Time_ms'}
            )
            
            $data = Get-PoorPerformingQuery -Orders:$orders -ConnectionString:$cn
            #Show-List $data
            Show-Grid -Data:$data -Title:$title
         }
         'mi' {
            $title = "Missing Index"
            write-host $title -ForegroundColor green 
            $data = Get-MissingIndex -ConnectionString:$cn -Database:$DB1
            Show-List $data
            Show-Grid -Data:$data -Title:$title 
         }
         'ui' {
            $title = "Unused Index"
            write-host $title -ForegroundColor green 
            $data = Get-UnusedIndex -ConnectionString:$cn -Database:$DB1
            Show-List $data
            Show-Grid -Data:$data -Title:$title 
         }
         'sessions'{
            $title = "Current Sessions"
            write-host $title -ForegroundColor green 
            $data = Get-Sessions -ConnectionString:$cn -Database:$DB1
            Show-List $data
            Show-Grid -Data:$data -Title:$title 
         }
         'requests' {
            $title = "Running Requests"
            write-host $title -ForegroundColor green 
            $data = Get-Requests -ConnectionString:$cn -Database:$DB1
            Show-List $data
            Show-Grid -Data:$data -Title:$title 
         }
         '' {
            exit
         }
     }
     pause
     #Write-Host "Press any kay" -ForegroundColor green
     #[System.Console]::ReadKey()
     #Write-Host -Object ('The key that was pressed was: {0}' -f [System.Console]::ReadKey().Key.ToString());
 }
 until ($selection -eq 'q')