Friday, November 27, 2020

SQL Server - Identify unused indexes

 In this blog, we learn about the index usage information (SYS.DM_DB_INDEX_USAGE_STATS) and analyze the index usage data (USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES) and will identify the unused index.

Indexes help us in covering and enhancing the performance of a large number of queries that try to retrieve data from the database table and SQL Server allows us to create up to 999 Non-clustered indexes and one Clustered indexes per each table and there is a Meta data table SYS.INDEXESwhich contains one row per each index in table or view.

 

SQL server stores the index usage information in the system table and it provides SYS.DM_DB_INDEX_USAGE_STATSview to access the index usage statistics information so with help of index usage information we can identify the un-used index and will remove them easily.

 

there are few important columns (USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES) in SYS.DM_DB_INDEX_USAGE_STATSthat help us to identify the bad or un-used index in table which are not being used in any query but in each update /insert operations , index data is getting updated.

 

·        USER_SEEKS - it stores the number of times the index is used to find a specific row.

·        USER_SCANS - it stores the number of times the leaf pages of the index are scanned.

·        USER_LOOKUPS - it stores the number of times a Clustered index is used by the Non-clustered index to fetch the full row .

·        USER_UPDATES - it stores the number of times the index data is modified

 

Here is a SQL query to pull the above information about the index.

 

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 ON I.[OBJECT_ID]S.[OBJECT_ID] AND I.INDEX_ID S.INDEX_ID

WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable')=1

      AND S.database_id=DB_ID()

      AND I.name='OrderOrderStatusDate_IDX'

 

Output:

SYS.DM_DB_INDEX_USAGE_STATS



 


As per output, the sql index OrderOrderStatusDate_IDX” is not being used but index data is modified by 28046 times. 

4 comments:

James Zicrov said...

Thank you so much for providing information about REST,PUT and DELETE.REST APIs are actually very useful and provide some edge over other complex operations.

SQL Server Load Rest Api

KITS Technologies said...

salesforce online courses
hadoop online courses
Data Science online courses
linux online courses
etl testing online courses
web methods online courses
business analyst online course
oracle adf online course
oracle rac online course

sankar said...

nice post.tableau training

ujwalaradach said...

Borgata Hotel Casino & Spa - Mapyro
The Borgata 의정부 출장안마 Hotel Casino & Spa 창원 출장마사지 is a 평택 출장마사지 three-block hotel and casino located in Atlantic 광주 출장안마 City, New Jersey. The property features 시흥 출장샵 2000 slot machines,

SQL Server - Identify unused indexes

 In this blog, we learn about the index usage information (SYS.DM_DB_INDEX_USAGE_STATS) and analyze the index usage data (USER_SEEKS, USER_S...