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

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Find-SqlUnusedIndex

Indexes overview

Instead of scanning your entire table for a key piece of information, with Indexes, SQL Server can look for the desired information within a particular section, which saves both time and resources.

The way SQL Server utilizes indexes is similar to the way we use alphabetizing to quickly look through phone books. For instance, when searching for Jane Smith, we know that we don’t have to look through each and every name. We can skip forward and look alphabetically somewhere between Smith, Ingrid and Smith, Kevin.

Why drop unused indexes?

If there are more categories than necessary, instead of speeding up the querying process by allowing SQL Server to look for the desired information within a particular section, it creates more work with more categories to maintain every time there is an INSERT, UPDATE, or DELETE.

To take the phone book metaphor a step further, we’re able to maintain multiple phone books, some of which, no one is reading at all.

Deciding which indexes to pare down

There are a number of factors in deciding which indexes should stay and go.

Find-SqlUnusedIndex will determine how much space you can save by dropping an index as well as the type of compression so you can make a more considered decision. It uses sys.m_db_index_usages_stats to find indexes that have no user seeks, scans and lookups. It also displays much space you can save by dropping the index. The type of compression is also displayed so that you can make a more informed decision.

This command will quit if it’s less than or equal to 6 days since restart and will provide warning that there may not be a long enough time for a solid evaluation if it’s less than or equal to 33 days since restart. For now, only CLUSTERED and NONCLUSTERED indexes are supported.

You can select the indexes you want to drop on the gridview and by click OK the drop statement will be generated.

Examples

To export the SQL for the unused indexes in server “sql2005” chosen from grid-view and writes them to the file “C:\temp\sql2005-UnusedIndexes.sql”

Find-SqlUnusedIndex -SqlServer sql2005 -FileName C:\temp\sql2005-UnusedIndexes.sql

To find exact Unused indexes on all user databases present on sqlserver2016 will be verified using SQL credentials.

Find-SqlUnusedIndex -SqlServer sqlserver2016 -SqlCredential $cred

To find exact Unused indexes on both db1 and db2 databases

Find-SqlUnusedIndex -SqlServer sqlserver2016 -Databases db1, db2

To find exact Unused indexes on all user databases

Find-SqlUnusedIndex -SqlServer sqlserver2016

Examples

This command was written by SQL Server MVP Aaron Nelson. You can find Aaron blogging at sqlvariant.com and on Twitter.

Get-Help

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

Source Code

Want to see the source code? View Find-SqlUnusedIndex.ps1 on GitHub
 

Related commands