.::: How to identify and monitor unused indexes in SQL Server from sqlshack :::.

-- A. Finding unused indexes
-- 1. Query 1
-- A simple query that can be used to get the list of unused indexes in SQL Server (updated indexes not used in any seeks, scan or lookup operations) is as follows:

SELECT @@servername as ServerName,@@Servicename as ServiceName,db_name() as DBName,
    objects.name AS Table_name,
    indexes.name AS Index_name,
    dm_db_index_usage_stats.user_seeks,
    dm_db_index_usage_stats.user_scans,
    dm_db_index_usage_stats.user_updates
FROM

    sys.dm_db_index_usage_stats
    INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
    INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID
WHERE
    dm_db_index_usage_stats.user_lookups = 0
    AND
    dm_db_index_usage_stats.user_seeks = 0
    AND
    dm_db_index_usage_stats.user_scans = 0
ORDER BY
    dm_db_index_usage_stats.user_updates DESC
    
-- 2. Query 2

SELECT @@servername as ServerName,@@Servicename as ServiceName,db_name() as DBName,
    objects.name AS Table_name,
    indexes.name AS Index_name,
    dm_db_index_usage_stats.user_seeks,
    dm_db_index_usage_stats.user_scans,
    dm_db_index_usage_stats.user_updates
FROM
    sys.dm_db_index_usage_stats
    INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
    INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID
WHERE
    indexes.is_primary_key = 0 -- This condition excludes primary key constarint
    AND
    indexes. is_unique = 0 -- This condition excludes unique key constarint
    AND
    dm_db_index_usage_stats. user_lookups = 0
    AND
    dm_db_index_usage_stats.user_seeks = 0
    AND
    dm_db_index_usage_stats.user_scans = 0
ORDER BY
    dm_db_index_usage_stats.user_updates DESC
 

-- 3. Query 3

SELECT @@servername as ServerName,@@Servicename as ServiceName,db_name() as DBName,
    objects.name AS Table_name,
    indexes.name AS Index_name,
    dm_db_index_usage_stats.user_seeks,
    dm_db_index_usage_stats.user_scans,
    dm_db_index_usage_stats.user_updates
FROM
    sys.dm_db_index_usage_stats
    INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
    INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID
WHERE
    indexes.is_primary_key = 0 --This line excludes primary key constarint
    AND
    indexes. is_unique = 0 --This line excludes unique key constarint
    AND
    dm_db_index_usage_stats.user_updates <> 0 -- This line excludes indexes SQL Server hasn’t done any work with
    AND
    dm_db_index_usage_stats. user_lookups = 0
    AND
    dm_db_index_usage_stats.user_seeks = 0
    AND
    dm_db_index_usage_stats.user_scans = 0
ORDER BY
    dm_db_index_usage_stats.user_updates DESC
    
    
-- B. Dropping the indexes
SELECT @@servername as ServerName,@@Servicename as ServiceName,db_name() as DBName,'DROP INDEX '+OBJECT_NAME(dm_db_index_usage_stats.object_id)+'.'+indexes.name AS Drop_Index, user_seeks, user_scans, user_lookups, user_updates
 
FROM
    sys.dm_db_index_usage_stats
    INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
    INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID
WHERE
    indexes.is_primary_key = 0 --This line excludes primary key constarint
    AND
    indexes. is_unique = 0 --This line excludes unique key constarint
    AND
    dm_db_index_usage_stats.user_updates <> 0 -- This line excludes indexes SQL Server hasn’t done any work with
    AND
    dm_db_index_usage_stats. user_lookups = 0
    AND
    dm_db_index_usage_stats.user_seeks = 0
    AND
    dm_db_index_usage_stats.user_scans = 0
ORDER BY
    dm_db_index_usage_stats.user_updates DESC
    

source
https://www.sqlshack.com/how-to-identify-and-monitor-unused-indexes-in-sql-server/ 

C. From https://stackoverflow.com/questions/1456496/how-do-i-monitor-and-find-unused-indexes-in-sql-database

DECLARE @MinimumPageCount int
SET @MinimumPageCount = 500

DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())

-- GET UNUSED INDEXES THAT APPEAR IN THE INDEX USAGE STATS TABLE

