< Back
Invoke-DbaDiagnosticQuery
Post
NAME Invoke-DbaDiagnosticQuery
SYNOPSIS
Invoke-DbaDiagnosticQuery runs the scripts provided by Glenn Berry's DMV scripts on specified servers
SYNTAX
Invoke-DbaDiagnosticQuery [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [-Database
<System.Object[]>] [-ExcludeDatabase <System.Object[]>] [-ExcludeQuery <System.Object[]>] [-SqlCredential
<Pscredential>] [-Path <System.IO.FileInfo>] [-QueryName <System.String[]>] [-UseSelectionHelper <Switch>]
[-InstanceOnly <Switch>] [-DatabaseSpecific <Switch>] [-ExcludeQueryTextColumn <Switch>] [-ExcludePlanColumn
<Switch>] [-NoColumnParsing <Switch>] [-OutputPath <String>] [-ExportQueries <Switch>] [-EnableException <Switch>]
[<CommonParameters>]
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/ca ... v-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
PARAMETERS
-Database [<System.Object[]>]
The database(s) to process. If unspecified, all databases will be processed
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DatabaseSpecific [<Switch>]
Run only database level queries
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDatabase [<System.Object[]>]
The database(s) to exclude
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludePlanColumn [<Switch>]
Use this switch to exclude the [Query Plan] column from relevant queries
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeQuery [<System.Object[]>]
The Queries to exclude
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeQueryTextColumn [<Switch>]
Use this switch to exclude the [Complete Query Text] column from relevant queries
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExportQueries [<Switch>]
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
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-InstanceOnly [<Switch>]
Run only instance level queries
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoColumnParsing [<Switch>]
Does not parse the [Complete Query Text] and [Query Plan] columns and disregards the ExcludeQueryTextColumn
and NoColumnParsing switches
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-OutputPath [<String>]
Directory to parsed diagnostics queries to. This will split them based on server, database name, and query.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Path [<System.IO.FileInfo>]
Alternate path for the diagnostic scripts
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-QueryName [<System.String[]>]
Only run specific query
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances. Can be either a string or SMO server
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-UseSelectionHelper [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
System.Management.Automation.PSObject[]
NOTES
Tags: Community, GlennBerry
Author: Andre Kamman (@AndreKamman), http://clouddba.io
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- 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.
RELATED LINKS
https://dbatools.io/Invoke-DbaDiagnosticQuery
SYNOPSIS
Invoke-DbaDiagnosticQuery runs the scripts provided by Glenn Berry's DMV scripts on specified servers
SYNTAX
Invoke-DbaDiagnosticQuery [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]> [-Database
<System.Object[]>] [-ExcludeDatabase <System.Object[]>] [-ExcludeQuery <System.Object[]>] [-SqlCredential
<Pscredential>] [-Path <System.IO.FileInfo>] [-QueryName <System.String[]>] [-UseSelectionHelper <Switch>]
[-InstanceOnly <Switch>] [-DatabaseSpecific <Switch>] [-ExcludeQueryTextColumn <Switch>] [-ExcludePlanColumn
<Switch>] [-NoColumnParsing <Switch>] [-OutputPath <String>] [-ExportQueries <Switch>] [-EnableException <Switch>]
[<CommonParameters>]
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/ca ... v-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
PARAMETERS
-Database [<System.Object[]>]
The database(s) to process. If unspecified, all databases will be processed
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-DatabaseSpecific [<Switch>]
Run only database level queries
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-EnableException [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeDatabase [<System.Object[]>]
The database(s) to exclude
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludePlanColumn [<Switch>]
Use this switch to exclude the [Query Plan] column from relevant queries
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeQuery [<System.Object[]>]
The Queries to exclude
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeQueryTextColumn [<Switch>]
Use this switch to exclude the [Complete Query Text] column from relevant queries
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExportQueries [<Switch>]
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
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-InstanceOnly [<Switch>]
Run only instance level queries
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-NoColumnParsing [<Switch>]
Does not parse the [Complete Query Text] and [Query Plan] columns and disregards the ExcludeQueryTextColumn
and NoColumnParsing switches
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-OutputPath [<String>]
Directory to parsed diagnostics queries to. This will split them based on server, database name, and query.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Path [<System.IO.FileInfo>]
Alternate path for the diagnostic scripts
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-QueryName [<System.String[]>]
Only run specific query
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlCredential [<Pscredential>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-SqlInstance [<Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>]
The target SQL Server instance or instances. Can be either a string or SMO server
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-UseSelectionHelper [<Switch>]
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
System.Management.Automation.PSObject[]
NOTES
Tags: Community, GlennBerry
Author: Andre Kamman (@AndreKamman), http://clouddba.io
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- 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.
RELATED LINKS
https://dbatools.io/Invoke-DbaDiagnosticQuery