Get-DbaHelpIndex

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

Synopsis

Returns size, row and configuration information for indexes in databases.

Description

This function will return detailed information on indexes (and optionally statistics) for all indexes in a database, or a given index should one be passed along.
As this uses SQL Server DMVs to access the data it will only work in 2005 and up (sorry folks still running SQL Server 2000).
For performance reasons certain statistics information will not be returned from SQL Server 2005 if an ObjectName is not provided.

The data includes:

• ObjectName: the table containing the index
• IndexType: clustered/non-clustered/columnstore and whether the index is unique/primary key
• KeyColumns: the key columns of the index
• IncludeColumns: any include columns in the index
• FilterDefinition: any filter that may have been used in the index
• DataCompression: row/page/none depending upon whether or not compression has been used
• IndexReads: the number of reads of the index since last restart or index rebuild
• IndexUpdates: the number of writes to the index since last restart or index rebuild
• SizeKB: the size the index in KB
• IndexRows: the number of the rows in the index (note filtered indexes will have fewer rows than exist in the table)
• IndexLookups: the number of lookups that have been performed (only applicable for the heap or clustered index)
• MostRecentlyUsed: when the index was most recently queried (default to 1900 for when never read)
• StatsSampleRows: the number of rows queried when the statistics were built/rebuilt (not included in SQL Server 2005 unless ObjectName is specified)
• StatsRowMods: the number of changes to the statistics since the last rebuild
• HistogramSteps: the number of steps in the statistics histogram (not included in SQL Server 2005 unless ObjectName is specified)
• StatsLastUpdated: when the statistics were last rebuilt (not included in SQL Server 2005 unless ObjectName is specified)

Syntax

Get-DbaHelpIndex
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-Database] <Object[]>]
    [[-ExcludeDatabase] <Object[]>]
    [[-InputObject] <Database[]>]
    [[-ObjectName] <String>]
    [-IncludeStats]
    [-IncludeDataTypes]
    [-Raw]
    [-IncludeFragmentation]
    [-EnableException]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Get-DbaHelpIndex -SqlInstance localhost -Database MyDB

Returns information on all indexes on the MyDB database on the localhost.

Example: 2

PS C:\> Get-DbaHelpIndex -SqlInstance localhost -Database MyDB,MyDB2

Returns information on all indexes on the MyDB & MyDB2 databases.

Example: 3

PS C:\> Get-DbaHelpIndex -SqlInstance localhost -Database MyDB -ObjectName dbo.Table1

Returns index information on the object dbo.Table1 in the database MyDB.

Example: 4

PS C:\> Get-DbaHelpIndex -SqlInstance localhost -Database MyDB -ObjectName dbo.Table1 -IncludeStats

Returns information on the indexes and statistics for the table dbo.Table1 in the MyDB database.

Example: 5

PS C:\> Get-DbaHelpIndex -SqlInstance localhost -Database MyDB -ObjectName dbo.Table1 -IncludeDataTypes

Returns the index information for the table dbo.Table1 in the MyDB database, and includes the data types for the key and include columns.

Example: 6

PS C:\> Get-DbaHelpIndex -SqlInstance localhost -Database MyDB -ObjectName dbo.Table1 -Raw

Returns the index information for the table dbo.Table1 in the MyDB database, and returns the numerical data without localized separators.

Example: 7

PS C:\> Get-DbaHelpIndex -SqlInstance localhost -Database MyDB -IncludeStats -Raw

Returns the index information for all indexes in the MyDB database as well as their statistics, and formats the numerical data without localized separators.

Example: 8

PS C:\> Get-DbaHelpIndex -SqlInstance localhost -Database MyDB -IncludeFragmentation

Returns the index information for all indexes in the MyDB database as well as their fragmentation

Example: 9

PS C:\> Get-DbaDatabase -SqlInstance sql2017 -Database MyDB | Get-DbaHelpIndex

Returns the index information for all indexes in the MyDB database

Optional Parameters

-SqlInstance

The target SQL Server instance or instances.

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

-InputObject

Allows piping from Get-DbaDatabase

-ObjectName

The name of a table for which you want to obtain the index information. If the two part naming convention for an object is not used it will use the default schema for the executing user. If not
passed it will return data on all indexes in a given database.

-IncludeStats

If this switch is enabled, statistics as well as indexes will be returned in the output (statistics information such as the StatsRowMods will always be returned for indexes).

-IncludeDataTypes

If this switch is enabled, the output will include the data type of each column that makes up a part of the index definition (key and include columns).

-Raw

If this switch is enabled, results may be less user-readable but more suitable for processing by other code.

-IncludeFragmentation

If this switch is enabled, the output will include fragmentation information.

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