Are Your Indexes Being Used Effectively?


You could see that the values for INDEX ID from 1…4 has incremented by 1 to 21 from the previous value of 20 and for INDEX ID 5 the value has remained unchanged. In this way you can identify which indexes are really helpful and which are very rarely used. If you are interested to know when each of the above indexes were last used then you can use the query below:

USE AdventureWorks
GO

DECLARE @TABLENAME sysname
SET @TABLENAME= ‘HumanResources.Employee’

SELECT DB_NAME(DATABASE_ID) AS [DATABASE NAME]
 , OBJECT_NAME(SS.OBJECT_ID) AS [OBJECT NAME]
 , I.NAME AS [INDEX NAME]
 , I.INDEX_ID AS [INDEX ID]
 , USER_SEEKS AS [NUMBER OF SEEKS]
 , LAST_USER_SEEK AS  [LAST USER SEEK]
 , USER_SCANS AS [NUMBER OF SCANS]
 , LAST_USER_SCAN AS [LAST USER SCAN]
 , USER_LOOKUPS AS [NUMBER OF BOOKMARK LOOKUPS]
 , LAST_USER_LOOKUP AS [LAST USER LOOKUP]
 , USER_UPDATES AS [NUMBER OF UPDATES]
 , LAST_USER_UPDATE AS [LAST USER UPDATE]
FROM    
   SYS.DM_DB_INDEX_USAGE_STATS SS
   INNER JOIN SYS.INDEXES I
       ON I.OBJECT_ID = SS.OBJECT_ID
            AND I.INDEX_ID = SS.INDEX_ID
WHERE DATABASE_ID = DB_ID()
  AND OBJECTPROPERTY(SS.OBJECT_ID,’IsUserTable’) = 1
 AND  SS.OBJECT_ID = OBJECT_ID(@TABLENAME)
ORDER BY USER_SEEKS
    , USER_SCANS
    , USER_LOOKUPS
      , USER_UPDATES ASC
GO

Before dropping an index it would be ideal to disable the index on the table and see is there is any performance hit when the index is disabled. Disabling a non clustered index prevents users from accessing the particular non clustered index defined on the underlying table. However, the index definition remains in metadata and index statistics are also kept on nonclustered indexes. You need to keep in mind that if you disable the clustered index by any chance then you will not be able to access the data in the underlying table until the index is dropped or rebuilt.

Disable IX_Employee_ManagerID index on HumanResources.Employee table
USE AdventureWorks
GO
ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee
DISABLE
GO

Enable IX_Employee_ManagerID index on HumanResources.Employee table
USE AdventureWorks
GO
ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee REBUILD
GO

Finally after the analysis when it is discovered that the queries are not using the IX_Employee_ManagerID index then you can execute the below TSQL code to drop the index on the HumanResources.Employee table.

Drop IX_Employee_ManagerID index on HumanResources.Employee table
USE AdventureWorks
GO
DROP INDEX IX_Employee_ManagerID ON HumanResources.Employee
GO

Conclusion
Using the sys.dm_db_index_usage_stats Dynamic Management View, you can easily identify indexes which are used often by queries and also ndexes which are rarely used. Once you have identified the indexes, the best approach is to disable the indexes for some time and see is there is any performance degradation once the indexes are disabled. If there is no performance degradation then you can go ahead and drop the index there by saving disk space and improving performace during Insert, Update and Delete operations.

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |