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 I 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:

 

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

SQL Server : Drop vs Disable Index

If you are dropping the index; the database will not have any information for dropped index and once the index is dropped, the metadata, statistics and index pages are removed.

But if you disable the existing index of table; database have the metadata of index but it will deallocate index page and the space is freed in database.

 

Here is a script to drop the SQL index from table

 

DROP INDEX OrderNumber_IX ON dbo.Order;

 

To drop all indexes of table

 

DROP INDEX ALL ON dbo.Order;

 

Here is a script to disable an index on table

 

ALTER INDEX OrderNumber_IX ON dbo.Order

Disable;

 

To disable all indexes of table

 

ALTER INDEX ALL ON dbo.Order

Disable;

 

Note : There is one additional data impact of  disabling the clustered index of table;  table will not be available for any DML operation except rebuild and drop command; so before disable clustered index; be very caution about its impact.

 

If you want to enable the disabled index on table; simply run the rebuild command

 

To enable a disabled index of table

 

ALTER INDEX OrderNumber_IX ON dbo.Order

rebuild;

 

To enable the all disabled index of table

 

ALTER INDEX ALL ON dbo.Order

rebuild;

SQL - How to modify system-versioned temporal table schema in SQL Server

In SQL Server, Temporal Table is used to keep the full history of data change for table without doing any extra coding or efforts. It will allow to you get table data at any point of time of past. For more information about SQL Server temporal tables, please visit temporal table in SQL Server .

If you want to modify (add or modify or delete column) the existing temporal table schema, you need to make sure history table schema should be sync.

Here are steps how to add new column ‘ZipCode’ in system-version temporal table.

1.   Turn off System Versioning

ALTER TABLE [dbo].[Office] SET (SYSTEM_VERSIONING = OFF);

2.   Modify the existing both Tables schema ( Office and OfficeHistory )

ALTER TABLE [dbo].[Office] ADD ZipCode varchar(10) NULL;

ALTER TABLE [dbo].[OfficeHistory] ADD ZipCode varchar(10) NULL;

3.   If data update is needed in history table, do it

Update [dbo].[OfficeHistory] SET ZipCode = '43212' where City = 'NYK'

4.   Turn on System Version option

        ALTER TABLE [dbo].[Office]

        SET

         (

            SYSTEM_VERSIONING = ON

           ( HISTORY_TABLE = [dbo].[OfficeHistory])

         );

Tables:

CREATE TABLE dbo.OfficeHistory(

     OfficeID int NOT NULL 

   , OfficeName varchar(50) NOT NULL

   , Street   varchar(50) NOT NULL

   , City varchar(50) NOT NULL

   , State varchar(2) NOT NULL

   ,SysStartTime   datetime2  NOT NULL,

   ,SysEndTime datetime2  NOT NULL,

)

CREATE TABLE Office  

(   

    OfficeID int NOT NULL PRIMARY KEY CLUSTERED

   , OfficeName varchar(50) NOT NULL

   , Street   varchar(50) NOT NULL

   , City varchar(50) NOT NULL

   , State varchar(2) NOT NULL

   , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL

   , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL

   , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)    

)  

WITH   

   (  

      SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OfficeHistory)  

   )

Other blogs related to Temporal Table:

SQL - How to create temporal table in SQL Server

SQL - How to truncate system version temporal table