Rajendra Gupta
Index information in a specified instance on a particular database using

Get-DbaHelpIndex command in DBATools

June 5, 2019 by

DBATools is an open-source PowerShell that contains a collection of useful commands. In this series of articles on DBATools (see TOC at the bottom) we performed installation of it. We also explored commands to do database backups, database restoration, Identity column maximum range threshold and create a SQL database.

In this article, we will fetch index information for specified SQL instance along with examples.

Overview of Index system catalog and DMV in SQL Server

Index creation and maintenance is an essential task for a DBA. We use system catalog views and DMV’s in SQL Server to fetch information about indexes. A few important DMV are as follows.

  • sys.dm_db_index_usage_stats : It gives a count of different index operations such as user seeks, user scans, last user scan, last user lookups
  • sys.dm_db_partition_stats: We get information about index pages and row count information for a partition in the specified database
  • Sys.indexes: It gives information about each index or heap in an object

We need to join these system catalog views and DMV to get information about the indexes. Executing the following query gives useful information about all indexes in the current database.

In the output, we can see all index information for the current database.

Get-DbaHelpIndex command in DBATools

We might need to perform specific tasks that required changing the t-SQL code and if you are not much familiar with writing t-SQL, it might be a problematic situation for you. DBATools commands are easy to use and we can use specific parameters to fetch the required information. We can also connect with multiple SQL instances, databases objects to fetch the information in a customized format as well. We can use Excel, CSV, HTML, text output formats.

Let’s explore commands in DBATools commands to get this information about indexes. If you have not installed DBATools, you can follow the article, DBATools PowerShell Module for SQL Server before proceeding for this article.

DBATools commands to retrieve index information

First, let’s get all commands related to keyword *Index* using Get-help command.

DBATools command to for keyword Index

In this article, we get in detailed information about Get-DbaHelpInex DBATools command.

Get-DbaHelpIndex command DBATools

You can check the Get-DbaHelpIndex command synopsis, syntax and description using the following command.

Get-DbaHelpIndex command help page

We can understand the output of this command with examples in the next section.

Example 1: Index information in a specified instance on a particular database using DBATools

The following command checks all indexes in AdventureWorks2017 database for Kashish\SQL2019CTP SQL instance.

Index information in a specified instance on a particular database

We might have a large number of indexes in a database. I would suggest using the output in a grid format using | Out-GridView parameter. You can use this grid format with any parameters in DBATools commands.

In the following screenshot ( part 1 and part 2) we can see all the objects and index information in respective columns.

Index information in a specified instance on a particular database using

Index information in a specified instance on a particular database using  - Part 2

Let’s understand the output of Get-DbaHelpIndex command.

  • Computer: It is the server name of the SQL instance. In my example, you can see the computer name, Kashish
  • InstanceName: It shows the instance name of the SQL Server. In my example, you can see the computer name SQL2019CTP
  • sqlInstance: It is the combination of the computer and Instance name. For the named instance in SQL Server, we use HostName\InstanceName format in SSMS or application connection string. You can see the SQL instance name Kashish\SQL2019CTP
  • ObjectName: it is the table name that contains an index in the specified database
  • IndexType: In this column, we get index types such as Clustered, Non-Clustered or ColumnStoreIndex. We get further information in this column about index type as the unique or primary key

