index-maintenance

EXECUTE dbo.IndexOptimize
@Databases = 'Everest_PreProd',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@UpdateStatistics = 'ALL',
@FillFactor = 80,
@Execute  = 'Y',
@LogToTable = 'Y'

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

SELECT
   schema_name(objects.schema_id) schema_name,
 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