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


Get-DbaHelpIndex

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)

Screenshots

Examples

To return information on all indexes on the MyDB database on the localhost.

Get-DbaHelpIndex -SqlServer localhost -Databases MyDB

To return information on all indexes on the MyDB, MyDB2 databases

Get-DbaHelpIndex -SqlServer localhost -Databases MyDB,MyDB2

To return index information on the object dbo.Table1 in the database MyDB

Get-DbaHelpIndex -SqlServer localhost -Databases MyDB -ObjectName dbo.Table1

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

Get-DbaHelpIndex -SqlServer localhost -Databases MyDB -ObjectName dbo.Table1 -IncludeStats

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

Get-DbaHelpIndex -SqlServer localhost -Database MyDB -ObjectName dbo.Table1 -IncludeDataTypes

To return the index information for the table dbo.Table1 in the MyDB database, and returns the numerical data with separators to make it more readable (ie 1234 becomes 1,234)

Get-DbaHelpIndex -SqlServer localhost -Database MyDB -ObjectName dbo.Table1 -FormatResults

To return the index information for all index in the MyDB database, as well as statistics, and formats the numerical data to be more redable

Get-DbaHelpIndex -SqlServer localhost -Database MyDB -IncludeStats -FormatResults

Author

This command was created by Nicholas Cain, SQL Server MCM. You can find Nic on Twitter and his blog.

Get-Help

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

Source Code

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

Related commands