commands

^

Find-DbaDbUnusedIndex

Author Aaron Nelson (@SQLvariant), SQLvariant.com
Availability Windows, Linux, macOS

 

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

Synopsis

Find unused indexes

Description

This command will help you to find Unused indexes on a database or a list of databases

For now only supported for CLUSTERED and NONCLUSTERED indexes

Syntax

Find-DbaDbUnusedIndex
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Database] <Object[]>]
    [[-ExcludeDatabase] <Object[]>]
    [-IgnoreUptime]
    [[-Seeks] <Int32>]
    [[-Scans] <Int32>]
    [[-Lookups] <Int32>]
    [[-InputObject] <Database[]>]
    [-EnableException]
    [<CommonParameters>]

 

Examples

 

Example: 1
PS C:\> Find-DbaDbUnusedIndex -SqlInstance sql2016 -Database db1, db2

Finds unused indexes on db1 and db2 on sql2016

Example: 2
PS C:\> Find-DbaDbUnusedIndex -SqlInstance sql2016 -SqlCredential $cred

Finds unused indexes on db1 and db2 on sql2016 using SQL Authentication to connect to the server

Example: 3
PS C:\> Get-DbaDatabase -SqlInstance sql2016 | Find-DbaDbUnusedIndex

Finds unused indexes on all databases on sql2016

Example: 4
PS C:\> Get-DbaDatabase -SqlInstance sql2019 | Find-DbaDbUnusedIndex -Seeks 10 -Scans 100 -Lookups 1000

Finds 'unused' indexes with user_seeks < 10, user_scans < 100, and user_lookups < 1000 on all databases on sql2019.
Note that these additional parameters provide flexibility to define what is considered an 'unused' index.

Optional Parameters

-SqlInstance

The SQL Server you want to check for unused indexes.

Alias
Required False
Pipeline true (ByValue)
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
Required False
Pipeline false
Default Value
-Database

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

Alias
Required False
Pipeline false
Default Value
-ExcludeDatabase

Specifies the database(s) to exclude from processing. Options for this list are auto-populated from the server.

Alias
Required False
Pipeline false
Default Value
-IgnoreUptime

Less than 7 days uptime can mean that analysis of unused indexes is unreliable, and normally no results will be returned. By setting this option results will be returned even if the Instance has been
running for less than 7 days.

Alias
Required False
Pipeline false
Default Value False
-Seeks

Specify a custom threshold for user seeks. The default for this parameter is 1.
The valid values are between 1 and 1000000 to provide flexibility on the definition of 'unused' indexes.
Note: The resulting WHERE clause uses the AND operator:
user_seeks < $Seeks
AND user_scans < $Scans
AND user_lookups < $Lookups

Alias
Required False
Pipeline false
Default Value 1
-Scans

Specify a custom threshold for user scans. The default for this parameter is 1.
The valid values are between 1 and 1000000 to provide flexibility on the definition of 'unused' indexes.
Note: The resulting WHERE clause uses the AND operator:
user_seeks < $Seeks
AND user_scans < $Scans
AND user_lookups < $Lookups

Alias
Required False
Pipeline false
Default Value 1
-Lookups

Specify a custom threshold for user lookups. The default for this parameter is 1.
The valid values are between 1 and 1000000 to provide flexibility on the definition of 'unused' indexes.
Note: The resulting WHERE clause uses the AND operator:
user_seeks < $Seeks
AND user_scans < $Scans
AND user_lookups < $Lookups

Alias
Required False
Pipeline false
Default Value 1
-InputObject

Enables piping from Get-DbaDatabase

Alias
Required False
Pipeline true (ByValue)
Default Value
-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