By this time, it is crystal clear that an index can boost up performance, but it comes at a price. Indexes need space in your desk to accommodate their own B-Tree and get updated each time a DML statement gets executed, so it is a good idea to check for any unused indexes in every business cycle.
Before executing the query to find the unused index, remember that we are going to use a
sys.dm_db_index_usage_stats dynamic management view that removes all the data at every restart of a SQL Server instance and starts collecting data from scratch again.
If we just restart the server or SQL Server instance and look for the statistics, it will show that no index is used and will suggest dropping (DROP) all indexes; ...