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.
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