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') |