IndexType information

  • Key Columns: It shows the key columns in an index. These key columns affect the page storage in the B-tree index
  • Included Columns: In SQL Server, we can define columns to the INCLUDE clause of a nonclustered index. These columns do not affect the order of the page in an index. It is an optional field, and we get no values if Included columns are not present in an index
  • Filter Definition: If we have used any filter condition is an index, we get it here. It is also an optional column
  • DataCompression: We can enable data compression on an index to reduce the overall size of a database. If we have used it in the index, we get value for it else output of this column is blank
  • IndexReads: We can use this column to get the number of reads of the index. As we know SQL Server resets value for DMV’s after SQL service restart, we get values from last restart or index rebuild
  • IndexUpdates: It shows the number of writes for a particular index since the last SQL Service restart or index rebuild
  • SizeKB: We get the size of an index in KB
  • IndexRows: We can track the number of rows in an index using this column. Ideally, a number of rows in a table and IndexRows column count are the same, but if we have used the filtered index, it may vary
  • IndexLookups: In this column, we get the number of index lookups for the clustered index or a heap
  • MostRecentlyUsed: It is a beneficial column to get last used timestamp of an index since the last restart of SQL Server. We can track this column to see if we need a particular index
  • StatsSampleRows: It shows the row sample size for a statistic. Once SQL Server builds statistics, it shows the number of rows used for these particular statistics
  • StatsRowMods: After the creation of Index statistics, the DML statements modify the statistics based on a number of inserts, updates. We can use this column to track the number of changes to the statistics after the last rebuild
  • HistogramSteps: It is the number of steps in a statistics Histogram
  • Statslast updated: It gives the timestamp for the last statistics update of an index.it is an essential step in the performance tuning to keep your statistics updated. It helps query optimizer to choose an appropriate execution plan
  • IndexFragInPercent: We should do regular index maintenance to remove fragmentation that influences query performance. We can use this column to get the Index fragmentation status

By default, You get all columns in the output. We can also choose the columns that we want to display in the output. Right click on any column and click Select Columns.

Choose columns in an output

If you do not want any column, select that column and move towards left. For example, in the following, we do not want the Statistics column in the output.

Choose columns in an output

Example 2: Index information in a specified instance on multiple databases using DBATools

We can specify multiple database names in -Database parameter. A comma should separate these multiple database names.

For example, in the following query, we want to retrieve index information for AdventureWorks2017 and SQLShackDemo database.

Index information in a specified instance on multiple databases

Example 3: Index information in a specified instance for a particular object using DBATools

Suppose you want to check index information for a particular object as part of a performance troubleshooting for a stored procedure. We can specify an object name using -ObjectName parameter.

For example, in the following query, we want to get index information for an object Production.ProductReview.

It checks for the specified object and returns all indexes information associated with this object.

Index information in a specified instance for a particular object

Example 4: Index information in a specified instance for a particular object with statistics t using DBATools

We can use the parameter -IncludeStats to return information for the index and statistics for a specified object.

Index information in a specified instance for a particular object with statistics

Example 5: Index information in a specified instance for a particular object along with fragmentation details using DBATools

We use the parameter -IncludeFragmentation to retrieve index information along with fragmentation status. By default, we do not get fragmentation status in the output of Get-DbaHelpIndex command.

Output without – IncludeFragmentation parameter Index information in a specified instance for a particular object along with fragmentation details

Output with – IncludeFragmentation parameter

Index information in a specified instance for a particular object along with fragmentation details output

Example 5: Index information in a specified instance for a particular object along with data types of index columns using DBATools

We might be interested to know the data type of index columns. Usually, we need to look at table properties to get column data types.

We can use –IncludeDataTypes in Get-DbaHelpIndex command to return data type of index columns as well in the output.

Output without – IncludeDataTypes parameter

 Index information in a specified instance for a particular object along with data types of index columns  using DBATools

Output with – IncludeDataTypes parameter

We can see data types of key columns along with each key column of an index. It does not show the data type information for the Included columns.

Index information in a specified instance for a particular object along with data types of index columns output

Conclusion

In this article, we explored DBATools command Get-DbaHelpIndex to get index information along with statistics, fragmentation, index usage details. We should use DBATools command to perform the administrative task without writing any t-SQL codes.

Table of contents

DBATools PowerShell Module for SQL Server
PowerShell SQL Server Validation Utility – DBAChecks
SQL Database Backups using PowerShell Module – DBATools
IDENTITY columns threshold using PowerShell SQL Server DBATools
DBATools PowerShell SQL Server Database Backups commands
SQL Restore Database using DBATools
Validate backups with SQL restore database operations using DBATools
Fix Orphan users in SQL Server using DBATools PowerShell
Creating a SQL Server Database using DBATools
Get SQL Database details using DBATools
Get-DbaHelpIndex command in DBATools

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
257 Views