dbatools is a free PowerShell module with over 200 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Get-DbaTopResourceUsage

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.

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

Screenshots

dbatools-Get-DbaTopResourceUsage

Examples

Return the 80 (20 x 4 types) top usage results by duration, frequency, IO, and CPU servers for servers sql2008 and sql2012.

Get-DbaTopResourceUsage -SqlInstance sql2008, sql2012

Return the highest usage by duration (top 20) and frequency (top 20) for the TestDB on sql2008.

Get-DbaTopResourceUsage -SqlInstance sql2008 -Type Duration, Frequency -Database TestDB

Return the highest usage by duration (top 30) and frequency (top 30) for the TestDB on sql2016.

Get-DbaTopResourceUsage -SqlInstance sql2016 -Limit 30

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.

Get-DbaTopResourceUsage -SqlInstance sql2008, sql2012 -ExcludeSystem

Return all the columns plus the QueryPlan column.

Get-DbaTopResourceUsage -SqlInstance sql2016| Select *

Author

This command was created by Chrissy LeMaire. You can find Chrissy on Twitter.

Get-Help

From PowerShell, execute Get-Help Get-DbaTopResourceUsage -Detailed for more information on this function.

Source Code

Want to see the source code? View Get-DbaTopResourceUsage.ps1 on GitHub
 

Related commands