Author | Nic Cain, sirsql.net |
Availability | Windows, Linux, macOS |
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.
Retrieves comprehensive index and statistics information from SQL Server databases for performance analysis and optimization.
This function queries SQL Server DMVs to return detailed index and statistics information for performance analysis, index maintenance planning, and identifying optimization opportunities. You can target all indexes in a database or focus on a specific table to analyze index usage patterns, sizes, and fragmentation levels.
Essential for DBAs performing index tuning, this command helps identify unused indexes for removal, oversized indexes consuming storage, and indexes requiring maintenance based on fragmentation or usage statistics. The data combines structural information (key columns, include columns, filters) with runtime metrics (reads, updates, last used) to provide a complete index health picture.
Uses SQL Server DMVs and system tables, requiring SQL Server 2005 or later. For performance reasons, certain statistics details are limited in SQL Server 2005 unless you specify a specific table with the ObjectName parameter.
The data includes:
Get-DbaHelpIndex
[[-SqlInstance] <DbaInstanceParameter[]>]
[[-SqlCredential] <PSCredential>]
[[-Database] <Object[]>]
[[-ExcludeDatabase] <Object[]>]
[[-InputObject] <Database[]>]
[[-ObjectName] <String>]
[-IncludeStats]
[-IncludeDataTypes]
[-Raw]
[-IncludeFragmentation]
[-EnableException]
[<CommonParameters>]
PS C:\> Get-DbaHelpIndex -SqlInstance localhost -Database MyDB
Returns information on all indexes on the MyDB database on the localhost.
PS C:\> Get-DbaHelpIndex -SqlInstance localhost -Database MyDB,MyDB2
Returns information on all indexes on the MyDB & MyDB2 databases.
PS C:\> Get-DbaHelpIndex -SqlInstance localhost -Database MyDB -ObjectName dbo.Table1
Returns index information on the object dbo.Table1 in the database MyDB.
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.
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.
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.
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.
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
PS C:\> Get-DbaDatabase -SqlInstance sql2017 -Database MyDB | Get-DbaHelpIndex
Returns the index information for all indexes in the MyDB database
The target SQL Server instance or instances.
Alias | |
Required | False |
Pipeline | false |
Default Value |
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 |
Specifies which databases to analyze for index and statistics information. Accepts multiple database names and wildcard patterns.
Use this when you need to focus your analysis on specific databases rather than scanning the entire instance.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Databases to skip during the index analysis process. Useful for excluding system databases or databases currently under maintenance.
Commonly used to exclude tempdb or databases that are offline or in restoring state.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Accepts database objects from Get-DbaDatabase for pipeline processing.
Enables filtering databases first, then analyzing only the indexes on those specific databases.
Alias | |
Required | False |
Pipeline | true (ByValue) |
Default Value |
Targets index analysis to a specific table using either single name (uses default schema) or two-part naming like 'schema.table'.
Essential when troubleshooting performance issues on a specific table or when you need detailed statistics information on SQL Server 2005 instances.
Alias | |
Required | False |
Pipeline | false |
Default Value |
Returns statistics objects in addition to indexes, providing complete picture of query optimization structures.
Use this when analyzing query plan issues or determining which statistics might be missing or stale for specific tables.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Adds data type information for all key and include columns in the index definitions.
Helpful when analyzing index key size, planning composite indexes, or understanding why certain indexes might be inefficient.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Returns numeric values without formatting (no thousands separators) and Size as a dbasize object.
Use this when feeding results into other functions or when you need precise numeric values for calculations.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
Adds fragmentation percentage data by querying sys.dm_db_index_physical_stats with DETAILED mode.
Critical for index maintenance planning but significantly increases execution time on large databases with many indexes.
Alias | |
Required | False |
Pipeline | false |
Default Value | False |
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 |