Get-DbaTopResourceUsage

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

Synopsis

Returns the top 20 resource consumers for cached queries based on four different metrics: duration, frequency, IO, and CPU.

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."

Syntax

Get-DbaTopResourceUsage
    [-SqlInstance] <DbaInstanceParameter[]>
    [[-SqlCredential] <PSCredential>]
    [[-Database] <Object[]>]
    [[-ExcludeDatabase] <Object[]>]
    [[-Type] <String[]>]
    [[-Limit] <Int32>]
    [-EnableException]
    [-ExcludeSystem]
    [<CommonParameters>]

Examples

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

Required Parameters

-SqlInstance

The target SQL Server instance or instances.

Optional Parameters

-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.

-Database

The database(s) to process - this list is auto-populated from the server. If unspecified, all databases will be processed.

-ExcludeDatabase

The database(s) to exclude - this list is auto-populated from the server

-Type

By default, all Types run but you can specify one or more of the following: Duration, Frequency, IO, or CPU

-Limit

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)

-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.

-ExcludeSystem

This will exclude system objects like replication procedures from being returned.