commands

^

Invoke-DbaDiagnosticQuery

Author Andre Kamman (@AndreKamman), http://andrekamman.com
Availability Windows, Linux, macOS

 

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: https://glennsqlperformance.com/resources/

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

Syntax

Invoke-DbaDiagnosticQuery
    [-SqlInstance] <DbaInstanceParameter[]>
    [-Database <Object[]>]
    [-ExcludeDatabase <Object[]>]
    [-ExcludeQuery <Object[]>]
    [-SqlCredential <PSCredential>]
    [-Path <FileInfo>]
    [-QueryName <String[]>]
    [-UseSelectionHelper]
    [-InstanceOnly]
    [-DatabaseSpecific]
    [-ExcludeQueryTextColumn]
    [-ExcludePlanColumn]
    [-NoColumnParsing]
    [-OutputPath <String>]
    [-ExportQueries]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

 

Examples

 

Example: 1
PS C:\> Invoke-DbaDiagnosticQuery -SqlInstance sql2016

Run the selection made by the user on the Sql Server instance specified.

Example: 2
PS C:\> Invoke-DbaDiagnosticQuery -SqlInstance sql2016 -UseSelectionHelper | Export-DbaDiagnosticQuery -Path C:\temp\gboutput

Provides a grid view 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.

Example: 3
PS C:\> Invoke-DbaDiagnosticQuery -SqlInstance localhost -ExportQueries -OutputPath "C:\temp\DiagnosticQueries"

Export All Queries to Disk

Example: 4
PS C:\> Invoke-DbaDiagnosticQuery -SqlInstance localhost -DatabaseSpecific -ExportQueries -OutputPath "C:\temp\DiagnosticQueries"

Export Database Specific Queries for all User Dbs

Example: 5
PS C:\> Invoke-DbaDiagnosticQuery -SqlInstance localhost -DatabaseSpecific -DatabaseName 'tempdb' -ExportQueries -OutputPath "C:\temp\DiagnosticQueries"

Export Database Specific Queries For One Target Database

Example: 6
PS C:\> Invoke-DbaDiagnosticQuery -SqlInstance localhost -DatabaseSpecific -DatabaseName 'tempdb' -ExportQueries -OutputPath "C:\temp\DiagnosticQueries" -QueryName 'Database-scoped Configurations'

Export Database Specific Queries For One Target Database and One Specific Query

Example: 7
PS C:\> Invoke-DbaDiagnosticQuery -SqlInstance localhost -UseSelectionHelper

Choose Queries To Export

Example: 8
PS C:\> [PSObject[]]$results = Invoke-DbaDiagnosticQuery -SqlInstance localhost -WhatIf

Parse the appropriate diagnostic queries by connecting to server, and instead of running them, return as [PSCustomObject[]] to work with further

Example: 9
PS C:\> $results = Invoke-DbaDiagnosticQuery -SqlInstance Sql2017 -DatabaseSpecific -QueryName 'Database-scoped Configurations' -DatabaseName TestStuff

Run diagnostic queries targeted at specific database, and only run database level queries against this database.

Required Parameters

-SqlInstance

The target SQL Server instance or instances. Can be either a string or SMO server

Alias
Required True
Pipeline true (ByValue)
Default Value

Optional Parameters

-Database

The database(s) to process. If unspecified, all databases will be processed

Alias DatabaseName
Required False
Pipeline false
Default Value
-ExcludeDatabase

The database(s) to exclude

Alias
Required False
Pipeline false
Default Value
-ExcludeQuery

The Queries to exclude

Alias
Required False
Pipeline false
Default Value
-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential). Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported. For MFA support, please use Connect-DbaInstance.

Alias Credential
Required False
Pipeline false
Default Value
-Path

Alternate path for the diagnostic scripts

Alias
Required False
Pipeline false
Default Value
-QueryName

Only run specific query

Alias
Required False
Pipeline false
Default Value
-UseSelectionHelper

Provides a grid view with all the queries to choose from and will run the selection made by the user on the Sql Server instance specified.

Alias
Required False
Pipeline false
Default Value False
-InstanceOnly

Run only instance level queries

Alias
Required False
Pipeline false
Default Value False
-DatabaseSpecific

Run only database level queries

Alias
Required False
Pipeline false
Default Value False
-ExcludeQueryTextColumn

Use this switch to exclude the [Complete Query Text] column from relevant queries

Alias
Required False
Pipeline false
Default Value False
-ExcludePlanColumn

Use this switch to exclude the [Query Plan] column from relevant queries

Alias
Required False
Pipeline false
Default Value False
-NoColumnParsing

Does not parse the [Complete Query Text] and [Query Plan] columns and disregards the ExcludeQueryTextColumn and NoColumnParsing switches

Alias
Required False
Pipeline false
Default Value False
-OutputPath

Directory to parsed diagnostics queries to. This will split them based on server, database name, and query.

Alias
Required False
Pipeline false
Default Value
-ExportQueries

Use this switch to export the diagnostic queries to sql files. Instead of running the queries, the server will be evaluated to find the appropriate queries to run based on SQL Version. These sql files will then be created in the OutputDirectory

Alias
Required False
Pipeline false
Default Value False
-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message. This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting. Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

Alias
Required False
Pipeline false
Default Value False
-WhatIf

Shows what would happen if the command would execute, but does not actually perform the command

Alias wi
Required False
Pipeline false
Default Value
-Confirm

Prompts to confirm certain actions

Alias cf
Required False
Pipeline false
Default Value

 

Want to see the source code for this command? Check out Invoke-DbaDiagnosticQuery on GitHub.
Want to see the Bill Of Health for this command? Check out Invoke-DbaDiagnosticQuery.