SELECT  
    Databases.name AS [Database]
    ,object_name(Indexes.object_id) AS [Table]
    ,Indexes.name AS [Index]
    ,PhysicalStats.page_count as [Page Count]
    ,CONVERT(decimal(18,2), PhysicalStats.page_count * 8 / 1024.0) AS [Total Index Size (MB)]
    ,CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Fragmentation (%)]
    ,ParititionStats.row_count AS [Row Count]
    ,CONVERT(decimal(18,2), (PhysicalStats.page_count * 8.0 * 1024) / ParititionStats.row_count) AS [Index Size Per Row (Bytes)]
    ,1 AS [Appears In Usage Stats Table]

FROM sys.dm_db_index_usage_stats UsageStats

INNER JOIN sys.indexes Indexes
    ON Indexes.index_id = UsageStats.index_id AND Indexes.object_id = UsageStats.object_id

INNER JOIN SYS.databases Databases
    ON Databases.database_id = UsageStats.database_id

INNER JOIN sys.dm_db_index_physical_stats (DB_ID(),NULL,NULL,NULL,NULL) AS PhysicalStats
    ON PhysicalStats.index_id = UsageStats.Index_id AND PhysicalStats.object_id = UsageStats.object_id

INNER JOIN SYS.dm_db_partition_stats ParititionStats
    ON ParititionStats.index_id = UsageStats.index_id AND ParititionStats.object_id = UsageStats.object_id

WHERE
    UsageStats.user_scans <= 10
    AND UsageStats.user_seeks <= 10
    AND UsageStats.user_lookups <= 10

    -- exclude heap indexes
    AND Indexes.name IS NOT NULL

    -- ignore indexes with less than a certain number of pages of memory
    AND PhysicalStats.page_count > @MinimumPageCount

    -- Exclude primary keys, which should not be removed
    AND Indexes.is_primary_key = 0

    -- ignore unique constraints - those shouldn't be removed
    AND Indexes.is_unique_constraint = 0
    AND Indexes.is_unique = 0

UNION ALL
(
    -- GET UNUSED INDEXES THAT DO **NOT** APPEAR IN THE INDEX USAGE STATS TABLE

    SELECT  
        Databases.Name AS [Database]
        ,Objects.NAME AS [Table]
        ,Indexes.NAME AS [Index]
        ,PhysicalStats.page_count as [Page Count]
        ,CONVERT(decimal(18,2), PhysicalStats.page_count * 8 / 1024.0) AS [Total Index Size (MB)]
        ,CONVERT(decimal(18,2), PhysicalStats.avg_fragmentation_in_percent) AS [Fragmentation (%)]
        ,-1 AS [Row Count]
        ,-1 AS [Index Size Per Row (Bytes)]
        ,0 AS [Appears In Usage Stats Table]

    FROM SYS.INDEXES Indexes

    INNER JOIN SYS.OBJECTS Objects
        ON Indexes.OBJECT_ID = Objects.OBJECT_ID

    LEFT JOIN sys.dm_db_index_physical_stats(@dbid, null, null, null, null) PhysicalStats
        ON PhysicalStats.object_id = Indexes.object_id AND PhysicalStats.index_id = indexes.index_id

    INNER JOIN sys.databases Databases
        ON Databases.database_id = PhysicalStats.database_id

    WHERE
        Objects.type = 'U' -- Is User Table

        -- exclude heap indexes
        AND Indexes.name IS NOT NULL

        -- exclude empty tables
        AND PhysicalStats.page_count <> 0

        -- Exclude primary keys, which should not be removed
        AND Indexes.is_primary_key = 0

        -- ignore unique constraints - those shouldn't be removed
        AND Indexes.is_unique_constraint = 0
        AND Indexes.is_unique = 0

        AND Indexes.INDEX_ID NOT IN
        (
            SELECT UsageStats.INDEX_ID
            FROM SYS.DM_DB_INDEX_USAGE_STATS UsageStats
            WHERE
                UsageStats.OBJECT_ID = Indexes.OBJECT_ID
                AND Indexes.INDEX_ID = UsageStats.INDEX_ID
                AND DATABASE_ID = @dbid
        )
)

ORDER BY [Table] ASC, [Total Index Size (MB)] DESC

