Find If Index is Being Used in Database

It is very often I get query that how to find if any index is being used in database or not. If any database has many indexes and not all indexes are used it can adversely affect performance. If number of index is higher it reduces the INSERT / UPDATE / DELETE operation but increase the SELECT operation. It is recommended to drop any unused indexes from table to improve the performance.

Before dropping the index it is important to check if index is being used or not. I have wrote quick script which can find out quickly if index is used or not. SQL Server 2005 and later editions have Dynamic Management Views (DMV) which can queried to retrieve necessary information.

We will run SELECT on Employee table of AdventureWorks database and check it uses Indexes or not. All the information about Index usage is stored in DMV – sys.dm_db_index_usage_stats. Let us run following query first and save our results.

USE AdventureWorks

SELECT DISTINCT OBJECT_NAME(sis.OBJECT_ID) TableName, si.name AS IndexName, sc.Name AS ColumnName,sic.Index_ID, sis.user_seeks, sis.user_scans, sis.user_lookups, sis.user_updatesFROM sys.dm_db_index_usage_stats sisINNER JOIN sys.indexes si ON sis.OBJECT_ID = si.OBJECT_ID AND sis.Index_ID = si.Index_IDINNER JOIN sys.index_columns sic ON sis.OBJECT_ID = sic.OBJECT_ID AND sic.Index_ID = si.Index_IDINNER JOIN sys.columns sc ON sis.OBJECT_ID = sc.OBJECT_ID AND sic.Column_ID = sc.Column_IDWHERE sis.Database_ID = DB_ID('AdventureWorks') AND sis.OBJECT_ID = OBJECT_ID('HumanResources.Employee');



SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],
       I.[NAME] AS [INDEX NAME],
       A.LEAF_INSERT_COUNT,
       A.LEAF_UPDATE_COUNT,
       A.LEAF_DELETE_COUNT FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
       INNER JOIN SYS.INDEXES AS I
         ON I.[OBJECT_ID] = A.[OBJECT_ID]
            AND I.INDEX_ID = A.INDEX_ID WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
         INNER JOIN SYS.INDEXES AS I 
           ON I.[OBJECT_ID] = S.[OBJECT_ID] 
              AND I.INDEX_ID = S.INDEX_ID WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 

Reference link-

https://blog.sqlauthority.com/2008/10/03/sql-server-2008-find-if-index-is-being-used-in-database/

OR

https://www.mssqltips.com/sqlservertip/1239/how-to-get-index-usage-information-in-sql-server/
OR
https://www.simple-talk.com/sql/performance/tune-your-indexing-strategy-with-sql-server-dmvs/

Generic Collections vs Non-Generic Collection

The basic difference is that generic collections are strongly typed and non-generic collections are not, unless they've been specially written to accept just a single type of data.

In the .NET Framework, the non-generic collections (ArrayList, Hashtable, SortedKist, Queue etc.) store elements internally in 'object' arrays which, can of course, store any type of data.

This means that, in the case of value types (int, double, bool, char etc), they have to be 'boxed' first and then 'unboxed' when you retrieve them which is quite a slow operation.

Whilst you don't have this problem with reference types, you still have to cast them to their actual types before you can use them.

In contrast, generic collections (List<T>, Dictionary<T, U>, SortedList<T, U>, Queue<T> etc) store elements internally in arrays of their actual types and so no boxing or casting is ever required.

This means that generic collections are faster than their non-generic counterparts when using value types and more convenient when using reference types. In short, the latter are now virtually redundant.