Find-DbaDbUnusedIndex

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.

-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. Options for this list are auto-populated from the server. If unspecified, all databases will be processed.

-ExcludeDatabase

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

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

-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

-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

-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

-InputObject

Enables piping from Get-DbaDatabase

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