functions/Invoke-DbaDiagnosticQuery.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
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
function Invoke-DbaDiagnosticQuery {
<#
.SYNOPSIS
Invoke-DbaDiagnosticQuery runs the scripts provided by Glenn Berry's DMV scripts on specified servers
 
.DESCRIPTION
This is the main function of the Sql Server Diagnostic Queries related functions in dbatools.
The diagnostic queries are developed and maintained by Glenn Berry and they can be found here along with a lot of documentation:
http://www.sqlskills.com/blogs/glenn/category/dmv-queries/
 
The most recent version of the diagnostic queries are included in the dbatools module.
But it is possible to download a newer set or a specific version to an alternative location and parse and run those scripts.
It will run all or a selection of those scripts on one or multiple servers and return the result as a PowerShell Object
 
.PARAMETER SqlInstance
The target SQL Server. Can be either a string or SMO server
  
.PARAMETER SqlCredential
Allows alternative Windows or SQL login credentials to be used
 
.PARAMETER Path
Alternate path for the diagnostic scripts
 
.PARAMETER Database
The database(s) to process. If unspecified, all databases will be processed
 
.PARAMETER ExcludeDatabase
The database(s) to exclude
  
.PARAMETER UseSelectionHelper
Provides a gridview with all the queries to choose from and will run the selection made by the user on the Sql Server instance specified.
 
.PARAMETER QueryName
Only run specific query
  
.PARAMETER InstanceOnly
Run only instance level queries
 
.PARAMETER DatabaseSpecific
Run only database level queries
  
.PARAMETER Silent
Use this switch to disable any kind of verbose messages or progress bars
  
.PARAMETER Confirm
Prompts to confirm certain actions
  
.PARAMETER WhatIf
Shows what would happen if the command would execute, but does not actually perform the command
 
 
.NOTES
Tags: Database, DMV
Author: André Kamman (@AndreKamman), http://clouddba.io
Website: https://dbatools.io
Copyright: (C) Chrissy LeMaire, clemaire@gmail.com
License: GNU GPL v3 https://opensource.org/licenses/GPL-3.0
 
.LINK
https://dbatools.io/Invoke-DbaDiagnosticQuery
 
.EXAMPLE
Invoke-DbaDiagnosticQuery -SqlInstance sql2016
 
Run the selection made by the user on the Sql Server instance specified.
 
.EXAMPLE
Invoke-DbaDiagnosticQuery -SqlInstance sql2016 -UseSelectionHelper | Export-DbaDiagnosticQuery -Path C:\temp\gboutput
 
Provides a gridview with all the queries to choose from and will run the selection made by the user on the SQL Server instance specified.
  
Then it will export the results to Export-DbaDiagnosticQuery.
 
#>

    
    [CmdletBinding(SupportsShouldProcess)]
    param (
        [parameter(Mandatory = $true, ValueFromPipeline = $true, Position = 0)]
        [Alias("ServerInstance", "SqlServer")]
        [DbaInstanceParameter[]]$SqlInstance,
        [PSCredential]$SqlCredential,
        [System.IO.FileInfo]$Path,
        [string[]]$QueryName,
        [switch]$UseSelectionHelper,
        [switch]$InstanceOnly,
        [switch]$DatabaseSpecific,
        [switch]$Silent
    )
    
    begin {
        
        function Invoke-DiagnosticQuerySelectionHelper {
            [CmdletBinding()]
            Param (
                [parameter(Mandatory = $true)]
                $ParsedScript
            )
            
            $ParsedScript | Select-Object QueryNr, QueryName, DBSpecific, Description | Out-GridView -Title "Diagnostic Query Overview" -OutputMode Multiple | Sort-Object QueryNr | Select-Object -ExpandProperty QueryName

        }
        
        Write-Message -Level Verbose -Message "Interpreting DMV Script Collections"
        
        $module = Get-Module -Name dbatools
        $base = $module.ModuleBase
        
        if (!$Path) {
            $Path = "$base\bin\diagnosticquery"
        }
        
        $scriptversions = @()
        $scriptfiles = Get-ChildItem "$Path\SQLServerDiagnosticQueries_*_*.sql"
        
        if (!$scriptfiles) {
            Write-Message -Level Warning -Message "Diagnostic scripts not found in $Path. Using the ones within the module."
            
            $Path = "$base\bin\diagnosticquery"
            
            $scriptfiles = Get-ChildItem "$base\bin\diagnosticquery\SQLServerDiagnosticQueries_*_*.sql"
            if (!$scriptfiles) {
                Stop-Function -Message "Unable to download scripts, do you have an internet connection? $_" -InnerErrorRecord $_
                return
            }
        }
        
        [int[]]$filesort = $null
        
        foreach ($file in $scriptfiles) {
            $filesort += $file.BaseName.Split("_")[2]
        }
        
        $currentdate = $filesort | Sort-Object -Descending | Select-Object -First 1
        
        foreach ($file in $scriptfiles) {
            if ($file.BaseName.Split("_")[2] -eq $currentdate) {
                $parsedscript = Invoke-DbaDiagnosticQueryScriptParser -filename $file.fullname
                
                $newscript = [pscustomobject]@{
                    Version = $file.Basename.Split("_")[1]
                    Script = $parsedscript
                }
                $scriptversions += $newscript
            }
        }
    }
    
    process {
        if (Test-FunctionInterrupt) { return }
        foreach ($instance in $SqlInstance) {
            $counter = 0
            try {
                Write-Message -Level Verbose -Message "Connecting to $instance"
                $server = Connect-SqlInstance -SqlInstance $instance -SqlCredential $sqlcredential
            }
            catch {
                Stop-Function -Message "Failure" -Category ConnectionError -ErrorRecord $_ -Target $instance -Continue
            }
            
            Write-Message -Level Verbose -Message "Collecting diagnostic query data from server: $instance"
            
            if ($server.VersionMinor -eq 50) {
                $version = "2008R2"
            }
            else {
                $version = switch ($server.VersionMajor) {
                    9 { "2005" }
                    10 { "2008" }
                    11 { "2012" }
                    12 { "2014" }
                    13 { "2016" }
                    14 { "2017" }
                }
            }
            
            if (!$instanceOnly) {
                $databases = $server.Query("Select Name from sys.databases where name not in ('master', 'model', 'msdb', 'tempdb')")
            }
            
            $parsedscript = $scriptversions | Where-Object -Property Version -eq $version | Select-Object -ExpandProperty Script
            
            if ($null -eq $first) { $first = $true }
            if ($UseSelectionHelper -and $first) {
                $QueryName = Invoke-DiagnosticQuerySelectionHelper $parsedscript
                $first = $false
            }
            
            if (!$instanceonly -and !$DatabaseSpecific -and !$QueryName) {
                $scriptcount = $parsedscript.count
            }
            elseif ($instanceOnly) {
                $scriptcount = ($parsedscript | Where-Object DBSpecific -eq $false).count
            }
            elseif ($DatabaseSpecific) {
                $scriptcount = ($parsedscript | Where-Object DBSpecific).count
            }
            elseif ($QueryName.Count -ne 0) {
                $scriptcount = $QueryName.Count
            }
            
            foreach ($scriptpart in $parsedscript) {
                
                if (($QueryName.Count -ne 0) -and ($QueryName -notcontains $scriptpart.QueryName)) { continue }
                if (!$scriptpart.DBSpecific -and !$DatabaseSpecific) {
                    if ($PSCmdlet.ShouldProcess($instance, $scriptpart.QueryName)) {
                        $counter++
                        if (!$silent) {
                            Write-Progress -Id 1 -ParentId 0 -Activity "Collecting diagnostic query data from $instance" -Status "Processing $counter of $scriptcount" -CurrentOperation $scriptpart.QueryName -PercentComplete (($counter / $scriptcount) * 100)
                        }
                        
                        try {
                            $result = $server.Query($scriptpart.Text)
                            Write-Message -Level Output -Message "Processed $($scriptpart.QueryName) on $instance"
                            
                            if (!$result) {
                                $result = [pscustomobject]@{
                                    ComputerName = $server.NetName
                                    InstanceName = $server.ServiceName
                                    SqlInstance = $server.DomainInstanceName
                                    Number = $scriptpart.QueryNr
                                    Name = $scriptpart.QueryName
                                    Description = $scriptpart.Description
                                    DatabaseSpecific = $scriptpart.DBSpecific
                                    DatabaseName = $null
                                    Notes = "Empty Result for this Query"
                                    Result = $null
                                }
                                Write-Message -Level Verbose -Message ("Empty result for Query {0} - {1} - {2}" -f $scriptpart.QueryNr, $scriptpart.QueryName, $scriptpart.Description)
                            }
                        }
                        catch {
                            Write-Message -Level Verbose -Message ('Some error has occured on Server: {0} - Script: {1}, result unavailable' -f $instance, $scriptpart.QueryName) -Target $instance -ErrorRecord $_
                        }
                        if ($result) {
                            
                            [pscustomobject]@{
                                ComputerName = $server.NetName
                                InstanceName = $server.ServiceName
                                SqlInstance = $server.DomainInstanceName
                                Number = $scriptpart.QueryNr
                                Name = $scriptpart.QueryName
                                Description = $scriptpart.Description
                                DatabaseSpecific = $scriptpart.DBSpecific
                                DatabaseName = $null
                                Notes = $null
                                Result = $result | Select-Object * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors
                            }
                        }
                    }
                }
                elseif ($scriptpart.DBSpecific -and !$instanceOnly) {
                    foreach ($currentdb in $databases) {
                        $dbname = $currentdb.name
                        if ($PSCmdlet.ShouldProcess(('{0} ({1})' -f $instance, $currentdb.name), $scriptpart.QueryName)) {
                            
                            if (!$silent) { Write-Progress -Id 1 -ParentId 0 -Activity "Collecting diagnostic query data from $dbname on $instance" -Status ('Processing {0} of {1}' -f $counter, $scriptcount) -CurrentOperation $scriptpart.QueryName -PercentComplete (($Counter / $scriptcount) * 100) }
                            Write-Message -Level Output -Message "Collecting diagnostic query data from $dbname for $($scriptpart.QueryName) on $instance"
                            try {
                                $result = $server.Query($scriptpart.Text,$currentdb.Name)
                                if (!$result) {
                                    $result = [pscustomobject]@{
                                        ComputerName = $server.NetName
                                        InstanceName = $server.ServiceName
                                        SqlInstance = $server.DomainInstanceName
                                        Number = $scriptpart.QueryNr
                                        Name = $scriptpart.QueryName
                                        Description = $scriptpart.Description
                                        DatabaseSpecific = $scriptpart.DBSpecific
                                        DatabaseName = $null
                                        Notes = "Empty Result for this Query"
                                        Result = $null
                                    }
                                    Write-Message -Level Verbose -Message ("Empty result for Query {0} - {1} - {2}" -f $scriptpart.QueryNr, $scriptpart.QueryName, $scriptpart.Description) -Target $scriptpart -ErrorRecord $_
                                }
                            }
                            catch {
                                Write-Message -Level Verbose -Message ('Some error has occured on Server: {0} - Script: {1} - Database: {2}, result will not be saved' -f $instance, $scriptpart.QueryName, $currentdb.Name) -Target $currentdb -ErrorRecord $_
                            }
                            
                            [pscustomobject]@{
                                ComputerName = $server.NetName
                                InstanceName = $server.ServiceName
                                SqlInstance = $server.DomainInstanceName
                                Number = $scriptpart.QueryNr
                                Name = $scriptpart.QueryName
                                Description = $scriptpart.Description
                                DatabaseSpecific = $scriptpart.DBSpecific
                                DatabaseName = $currentdb.name
                                Notes = $null
                                Result = $result | Select-Object * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors
                            }
                        }
                    }
                }
            }
        }
    }
}