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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name ,IX.name AS Index_Name ,IX.type_desc Index_Type ,SUM(PS.[used_page_count]) * 8 IndexSizeKB ,IXUS.user_seeks AS NumOfSeeks ,IXUS.user_scans AS NumOfScans ,IXUS.user_lookups AS NumOfLookups ,IXUS.user_updates AS NumOfUpdates ,IXUS.last_user_seek AS LastSeek ,IXUS.last_user_scan AS LastScan ,IXUS.last_user_lookup AS LastLookup ,IXUS.last_user_update AS LastUpdate FROM sys.indexes IX INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1 GROUP BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,IX.type_desc ,IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,IXUS.last_user_seek ,IXUS.last_user_scan ,IXUS.last_user_lookup ,IXUS.last_user_update |
In the output, we can see all index information for the current database.
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.
|
1 |
>Get-help *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.
|
1 |
>Get-Help Get-DbaHelpIndex |
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.
|
1 |
> Get-DbaHelpIndex -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 |
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.
|
1 |
> Get-DbaHelpIndex -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 | Out-GridView |
In the following screenshot ( part 1 and part 2) we can see all the objects and index information in respective columns.
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
- 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.
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.
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.
|
1 |
> Get-DbaHelpIndex -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017,SQLShackDemo | Out-GridView |
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.
|
1 |
> Get-DbaHelpIndex -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 -Object Production.ProductReview | Out-GridView |
It checks for the specified object and returns all indexes information associated with this 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.
|
1 |
> Get-DbaHelpIndex -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 -Object Production.ProductReview -IncludeStats | Out-GridView |
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
Output with – IncludeFragmentation parameter
|
1 |
> Get-DbaHelpIndex -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 -Object Production.ProductReview -IncludeFragmentation | Out-GridView |
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
Output with – IncludeDataTypes parameter
|
1 |
> Get-DbaHelpIndex -SqlInstance Kashish\SQL2019CTP -Database AdventureWorks2017 -Object Production.ProductReview -IncludeDataTypes | Out-GridView |
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.
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
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023