|
I've seen some other discussions that were focused on some other indexes. This is NOT a general question, I'm concerned about these 2 specific indexes that are created by Fluent API here outbox.HasIndex(p => p.ExpirationTime);
outbox.HasIndex(p => p.EnqueueTime);I've observed that SQL Server Database Engine have not been using them in weeks even for once. SELECT
OBJECT_NAME(s.[object_id]) AS [TableName],
i.name AS [IndexName],
i.index_id,
s.user_seeks,
s.user_scans,
s.user_lookups,
s.user_updates,
p.rows AS [TableRows]
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
INNER JOIN sys.partitions AS p ON p.[object_id] = s.[object_id] AND p.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
AND s.database_id = DB_ID()
AND s.user_seeks = 0
AND s.user_scans = 0
AND s.user_lookups = 0
AND i.is_primary_key = 0 -- Don't list Primary Keys
AND i.is_unique = 0 -- Don't list Unique Constraints
ORDER BY s.user_updates DESC;These are not Primary Keys or Unique Constraints, so I was wondering are these really helpful indexes? |
Answered by
phatboyg
Jul 2, 2026
Replies: 1 comment
|
You're right, these seem unnecessary and should probably be removed. |
0 replies
Answer selected by
yasmoradi
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
You're right, these seem unnecessary and should probably be removed.