DeoCmdlet3.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
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')