Get DbaHelpIndex command in DBATools
Get-DbaHelpIndex command in DBATools
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 [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022
SQLShack
SQL Server training EspañolGet-DbaHelpIndex command in DBATools
June 5, 2019 by Rajendra Gupta 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. 1234567891011121314151617 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 LastUpdateFROM sys.indexes IXINNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_IDINNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_idWHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1GROUP 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-GridViewExample 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-GridViewExample 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-GridViewExample 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
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 Author Recent Posts Rajendra GuptaHi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.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 [email protected]
View all posts by Rajendra Gupta Latest posts by Rajendra Gupta (see all) Copy data from AWS RDS SQL Server to Azure SQL Database - October 21, 2022 Rename on-premises SQL Server database and Azure SQL database - October 18, 2022 SQL Commands to check current Date and Time (Timestamp) in SQL Server - October 7, 2022