/*
Index Efficiency & Maintenance Advisor
Identifies:
1. Duplicate indexes (same keys/includes)
2. Underutilized indexes (low reads, high writes)
3. Highly fragmented indexes
4. Indexes with high maintenance cost relative to value
*/
DECLARE
@MinPageCount INT = 1000, -- Minimum data pages (8KB/page) to consider
@FragThreshold FLOAT = 30.0, -- Min fragmentation % to flag
@UsageDaysThreshold INT = 30, -- Min server uptime days for usage stats
@ReadsPerMBThreshold INT = 10, -- Reads/MB threshold (usage efficiency)
@UpdateReadRatioThreshold INT = 10; -- Writes vs Reads ratio threshold

WITH IndexData AS (
SELECT
DB_NAME() AS DatabaseName,
SCHEMA_NAME(t.schema_id) AS SchemaName,
t.name AS TableName,
i.name AS IndexName,
i.type_desc AS IndexType,
i.is_primary_key,
i.is_unique,
i.is_disabled,
COALESCE(s.user_seeks, 0) AS user_seeks,
COALESCE(s.user_scans, 0) AS user_scans,
COALESCE(s.user_lookups, 0) AS user_lookups,
COALESCE(s.user_updates, 0) AS user_updates,
s.last_user_seek,
s.last_user_scan,
s.last_user_lookup,
ps.page_count,
ps.avg_fragmentation_in_percent,
(ps.page_count * 8.0 / 1024) AS SizeMB,
COALESCE(STUFF((
SELECT ', ' + COL_NAME(ic.object_id, ic.column_id)
FROM sys.index_columns ic
WHERE
ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.is_included_column = 0
ORDER BY ic.key_ordinal
FOR XML PATH('')
),1,2,''), '') AS KeyColumns,
COALESCE(STUFF((
SELECT ', ' + COL_NAME(ic.object_id, ic.column_id)
FROM sys.index_columns ic
WHERE
ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.is_included_column = 1
ORDER BY ic.index_column_id
FOR XML PATH('')
),1,2,''), '') AS IncludedColumns
FROM
sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats s ON
s.object_id = i.object_id
AND s.index_id = i.index_id
AND s.database_id = DB_ID()
CROSS APPLY sys.dm_db_index_physical_stats(
DB_ID(),
i.object_id,
i.index_id,
NULL,
'LIMITED'
) ps
WHERE
i.type > 0 -- Exclude heaps
AND t.is_ms_shipped = 0
AND ps.alloc_unit_type_desc = 'IN_ROW_DATA'
AND ps.page_count > @MinPageCount
),
DuplicateIndexes AS (
SELECT
DatabaseName,
SchemaName,
TableName,
KeyColumns,
IncludedColumns,
COUNT(*) AS DupCount
FROM IndexData
GROUP BY
DatabaseName,
SchemaName,
TableName,
KeyColumns,
IncludedColumns
HAVING COUNT(*) > 1
),
ServerUptime AS (
SELECT
DATEDIFF(DAY, sqlserver_start_time, GETDATE()) AS UptimeDays
FROM sys.dm_os_sys_info
)
SELECT
id.DatabaseName,
id.SchemaName,
id.TableName,
id.IndexName,
id.IndexType,
id.is_primary_key,
id.is_unique,
id.SizeMB,
id.avg_fragmentation_in_percent AS FragmentationPct,
id.user_seeks,
id.user_scans,
id.user_lookups,
id.user_updates,
(id.user_seeks + id.user_scans + id.user_lookups) AS TotalReads,
id.KeyColumns,
id.IncludedColumns,
COALESCE(di.DupCount, 1) AS DupCount,
CASE
WHEN di.DupCount > 1 THEN 'DUPLICATE'
WHEN su.UptimeDays > @UsageDaysThreshold
AND (id.user_seeks + id.user_scans + id.user_lookups) = 0
AND id.user_updates > 0 THEN 'UNUSED (COSTLY WRITES)'
WHEN su.UptimeDays > @UsageDaysThreshold
AND (id.user_seeks + id.user_scans + id.user_lookups) > 0
AND (id.user_seeks + id.user_scans + id.user_lookups) < (id.SizeMB * @ReadsPerMBThreshold) AND id.user_updates > (id.user_seeks + id.user_scans + id.user_lookups) * @UpdateReadRatioThreshold
THEN 'INEFFICIENT (HIGH WRITE/READ RATIO)'
WHEN id.avg_fragmentation_in_percent > @FragThreshold THEN 'HIGH FRAGMENTATION'
ELSE 'ANALYZE MANUALLY'
END AS IssueType,
CASE
WHEN di.DupCount > 1 THEN 'Consider dropping duplicate indexes'
WHEN su.UptimeDays <= @UsageDaysThreshold THEN 'Insufficient usage data - server restarted ' + CAST(su.UptimeDays AS VARCHAR) + ' days ago' WHEN (id.user_seeks + id.user_scans + id.user_lookups) = 0 THEN 'Potential candidate for removal. Verify with business logic first.' ELSE 'Evaluate index maintenance/usage patterns' END AS Recommendation, id.last_user_seek, id.last_user_scan FROM IndexData id LEFT JOIN DuplicateIndexes di ON id.DatabaseName = di.DatabaseName AND id.SchemaName = di.SchemaName AND id.TableName = di.TableName AND id.KeyColumns = di.KeyColumns AND id.IncludedColumns = di.IncludedColumns CROSS JOIN ServerUptime su WHERE di.DupCount > 1
OR (
su.UptimeDays > @UsageDaysThreshold
AND (
(id.user_seeks + id.user_scans + id.user_lookups) = 0
OR id.avg_fragmentation_in_percent > @FragThreshold
OR (id.user_seeks + id.user_scans + id.user_lookups) < (id.SizeMB * @ReadsPerMBThreshold) ) ) ORDER BY id.SizeMB DESC, di.DupCount DESC;