< Back
Get-DbaTopResourceUsage
Post
NAME Get-DbaTopResourceUsage
SYNOPSIS
Returns the top 20 resource consumers for cached queries based on four different metrics: duration, frequency, IO,
and CPU.
SYNTAX
Get-DbaTopResourceUsage [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>
[[-SqlCredential] <Pscredential>] [[-Database] <System.Object[]>] [[-ExcludeDatabase] <System.Object[]>] [[-Type]
<System.String[]>] [[-Limit] <Int>] [-EnableException <Switch>] [-ExcludeSystem <Switch>] [<CommonParameters>]
DESCRIPTION
Returns the top 20 resource consumers for cached queries based on four different metrics: duration, frequency, IO,
and CPU.
This command is based off of queries provided by Michael J. Swart at http://michaeljswart.com/go/Top20
Per Michael: "I've posted queries like this before, and others have written many other versions of this query. All
these queries are based on sys.dm_exec_query_stats."
PARAMETERS
-Database [<System.Object[]>]
The database(s) to process - this list is auto-populated from the server. If unspecified, all databases will
be processed.
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 - this list is auto-populated from the server
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeSystem [<Switch>]
This will exclude system objects like replication procedures from being returned.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Limit [<Int>]
By default, these query the Top 20 worst offenders (though more than 20 results can be returned if each of the
top 20 have more than 1 subsequent result)
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Type [<System.String[]>]
By default, all Types run but you can specify one or more of the following: Duration, Frequency, IO, or CPU
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
NOTES
Tags: Query, Performance
Author: Chrissy LeMaire (@cl), netnerds.net
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Get-DbaTopResourceUsage -SqlInstance sql2008, sql2012
Return the 80 (20 x 4 types) top usage results by duration, frequency, IO, and CPU servers for servers sql2008 and
sql2012
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Get-DbaTopResourceUsage -SqlInstance sql2008 -Type Duration, Frequency -Database TestDB
Return the highest usage by duration (top 20) and frequency (top 20) for the TestDB on sql2008
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Get-DbaTopResourceUsage -SqlInstance sql2016 -Limit 30
Return the highest usage by duration (top 30) and frequency (top 30) for the TestDB on sql2016
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Get-DbaTopResourceUsage -SqlInstance sql2008, sql2012 -ExcludeSystem
Return the 80 (20 x 4 types) top usage results by duration, frequency, IO, and CPU servers for servers sql2008 and
sql2012 without any System Objects
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>Get-DbaTopResourceUsage -SqlInstance sql2016| Select-Object *
Return all the columns plus the QueryPlan column
RELATED LINKS
https://dbatools.io/Get-DbaTopResourceUsage
SYNOPSIS
Returns the top 20 resource consumers for cached queries based on four different metrics: duration, frequency, IO,
and CPU.
SYNTAX
Get-DbaTopResourceUsage [-SqlInstance] <Sqlcollaborative.Dbatools.Parameter.DbaInstanceParameter[]>
[[-SqlCredential] <Pscredential>] [[-Database] <System.Object[]>] [[-ExcludeDatabase] <System.Object[]>] [[-Type]
<System.String[]>] [[-Limit] <Int>] [-EnableException <Switch>] [-ExcludeSystem <Switch>] [<CommonParameters>]
DESCRIPTION
Returns the top 20 resource consumers for cached queries based on four different metrics: duration, frequency, IO,
and CPU.
This command is based off of queries provided by Michael J. Swart at http://michaeljswart.com/go/Top20
Per Michael: "I've posted queries like this before, and others have written many other versions of this query. All
these queries are based on sys.dm_exec_query_stats."
PARAMETERS
-Database [<System.Object[]>]
The database(s) to process - this list is auto-populated from the server. If unspecified, all databases will
be processed.
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 - this list is auto-populated from the server
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-ExcludeSystem [<Switch>]
This will exclude system objects like replication procedures from being returned.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Limit [<Int>]
By default, these query the Top 20 worst offenders (though more than 20 results can be returned if each of the
top 20 have more than 1 subsequent result)
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.
Required? false
Position? named
Default value
Accept pipeline input? False
Accept wildcard characters? false
-Type [<System.String[]>]
By default, all Types run but you can specify one or more of the following: Duration, Frequency, IO, or CPU
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
NOTES
Tags: Query, Performance
Author: Chrissy LeMaire (@cl), netnerds.net
Website: https://dbatools.io
Copyright: (c) 2018 by dbatools, licensed under MIT
License: MIT https://opensource.org/licenses/MIT
-------------------------- EXAMPLE 1 --------------------------
PS C:\\>Get-DbaTopResourceUsage -SqlInstance sql2008, sql2012
Return the 80 (20 x 4 types) top usage results by duration, frequency, IO, and CPU servers for servers sql2008 and
sql2012
-------------------------- EXAMPLE 2 --------------------------
PS C:\\>Get-DbaTopResourceUsage -SqlInstance sql2008 -Type Duration, Frequency -Database TestDB
Return the highest usage by duration (top 20) and frequency (top 20) for the TestDB on sql2008
-------------------------- EXAMPLE 3 --------------------------
PS C:\\>Get-DbaTopResourceUsage -SqlInstance sql2016 -Limit 30
Return the highest usage by duration (top 30) and frequency (top 30) for the TestDB on sql2016
-------------------------- EXAMPLE 4 --------------------------
PS C:\\>Get-DbaTopResourceUsage -SqlInstance sql2008, sql2012 -ExcludeSystem
Return the 80 (20 x 4 types) top usage results by duration, frequency, IO, and CPU servers for servers sql2008 and
sql2012 without any System Objects
-------------------------- EXAMPLE 5 --------------------------
PS C:\\>Get-DbaTopResourceUsage -SqlInstance sql2016| Select-Object *
Return all the columns plus the QueryPlan column
RELATED LINKS
https://dbatools.io/Get-DbaTopResourceUsage