D. Command Index Unusage again >> will implement plan

 

SELECT DISTINCT @@servername as ServerName,db_name() as DBName,
    objects.name AS Table_name,
    indexes.name AS Index_name,
    indexes.type_desc AS Index_type,
    --STRING_AGG(COLUMNS.NAME,',') AS Index_columns,
    STRING_AGG(COLUMNS.NAME,',') AS  Include,
    '' AS Partition,
    dm_db_index_usage_stats.user_seeks AS No_of_seeks,
    dm_db_index_usage_stats.user_scans AS No_of_scans,
    dm_db_index_usage_stats.user_updates As No_of_updates,
    dm_db_index_usage_stats.user_lookups As No_of_lookups,
     '' AS Comment
     --stuff((select DISTINCT ','+columns.name from sys.columns a where a.object_id = dm_db_index_usage_stats.object_id for XML PATH('')), 1 ,1, '') cOLUMN_NAMES

FROM
    sys.dm_db_index_usage_stats
    INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID
    INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id
    INNER JOIN sys.index_columns  ON index_columns.object_id = indexes.object_id and index_columns.index_id = indexes.index_id
    INNER JOIN sys.columns  ON columns.object_id = index_columns.object_id and columns.column_id = index_columns.column_id
    AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID

-- WHERE         objects.name  IN  ('dbo.barang') and index_columns.is_included_column =1
--AND
    --dm_db_index_usage_stats.user_lookups = 0
    --AND
   -- dm_db_index_usage_stats.user_seeks = 0
   -- AND
   -- dm_db_index_usage_stats.user_scans = 0
GROUP BY
 objects.name ,
 indexes.name ,
indexes.type_DESC,
dm_db_index_usage_stats.user_seeks ,
dm_db_index_usage_stats.user_scans ,
dm_db_index_usage_stats.user_updateS,
dm_db_index_usage_stats.user_lookups
ORDER BY
  indexes.name DESC


  --select * from sys.index_columns
  --select * from sys.indexes
  --select * from sys.columns
 

E. Need lab cause stil error my lab 

SELECT @@servername as ServerName, db.[name] AS [DatabaseName]
    ,id.[object_id] AS [ObjectID]
    ,OBJECT_NAME(id.[object_id], db.[database_id]) AS [ObjectName]
    ,id.[statement] AS [FullyQualifiedObjectName]
    ,id.[equality_columns] AS [EqualityColumns]
    ,id.[inequality_columns] AS [InEqualityColumns]
    ,id.[included_columns] AS [IncludedColumns]
    ,gs.[unique_compiles] AS [UniqueCompiles]
    ,gs.[user_seeks] AS [UserSeeks]
    ,gs.[user_scans] AS [UserScans]
    ,gs.[last_user_seek] AS [LastUserSeekTime]
    ,gs.[last_user_scan] AS [LastUserScanTime]
    ,gs.[avg_total_user_cost] AS [AvgTotalUserCost]  -- Average cost of the user queries that could be reduced by the index in the group.
    ,gs.[avg_user_impact] AS [AvgUserImpact]  -- The value means that the query cost would on average drop by this percentage if this missing index group was implemented.
    ,gs.[system_seeks] AS [SystemSeeks]
    ,gs.[system_scans] AS [SystemScans]
    ,gs.[last_system_seek] AS [LastSystemSeekTime]
    ,gs.[last_system_scan] AS [LastSystemScanTime]
    ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]
    ,gs.[avg_system_impact] AS [AvgSystemImpact]  -- Average percentage benefit that system queries could experience if this missing index group was implemented.
    ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
    ,'CREATE INDEX [IX_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN '_'
        ELSE ''
        END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN ','
        ELSE ''
        END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]
    ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK) ON gs.[group_handle] = ig.[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK) ON ig.[index_handle] = id.[index_handle]
INNER JOIN [sys].[databases] db WITH (NOLOCK) ON db.[database_id] = id.[database_id]
WHERE  db.[database_id] = DB_ID()
--AND OBJECT_NAME(id.[object_id], db.[database_id]) = 'YourTableName'
ORDER BY ObjectName, [IndexAdvantage] DESC
OPTION (RECOMPILE);